Playbook: Expanding the Gold Layer¶

Purpose: Step-by-step guide to add new Gold models (facts, dims, aggregates, or reports) to an existing client. Assumes Silver models for the client already exist.

Before You Start¶

Confirm: - [ ] Silver models for this client are deployed and populated - [ ] CORE_DB.SILVER.ROSTER_{CLIENT} is available (if building facts that need agent enrichment) - [ ] Client glossary exists if needed (e.g., {CLIENT}_DB.SILVER.INBOX_GLOSSARY) - [ ] You know the grain of the new model (one row per what?) - [ ] Check dbt-model-registry.md — does this model already exist?

Decision: Which Gold Type?¶

You want to...	Build a...	Prefix
Enrich raw events with roster/glossary data	Fact	`fct_`
Track current state of an entity (agent, contact)	Dimension	`dim_`
Pre-aggregate a metric for reuse across reports	Aggregate	`agg_`
Create a Tableau-ready consumption view	Report	`rpt_`

Reports should almost always source from Aggregates, not from Facts directly (better performance).

Phase 1 — Gold Fact Model (`fct_`)¶

Purpose: Enriched event-level data. Joins Silver events with rosters and glossaries.

Step 1.1 — Create the fact model¶

Location: apps/dbt/dbt_xo_models/models/gold/{client_lower}/fct_{object}.sql

{{ config(
    database=env_var('ENVIRONMENT', 'dev') == 'prod' and '{CLIENT}_DB' or '{CLIENT}_DB_DEV',
    schema='GOLD',
    materialized='dynamic_table',
    target_lag='1 hour',
    snowflake_warehouse='XO_TRANSFORM_WH'
) }}

WITH contacts AS (
    SELECT * FROM {{ ref('contact_timestamps') }}   -- Silver model
),

roster AS (
    SELECT *
    FROM {{ source('core_silver', 'roster_{client_lower}') }}
    WHERE IS_XO_AGENT = TRUE                        -- Filter to XO agents only
),

glossary AS (
    SELECT *
    FROM {{ source('{client_lower}_silver', 'inbox_glossary') }}
)

SELECT
    c.CONTACT_ID,
    c.CREATED_AT,
    c.AGENT_ID,

    -- Roster enrichment
    r.AGENT_NAME,
    r.TEAM_NAME,
    r.IS_XO_AGENT,

    -- Glossary enrichment
    g.INBOX_NAME,
    g.CHANNEL_TYPE,

    -- Metadata
    c.RECORD_KEY,
    c.DATE_TO_WAREHOUSE

FROM contacts c
LEFT JOIN roster r ON c.AGENT_ID = r.AGENT_ID
LEFT JOIN glossary g ON c.INBOX_ID = g.INBOX_ID

WHERE r.IS_XO_AGENT = TRUE                         -- Exclude non-XO agents

Rules for facts: - Source from Silver only (never Bronze) - Always JOIN with roster, filter IS_XO_AGENT = TRUE - Same grain as the Silver source (event-level) - No aggregation - Include RECORD_KEY and DATE_TO_WAREHOUSE for traceability

Step 1.2 — Add schema.yml entry¶

models:
  - name: fct_contacts
    description: >
      Enriched contact events from Gladly, joined with XO agent roster and inbox glossary.
      Grain: one row per contact (CONTACT_ID). Filtered to XO-managed agents only.
    columns:
      - name: contact_id
        description: "Gladly contact ID — primary key"
        tests:
          - unique
          - not_null
      - name: agent_id
        description: "XO agent identifier — foreign key to dim_agent"
        tests:
          - not_null
      - name: created_at
        description: "Contact creation timestamp (UTC)"
        tests:
          - not_null

Phase 2 — Gold Aggregate Model (`agg_`)¶

Purpose: Pre-aggregated metrics, reusable building blocks for multiple report views.

Naming: agg_{subject}_{grain} → agg_agent_daily_contacts

Step 2.1 — Create the aggregate model¶

Location: apps/dbt/dbt_xo_models/models/gold/{client_lower}/agg_{subject}_{grain}.sql

{{ config(
    database=env_var('ENVIRONMENT', 'dev') == 'prod' and '{CLIENT}_DB' or '{CLIENT}_DB_DEV',
    schema='GOLD',
    materialized='dynamic_table',
    target_lag='1 hour',
    snowflake_warehouse='XO_TRANSFORM_WH'
) }}

WITH facts AS (
    SELECT * FROM {{ ref('fct_contacts') }}         -- Source from facts, not Silver
)

