Databricks SQL Tutorial: Publish Aggregated Gold Tables

This tutorial guides you through publishing aggregated “gold” tables in Databricks SQL. We will build this process incrementally, focusing on the core functionality. The goal is to understand how to efficiently publish pre-calculated data for downstream consumption.

Script 1: Create and Populate a Sample Silver Table

First, we create a small, synthetic silver table for demonstration purposes. This will represent our raw, detailed data.

Now, let’s verify the data.

Output:

sale_id | product_id | customer_id | sale_date  | quantity | price
--------|------------|-------------|------------|----------|--------
       1 |         101 |         201 | 2023-01-15 |        2 |  25.00
       2 |         102 |         202 | 2023-02-20 |        1 |  50.00
       3 |         101 |         203 | 2023-03-10 |        3 |  25.00
       4 |         103 |         201 | 2023-04-05 |        1 |  75.00
       5 |         102 |         204 | 2023-05-12 |        2 |  50.00

Script 2: Create an Aggregated Gold Table

Next, we aggregate the silver sales data to create a gold table. This gold table will represent aggregated sales data.

Let’s verify the aggregated data.

Output:

product_id | total_quantity | total_revenue
-----------|-----------------|----------------
        101 |               6 |         150.00
        102 |               3 |        150.00
        103 |               1 |         75.00

Script 3: Publish the Gold Table

Finally, we publish the `gold_sales_by_product` table to a new catalog. This makes the aggregated data available for other users and applications.

Now, let’s select from the published gold table.

Output:

product_id | total_quantity | total_revenue
-----------|-----------------|----------------
        101 |               6 |         150.00
        102 |               3 |        150.00
        103 |               1 |         75.00