Data Engineering

Data Engineering

Overview

Data engineering is the foundation of every data-driven decision. Bad data pipelines silently corrupt analytics, break ML models, and lead to business decisions based on false premises. This skill covers designing pipelines that are correct, observable, and resilient — from ingestion to serving.

The data engineering contract: Every pipeline must guarantee that what lands in the destination is what the source intended, or it must fail loudly. Silent data corruption is the worst failure mode.

When to Use

• Building ETL, ELT, or streaming data pipelines
• Designing data warehouse schemas (star, snowflake, data vault)
• Migrating data between systems or formats
• Setting up data quality monitoring and anomaly detection
• Creating CDC (change data capture) pipelines
• Building feature stores for machine learning
• Handling schema evolution without breaking consumers

NOT for:

• Simple one-off data exports (use a script)
• Real-time systems with sub-second latency requirements (use stream processing)
• Data science / analysis work (this skill is about moving and transforming data, not interpreting it)

The Data Pipeline Process

Step 1: Define the Data Contract

Before writing any pipeline code, define what correctness means:

DATA CONTRACT:
├── Source: [system, table, API, file format]
├── Destination: [system, table, format]
├── Schema: [field names, types, nullability, defaults]
├── Volume: [records/day, peak throughput, growth rate]
├── Latency: [batch hourly / batch daily / streaming / near-real-time]
├── Quality rules: [uniqueness, referential integrity, range checks]
├── Retention: [how long to keep, compliance requirements]
└── SLA: [acceptable downtime, max lag, error rate threshold]

Schema definition example:

# data_contract.yaml
source:
  system: production_postgres
  table: orders
  
destination:
  system: snowflake
  schema: analytics
  table: fact_orders
  
schema:
  order_id: { type: BIGINT, nullable: false, unique: true }
  customer_id: { type: BIGINT, nullable: false }
  order_date: { type: TIMESTAMP, nullable: false }
  amount: { type: DECIMAL(10,2), nullable: false, min: 0 }
  status: { type: VARCHAR(20), nullable: false, enum: [pending, paid, shipped, cancelled] }
  
quality_rules:
  - column: order_id
    check: not_null
  - column: amount
    check: range
    min: 0
    max: 100000
  - table: fact_orders
    check: referential_integrity
    references: dim_customers.customer_id

Step 2: Choose the Right Pattern

WORKLOAD TYPE → PATTERN
─────────────────────────────────────────────────
Batch, hourly/daily         → Scheduled ETL (Airflow, Dagster, Prefect)
Streaming, = CURRENT_DATE - INTERVAL '7 days'
  GROUP BY 1
),
warehouse_counts AS (
  SELECT DATE(order_date) as dt, COUNT(*) as cnt
  FROM analytics.fact_orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '7 days'
  GROUP BY 1
)
SELECT 
  s.dt,
  s.cnt as source_count,
  w.cnt as warehouse_count,
  ABS(s.cnt - w.cnt) as diff,
  CASE 
    WHEN ABS(s.cnt - w.cnt) > (s.cnt * 0.01) THEN 'ALERT'
    ELSE 'OK'
  END as status
FROM source_counts s
LEFT JOIN warehouse_counts w ON s.dt = w.dt
ORDER BY s.dt DESC;

Common Rationalizations

| Rationalization | Reality | |---|---| | "We'll fix data quality issues downstream" | Downstream fixes are expensive and error-prone. Fix at the source or at ingestion. Every transformation step that receives dirty data compounds the problem. | | "Schema changes are rare" | They happen constantly — new features, new tracking, new regulations. Not having a schema evolution strategy means fire drills. | | "Our data is small, we don't need this" | Small data grows. Building quality gates when you have 1M rows is easier than retrofitting them at 1B rows. | | "The pipeline works, that's enough" | "Works" means nothing without data quality validation. A pipeline that loads 100% of rows with 50% nulls in key fields is broken. | | "We'll add observability later" | You need observability to debug the first failure. Add it when you build the pipeline, not after it breaks. |

Red Flags

• No data quality checks in the pipeline
• Schema changes applied directly without compatibility testing
• Pipelines running without monitoring or alerting
• "We think the data is correct" instead of "We've proven the data is correct"
• Manual fixes to production data without logging the change
• No reconciliation between source and destination
• Data consumers discovering schema changes before pipeline owners
• Pipelines with no retry logic or dead letter queues

Verification

Before a pipeline goes to production:

• [ ] Data contract is documented and agreed upon by consumers
• [ ] Schema is versioned and registered
• [ ] Quality gates validate all five dimensions (completeness, accuracy, consistency, timeliness, uniqueness)
• [ ] Pipeline is idempotent (rerunning doesn't create duplicates)
• [ ] Failure modes are handled (retry, circuit breaker, dead letter queue)
• [ ] Metrics are emitted and dashboards exist
• [ ] Reconciliation query runs daily and alerts on mismatch
• [ ] Schema evolution strategy is documented
• [ ] Rollback procedure exists for bad loads

See Also

• For testing data pipelines, follow test-driven-development
• For handling failures, use debugging-and-error-recovery
• For monitoring and alerting, see observability-and-instrumentation

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: borhen68
• Source: borhen68/SkillEngine
• License: MIT

Install and usage instructions live in the source repository linked above.