Medallion.Gold

by

Overview:

The Gold Layer serves as the delivery centre of the data warehouse, providing secure and efficient access to the finalised data. This layer is responsible for delivering data to end-users and applications, ensuring that it is ready for analysis, reporting, and other business intelligence activities.

Key Characteristics:

  1. Delivery Center:
    • Global Data Warehouse Database: The Gold Layer operates within the Global Data Warehouse database, a centralised repository where all finalised data is stored and accessed.
    • Pre-filled Snapshots: The database is pre-filled with the latest versions of snapshot tables created in the Silver Layer, ensuring that the most up-to-date data is available for queries.
  2. Data Access and Security:
    • SQL Stored Procedure (get.myview): The primary component for data access in the Gold Layer is the get.myview SQL stored procedure. This procedure is responsible for building and delivering the requested view based on user queries.
    • Security and Token Validation: Before delivering the data, get.myview assesses security permissions and performs token validation to ensure that only authorised users can access the requested data. This layer of security ensures compliance with data governance policies and protects sensitive information.
  3. Dynamic View Construction:
    • Building Views: get.myview dynamically constructs the requested view by pulling the relevant data from the pre-filled snapshots. This process allows for flexible and efficient data delivery tailored to specific user needs.
    • Modular Data Blocks (Databricks): The procedure also integrates with modular data blocks, referred to as Databricks. These blocks are pre-processed, reusable data objects that encapsulate specific data sets or calculations. By leveraging these modular blocks, get.myview can efficiently assemble comprehensive views with modularity and reusability.
  4. Historical Data Access:
    • Stored Procedure (get.myhistory): This stored procedure allows users to access a time-based series of snapshots, providing historical views of the data. This enables trend analysis and historical reporting by leveraging the weekly snapshots maintained in the Silver Layer.

Workflow Summary:

  1. Data Prefilling: The Gold Layer database is pre-filled with the latest snapshot tables from the Silver Layer.
  2. Request Handling: When a data request is made, the get.myview stored procedure is triggered.
  3. Security Validation: The procedure performs security checks and token validation to ensure authorized access.
  4. View Construction: get.myview constructs the requested view from the snapshot tables and integrates with Databricks for any additional processing.
  5. Historical Data Access: The get.myhistory procedure provides access to historical snapshots for time-based analysis.
  6. Data Delivery: The finalized view is delivered to the user, ready for analysis and reporting.

Importance:

  • Secure Data Access: The Gold Layer ensures that data access is secure and compliant with organizational policies, protecting sensitive information.
  • Up-to-date Data: By using pre-filled snapshots, the Gold Layer guarantees that users have access to the most current and accurate data.
  • Flexible and Efficient Delivery: The dynamic construction of views and integration with Databricks provide a flexible and efficient data delivery mechanism, tailored to specific user requirements.
  • Historical Analysis: The ability to access historical snapshots through get.myhistory allows for robust trend analysis and historical reporting.

RAID

Risks:

  • Data Security: Unauthorized access to sensitive data views can lead to data breaches.
  • Data Accuracy: Inaccurate data in snapshots can lead to incorrect insights and decisions.
  • Scalability: Ensuring the system can handle increasing data volumes and user queries without performance degradation.

Issues:

  • Stored Procedure Errors: Errors in stored procedures like get.myview can lead to incorrect data being retrieved.
  • Token Validation: Ensuring robust token validation mechanisms to prevent unauthorized access.
  • Integration Challenges: Integrating modular data blocks (databricks) correctly to build the requested views can be complex.

Dependencies:

  • Stored Procedures: Reliable and well-optimized stored procedures for data retrieval.
  • Authentication and Authorization: Strong authentication and authorization mechanisms.
  • Business Intelligence Tools: Integration with BI tools for data visualization and reporting.

Assumptions:

  • Stored Procedure Maintenance: Assumption that stored procedures are regularly maintained and updated.
  • Security Measures: Assumption that security measures effectively protect data access.
  • Timely Data Updates: Assumption that snapshots in the gold layer are updated regularly and accurately.

Opportunities:

  • Customizable Views: Providing customizable views for different user requirements and analyses.
  • Data Aggregation: Implementing data aggregation techniques to provide summarized insights.
  • Integration with BI Tools: Integrating with business intelligence tools for advanced data visualization and reporting.

Mitigations:

  • Secure Access: Ensuring secure access to data views through strong authentication and authorization mechanisms.
  • Stored Procedure Testing: Regularly testing and validating stored procedures to prevent errors.
  • Efficient Query Design: Designing efficient queries to minimize performance issues and ensure fast data retrieval.

Leave a Comment