I am currently building a Type 2 dimension prototype for our datamart on Snowflake and being from a SSIS background, I am used to having a SQL query as the source of the data flow. I know Matillion provides many transforms to accomplish the same goal as a SQL query (join, aggregate, ...) but it seems so much easier to simply hand-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
@callaire-c - as you rightly mentioned, hand-coding a SQL query would be easier if you are migrating from an existing platform.
But by leveraging the Matillion transformation components you are future proofing your data pipeline
- by making it easier to modify / make changes
- improving explainability for new and junior data engineers
- simplify troubleshooting and data lineage questions.
- iteratively build your data pipeline by running each step and checking results.
I had similar apprehensions but once you start using the low-code methodology of building transformations, you would not want to hand-code your SQL queries.
Hope this helps.