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?