3.03 · Deep Dive: Gold Layer - Business Analytics & Reporting

graph LR
    S["🥈 Silver<br/>(Validated)"]
    BL["Business<br/>Logic"]
    CD["Conformed<br/>Dimensions"]
    FF["Fact<br/>Tables"]
    AGG["Aggregations"]
    G["🥇 Gold<br/>(Business-Ready)"]

    S -->|Define measures| BL
    BL -->|Deduped refs| CD
    CD -->|Join dimensions| FF
    FF -->|Pre-aggregate| AGG
    CD --> G
    FF --> G
    AGG --> G

CREATE OR REPLACE TABLE gold.dim_customer AS
SELECT
  -- Surrogate key (for performance)
  GENERATE_UNIQUE_ID() as customer_key,

  -- Business key (for traceability)
  c.customer_id,

  -- Customer attributes
  c.first_name,
  c.last_name,
  CONCAT(c.first_name, ' ', c.last_name) as full_name,
  c.email,
  c.phone,

  -- Derived attributes (business logic)
  LOWER(REGEXP_EXTRACT(c.email, r'^[^@]+')) as email_prefix,
  EXTRACT(YEAR FROM CURRENT_DATE()) - EXTRACT(YEAR FROM c.birth_date) as age,

  -- SCD Type 2 tracking
  c.effective_date,
  c.end_date,
  c.is_current,

  -- Reference to geography
  g.country_key,
  g.country_name,
  g.region_name,

  -- Customer segment (from ML or business rules)
  s.customer_segment,
  s.lifetime_value_bucket,

  -- Metadata
  CURRENT_TIMESTAMP() as _dbt_updated_at,
  CURRENT_DATE() as _dbt_load_date
FROM silver.customers c
LEFT JOIN gold.dim_geography g
  ON c.country_code = g.country_code
LEFT JOIN gold.dim_customer_segment s
  ON c.customer_id = s.customer_id
  AND CURRENT_DATE() BETWEEN s.effective_date AND COALESCE(s.end_date, '2099-12-31')
WHERE c.is_current = TRUE;

CREATE OR REPLACE TABLE gold.fact_sales AS
SELECT
  -- Grain: One row per order line item per transaction

  -- Fact keys (foreign keys to dimensions)
  dk.customer_key,
  dp.product_key,
  ds.store_key,
  dd.date_key,

  -- Degenerate dimensions (attributes specific to transaction)
  o.order_id,
  o.line_item_number,

  -- Measures (numeric facts)
  o.quantity,
  o.unit_price,
  o.unit_cost,
  (o.quantity * o.unit_price) as extended_price,
  (o.quantity * o.unit_cost) as extended_cost,
  (o.quantity * (o.unit_price - o.unit_cost)) as gross_profit,

  -- Derived measures
  CASE
    WHEN o.discount_percent > 0.2 THEN 'HIGH'
    WHEN o.discount_percent > 0.1 THEN 'MEDIUM'
    ELSE 'LOW'
  END as discount_tier,

  -- Metadata
  CURRENT_TIMESTAMP() as _dbt_updated_at
FROM silver.orders o
LEFT JOIN gold.dim_customer dk ON o.customer_id = dk.customer_id
LEFT JOIN gold.dim_product dp ON o.product_id = dp.product_id
LEFT JOIN gold.dim_store ds ON o.store_id = ds.store_id
LEFT JOIN gold.dim_date dd ON DATE(o.order_date) = dd.date_actual
WHERE o._is_valid = TRUE;

CREATE OR REPLACE TABLE gold.agg_daily_revenue AS
SELECT
  dd.date_key,
  dd.date_actual,
  dd.year,
  dd.month,
  dd.day_of_month,

  dp.product_key,
  dp.product_id,
  dp.product_name,
  dp.category,

  ds.store_key,
  ds.store_id,
  ds.store_name,
  ds.region,

  -- Aggregated measures
  COUNT(DISTINCT f.order_id) as num_orders,
  SUM(f.quantity) as total_quantity,
  SUM(f.extended_price) as total_revenue,
  SUM(f.extended_cost) as total_cost,
  SUM(f.gross_profit) as total_profit,

  -- Derived metrics
  SAFE_DIVIDE(SUM(f.gross_profit), SUM(f.extended_price)) as profit_margin,
  SAFE_DIVIDE(SUM(f.extended_price), COUNT(DISTINCT f.order_id)) as avg_order_value,

  CURRENT_TIMESTAMP() as _dbt_updated_at
FROM gold.fact_sales f
LEFT JOIN gold.dim_date dd ON f.date_key = dd.date_key
LEFT JOIN gold.dim_product dp ON f.product_key = dp.product_key
LEFT JOIN gold.dim_store ds ON f.store_key = ds.store_key
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13;

Fact table (center) connected to dimension tables (points)

        ┌─────────────┐
        │ dim_customer│
        └──────┬──────┘
               │
    ┌──────────┼──────────┐
    │          │          │
