Skip to content

ADR 008 — Gold Layer Architecture: Facts, Dimensions, Aggregates, Reports

Date: 2026-01-05 Status: Accepted Deciders: Data Engineering Team Superseded by: Partially superseded by ADR 019 (Gold layer naming, structure, and KPI conventions)

Context

With Bronze and Silver layers established, we need to define the Gold layer architecture for enriched, aggregated, and consumption-ready data.

Key challenges: 1. Multiple types of models in Gold (enriched facts, dimensions, aggregates, reports) 2. Need clear separation between building blocks (reusable) and consumption layer (end-user facing) 3. Performance optimization (when to materialize vs use views) 4. Naming conventions that indicate model type without redundancy

Decision

Implement a four-type Gold layer architecture:

1. Facts (fct_ prefix)

  • Purpose: Enriched granular data (same grain as Silver, but with JOINs)
  • Materialization: table or incremental
  • Characteristics:
  • Same grain as Silver (row per event/transaction)
  • JOIN with employee rosters (CORE_DB)
  • JOIN with glossaries (client DB or CORE_DB)
  • Filter to relevant records (e.g., XO agents only)
  • Source of Truth for ad-hoc analysis

Example: 

GOLD.FCT_CONTACTS
  - Contact-level facts
  - All Silver columns + employee metadata + glossary metadata
  - Filtered to XO agents
  - Materialized as table

2. Dimensions (dim_ prefix)

  • Purpose: Current state entities and reference data
  • Materialization: table or incremental (SCD Type 1 or Type 2)
  • Characteristics:
  • Current or historical state of entities
  • Slowly changing dimensions
  • Reference data for joins

Example: 

GOLD.DIM_AGENT
  - Current agent roster
  - One row per active agent
  - SCD Type 1 (latest state)

3. Aggregates (agg_ prefix)

  • Purpose: Pre-aggregated base metrics (building blocks for reports)
  • Materialization: table or incremental
  • Characteristics:
  • Single grain (agent-day, channel-hour, etc.)
  • Standard KPI calculations
  • Reusable across multiple reports
  • Few or no JOINs
  • Pre-calculated for performance

Example: 

GOLD.AGG_AGENT_DAILY
  - Row per agent per day
  - Pre-aggregated from fct_contacts
  - Standard KPIs + client-specific KPIs
  - Used by multiple report views

4. Reports (rpt_ prefix)

  • Purpose: End-user consumption layer (Tableau, analysts)
  • Materialization: view (virtual, zero storage)
  • Characteristics:
  • JOINs aggregates with dimensions and targets
  • Calculated fields (variance, performance flags)
  • Tableau-optimized (all needed columns)
  • Directly consumed by end users
  • Always fresh (computed on query)

Example: 

GOLD.RPT_AGENT_DASHBOARD
  - Tableau agent dashboard
  - JOINs: agg_agent_daily + dim_agent + targets
  - Calculated: variance, performance flags
  - View (no storage cost)

Rationale

Why separate Facts and Aggregates?

Facts (grain-level): - Needed for ad-hoc deep-dive analysis - Power users query these for custom analysis - Too slow to aggregate on-the-fly (millions of rows)

Aggregates (rolled-up): - Pre-calculated for dashboard performance - Reusable across multiple reports - Smaller data size (faster queries)

Why separate Aggregates and Reports?

Aggregates (building blocks): - Reusable base metrics - Single grain, minimal JOINs - Source data for multiple reports

Reports (consumption): - Customized per consumer (Tableau, analysts) - JOINs with current rosters/targets (always fresh) - Calculated fields (business logic) - Zero storage cost (views)

Why use views for Reports?

  • ✅ Always fresh (roster and target changes immediately reflected)
  • ✅ Zero storage cost
  • ✅ Easy to modify (change view, all dashboards updated)
  • ✅ Centralized business logic (not in Tableau)

Performance: Reports query pre-aggregated tables (fast), not raw facts.

