Data Pipeline

Data Pipeline Patterns

Architecture patterns and best practices for data pipeline design, with focus on the medallion architecture (Raw → Staging → Enriched) and declarative schema management.

Medallion Architecture

The three-layer pattern for organizing data warehouses:

• Raw / Bronze: Preserve source data exactly as received (append-only)
• Staging / Silver: Cleaned, deduplicated, conformed data
• Enriched / Gold: Business-ready, joined, aggregated data

See references/medallion-architecture.md for:

• Layer definitions and purposes
• Deduplication patterns
• View vs materialized table choices
• Temporal joins for slowly-changing dimensions
• Refresh strategies

YAML Schema Management

Prefer YAML files over inline SQL DDL or JSON for schema definitions:

Benefits:

• Version control friendly (line-by-line diffs)
• Human readable with comments
• Separation of concerns (schema vs infrastructure)
• Consistent across upload scripts, docs, and IaC

See references/yaml-schema-patterns.md for:

• YAML structure examples
• Terraform integration via jsonencode(yamldecode(file()))
• Schema evolution patterns (safe vs unsafe changes)
• Field naming conventions
• Deprecation workflow
• Multi-source schema strategies

Quick Reference

Layer Selection

Schema Change Safety

Common Gotchas

• Deduplication: Always use ROW_NUMBER() window functions in Staging layer, not DISTINCT (DISTINCT is non-deterministic for ties)
• Field order: When using Terraform jsonencode(yamldecode()) for BigQuery, field order in YAML must match BQ (see opentofu skill)
• Extraction idempotency: Make extract scripts safe to re-run (upsert logic, deduplication in Raw layer)