Biopharmaceutical Company Achieves Precise and Efficient Data Management With Derived Databases and Snowflake Automation, Implemented by AntStack

With AntStack they wanted to achieve the following objectives:

• Introduce derived databases as a structured transition from domain databases, enhancing both specificity and organizational efficiency.
• Centralise metadata management for improved tracking.
• implementation of Snowflake procedures to automate the creation of views based on metadata.

Solution Approach:

1. Introduction of Derived Database: Derived databases are implemented as a structured transition from domain databases. Instead of physical tables, these derived databases contain views, which are virtual representations of data based on specific use cases or requirements. This approach allows for greater specificity and flexibility in data organization and retrieval.

   
   

2. Central Metadata Database : This database is dedicated to storing metadata related to the transition from domain databases to derived databases. It acts as a central point for tracking and managing metadata, providing a clear record of database transitions and associated details.

   • Metadata table structure:

   ID NUMBER(38,0) primary key,
   SOURCE_DB VARCHAR(300),
   SOURCE_SCHEMA VARCHAR(300),
   SOURCE_TABLE_NAME VARCHAR(300),
   TARGET_DB VARCHAR(300),
   TARGET_SCHEMA VARCHAR(300),
   INSERTED_DATE DATE DEFAULT CURRENT_DATE()

   • Users populate metadata table with transition details from domain to derived, specifying source and target details.
     

3. Snowflake Procedure: Developed a Snowflake procedure to read metadata from the metadata table and create views.

   • Procedure variations ( 3 variations):
     • Takes “target_db” as input, filters entries for the specific target database and creates those views.
     • Takes “IDs” (list of IDs in comma-separated string format[e.g. - ‘1,2,34,’]) as input, targets specific rows in the metadata table and creates those views.
     • Reads the entire table and creates views for all entries.