Database Schema Metadata Management
For details of your database schema metadata, the ANSI-standard set of information_schema views provides helpful schema details as a read-only, “point in time” reference. However, what if you would like to (a) have a historical view of this data, and/or (b) would like to supplement this metadata with your own data extensions? If this problem strikes a chord, please read on…
Anyone who has previously struggled with database/application change management can attest to the importance of being able to track database schema changes. But the need to extend schema metadata might be less clear. A while ago I was looking for a way to auto-generate SQL MERGE statements, specifically for Data Warehouse population of “Kimball style” dimension tables and attributes. To enable this, I needed to first know the dimension table’s natural (business) key, and whether each column should be treated as a ‘Type-1’, or ‘Type-2’ (historical) attribute.
This requirement eventually evolved into a mini-application, called ‘snap_mart’, consisting of a back-end SQL Server database, and a front-end user interface (download here). This solution creates a historical collection of information_schema views, placed in a standard dimensional model, providing metadata extensions typically needed for a data warehouse.
This Metadata solution consists of:
a database DDL script (bimlsnap_v2)
a metadata manager front-end application
a ‘snap_mart Refresh’ SSIS project (used to extract database metadata from either SQL Server or the cloud database platform: snowflake.)
To find out more, please ping us at info@bitracks.com
Automating MERGE statements
As mentioned earlier, one driving factor for this Database Metadata solution was the need to generate numerous MERGE statements to support population of dimension tables in a Data Warehouse. For this magic to happen, we need to add a code generator to leverage our extended database metadata. Although not currently available as a stand alone script, we do provide this capability in BimlSnap, our game changing Biml centric ELT/ETL product. Using BimlSnap, MERGE statements are rendered as a parameter driven SQL Expression, which can then be generated as an SSIS project using BimlExpress, the free Visual Studio add-in from Varigence. For more information on this accelerated ELT approach, please visit www.bimpsnap.com