dev-data

name: dev-data description: "Data engineering and analysis guide for orchestrated sub-agents. Data pipelines, ETL/ELT design, data quality validation, SQL optimization, and analysis patterns. Injected when role=data." metadata: { "keywords": ["dashboard-db", "sqlite", "audit-log-schema", "connector-data"]

}

Dev-Data — Data Engineering & Analysis Guide

Production-grade data engineering patterns for building reliable data systems.

C0/C1 work (small local patches): See dev §0.0 Work Classifier + §0.1 Patch Fast-Path before reading references.

When to Activate

• Building data pipelines or ETL/ELT processes
• Processing CSV, JSON, Parquet, or Excel files
• Writing SQL queries or designing database schemas
• Setting up data quality checks or validation
• Performing data analysis, aggregation, or reporting
• Choosing between batch and streaming architectures

External/current data evidence

For current external dataset contracts, source freshness, pipeline/tool version behavior, provider data API changes, or public benchmark/source claims, read the active search skill and follow its query-rewrite, source-fetch, and evidence-status rules. Use browser fetch/open/text/get-dom/snapshot only after candidate URLs exist and the claim needs browser-verifiable source evidence.

1. Data Processing Principles

Five rules that apply to every data task:

2. Data Ingestion Patterns

Format-Specific Guidance

Incremental Loading

For large or frequently updated data sources:

1. Use a watermark column (e.g., updated_at, id) to track the last processed record.
2. Store the watermark after successful load. On failure, restart from the last saved watermark.
3. Process in batches (tune based on source limits and memory), not all-at-once.
4. Validate row counts: loaded_rows should equal source_rows_since_watermark.

Schema Validation on Ingest

Before any transformation, validate incoming data:

✅ Check: Expected columns exist
✅ Check: Data types match (string, number, date, boolean)
✅ Check: Required fields are not null
✅ Check: Values are within expected ranges
✅ Check: No unexpected duplicate keys
❌ Fail: If any check fails, write to error log with row details. Don't silently drop.

3. ETL/ELT Pipeline Design

Layer Architecture

Rules:

• Keep staging immutable. Copy first, transform in a separate step — this enables replay and debugging.
• One transformation per step. Don't combine cleaning + joining + aggregating in one function. Chain separate steps.
• Incremental processing. Process only new/changed records when possible. Full reloads only when schema changes.

dbt Integration Patterns

When using dbt for transformations, follow the staging → intermediate → mart layer architecture:

Rules:

• Staging models: rename, cast, filter NULLs — no joins, no business logic
• Intermediate models: joins across staging, deduplication, business transforms
• Mart models: aggregations, final business entities consumed by BI/analytics
• Every model has a schema.yml with tests (not_null, unique, relationships, custom SQL).
• Run validation tests in CI and after significant changes — treat test failures as pipeline failures.
• Use dbt source freshness to monitor upstream data staleness

Error Handling in Pipelines

Orchestration Basics

When pipelines have multiple steps with dependencies:

• Define tasks as a DAG (Directed Acyclic Graph). Each task depends on its upstream tasks.
• Each task must be independently retryable. If step 3 fails, you restart step 3, not step 1.
• Set reasonable retries (2-3) with delay (5 min between attempts).
• Add timeout per task to prevent hung pipelines.
• Alert on failure: email, Slack, or monitoring dashboard.

4. Data Quality

Validation Checks

Run these after every pipeline step, not just at the end:

Quality Tool Integration

Use a layered quality strategy — different tools at different pipeline stages:

Validate data dimensions: completeness, uniqueness, range, format, referential integrity, freshness.

Rule: Run validation on every pipeline step — skipping "because the data looks fine" leads to silent downstream corruption.

Data Contracts

For datasets shared between teams, define a contract:

A data contract must include:

• name, owner, version
• schema: column name, type, nullability, uniqueness, allowed values
• SLA: freshness threshold, minimum completeness percentage
• consumers: list of downstream teams/systems

Changes to a contracted schema require versioning and consumer notification.

5. Analysis & Reporting

Always Start with Summary Statistics

Before any deep analysis, provide:

Output Formats

Statistical Reporting

When analysis involves statistics:

• State the method used and its assumptions.
• Report confidence intervals, not just point estimates.
• Visualize distributions (histograms, box plots), not just averages.
• Distinguish correlation from causation explicitly.

6. Architecture Decisions

Batch vs. Streaming

Default to batch. Streaming adds significant complexity in error handling, state management, and debugging. Only use streaming when latency requirements genuinely demand it.

Streaming Decision Tiers (heuristic guidance)

Kafka essentials for data engineers:

• Partition by expected throughput — avoid excessive partitions
• Use Schema Registry for backwards-compatible evolution
• Default to at-least-once delivery + idempotent consumers
• Use exactly-once only for financial/billing (transactional producers + consumers)
• Monitor consumer lag via Prometheus/Grafana

See references/streaming.md for Kafka configuration, CDC patterns, and windowing.

Storage Selection

Tool Selection

Tool Decision Matrix

Decision rule:

See references/tools.md for full patterns and code examples. See references/ml-pipeline.md for ML training pipelines, experiment tracking (MLflow 3.x), feature stores (Feast), and data versioning (DVC/Delta Lake).

7. Data Governance & PII

Data Classification

PII Handling Checklist

Before building any pipeline that touches PII:

• Classify all columns by sensitivity level
• Apply masking/tokenization for non-production environments (static masking)
• Implement dynamic masking for production queries (role-based)
• Set data retention TTL — don't keep PII longer than needed
• Support right-to-erasure (GDPR Article 17): cascading delete across all pipeline stages
• Log all PII access for audit trail
• Mask raw PII values before logs and traces — use structured logging with redaction

GDPR/CCPA Quick Reference

See references/governance.md for detailed implementation patterns, row-level security, and retention policies.

8. Query Performance Guidelines

• Every query that runs in production: EXPLAIN ANALYZE before deploy
• Slow query threshold: > 100ms for OLTP, > 5s for OLAP/analytics
• Index strategy: B-tree for equality/range, GIN for array/JSONB, GiST for geo
• Missing index detection: pg_stat_user_tables → seq_scan / idx_scan ratio
• Partition tables > 10M rows if query patterns allow time-range or hash partitioning
• Never SELECT * in production code — specify columns

For pipeline observability, follow the OpenTelemetry patterns in dev-backend/references/core/observability.md. Instrument pipeline stages as spans, data quality checks as events.

When pipeline errors surface through APIs, use the AppError taxonomy from dev-backend/SKILL.md §3. Map pipeline failures to appropriate HTTP status codes (422 for validation, 502 for upstream failures, 503 for capacity).

For data API patterns (pagination of large datasets, cursor-based access, streaming responses), see dev-backend/references/core/api-design.md.

9. Companion Skills

Data engineering does not exist in isolation. Cross-reference these skills when your pipeline connects to other systems:

Integration patterns:

• Data APIs serving frontend dashboards must use the standard response envelope (dev-backend §5)
• PII pipelines must classify columns and apply masking per dev-security guidance before this skill's §7 rules
• Data contract changes (§4 Data Contracts) must notify downstream consumers including frontend teams