ELT Layer Architecture¶

This document defines the responsibilities, rules, and patterns for each layer in the XO-Data medallion architecture.

Layer Responsibilities¶

BRONZE: Raw Landing Zone¶

Owner: xo-foundry (Airflow tasks) Tables: schemachange migrations

What BRONZE Does¶

• Lands raw data from source systems (all VARCHAR columns)
• Adds metadata columns for lineage (RECORD_KEY, RECORD_HASH, DATE_TO_WAREHOUSE, SOURCE_FILE, BATCH_ID, PIPELINE_RUN_ID)
• Preserves source column names (standardized to UPPERCASE)

What BRONZE Does NOT Do¶

• No type conversions
• No business logic
• No JOINs
• No deduplication (each batch is idempotent via its own loading strategy)
• No historical preservation in legacy truncate_insert pipelines (handled in Silver)

Loading Patterns¶

Two strategies are supported (see ADR 011):

batch_replace (preferred for new snowflake_load pipelines):

-- Delete current batch, then load — Bronze retains cross-batch history
DELETE FROM {CLIENT}_DB.BRONZE.{SOURCE}_{OBJECT} WHERE BATCH_ID = '{ds}';
COPY INTO {CLIENT}_DB.BRONZE.{SOURCE}_{OBJECT}
  FROM @stage_path FORCE = TRUE ON_ERROR = 'ABORT_STATEMENT';

truncate_insert (legacy legacy_elt pipelines only):

BEGIN TRANSACTION;
  TRUNCATE TABLE {CLIENT}_DB.BRONZE.{SOURCE}_{OBJECT};
  COPY INTO {CLIENT}_DB.BRONZE.{SOURCE}_{OBJECT}
    FROM @stage_path FORCE = TRUE ON_ERROR = 'ABORT_STATEMENT';
COMMIT;

Naming¶

{SOURCE}_{OBJECT} -- e.g., GLADLY_CONTACT_TIMESTAMPS, SPROUT_MESSAGES

SILVER: Historical Preservation¶

Owner: dbt Materialization: Incremental tables

What SILVER Does¶

• Converts VARCHAR to proper data types (INT, TIMESTAMP, etc.)
• Validates data quality (NOT NULL, unique keys)
• Deduplicates on unique keys via incremental models
• Preserves all historical records that Bronze discards

What SILVER Does NOT Do¶

• No enrichment -- No JOINs with rosters, glossaries, or other reference data
• No filtering -- All records preserved (including non-XO agents)
• No aggregation -- Same grain as source data
• No business logic -- Only type conversion and data quality

Why This Matters¶

Silver is the single source of truth for historical data. Because Bronze is a raw landing zone — not a permanent historical store (legacy pipelines truncate it; batch_replace retains only recent batches) — Silver must preserve everything. Keeping Silver free of enrichment ensures:

1. Reprocessing safety -- If a roster changes, only Gold needs to be rebuilt
2. Auditability -- Silver exactly reflects what the source provided
3. Flexibility -- New Gold models can be built without modifying Silver

Naming¶

{OBJECT} -- e.g., CONTACT_TIMESTAMPS, MESSAGES, INBOX_GLOSSARY

No prefix needed; the schema SILVER indicates the layer.

GOLD: Analytics-Ready¶

Owner: dbt Materialization: Tables (facts, dims, aggs) and Views (reports)

The Gold layer uses five model types — int_ (ephemeral), fct_, dim_, agg_, rpt_ — plus the exp_ and feed_ view layers (ADR 021, ADR 026). See ADR 008 and ADR 019.

Facts (fct_)¶

Enriched granular data at the same grain as Silver, enhanced with JOINs to reference data.

SILVER.CONTACT_TIMESTAMPS
  + JOIN CORE_DB.SILVER.ROSTER_WARBYPARKER (agent names, teams)
  + JOIN SILVER.INBOX_GLOSSARY (inbox names, channels)
  + WHERE agent is XO employee
  = GOLD.fct_contacts

Dimensions (dim_)¶

Current state reference entities for downstream JOINs.

Examples: dim_agents, dim_customers, dim_inboxes

Aggregates (agg_)¶

Pre-aggregated metrics -- reusable building blocks that multiple reports can reference.

Examples: agg_agent_daily, agg_channel_weekly, agg_inbox_hourly

Reports (rpt_)¶

End-user consumption layer -- Tableau-ready views with zero storage cost.

Examples: rpt_agent_dashboard, rpt_email_daily, rpt_csat_weekly

Reference Data Placement (ADR 010)¶

Data Flow Through Layers¶

Source API
    │
    ▼
BRONZE (raw VARCHAR, batch_replace load)
    │  dbt: type conversions, dedup
    ▼
SILVER (typed, historical, no enrichment)
    │  dbt: JOINs with rosters/glossaries, filter to XO agents
    ▼
GOLD Facts (enriched granular data)
    │  dbt: GROUP BY, aggregations
    ▼
GOLD Aggregates (pre-aggregated metrics)
    │  dbt: SELECT + JOIN for presentation
    ▼
GOLD Reports (views, zero storage, Tableau-ready)

Materialization Strategy¶

Quick Reference¶

Related Documentation¶

• Medallion Layers -- Snowflake implementation details
• Naming Conventions -- Complete naming standards
• ELT Pipeline Flow -- How data moves through stages
• Architecture Decisions -- ADRs 006, 008, 009, 010