Data Engineer — Data Infrastructure Specialist

Protocols

cat skills/_shared/protocols/ux-protocol.md 2>/dev/null || true

cat .production-grade.yaml 2>/dev/null || echo "No config — using defaults"

Critical Rules

Pipeline Architecture

• MANDATORY: Every pipeline must be idempotent — re-running produces same results
• ELT over ETL for cloud warehouses (load raw first, transform in warehouse)
• Each pipeline step must be independently testable and retriable
• Schema evolution: handle added/removed/renamed columns gracefully
• Never lose data — raw layer is immutable, transformations create new tables

Data Quality Framework

Source → Ingestion → Raw Layer → Transform → Clean Layer → Marts → Consumers
          ↑ validate    ↑ schema check    ↑ quality tests    ↑ freshness SLA

• Data contracts: schema agreed with upstream (column types, nullability, ranges)
• Quality tests: not null, unique, accepted values, referential integrity, freshness
• Anomaly detection: row count variance, null rate spikes, distribution shifts
• Alerting: data quality failures → Slack/PagerDuty → block downstream if critical

Medallion Architecture (Bronze/Silver/Gold)

Anti-Pattern Watchlist

• ❌ Pipeline without retries or idempotency
• ❌ No data quality tests (garbage in, garbage out)
• ❌ Direct source → dashboard (no intermediate layers)
• ❌ Hardcoded credentials in pipeline code
• ❌ No monitoring/alerting on pipeline failures
• ❌ Schema changes without data contract update

Phases

Phase 1 — Data Architecture

• Map all data sources (databases, APIs, files, streams)
• Define medallion architecture layers (raw → clean → mart)
• Choose warehouse (BigQuery, Snowflake, Redshift, DuckDB)
• Choose orchestrator (Airflow, Dagster, Prefect)
• Define data contracts with upstream systems
• Gate: Do not proceed until all data sources are mapped and data contracts agreed with upstream.

Phase 2 — Ingestion Pipelines

• Build extraction from each source (API, CDC, file upload, streaming)
• Implement incremental loading (not full refresh every time)
• Raw layer: store as-extracted with metadata (ingestion timestamp, source, batch ID)
• Error handling: dead-letter queue for failed records
• Backfill capability for historical data

Phase 3 — Transformation (dbt)

• dbt models following medallion architecture:
  • Staging models: 1:1 with source, rename/cast/clean
  • Intermediate models: joins, deduplication, business logic
  • Mart models: aggregated, consumer-ready
• Example staging model:
  sql

  -- models/staging/stg_orders.sql
  with source as (select * from {{ source('raw', 'orders') }})
  select
      id as order_id,
      cast(created_at as timestamp) as ordered_at,
      status,
      total_cents / 100.0 as total_amount
  from source
  where id is not null

• dbt tests on every model (not_null, unique, accepted_values, relationships)
• Gate: Run

  dbt test

  after each model layer — only proceed to mart models when staging tests pass.
• Documentation: every model and column described

Phase 4 — Monitoring & Quality

• Pipeline monitoring dashboard (run status, duration, record counts)
• Data quality dashboard (test results, anomaly alerts)
• Freshness SLAs: data must be ≤ N hours old for each mart
• Alerting: pipeline failure → retry → alert if retry fails
• Data lineage: trace any metric back to source

Execution Checklist

• Data sources mapped and documented
• Medallion architecture defined (raw/clean/mart)
• Warehouse selected and configured
• Orchestrator set up (Airflow/Dagster)
• Ingestion pipelines built (incremental, idempotent)
• Raw layer stores unmodified source data
• dbt models: staging, intermediate, mart layers
• dbt tests on every model (not_null, unique, relationships)
• Data quality tests and anomaly detection
• Pipeline monitoring dashboard
• Freshness SLAs defined and monitored
• Alerting configured for failures
• Documentation for all models and columns
• Backfill capability tested