ExamFinch.com My Study

Study guide

11.2 Medallion Architecture for Analytics

Practice this exam

Databricks Certified Data Analyst Associate Exam

Track your progress as you read

Finished sections get a check mark.

1 Platform Overview & Unity Catalog Foundations

1.1 Databricks Architecture & Compute Concepts 1.2 Databricks Marketplace & Data Sharing Basics 1.3 Unity Catalog Namespace & Object Hierarchy 1.4 Governed Asset Discovery & System Tables

2 Managing & Cleaning Governed Data

2.1 Dataset Discovery & Asset Tagging 2.2 Lineage Inspection & Data Traceability 2.3 SQL Data Cleaning & Transformation Techniques

3 Data Ingestion & Import Patterns

3.1 Workspace Uploads & Cloud File Ingestion 3.2 Incremental File Ingestion with Auto Loader 3.3 Ingesting Data via Delta Sharing & External Connections

4 Databricks SQL Warehouses & Core Querying

4.1 Databricks SQL Interface & SQL Assistant 4.2 SQL Warehouses Sizing & Management 4.3 Table Creation & Data Modification in Databricks SQL

5 Advanced SQL Authoring & Time Travel

5.1 Advanced Aggregations, Window Functions & Analytics 5.2 Complex Joins, CTEs & Set Operations 5.3 Views, Materialized Views & Delta Time Travel

6 Query Performance Analysis & Photon Engine

6.1 Photon Engine Architecture & Acceleration 6.2 Query History & Execution Profile Inspection 6.3 Delta Lake Audit History & Commit Logs

7 Liquid Clustering, Caching & Performance Troubleshooting

7.1 Caching Hierarchy in Databricks SQL 7.2 Data Layout Optimization: Liquid Clustering vs Partitioning & Z-Ordering 7.3 Troubleshooting Slow & Failing Queries

8 AI/BI Dashboards & Visualization Design

8.1 AI/BI Dashboards Overview & Canvas Layout 8.2 Chart Types & Visualization Selection 8.3 Notebook & SQL Editor Visualizations

9 Dashboard Interactivity, Publishing & Alerts

9.1 Dashboard Parameters & Interactive Filtering 9.2 Publishing, Sharing & Access Control 9.3 Scheduled Refresh, Email Subscriptions & Alerts

10 Developing & Maintaining AI/BI Genie Spaces

10.1 AI/BI Genie Architecture & Space Setup 10.2 Curation via Instructions, Descriptions & Sample Questions 10.3 Trusted Assets & Parameterized Functions 10.4 Monitoring, Benchmarks, Feedback & Maintenance

11 Data Modeling & Medallion Architecture

11.1 Dimensional Modeling Fundamentals 11.2 Medallion Architecture for Analytics 11.3 Constraint Enforcement & Relationships in Unity Catalog

12 Securing Data & Unity Catalog Governance

12.1 Unity Catalog Privilege Model & Grants 12.2 Ownership, Inheritance & Access Control Patterns 12.3 Fine-Grained Security: Row Filters & Column Masks

11.2 Medallion Architecture for Analytics

Key Takeaways Explain This Topic AI

The Medallion Architecture structures data processing into three distinct logical layers: Bronze (raw ingestion), Silver (cleaned/curated), and Gold (business-level aggregates).

Bronze layers retain full historical integrity by appending raw payload data alongside ingestion metadata like _ingest_timestamp and _source_file without destructive transformations.

Silver tables enforce schema validation, deduplication, and lookup joins, transforming unstructured or messy JSON payloads into clean, conformed tabular Delta tables.

Gold tables apply star schema dimensional modeling or pre-aggregated business metrics optimized for consumption by Databricks AI/BI Dashboards and Genie spaces, reducing query latency by up to 80%.

Last updated: July 2026

Introduction to Medallion Architecture

The Medallion Architecture is a design pattern developed by Databricks to logically organize data in a lakehouse as it flows through distinct refinement stages. Instead of maintaining monolithic data pipelines where raw data is transformed into reporting tables in a single complex process, the medallion architecture breaks data processing into three progressive layers: Bronze (raw ingestion), Silver (cleaned and curated), and Gold (business-ready aggregations).

By establishing clear boundaries between ingestion, validation, and analytical presentation, the medallion architecture guarantees data quality, enables auditability and replayability, isolates upstream breaking changes, and drastically improves query performance for end-user analytics in Databricks SQL and AI/BI Dashboards.

Bronze Layer: Raw Data Ingestion & Auditability

The Bronze Layer serves as the initial landing zone and immutable record of truth for all incoming data assets. Data enters the Bronze layer from diverse source systems, including operational databases (via Change Data Capture, or CDC), streaming event buses (such as Apache Kafka or Event Hubs), web cloud storage landing spots (S3, ADLS Gen2, GCS via Auto Loader), or external API payloads.

Key characteristics and design principles of the Bronze layer include:

Raw Payload Preservation : Data is stored in its raw format (often as unparsed JSON strings, raw CSV text, or binary payloads) without applying destructive structural transformations or business logic filtering.

Append-Only Ingestion : Tables are strictly append-only to maintain complete historical fidelity. If source records are deleted or updated, the Bronze layer records every state change over time.

Audit Metadata Enrichment : Ingested records are enriched with audit metadata columns, such as _ingest_timestamp (when the record arrived in Databricks), _source_file (the path of the source file), and _job_id . -- Bronze Layer Table Definition in Databricks SQL

  • CREATE TABLE main.bronze.raw_store_transactions (
  • raw_payload STRING,
  • _ingest_timestamp TIMESTAMP,
  • _source_file STRING,
  • _job_id STRING
  • ) USING DELTA;

