ELT Evaluation Rubrics¶

Last Updated: 2026-06-11

Quality verification checklists for every layer of the XO-Data ELT pipeline. These rubrics are embedded in the ELT Pipeline issue template and serve as the acceptance gate before declaring any pipeline layer complete.

When to Use These Rubrics¶

Rubric A: Extraction Verification¶

For any issue touching xo-foundry extraction, YAML configs, or Bronze DDL.

• Config validates: uv run xo-foundry validate-config --config <path> passes with no errors
• Load strategy matches ADR 001: full_refresh for Gladly event sources, incremental for Google Sheets, batch_replace for Bronze loading
• Data fidelity: all values extracted as strings — no pandas type inference, no float IDs, no leading zeros stripped
• Column headers: raw headers match source API exactly; standardized headers (post-staging) are uppercase and SQL-safe (underscores, no spaces)
• 7 metadata columns in Bronze DDL: RECORD_KEY, RECORD_HASH, DATE_TO_WAREHOUSE, SOURCE_FILE, BATCH_ID, PIPELINE_RUN_ID, UPDATE_DATE — all VARCHAR, all present
• RECORD_KEY strategy documented: natural key (e.g., Gladly ID) or composite hash — document in the Bronze DDL comment header
• S3 paths follow ADR 009: {bucket}/{domain}/{report}/{load_strategy}/{date}/ — verify in config YAML and actual S3 output
• Bronze DDL column count: matches the CSV column output of the extractor; all data columns are VARCHAR
• Naming convention: Bronze table is {SOURCE}_{OBJECT} (e.g., GLADLY_CONTACTS, GSHEETS_CSAT); source registered in sources.yml
• No hardcoded credentials: all secrets reference Airflow Variable names in YAML configs — nothing embedded in code or config files

Verification command:

uv run xo-foundry validate-config --config apps/airflow/xo-pipelines/dags/configs/<client>/<source>.yml

Rubric B: Silver Verification¶

For any issue creating or modifying a Silver dbt model.

• Row count integrity: Silver row count matches expected count versus Bronze — run this and paste results in an issue comment:

SELECT COUNT(*) FROM {CLIENT}_DB.BRONZE.{SOURCE}_{OBJECT};
SELECT COUNT(*) FROM {CLIENT}_DB.SILVER.{OBJECT};

• No JOINs: Silver is a single-source SELECT from one Bronze table only — no JOINs to rosters, glossaries, or other models
• No filters: every Bronze row survives into Silver — no WHERE clause that drops records
• No aggregation: same grain as the Bronze source — one row in Bronze = one row in Silver (after dedup)
• Explicit type conversions: all casts are deliberate — VARCHAR → TIMESTAMP_NTZ, NUMBER, BOOLEAN, etc. No implicit casts.
• Deduplication is correct:
• Incremental sources (e.g., event log): deduplicate by RECORD_KEY across all batches
• Current-state sources (e.g., GSheets): keep latest BATCH_ID per key
• schema.yml entry complete:
• description states grain explicitly (e.g., "One row per contact. Source: GLADLY_CONTACTS.")
• Primary key column has both unique and not_null tests
• All foreign key columns have not_null tests
• Naming convention: file is {object}.sql in models/{client}/silver/ — no layer prefix (e.g., csat.sql, not silver_csat.sql)
• Metric spot-check: verify 3 date ranges (e.g., last week, last month, a quarter ago) — document row counts, distinct PKs, NULL counts in an issue comment

Pattern to follow: apps/dbt/xo-medallion/models/warbyparker/silver/contacts.sql

Rubric C: Gold Verification¶

For any issue creating a Gold dbt model (fct_, dim_, agg_, rpt_).

• Prefix and materialization correct:

• Source layer rules respected:
• Facts (fct_) source from Silver only
• Aggregates (agg_) source from Gold facts only — never Silver
• Reports (rpt_) source from Gold aggregates (preferred) or Gold facts
• Business logic validated against at least one reference — document comparison in issue comment:
• Legacy OPERATIONS view (e.g., OPERATIONS.STAGING.STAGE_WARBYPARKER_CSAT)
• Source tool dashboard (e.g., Gladly metrics, Google Sheets actuals)
• XO Ops tracker
• Roster enrichment (for fct_ and agg_):
• JOIN key documented (e.g., ADVISOR_NAME → GLADLY_NAME)
• Non-XO agents filtered: WHERE roster.IS_XO_AGENT = TRUE
• Roster source documented: CORE_DB.SILVER.ROSTER_{CLIENT}
• schema.yml entry complete:
• description states grain explicitly
• Primary key has unique + not_null tests
• meta.replaces references the legacy OPERATIONS view this replaces
• Naming follows ADR 009: {prefix}_{object} in models/{client}/gold/
• dbt tests pass: dbt test --select <model_name> — zero failures

Patterns to follow:

• apps/dbt/xo-medallion/models/warbyparker/gold/fct_contacts.sql — fact table pattern
• apps/dbt/xo-medallion/models/warbyparker/intermediate/int_contacts_hold.sql — intermediate model pattern (LAG window functions)

Rubric D: End-to-End Verification¶

Run this after all layers are complete — extraction, Silver, and Gold.

• Data lineage trace: pick 1 record and trace it through every layer. Paste the 4-query sequence in an issue comment:

-- 1. Source S3 file (verify it exists and has the right columns)
-- 2. Bronze table (RECORD_KEY + raw columns)
-- 3. Silver table (same RECORD_KEY, typed columns)
-- 4. Gold table (enriched, XO agents only)

• Idempotency verified: re-run the DAG for the same date → no duplicate rows appear in Gold, output is byte-identical
• Full dbt test suite passes: dbt test --select tag:{client}_{source} — zero failures

Quick Reference: Layer Rules¶

Related Resources¶

• ADR 001 — Load Strategy Terminology
• ADR 008 — Gold Layer Architecture
• ADR 009 — Naming Conventions
• ADR 011 — Bronze batch_replace
• ELT Layer Architecture Best Practices
• dbt Development Workflow
• New Extractor Checklist
• ELT Pipeline Issue Template