Designing the Gold Layer

In the previous two lectures, we built Bronze (raw, unfiltered preservation) and Silver (cleaned, typed, deduplicated, trustworthy data). In this lecture, let's build the final layer — Gold — where all of that becomes an actual business answer.

Design Principle: Answer a Specific Business Question

Everything in Bronze and Silver was about correctness and preservation. Gold is about speed and usability.

Gold tables are pre-aggregated, read-optimized, and built for a specific consumer — a BI tool, a dashboard, a weekly business report.

The Design Question: Table or View?

Every Gold table forces you to make a decision: should this be a materialized table, or a view?

• A view is simpler — it just wraps the Silver query. Every time someone queries it, the underlying computation runs fresh.
• A materialized table stores the actual result. Querying it just reads pre-computed data — fast, but it needs to be refreshed whenever the underlying data changes.

The right answer depends on query frequency and computation cost:

• If a Gold metric is queried hundreds of times a day, and the underlying Silver join is expensive, materialize it.
• If it's queried once a day, and Silver is fast, a view is perfectly fine.

We'll build this one as a materialized table, because that's the production-realistic choice for a revenue metric — something likely to be queried frequently, by multiple dashboards and reports.

Step 1: Build the Gold Table

sqlCopy
%sql
-- Gold: daily revenue by customer tier
-- Business rule: only completed orders count as revenue
-- This filter is a documented Gold decision, not a Silver contract

DROP TABLE IF EXISTS dev.dbx_course.gold_daily_revenue;

CREATE TABLE dev.dbx_course.gold_daily_revenue AS
SELECT
    o.order_date,
    c.tier                          AS customer_tier,
    COUNT(DISTINCT o.order_id)      AS order_count,
    COUNT(DISTINCT o.customer_id)   AS unique_customers,
    SUM(o.order_total)              AS total_revenue,
    ROUND(AVG(o.order_total), 2)    AS avg_order_value,
    CURRENT_TIMESTAMP()             AS last_refreshed
FROM dev.dbx_course.silver_orders o
JOIN dev.dbx_course.silver_customers c
    ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
GROUP BY o.order_date, c.tier
ORDER BY o.order_date, c.tier;

Let's look at what this table actually does:

• Joins Silver orders with Silver customers — pulling in the customer's tier, which lives in a separate table.
• Aggregates — counts distinct orders, counts distinct customers, sums total revenue, and computes average order value, all grouped by order_date and customer_tier.
• last_refreshed — a timestamp column recording exactly when this table was last built. Genuinely useful in production, so anyone looking at this table can immediately tell how fresh (or stale) the numbers are.
• WHERE o.status = 'completed' — this is the detail worth pausing on.

Step 2: Verify the Result

sqlCopy
%sql
SELECT * FROM dev.dbx_course.gold_daily_revenue;

Gold daily revenue result table

The result: 6 rows, one per unique order_date + customer_tier combination, each with its order count, unique customer count, total revenue, and average order value.

Why WHERE status = 'completed' Belongs in Gold — Not Silver

This is the single most important design lesson in this lecture, so it's worth spelling out clearly.

Notice what we excluded: anything WHERE status is not 'completed'. This is a business rule — a decision about what counts as "real" revenue.

This filter is appropriate in Gold, and only in Gold, because:

• Gold is opinionated. It knows exactly what the business counts as revenue — in this case, only completed orders.
• That filter does not belong in Silver, because Silver should be complete. Silver's job is to be a trustworthy, full representation of the (cleaned) data — not to pre-decide which subset of it matters for one particular business question.
• If someone later asks "what was the value of cancelled orders?" — Silver can answer that, because it still has every status, cleaned and intact. Gold cannot answer that question, and that's completely fine — Gold was never trying to answer every question. It was built to answer one question well: daily revenue by tier.

This is exactly the Medallion contract we covered in the very first lecture of this chapter, now shown in a concrete, working example: Silver stays complete and neutral; Gold makes deliberate, documented, business-specific decisions on top of it.

Alternative: Gold as a View

For comparison, here's the same logic built as a view instead of a materialized table:

sqlCopy
%sql
-- Alternative: Gold as a view
-- Always current, no refresh required, re-runs the query on every call
-- Choose this when data freshness matters more than query speed

DROP VIEW IF EXISTS dev.dbx_course.gold_daily_revenue_view;

CREATE VIEW dev.dbx_course.gold_daily_revenue_view AS
SELECT
    o.order_date,
    c.tier                          AS customer_tier,
    COUNT(DISTINCT o.order_id)      AS order_count,
    COUNT(DISTINCT o.customer_id)   AS unique_customers,
    SUM(o.order_total)              AS total_revenue,
    ROUND(AVG(o.order_total), 2)    AS avg_order_value
FROM dev.dbx_course.silver_orders o
JOIN dev.dbx_course.silver_customers c
    ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
GROUP BY o.order_date, c.tier;

sqlCopy
%sql
SELECT * FROM dev.dbx_course.gold_daily_revenue_view ORDER BY order_date, customer_tier;

Notice: identical query logic, same result — but this version has no last_refreshed column, and doesn't need one. A view is always current, because it re-runs the underlying query on every single call. There's no "staleness" to track, because there's no stored, cached result to go stale in the first place.

Choose a view over a table when data freshness matters more than query speed — the trade-off being that every query against a view repeats the full join-and-aggregate computation, every time.

Summary

The core lesson of Gold: it's allowed to be opinionated. Business rules like "only completed orders count as revenue" belong here, clearly documented as a Gold-layer decision — never smuggled into Silver, where they'd quietly narrow what Silver is supposed to represent.

With Bronze, Silver, and Gold now all built, our Medallion pipeline is complete — a full journey from raw, duplicate-laden source data to a clean, business-ready revenue table.

See you again. Keep learning, and keep growing!