### Prompt (give Claude / Gemini exactly this) ###

I'm building a local medallion pipeline for UCC-1 financing statement
analytics.
Stack: Python, DuckDB (local), Airflow (Docker), dbt (Gold), Great
Expectations.
Data: 50 synthetic UCC filings, 3 states (CA, TX, NY), CSV format.

Design the pipeline architecture:
1. Bronze layer schema (raw CSV fields -> DuckDB table)
2. Silver layer transformations (§9-503 normalization, dedup logic)
3. Gold layer schema (entity risk profiles -- debtor entity ->
   active_filings -> risk_tier)
4. Canonical field names across all layers (Python snake_case)
5. What belongs in spec.md vs what belongs in code comments?

Output a field contract table: field | layer | type | transformation rule

### Reference response (Claude Opus, paraphrased) ###

Field Contract for UCC Data Pipeline:

BRONZE LAYER (raw, no transforms)
  filing_number          VARCHAR    Source: "Filing Number" column, as-is
  debtor_name            VARCHAR    Source: "Debtor Name" column, raw
  secured_party_name     VARCHAR    Source: "Secured Party" column, raw
  collateral_description VARCHAR    Source: "Collateral", raw
  filing_date            VARCHAR    Source: "Date Filed", as string at Bronze
  lapse_date             VARCHAR    Source: "Lapse Date", as string at Bronze
  state_code             VARCHAR    Source: "State", raw (may be name or code)
  status                 VARCHAR    ACTIVE | LAPSED | TERMINATED

SILVER LAYER (normalized + deduped)
  debtor_search_key  VARCHAR    §9-503: UPPER, strip articles, strip punct
  entity_id          VARCHAR    md5(debtor_search_key) - 32 hex chars
  filing_date        DATE       cast from VARCHAR (multiple input formats)
  state_code         VARCHAR(2) ISO 2-char (TEXAS -> TX, NEW YORK -> NY)
  is_active          BOOLEAN    status == 'ACTIVE'
  _silver_ts         TIMESTAMP  pipeline run timestamp

GOLD LAYER (aggregated risk profiles)
  entity_id              VARCHAR(32)  md5 hash, not UUID
  legal_name             VARCHAR      canonical debtor_search_key (longest)
  total_filings          INTEGER
  active_filing_count    INTEGER      COUNT WHERE status='ACTIVE'
  risk_score             DOUBLE       weighted formula (active+volume+recency)
  risk_tier              VARCHAR      LOW | MEDIUM | HIGH | CRITICAL
  last_updated           TIMESTAMP

→ What belongs in spec.md: canonical field names, normalization
  algorithm, risk tier thresholds, acceptance scenarios, entity_id
  derivation rule.
→ What belongs in code comments: implementation details, SQL specifics,
  performance notes.
