Building an Aggregated Gold Table for Business Use Cases

The Purpose and Objectives of the Gold Layer

The Gold layer is not just another storage location; it's a product designed for your data consumers. Its primary goal is to provide data in a way that is simple to query, highly performant, and directly aligned with business domains. Before we start building, it's essential to understand the design objectives that define a production-grade Gold layer.

As you've just read, the key objectives for the Gold layer are:

1. Simplicity for Consumption: Tables are often denormalized (wide) to answer common business questions without requiring complex joins.
2. Performance Optimization: We optimize for fast read access. Heavy computational work like joins and aggregations is done before the data lands in the Gold table.
3. Auditability & Lineage: Gold tables must be trustworthy. We need to be able to trace data back to its source.
4. Modular, Declarative Business Logic: A metric like "monthly recurring revenue" should have a single, clear definition implemented in one place.
5. Reusability: Build domain-centric tables (e.g., product_sales_summary) that can serve multiple teams and use cases, rather than one-off report tables.

Given your experience building data platforms, these goals of creating optimized, business-facing data marts should be familiar. The key difference here is the specific toolset Databricks provides to achieve these goals efficiently.

Core Patterns for Aggregated Gold Tables

To meet the objectives above, we can use several design patterns. For creating aggregated tables, two patterns are particularly effective.

• Entity-Centric Fact Tables: These tables provide a snapshot or summary of an entity (like a customer or product) over a specific time period. The grain of the table is one row per entity per time period (e.g., customer_id + day).
• Metric Tables: These offer a highly flexible, "narrow" format where each row represents a single metric value for a specific entity at a point in time. This is very useful for time-series analysis and ML feature stores.

Let's look at a very simple, practical example of creating an aggregated Gold table.

The code you just reviewed is a classic example of an aggregation:

-- This query aggregates the number of customers per email domain from the silver table.
SELECT 
  SPLIT(email, '@')[1] AS email_domain, 
  COUNT(*) AS user_count 
FROM unity.silver.customers_cleaned 
GROUP BY 
  SPLIT(email, '@')[1];

This query takes the clean customers_cleaned table from the Silver layer, performs an aggregation, and produces a result that directly answers a business question: "How many users do we have from each email domain?". This is the fundamental principle of a Gold table.

Implementing Gold Tables with Delta Live Tables

While simple SQL queries work, the most robust and efficient way to build Gold tables in Databricks is by using Delta Live Tables (DLT), especially with Materialized Views (MVs).

In DLT, you have two primary types of datasets:

• Streaming Tables: Designed for append-only streaming sources. They process data incrementally as it arrives. You used these to build your Bronze and Silver tables.
• Materialized Views: The result of a query that is pre-computed. MVs are ideal for the Gold layer because they are automatically and efficiently updated when their input data changes. They are perfect for handling complex aggregations and joins that need to be kept fresh.

Let's explore this concept further.

As you saw, Materialized Views in DLT are incredibly powerful for the Gold layer. They abstract away the complexity of maintaining aggregates. You simply declare the final state you want, and DLT's engine (including a smart optimizer called Enzyme) figures out the most efficient way to keep it updated.

Now let's see a complete end-to-end example of building Gold tables from Silver tables in a DLT pipeline.

Here's a representative code snippet from the video, showing how to create an aggregated Gold table in Python using DLT:

import dlt
from pyspark.sql.functions import avg, col

@dlt.table(
  name="gold_aggregated_telematics",
  comment="Aggregated telematics data, averaged per device."
)
def gold_aggregated_telematics():
  return (
    dlt.read_stream("silver_telematics_clean")
      .groupBy("chassis_number")
      .agg(
        avg("speed").alias("avg_speed"),
        avg("latitude").alias("avg_latitude"),
        avg("longitude").alias("avg_longitude")
      )
  )

This code defines a Gold table that is automatically and incrementally updated whenever new data arrives in the silver_telematics_clean table. This declarative approach is central to building modern, efficient data pipelines on Databricks.

Conclusion

In this lesson, you learned how to transition from the cleansed Silver layer to the business-focused Gold layer. We've established that the Gold layer is about creating performant, reusable, and easily consumable data products for your organization.

Key Takeaways:

• The Gold layer is designed for business consumption, optimizing for read performance and ease of use by aggregating and denormalizing data.
• Entity-centric fact tables and metric tables are common, effective patterns for structuring Gold layer data to answer specific business questions.
• Delta Live Tables (DLT) Materialized Views are the recommended tool in Databricks for building aggregated Gold tables. They automatically handle incremental updates, making your pipelines more efficient and easier to maintain.
• The process involves reading from one or more Silver tables, applying business logic through joins and aggregations, and materializing the result as a Gold table.

Preview of the Next Lesson:

Our Gold tables currently represent the latest state of the business. But what happens when dimensional data changes over time? For example, what if a customer moves to a new address or a product is assigned to a new category? In the next two lessons, we will dive deep into handling these changes by implementing Type 1 and Type 2 Slowly Changing Dimensions (SCDs) in Delta Lake, a critical skill for professional data engineers.