Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

How to start with a custom SELECT statement?

I'm new to Matillion. I've started with an Orchestration job that stages some data from my source.

Now, say I want to create a Transformation job, and start it with a custom query of my staging tables -- including some joins and data transformations.

(I understand that maybe I can get the same effect by using Matillion's various Transformation Components. But, let's say I just want to write the SQL myself. Or, say I think I can write SQL that is more efficient, or does something that can't be accomplished using Matillion's Transformation Components.)

What is the best way of doing this?

My idea so far is to use a "SQL Script" Component, in a preceding Orchestration job, to define a view using my custom SELECT statement. Then, in my Transformation job, start it with a SELECT * FROM that view. Would this work?

Are there any other options?

2 Community Answers

Matillion Agent  

Laura Malins —

Hi Kevin

That would certainly work.

Another option is that we have a SQL component in the Transform folder in the Transformation Job which you can use to directly write your SQL to do the transform, you can then feed the results into another Transformation component if required. This method would mean you don’t need to manually create a view.

Thanks
Laura


Kevin Havice —

Yes, just what I needed! I see in the documentation of the "SQL" Component how "The SQL Component can be used as a source (e.g. the first component in the Transformation job workflow)."

And also, it says how to reference the data inputs, when it is not the first component in the workflow.

Brilliant, thanks Laura!

Post Your Community Answer

To add an answer please login