SELECT
    AGENT_ID,
    AGENT_NAME,
    TEAM_NAME,
    DATE_TRUNC('day', CREATED_AT)                   AS CONTACT_DATE,

    -- Metrics
    COUNT(DISTINCT CONTACT_ID)                      AS TOTAL_CONTACTS,
    COUNT(DISTINCT CASE WHEN CHANNEL_TYPE = 'EMAIL' THEN CONTACT_ID END) AS EMAIL_CONTACTS,
    COUNT(DISTINCT CASE WHEN CHANNEL_TYPE = 'CHAT'  THEN CONTACT_ID END) AS CHAT_CONTACTS

FROM facts
GROUP BY 1, 2, 3, 4

Rules for aggregates: - Source from Gold facts only (never Silver, never Bronze) - Include the grain in the model name (_daily, _weekly, _hourly) - Include dimension columns needed for slicing (AGENT_ID, TEAM_NAME, etc.) - No business-logic filters beyond grain definition - No JOINs — facts already have enrichment

Step 2.2 — Add schema.yml entry¶

- name: agg_agent_daily_contacts
  description: >
    Daily contact volume per XO agent. Grain: one row per (agent, date).
    Source: fct_contacts. Reusable building block for agent performance reports.
  columns:
    - name: agent_id
      tests: [not_null]
    - name: contact_date
      tests: [not_null]
    - name: total_contacts
      description: "Total contacts handled by agent on this date"

Phase 3 — Gold Report View (`rpt_`)¶

Purpose: Tableau-ready consumption layer. Always a view (zero storage).

Naming: rpt_{purpose} → rpt_email_daily

Step 3.1 — Create the report view¶

Location: apps/dbt/dbt_xo_models/models/gold/{client_lower}/rpt_{purpose}.sql

{{ config(
    database=env_var('ENVIRONMENT', 'dev') == 'prod' and '{CLIENT}_DB' or '{CLIENT}_DB_DEV',
    schema='GOLD',
    materialized='view'                             -- ALWAYS a view, never dynamic table
) }}

SELECT
    CONTACT_DATE                                    AS "Date",
    AGENT_NAME                                      AS "Agent",
    TEAM_NAME                                       AS "Team",
    TOTAL_CONTACTS                                  AS "Total Contacts",
    EMAIL_CONTACTS                                  AS "Email Contacts",
    CHAT_CONTACTS                                   AS "Chat Contacts",

    -- Derived metrics for Tableau
    ROUND(EMAIL_CONTACTS / NULLIF(TOTAL_CONTACTS, 0) * 100, 1)  AS "Email %"

FROM {{ ref('agg_agent_daily_contacts') }}          -- Source from aggregates, not facts

Rules for reports: - Materialized as view — never dynamic_table or table - Source from aggregates (not facts directly — performance) - Column aliases use "Title Case" for Tableau readability - Light derived metrics are OK (ratios, percentages) - No complex joins or business logic — that belongs in facts/aggs

Step 3.2 — Add schema.yml entry¶

- name: rpt_email_daily
  description: >
    Daily email contact performance for Tableau. View over agg_agent_daily_contacts.
    Grain: one row per (agent, date). Zero storage cost.

Phase 4 — Validation¶

Step 4.1 — Compile and test locally¶

# Compile all new models
dbt compile --select gold.{client_lower}.*

# Run tests
dbt test --select gold.{client_lower}.*

# Check row counts make sense
# Facts should have same row count as Silver (before filtering)
# Aggs should have fewer rows (compressed by grain)
# Reports should have same rows as their agg source

Step 4.2 — Compare against existing (if replacing a legacy view)¶

If this Gold model replaces a legacy view in apps/snowflake-schema/:

-- Compare row counts
SELECT 'legacy' AS source, COUNT(*) FROM OPERATIONS.REPORTING.OLD_VIEW
UNION ALL
SELECT 'new' AS source, COUNT(*) FROM WBP_DB_DEV.GOLD.RPT_EMAIL_DAILY;

-- Compare spot-check values for a specific agent/date

Phase 5 — Deploy to Production¶

Step 5.1 — Deploy dbt to prod¶

Trigger the production Airflow DAG with ENVIRONMENT=prod. Verify Gold tables/views appear in {CLIENT}_DB.GOLD.

Step 5.2 — Update model registry¶

Add new models to dbt-model-registry.md.

Step 5.3 — Tableau update (if applicable)¶

Update or create Tableau data source pointing to production rpt_ view
Validate field names match what Tableau expects

Checklist Summary¶

See also: ADR 008 | ADR 009 | ADR 010