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

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

17 views0 comments

Recent Posts

See All