How to retrieve automatically the SQL from within a Matillion Orchestration and Transformation job?

Matillion ETL
1

Hi,

I would like to generate in a file on AWS S3 the SQL of my Matillion job or retrieve it with another method if possible, to be able to reuse it later. It is manually possible to retrieve the SQL code on Job Matillion, as on the attached file. I would like to be able to automate this process.

2

I think this will never be possible as it interferes with Matillion's business model. They charge you a licence fee for running the environment where you can execute your job. If you now choose to run your jobs with a different execution engine they will loose this money. That's why there never will be an "official" feature for this.

3

Hi @Michael​ , thanks for your feedback, actually that is not the point. Maybe i didn't explain well what i wanted to do. I am just trying to find a way to automatelly save the job in a sql file as shown on the pic, to not copy and paste each time for having a backup. For the moment you can only export a job in a JSON file, not in SQL even if the SQL part is available. Like you said Matillion charge us for using the technology, so why can't you save your work you paid for as you want.

4

I totally understand your point and I think you reply just underlines what I already said. Of course you can export your work to a local file. But for running the JSON again, you have to run a Matillion instance. Though, if you have the SQL, you can execute that script in your target Database (Snowflake, Redshift, ....) WITHOUT running a Matillion instance (and thus not paying them). So if you plan to execute your jobs with Matillion anyways, there is no need for an additional SQL backup.

5

Heya, you could always have a Create View-output in your transformations and pick the ddl from there. And if you want to make a full process of it, change your design to have 'insert views', jobs that create these views and separate jobs that insert using these views into tables etc. Hence the SQL metadata is stored in the view and you can use your database to copy/store/export the view ddl how much you want.

 

But otherwise it really interferes with the Matillion's business model, as Michael mentioned already.

6

Ok i got it. Thanks for your comments