![Shaping Structured Data: Leveraging Derived Databases and Snowflake Procedures for Seamless Data Management Artwork](https://cdn.antstack.com/large_Sage_Data_Engineering_Case_Study_Derived_Databases_Implementation_f16aeda14c.png)
Shaping Structured Data: Leveraging Derived Databases and Snowflake Procedures for Seamless Data Management
HealthcareBiopharmaceutical Company Achieves Precise and Efficient Data Management With Derived Databases and Snowflake Automation, Implemented by AntStack
About Shaping Structured Data: Leveraging Derived Databases and Snowflake Procedures for Seamless Data Management
The client, a dedicated biopharmaceutical company, is on a mission to revolutionize the landscape of brain health. Their commitment to developing groundbreaking therapies holds the promise of transforming the lives of individuals grappling with challenging brain disorders. Through innovative approaches, the client is exploring new avenues to enhance brain health. Their comprehensive franchise programs, focusing on depression, neurology, and neuropsychiatry, aspire to reshape the understanding and treatment of various brain disorders.
The Challenge
- Unstructured or cluttered databases in Snowflake: Managing unstructured or cluttered databases in Snowflake posed significant challenges for data organization and retrieval.
- Lack of specificity in existing databases for various use cases and requirements: Existing databases lacked specificity for various use cases and requirements, making it difficult to extract relevant information efficiently.
- Access control and user permissions: Managing user access and permissions within the databases presented challenges in ensuring data security and confidentiality.
Our Goals
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:
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.
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.
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.
- Procedure variations ( 3 variations):
Our Impact
Enhanced Data Management and Utilization
- Transition from unstructured databases to structured derived databases.
- Specificity achieved through use-case-focused derived databases.
- Improved data accessibility, usability, and maintenance.
Centralized Metadata Control and Transparency
- Metadata table centralizes metadata, providing a clear record of transitions.
- Improved visibility and accountability throughout the transition process.
Streamlined View Creation and User Flexibility in Snowflake
- Snowflake procedure automates the creation of views based on metadata.
- Users provided with flexibility through multiple input variations.
Targeted Access Management
- Tailored access to specific use-case users with role-based access control.
Results that matter
The adoption of derived databases, central metadata management, and Snowflake procedures has streamlined data organization and automation. Structured views tailored to specific use cases have improved efficiency and flexibility, while user-specific access has enhanced data privacy. The central metadata database enables transparent tracking, and Snowflake procedures automate view creation based on metadata, providing seamless execution. This approach offers the client a structured and tailored database management solution.