This is simple generic pipeline to materialize logical dimension and fact tables created on Synapse serverless layer data lake and create view on synapse over materialized data.
Below is the graphical representation of how we can transform the data from raw data to a materialized data model:
- Create container in the storage account where materialized data will be stored.
- Run DataTransform_TargetDB.sql, change appropriate values and run script on Synapse Serverless to create target database, external data source and stored procedure to create views for materialized data.
- Run DataTransform_SourceDB.sql script on Source Synapse Serverless database to create stored procedure to collect source metadata information.
- Deploy ADF template by providing required parameters
- Run DimTableCopy and FactTableCopy pipelines