Using SQL Component as a Source in Transformation

Matillion ETL
1

I am currently building a Type 2 dimension prototype for our Snowflake datamart and being from a SSIS background, I am used to using a SQL query as the source of the transformation. I know that Matillion provides many transforms to accomplish the same goal as a SQL query (join, aggregate, ...) but it seems so much easier to simply write the equivalent query and use the SQL component as the source in a transformation job.

 

I would like to have your thoughts on this, is there a downside to using a SQL query instead of the Matillion transforms ? What is your experience on this ?

 

Chris

2

It's not something wrong to directly use SQL Query as a Source to your transformation. However, you are limiting the benefits of using a GUI based transformation tool, by writing the SQL directly. Also, you may lose the data lineage if you are leveraging the tool's functionality​