Marc Bushong doesn’t want to copy and paste:
Developing ETLs/ELTs can be a complex process when you add in business logic, large amounts of data, and the high volume of table data that needs to be moved from source to target. This is especially true in analytical workloads involving Azure SQL when there is a need to either fully reload a table or incrementally update a table. In order to handle the logic to incrementally update a table or fully reload a table in Azure SQL (or Azure Synapse), we will need to create the following assets:
- Metadata table in Azure SQL
- This will contain the configurations needed to load each table end to end
- Metadata driven pipelines
- Parent and child pipeline templates that will orchestrate and execute the ETL/ELT end to end
- Custom SQL logic for incremental processing
- Dynamic SQL to perform the delete and insert based on criteria the user provides in the metadata table
Read on for the demonstration, which reads from one Azure SQL DB into another.