Designing Medallion Architecture Data Flows

Introduction

Welcome to the next step in our journey to becoming a Databricks Certified Data Engineer Professional. In the last lesson, we focused on the "how" of building pipelines by analyzing scenarios to choose between the declarative power of Delta Live Tables and the imperative control of Databricks Workflows. We learned that the best approach is often a hybrid one: using a Workflow to orchestrate a DLT pipeline that handles the core data transformations.

Now, we shift our focus from the orchestration tools to the data itself. This lesson tackles the fundamental design pattern for structuring data within the Databrabs Lakehouse: the Medallion Architecture. Our goal is to master the learning outcome: Analyze a scenario to design a Medallion Architecture data flow across bronze, silver, and gold layers.

You'll learn not just to describe the layers, but to think like a data architect—starting with a business requirement and working backward to design a logical, efficient, and high-quality data flow.

1. The Medallion Architecture: A Conceptual Overview

Before we design a data flow, we need a solid understanding of the purpose of each layer. The Medallion Architecture gets its name from the idea of refining raw material (bronze) into a more valuable, polished state (silver), and finally into a finished, high-value product (gold).

Let's watch a video that provides a clear, conceptual breakdown of what the "Lakehouse" and "Medallion" terms mean and the distinct role of each data layer.

How to approach data architecture | Design Lakehouse Architecture | Data Platform Solution Design

This video from Learning Journal offers an excellent conceptual foundation for the Medallion Architecture. It clearly explains the 'why' behind each layer.

Please watch the segment from 02:56 to 14:40. As you watch, focus on answering these questions for each layer: Bronze: What is the state of the data? What is its primary purpose? Silver: What key transformations happen here? How does the data quality improve? Gold: Who is the target audience for this layer? What is its primary characteristic?

To summarize and provide a visual anchor, the Medallion Architecture organizes data processing in stages to incrementally improve data quality and structure.

Medallion Architecture Data Flow — This diagram shows the typical flow of data from external sources through the Bronze (raw), Silver (cleaned), and Gold (aggregated) layers. The Gold layer tables are then consumed by analytics tools and business applications.

The key takeaway is this progression:

Bronze (Raw Data): An append-only, immutable copy of the source data. Its main purpose is to provide a historical archive and a source for reprocessing the entire pipeline if needed. Think of it as your "single source of truth."
Silver (Cleansed & Conformed Data): The data from the Bronze layer is refined. This is where you handle data cleansing (e.g., fixing data types, handling nulls), enrichment (e.g., joining with lookup tables), and filtering. The data is structured for more reliable use by data analysts and engineers.
Gold (Business-Ready Data): These are tables optimized for end-user consumption. They are often aggregated, pre-joined, and organized into data models (like a star schema) to directly answer specific business questions and power BI dashboards.

2. From Theory to Practice: Transforming Data Between Layers

Understanding the layers is one thing; implementing the transformations between them is another. Given your preference for hands-on examples, let's watch a practical demonstration of building a Medallion pipeline using Databricks. This video uses what was formerly called Delta Live Tables, now part of Lakeflow, which connects directly to our previous lesson.

Build a Medallion Architecture with Databricks Lakeflow | Data Quality, Aggregation & Orchestration

This video by Thomas Hass walks through the code required to build a pipeline, showing the specific types of transformations that occur at each stage.

Watch the segments from 02:26 to 18:26. Pay close attention to the differences in the code's intent: Bronze to Silver (02:26 - 14:59): Notice the focus on casting data types (e.g., string to date), implementing data quality rules with expect all or drop, and splitting columns (e.g., full name into first and last name). Silver to Gold (14:59 - 18:26): Observe how the logic shifts from cleaning to business value. The code now focuses on aggregations (GROUP BY) and pre-joining different silver tables to create materialized views for BI consumption.

This practical demonstration solidifies the conceptual model. The transition from Bronze to Silver is about technical refinement, while the transition from Silver to Gold is about creating business value.

3. The Design Process: Working Backward from the Goal

The core skill for the professional certification isn't just knowing the layers, but being able to design a pipeline for a new scenario. The most effective way to do this is to work backward from the final requirement.

The Learning Journal video has an excellent section that walks through this exact design process.

How to approach data architecture | Design Lakehouse Architecture | Data Platform Solution Design