┌───┴───┐  ┌──▼───┐  ┌──┴────┐
│dim_   │  │fact_ │  │dim_   │
│date   │──┤sales ├──┤product│
└───┬───┘  └──▲───┘  └──┬────┘
    │         │         │
    └─────────┼─────────┘
          ┌──┴────┐
          │dim_   │
          │store  │
          └───────┘

Dimensions are further normalized

         ┌─────────────────┐
         │ dim_customer    │
         └────────┬────────┘
                  │
    ┌─────────────┼──────────────┐
    │             │              │
┌───┴────┐   ┌───▼──┐   ┌──────┴──┐
│dim_date │   │fact_ │   │dim_prod │
│         │───┤sales ├───┤         │
└────┬────┘   └──▲───┘   └────┬────┘
     │           │            │
     │      ┌────┴─────┐      │
     └──────┤ dim_     │      │
            │ store    │      │
            └──────────┘      │
                              │
                         ┌────┴─────┐
                         │dim_       │
                         │category   │
                         └───────────┘

Hub-Link-Satellite pattern for maximum flexibility

┌────────────┐
│ Hub_       │ (Business keys)
│ Customer   │
└────────────┘
      │
      ├──────→ Link_Customer_Order
      │        ├──→ Sat_Customer
      │        └──→ Sat_Order
      │
      └──────→ Link_Customer_Geography
               └──→ Sat_Geography

-- Add business-derived columns
CREATE OR REPLACE TABLE gold.fact_sales_enhanced AS
SELECT
  f.*,

  -- Customer lifetime value tier (business logic)
  CASE
    WHEN clv.lifetime_value > 100000 THEN 'VIP'
    WHEN clv.lifetime_value > 50000 THEN 'HIGH_VALUE'
    WHEN clv.lifetime_value > 10000 THEN 'MEDIUM_VALUE'
    ELSE 'LOW_VALUE'
  END as customer_value_tier,

  -- Seasonality flag (business logic)
  CASE
    WHEN EXTRACT(MONTH FROM f.order_date) IN (11, 12) THEN 'HOLIDAY'
    WHEN EXTRACT(MONTH FROM f.order_date) IN (6, 7, 8) THEN 'SUMMER'
    ELSE 'REGULAR'
  END as seasonality,

  -- Product affinity (business logic)
  CASE
    WHEN dp.category IN ('Electronics', 'Computers') THEN 'TECH_AFFINITY'
    WHEN dp.category IN ('Books', 'Media') THEN 'CONTENT_AFFINITY'
    ELSE 'GENERAL'
  END as affinity_group
FROM gold.fact_sales f
LEFT JOIN gold.dim_customer dk ON f.customer_key = dk.customer_key
LEFT JOIN gold.dim_product dp ON f.product_key = dp.product_key
LEFT JOIN customer_lifetime_values clv ON dk.customer_id = clv.customer_id;

-- Track dimension changes over time
CREATE OR REPLACE TABLE gold.dim_product_scd2 AS
SELECT
  ROW_NUMBER() OVER (ORDER BY sp.product_id, sp.effective_date) as product_key,
  sp.product_id,
  sp.product_name,
  sp.category,
  sp.price,
  sp.effective_date,
  sp.end_date,
  CASE WHEN sp.end_date IS NULL THEN TRUE ELSE FALSE END as is_current,
  ROW_NUMBER() OVER (PARTITION BY sp.product_id ORDER BY sp.effective_date) as version_number
FROM silver.products sp
WHERE sp.is_current = TRUE OR sp.end_date > CURRENT_DATE() - INTERVAL 90 DAY;

# dbt/models/gold/gold_fact_sales.yml
version: 2

models:
  - name: gold_fact_sales
    description: "Fact table of sales transactions with SLA: 99.95% accuracy, loaded by 6 AM daily"

    tests:
      - dbt_utils.recency:
          datepart: day
          interval: 1
          field: _dbt_updated_at

      - dbt_utils.equality:
          compare_model: ref('expected_fact_sales')
          tolerance_percent: 0.05

    columns:
      - name: order_id
        tests:
          - not_null
          - unique

      - name: customer_key
        tests:
          - not_null
          - relationships:
              to: ref('gold_dim_customer')
              field: customer_key

      - name: extended_price
        tests:
          - not_null
          - positive_values:
              column_name: extended_price

      - name: gross_profit
        tests:
          - expression_is_true:
              expression: 'gross_profit <= extended_price'

-- dbt: Incremental refresh of fact table
{{
  config(
    materialized='incremental',
    unique_key='order_id',
    on_schema_change='fail',
    partition_by={
      'field': 'order_date',
      'data_type': 'date',
      'granularity': 'day'
    },
    cluster_by=['customer_key', 'product_key']
  )
}}

SELECT
  o.order_id,
  o.customer_id,
  o.product_id,
  o.order_date,
  ...
FROM {{ ref('silver_orders') }} o
{% if execute and execute_macros %}
  WHERE o.order_date >= (SELECT MAX(order_date) FROM {{ this }})
{% endif %}