Shaping Structured Data: Leveraging Derived Databases and Snowflake Procedures for Seamless Data Management

Healthcare

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

Shaping Structured Data: Leveraging Derived Databases and Snowflake Procedures for Seamless Data Management Artwork

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:

  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. flowdiagram.jpg


  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.

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.
 Enhanced Data Management and Utilization

Centralized Metadata Control and Transparency

  • Metadata table centralizes metadata, providing a clear record of transitions.
  • Improved visibility and accountability throughout the transition process.
Centralized Metadata Control and Transparency

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.
Streamlined View Creation and User Flexibility in Snowflake

Targeted Access Management

  • Tailored access to specific use-case users with role-based access control.
Targeted Access Management

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.

Cookies Icon

These cookies are used to collect information about how you interact with this website and allow us to remember you. We use this information in order to improve and customize your browsing experience and for analytics and metrics about our visitors on this website.

If you decline, your information won’t be tracked when you visit this website. A single cookie will be used in your browser to remember your preference not to be tracked.

Build With Us