This segment demonstrates the thought process of a data architect. It shows how to start with the end-user's needs (the Gold layer) and use that to define the preceding layers.

Watch the detailed example from 21:38 to 37:23. The key insight here is the design methodology: Start with the Gold Layer: First, understand and model the final output your business users need (e.g., workout_bpm_summary). Define the Silver Layer: Ask, 'What clean, conformed data entities do I need to build that Gold table?' This leads to identifying tables like users, workouts, and heart_rate. Define the Bronze Layer: Ask, 'What raw data sources must I ingest to create those Silver tables?' This identifies the source systems, like Azure SQL and Kafka topics. Define the Landing Zone: Finally, determine how to get that raw data into the Lakehouse platform.

This "work backward" approach is a critical technique. It ensures your pipeline is purpose-built to deliver business value and avoids ingesting or processing data that doesn't contribute to the final goal.

4. Scenario Analysis: Design Your Own Data Flow

Now, let's put this into practice. Read the following scenario and, using the "work backward" method, outline your proposed design for the bronze, silver, and gold layers.

Scenario:
You are a data engineer for an online retail company. The marketing team wants a dashboard that shows daily total sales revenue and unique customer count per product category. This dashboard needs to be updated daily.

Your available data sources are:

A production database with a sales table (transactional, one row per item sold). It contains transaction_id, product_id, customer_id, quantity, price_per_item, and sale_timestamp.
A separate production database with a products table (a dimension table). It contains product_id, product_name, and category_id.
A CSV file in cloud storage, updated infrequently, which maps category_id to category_name.

Based on this, how would you design the Medallion data flow? Describe the tables you would create in each layer and the purpose of each.

Click for a sample design

Here is one possible design, following the "work backward" approach:

1. Gold Layer Design (Start here)

Goal: Power the marketing dashboard.
Table: daily_category_sales_summary
Columns: report_date, category_name, total_revenue, unique_customer_count.
Description: This is a highly aggregated table. Each row provides the final numbers for a specific category on a specific day. It's designed for fast, simple queries from the BI tool (e.g., SELECT * FROM daily_category_sales_summary WHERE report_date = 'YYYY-MM-DD').

2. Silver Layer Design

Goal: Create clean, conformed tables needed to build the Gold table.
Table 1: sales_transactions_cleaned
- Columns: transaction_id, product_id, customer_id, quantity, price_per_item, sale_timestamp (now a proper timestamp type), total_item_revenue.
- Description: Read from the bronze sales table. Here we would perform data quality checks (e.g., ensure quantity and price_per_item are positive), cast data types correctly, and perhaps calculate the total_item_revenue (quantity * price_per_item).
Table 2: products_enriched
- Columns: product_id, product_name, category_id, category_name.
- Description: This table is created by joining the bronze products table with the bronze categories table. It provides a single, enriched view of product information, linking a product directly to its category name. This avoids doing this join repeatedly in downstream queries.

3. Bronze Layer Design

Goal: Ingest all raw source data with no transformations.
Table 1: raw_sales
- Description: A direct, append-only copy of the sales table from the production database. Ingested daily. Stored as-is, preserving original data types and any potential "dirty" data.
Table 2: raw_products
- Description: A copy of the products table from its source database.
Table 3: raw_categories
- Description: A copy of the category_id to category_name mapping from the source CSV file.

This design cleanly separates concerns, making the pipeline robust, easy to maintain, and efficient.

Conclusion

In this lesson, you moved from understanding orchestration tools to designing the data architecture they operate on. This is a crucial step up in responsibility and a key skill for a professional-level data engineer.

Key Takeaways:

The Medallion Architecture incrementally refines data across three layers: Bronze (raw), Silver (clean), and Gold (business-aggregated).
Transformations from Bronze to Silver focus on data quality and cleansing.
Transformations from Silver to Gold focus on aggregation and business logic to prepare data for specific use cases.
The most effective design strategy is to work backward from the business requirement: define the Gold tables first, then the Silver tables needed to build them, and finally the Bronze tables needed as a source.

Preview of the Next Lesson:

Now that we have a high-level design for our data flow, we need to implement the logic within it. A common and critical challenge in data warehousing is handling changes to dimension data over time. In our next lesson, we will dive into a specific Silver/Gold layer implementation pattern: Implementing Type 1 slowly changing dimensions (SCDs) in Delta Lake to overwrite existing data.