dbt Models¶

This page documents the dbt transformation models in the XO-Data platform. dbt handles the BRONZE → SILVER → GOLD transformation pipeline after xo-foundry loads raw data.

dbt project location: apps/dbt/dbt_xo_models/

Model Lineage: Warby Parker¶

flowchart TD
    B1[BRONZE\nGLADLY_CONTACT_TIMESTAMPS] --> S1[SILVER\ncontact_timestamps]
    B2[BRONZE\nGLADLY_CONVERSATION_TIMESTAMPS] --> S2[SILVER\nconversation_timestamps]
    B3[BRONZE\nGLADLY_WORK_SESSIONS] --> S3[SILVER\nwork_sessions]
    B4[BRONZE\nGLADLY_AGENT_DURATIONS] --> S4[SILVER\nagent_durations]
    SEED[SEED\nai_agents.csv] --> RPT
    ROSTER[OPERATIONS.BAMBOOHR\nROSTER_WARBYPARKER\n⚠️ temporary bridge] --> FCT

    S1 --> INT[GOLD int\nint_contacts_hold]
    S1 --> FCT[GOLD fct\nfct_contacts]
    INT --> FCT
    S3 --> AGG[GOLD agg\nagg_agent_daily_contacts]
    FCT --> AGG
    AGG --> RPT[GOLD rpt\nrpt_email_daily\nview]

Silver Models¶

Silver models are incremental append-only — they accumulate history from Bronze tables that are cleared on each run (batch_replace strategy).

contact_timestamps¶

Preserves the complete history of contact-level events from the Gladly ContactTimestampsReport API.

conversation_timestamps¶

Preserves conversation-level event history from ConversationTimestampsReport.

work_sessions¶

Preserves work session history from WorkSessionsReportV3. Filters out unrouted contacts (null WORK_SESSION_ID).

agent_durations¶

Preserves agent duration history from AgentDurationsReportV2.

Gold Models¶

Gold models add business enrichment — roster JOINs, aggregations, and report-ready views.

int_contacts_hold (intermediate)¶

Shared by fct_contacts and agg_agent_daily_contacts. Replaces OPERATIONS.STAGING.STAGE_WARBYPARKER_CONTACTS_HOLD.

fct_contacts¶

Master contacts fact table with roster enrichment. Tracks contact status, queue times, handle times, agent attribution, and hold durations.

Replaces: OPERATIONS.AGGREGATE.WARBYPARKER_CONTACTS_MODEL

agg_agent_daily_contacts¶

Daily agent-level aggregation with roster enrichment and tenure bucketing. Reusable building block for multiple reports.

Replaces: OPERATIONS.AGGREGATE.WARBYPARKER_AGENT_DAILY_CONTACTS

rpt_email_daily¶

Daily email performance report for AI-enabled agents. Tracks email volume, handle times, emails per hour, SLA compliance, and AI wave categorization (pre/post AI enablement date).

Replaces: OPERATIONS.AGGREGATE.WARBYPARKER_RPT_EMAIL_DAILY

Key columns:

Seeds¶

ai_agents¶

Used by rpt_email_daily to compute WAVE_PERIOD — whether a given date is before or after the agent's AI enablement date.

Environment Configuration¶

The dbt project switches between dev and prod via an environment variable:

# dbt_project.yml
vars:
  environment: "{{ env_var('ENVIRONMENT', 'dev') }}"

models:
  dbt_xo_models:
    silver:
      warbyparker:
        +database: "{{ 'WBP_DB' if env_var('ENVIRONMENT', 'dev') == 'prod' else 'WBP_DB_DEV' }}"
    gold:
      warbyparker:
        +database: "{{ 'WBP_DB' if env_var('ENVIRONMENT', 'dev') == 'prod' else 'WBP_DB_DEV' }}"

Running dbt¶

# From apps/dbt/dbt_xo_models/
# Run all models
dbt run --select tag:warbyparker

# Run only Silver
dbt run --select silver.warbyparker.*

# Run only Gold
dbt run --select gold.warbyparker.*

# Run specific model + its downstream dependencies
dbt run --select fct_contacts+

# Seed AI agents lookup
dbt seed --select warbyparker.ai_agents

# Full refresh (rebuilds Silver from Bronze history)
dbt run --full-refresh --select tag:warbyparker

Airflow Integration¶

dbt runs are orchestrated by Astronomer Cosmos, integrated into the warbyparker_gladly_daily DAG:

warbyparker_gladly_daily
├── extract_contact_timestamps
├── extract_conversation_timestamps
├── extract_work_sessions
├── extract_agent_durations
├── [stage tasks]
├── [load to bronze tasks]
└── dbt_transform (DbtTaskGroup)
    ├── silver.contact_timestamps
    ├── silver.conversation_timestamps
    ├── silver.work_sessions
    ├── silver.agent_durations
    ├── gold.int_contacts_hold
    ├── gold.fct_contacts
    ├── gold.agg_agent_daily_contacts
    └── gold.rpt_email_daily

DAG config: apps/airflow/xo-pipelines/dags/configs/warbyparker-gladly-daily.yaml

Related Documentation¶

• Medallion Layers — Layer architecture overview
• ELT Layer Architecture — Layer responsibilities
• Architecture Decisions — ADRs 008, 009, 010, 011