The Metadata Driven Data Warehouse
summary
Shows how to turn SQL Server metadata and DMVs into executable pipelines: uses STRING_AGG and information_schema to auto-generate SELECTs that produce column lists, counts, max lengths and data types for every table; uses index naming (BK prefix) to discover business keys and build dynamic INSERT/UPDATE/DELETE or merge logic; runs orchestration in Azure Data Factory with parameterized linked services and create-table option; handles incremental loads via a last-seen log; and auto-creates Power BI measures with Tabular Editor scripting.
AI-written summary · about
chapters (10)
- 0:00Introduction
- 3:14Defining metadata
- 5:48Locating metadata in SQL
- 8:43Dynamic SQL and string aggregation
- 17:43Azure Data Factory pipelines
- 23:11Building transformation layers
- 31:50Metadata in Power BI
- 35:40Q&A session
- 40:30Metadata strategy summary
- 42:52Simpson Associates framework
related talks
31mMetadata-based Dynamic ETLs
25mFrom Data Warehouse To Self-service BI: Our Journey To Data Mesh
12mModern Data Warehouse for Small and Medium Business
55mBuilding Modern Data Warehouse with Azure Synapse Analytics!
32mHow to Build a Data Warehouse?
50mFrom SSIS to Synapse to Fabric - A Data Architects Case Study
01h 00mBuilding an Effective Data Warehouse Architecture
01h 01mModern Data Warehousing Strategy