Consequences

Positive

✅ Clear separation of concerns: - Facts = SOT for analysts - Aggregates = Building blocks - Reports = End-user consumption

✅ Performance optimization: - Pre-aggregate expensive operations - Views are fast (query pre-aggregated data)

✅ Cost efficiency: - Store only facts and aggregates - Reports are views (zero storage)

✅ Flexibility: - Modify reports without reprocessing data - Business logic in dbt (version controlled)

✅ Reusability: - One aggregate table → many report views

Negative

⚠️ More layers to understand: - Need to train users on which to query (facts vs agg vs reports) - Solution: Clear naming + documentation

⚠️ View performance depends on aggregate freshness: - If aggregate is stale, report shows stale data - Solution: Refresh aggregates frequently

Implementation

Data Flow:

BRONZE (xo-foundry)
  └─ Raw landing (truncated daily)
       ↓
SILVER (dbt)
  └─ Historical preservation (no enrichment)
       ↓
GOLD FACTS (dbt)
  └─ Enriched entities (employee + glossary + filtering)
       ↓
GOLD AGGREGATES (dbt)
  └─ Pre-aggregated metrics (building blocks)
       ↓
GOLD REPORTS (dbt)
  └─ Consumption views (Tableau-ready)

Example for Warby Parker:

-- 1. FACT (enriched granular data)
GOLD.FCT_CONTACTS
  - Materialized: table
  - Grain: row per contact event
  - Enrichment: employee + glossary metadata
  - Filter: XO agents only
  - Users: Power analysts

-- 2. AGGREGATE (base metrics)
GOLD.AGG_AGENT_DAILY
  - Materialized: table
  - Grain: row per agent per day
  - Source: fct_contacts
  - Reused by: 3+ report views
  - Users: Power users, reports

-- 3. REPORT (consumption)
GOLD.RPT_AGENT_DASHBOARD
  - Materialized: view
  - Source: agg_agent_daily + roster + targets
  - Calculated: variance, flags
  - Users: Tableau, business users

Alternatives Considered

Alternative 1: Just Facts and Reports (no Aggregates)

Rejected because: - Reports would aggregate facts on-the-fly (slow) - No reusability (each report aggregates separately) - Higher compute costs

Alternative 2: Materialize Reports as Tables

Rejected because: - Storage cost (duplicate data) - Stale roster/target data - Harder to maintain (change requires rebuild)

Exception: Can materialize complex reports as tables if: - Very expensive joins (many tables) - Query performance critical - Stale data acceptable

Alternative 3: Single "Gold" layer (no subtypes)

Rejected because: - No clear purpose for each table - Users confused about what to query - Mixed concerns (enrichment + aggregation + presentation)

  • ADR 001: Load strategy terminology (full_refresh, incremental, historical)
  • ADR 004: Metadata columns and hashing strategy
  • ADR 006: Bronze truncate/force strategy
  • ADR 009: Naming conventions for Gold layer (see below)
  • ADR 010: Reference data placement (rosters, glossaries)

Notes

Standard KPI Schema: All clients should implement standard KPI columns in agg_*_daily tables: - KPI_DATE, AGENT_ID, AGENT_EMAIL - CONTACT_COUNT, CONVERSATION_COUNT - AVG_HANDLE_TIME, AVG_AHT - AVG_CSAT, AVG_QA_SCORE

Client-specific KPIs are added as additional columns.

View Naming: Use rpt_ prefix for reports (not v_) for consistency with fct_, dim_, agg_ (all 3-4 char prefixes).

dbt Materialization Config: 

# dbt_project.yml
models:
  gold:
    facts:
      +materialized: table
      +tags: ['gold', 'facts']
    dimensions:
      +materialized: table
      +tags: ['gold', 'dimensions']
    aggregates:
      +materialized: table
      +tags: ['gold', 'aggregates']
    reports:
      +materialized: view
      +tags: ['gold', 'reports']