Many companies have legacy databases that are key to their day to day transaction processing. How can we consolidate data from various relational database management systems and/or sources into a single pane of glass for reporting?

Metadata driven data pipelines can be used to bring in the data into a raw schema. For small to medium tables (files), a full load pattern can be used. For larger tables (files) an incremental load pattern reduces the volume of the data that is transferred.

Segregation of the medallion data quality zones is key to security and management. The bronze layer uses the raw schema; the silver layer composes views over two staging schemas (active and inactive).; and the gold layer is typical flattening of the silver views for reporting.

The loading of data into the inactive schema allows the developer to rebuild the silver tables without impacting the production reports. Once all tables in the inactive schema are refreshed, a stored procedure can repoint the views so that the new data will suddenly appear. Of course, a control table will be needed to keep track of which schema is currently active.

The Fabric Warehouse has been re-written using the one lake delta file format. While mirroring can be used for modern data sources, older databases that are considered technical debt are not supported. This design pattern will support any data source that is supported by the copy activity.