1

The schema of one of the databases that is connected to our business Intelligent data warehouse is constantly evolving (CICD), consequently Each time a change occurred, we need to manually update the ETL pipeline to map the new changes. An example of the changes in the source database could something like dropping a column, table, or adding a new table. Etc..

We currently use Matillion for EL and snowflake database.

I was wondering whether there were better ways to detect these changes and automatically update the snowflake schema.

ryan
  • 101
  • 9
  • You can write a Snowflake procedure which can read the Snowflake schema and check if the table/rows are different between the Source and the target Snowflake, if there is a change then you can generate a script and update the Snowflake tables. BTW what is your source DB ? – Himanshu Kandpal Jul 19 '22 at 13:13
  • Since you mention Matillion, note that they do have support for schema drift in their data loader and change detection for tables. You might want to check your license to see if you have access to those products. https://www.matillion.com/resources/blog/matillion-data-loader-v1-27-more-support-for-schema-drift and https://documentation.matillion.com/docs/2329042 – Jim Demitriou Jul 20 '22 at 05:03
  • @HimanshuKandpal source deb IS Mysql, we have built a similar procedure, but as the database scales it is taking longer to run the script and therefore increase the time lag between Production database and BI database, which something we need to avoid – ryan Jul 21 '22 at 10:27
  • @JimDemitriou this interesting, thank you – ryan Jul 21 '22 at 10:27
  • @ryan, can you please let us know how many schemas and tables you have for which you are running the process ? Do you see the scaling problem happening when you have more structure changes or is it because the load operation is taking longer ? – Himanshu Kandpal Jul 21 '22 at 16:03
  • @HimanshuKandpal sorry for the delay, we have 2 schemas with 140 tables. the scaling problem seems to be impacted by both changes and loading – ryan Aug 02 '22 at 14:21

0 Answers0