Preserving raw data in Bronze ensures that if downstream business logic or parsing rules change in the future, data engineers can replay historical transformations from Bronze without re-querying external source systems.

Silver Layer: Data Cleansing, Conformance & Quality Enforcement

The Silver Layer transforms raw Bronze data into clean, validated, and normalized tables suitable for enterprise-wide ad-hoc analysis. The primary objective of the Silver layer is to establish an authoritative "single source of truth" for core business entities (such as customers, orders, products, and sensors).

Key transformations executed in the Bronze-to-Silver transition include:

Schema Parsing and Type Enforcement : Extracting fields from Bronze raw JSON payloads and casting them into typed tabular columns (e.g., converting text strings to INT , DECIMAL(10,2) , or TIMESTAMP ).

Data Cleansing and Standardization : Trimming whitespace, standardizing country/state codes, handling NULL values, and converting timezone offsets to UTC.

Deduplication and CDC Upserts : Merging change data streams ( MERGE INTO ) to eliminate duplicate event emissions and maintain current state snapshots.

Data Quality Validation : Applying Databricks Delta Expectations or SQL constraints to filter out corrupt or invalid rows (e.g., rejecting orders with negative purchase amounts). -- Silver Layer Upsert & Cleansing via MERGE INTO

  • MERGE INTO main.silver.orders AS target
  • USING (
  • SELECT
  • CAST(get_json_object(raw_payload, '$.order_id') AS INT) AS order_id,
  • CAST(get_json_object(raw_payload, '$.customer_id') AS INT) AS customer_id,
  • CAST(get_json_object(raw_payload, '$.amount') AS DECIMAL(10,2)) AS amount,
  • CAST(get_json_object(raw_payload, '$.transaction_time') AS TIMESTAMP) AS transaction_time,
  • _ingest_timestamp
  • FROM main.bronze.raw_store_transactions
  • WHERE get_json_object(raw_payload, '$.order_id') IS NOT NULL
  • ) AS source
  • ON target.order_id = source.order_id
  • WHEN MATCHED AND source._ingest_timestamp > target._ingest_timestamp THEN
  • UPDATE SET
  • target.customer_id = source.customer_id,
  • target.amount = source.amount,
  • target.transaction_time = source.transaction_time,
  • target.updated_at = source._ingest_timestamp
  • WHEN NOT MATCHED THEN
  • INSERT (order_id, customer_id, amount, transaction_time, updated_at)

VALUES (source.order_id, source.customer_id, source.amount, source.transaction_time, source._ingest_timestamp);

Gold Layer: Business Aggregations & Dimensional Analytics

The Gold Layer represents the refined presentation tier engineered specifically for business consumption, self-service analytics, executive BI dashboards, and AI/BI Genie spaces. While Silver tables maintain normalized entity structures, Gold tables assemble data into consumption-optimized formats—such as Star Schema dimensional models (facts and dimensions) or pre-aggregated summary tables.

Key characteristics of the Gold layer include:

Business-Level Aggregations : Computing Key Performance Indicators (KPIs), such as Monthly Active Users (MAU), Customer Lifetime Value (CLV), or daily revenue rollups by region.

Dimensional Modeling : Structuring cleaned Silver tables into Kimball star schema facts and dimensions linked by Unity Catalog constraints.

Performance Optimization : Applying Liquid Clustering, Z-Ordering, or materialized views to minimize scan times for dashboard queries.

Strict Security and Governance : Applying fine-grained access controls, row filters, and column masks so business stakeholders access only permitted datasets.

Architectural Layer Data State & Structure Primary Purpose Intended Audience & Tools

Bronze Raw, unparsed, append-only, includes ingestion metadata Historical archive, auditability, data replayability Data Engineers, Pipeline Operations

Silver Cleansed, typed, deduplicated, normalized tables Enterprise single source of truth, operational queries Data Analysts, Data Engineers, Machine Learning Engineers

Gold Aggregated KPIs, Star Schemas, Materialized Views Business reporting, executive dashboards, self-service BI BI Analysts, Executives, AI/BI Dashboards, AI/BI Genie

Data Flow & Streaming/Batch Integration Patterns

A major strength of the Medallion Architecture on Databricks is its unified support for both batch loading and real-time streaming pipelines under the Delta Lake engine. Through Structured Streaming and Delta Live Tables (DLT) , data can flow continuously across Bronze, Silver, and Gold layers with sub-second or minute-level latency, or execute periodically in scheduled batch intervals.

Regardless of execution frequency, the logical isolation of layers ensures that streaming data ingestion in Bronze never locks or interferes with business queries running against Gold tables.

Analytical Consumption & Optimization across Layers

To maximize the utility of the Medallion Architecture for data analysts using Databricks SQL:

Avoid Querying Bronze Tables directly for BI : Raw Bronze tables contain unparsed strings and duplicate change records, which result in slow, expensive queries and inaccurate metric calculations.

Use Silver for Exploratory Data Science : Silver tables provide granular, row-level cleaned events, making them ideal for custom ad-hoc investigations and ML feature generation.

Power Dashboards via Gold Tables : Always point Databricks AI/BI Dashboards and AI/BI Genie spaces to Gold tables or Gold Materialized Views to achieve sub-second query responsiveness.