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.
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.

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

---

1. Data Processing Principles

Five rules that apply to every data task:

Principle	What It Means
Pipeline thinking	Every pipeline is Extract → Transform → Load. Keep each stage as an independent, testable function.
Schema-first	Define expected columns, types, and constraints BEFORE writing transformation logic.
Defensive parsing	External data will have nulls, wrong types, extra columns, missing columns, and encoding issues. Assume all of these.
Idempotent operations	Running the same pipeline twice on the same input must produce the same output. Use upsert patterns, not blind inserts.
Fail fast, fail loud	Raise errors on invalid data immediately — silent failures produce wrong downstream results that are harder to debug.

---

2. Data Ingestion Patterns

Format-Specific Guidance

Format	Best For	Watch Out For
CSV	Simple tabular data, human-readable	Encoding (UTF-8 BOM), delimiter ambiguity, multiline values, inconsistent quoting
JSON	Nested structures, API responses	Large files (stream, don't load all at once), deeply nested objects, encoding
Parquet	Large analytical datasets, columnar queries	Requires library support, not human-readable, schema evolution
Excel	Business user handoffs	Multiple sheets, merged cells, formulas vs. values, date formatting
Database	Production system access	Connection pooling, query timeouts, use read replicas for analytics

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

Scenario	Pattern
Invalid records	Write to dead-letter table/file for manual review. Preserve every record for debugging.
Source unavailable	Retry with exponential backoff (1s, 2s, 4s). Alert after 3 failures.
Schema mismatch	Halt pipeline. Log expected vs. actual schema. Don't attempt partial loads.
Duplicate records	Use upsert (INSERT ON CONFLICT UPDATE) or deduplicate with window functions.

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:

Check	What It Validates	Example
Not null	Required fields have values	WHERE order_id IS NULL → 0 rows
Unique	No duplicates on key columns	COUNT(*) = COUNT(DISTINCT id)
Range	Numeric values within bounds	amount BETWEEN 0 AND 1,000,000
Categorical	Values in allowed set	status IN ('pending', 'active', 'closed')
Freshness	Data is recent enough	MAX(updated_at) > NOW() - INTERVAL '24 hours'
Row count	No unexpected data loss or explosion	Within ±10% of previous run
Referential	Foreign keys point to existing records	customer_id EXISTS IN customers

Quality Tool Integration

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

Stage	Tool	Purpose
Ingest	Great Expectations	Validate raw data against expectations before staging
Transform	dbt tests	Assert model-level quality (not_null, unique, relationships, custom SQL)
Production	Soda / Monte Carlo	Real-time monitoring, anomaly detection, SLA enforcement

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:

Metric	What to Report
Row count	Total records in dataset
Column inventory	Name, type, null count per column
Numeric summary	min, max, mean, median, std dev
Categorical summary	Unique values, top 5 most frequent
Time range	Earliest and latest timestamp
Data quality	Null percentage, duplicate percentage

Output Formats

Format	When to Use
Markdown tables	Inline reports, ≤50 rows, quick summaries
JSON	Programmatic consumption, API responses
CSV export	Handoff to spreadsheet users, large datasets
HTML + charts	Dashboards, visual reports (Chart.js, Mermaid diagrams)

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

Condition	Choose
Real-time insight required (sub-minute latency)	Streaming (Kafka + Flink, Spark Structured Streaming, or Kafka Streams depending on complexity)
Exactly-once semantics needed	Kafka transactional producers + Flink/Spark
Latency >1 min acceptable, volume >1TB/day	Distributed batch (Spark, Databricks)
Latency >1 min acceptable, volume <1TB/day	Single-node batch (SQL, Python, dbt)

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)

Latency Requirement	Framework	Complexity
Sub-100ms, complex stateful	Apache Flink	High (dedicated cluster)
Sub-second, existing Spark infra	Spark Structured Streaming	Medium
Sub-second, Kafka-centric	Kafka Streams (embedded library)	Low-Medium
Minutes acceptable	Batch with frequent scheduling	Low

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

Need	Choose
SQL analytics, BI dashboards, structured queries	Data warehouse (Snowflake, BigQuery, PostgreSQL)
ML training, unstructured data, large-scale storage	Data lake (S3/GCS + Parquet or Delta format)
Both SQL and ML needs	Lakehouse (Delta Lake, Apache Iceberg)
Real-time key-value lookups, caching	Redis, DynamoDB
Graph relationships	Neo4j, Neptune

Tool Selection

Category	Options
Orchestration	Airflow, Prefect, Dagster
Transformation	dbt, Spark, plain SQL
Streaming	Kafka, Kinesis, Pub/Sub
Quality	Great Expectations, dbt tests, Soda, custom validators
Monitoring	Prometheus, Grafana, Datadog, Monte Carlo
Local analysis	DuckDB (in-process SQL), Polars (fast DataFrame), pandas (exploration/ML)

Tool Decision Matrix

Factor	pandas	Polars	DuckDB
Best for	<1GB, exploration, ML prep	>1GB, batch ETL, performance	SQL analytics, ad-hoc queries
Execution	Single-threaded, eager	Multi-threaded Rust, lazy eval	Vectorized, auto disk spill
Speed (groupby/join)	Baseline	5-10x faster	Matches Polars on SQL-native
Memory	Full load into RAM	Streaming, lazy chains	Spill-to-disk for out-of-core
API style	DataFrame (imperative)	DataFrame (expression-based)	SQL-first
ML interop	Excellent (scikit-learn, etc.)	Good (.to_pandas())	Good (.fetchdf())
File format	CSV, JSON, Excel	CSV, Parquet, Arrow-native	CSV, Parquet, JSON, S3 direct

Decision rule:

Data size / workflow	Recommended tool
Small (<100MB), interactive exploration	pandas
Medium (100MB-10GB), batch transforms	Polars
SQL-first analytics, any size	DuckDB
Blended workflow	Polars transforms, DuckDB aggregations (zero-copy via Arrow)

See references/tools.md for full patterns and code examples.

---

7. Data Governance & PII

Data Classification

Level	Examples	Handling
Public	Aggregated metrics, public reports	No restrictions
Internal	Business KPIs, operational data	Access controls, no external sharing
Confidential	Customer data, financial records	Encryption at rest, column-level masking
Restricted	SSN, payment data, health records	Tokenization, row-level security, audit logging

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

Requirement	Engineering Pattern
Right to erasure	Soft delete → batch purge → propagate to downstream stores including data lake
Data minimization	Collect only necessary fields; TTL on non-essential data
Consent tracking	Consent event store with versioned preferences; consent-aware pipeline branches
Data portability	Standardized export endpoint (JSON/CSV) per user request

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

---

8. Companion Skills

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

Companion	When to Consult	Key Sections
dev-backend	Exposing data via API, response envelope shape, pagination	§5 API Response Contract, §2 Layered Architecture
dev-security	PII handling, data classification, access controls, audit logging	§1 Input Validation, §4 Secrets, §8 Pre-Flight
dev-testing	Pipeline validation, contract tests for data APIs, CI gates	§2 Backend & API Testing, §3 Contract Testing
dev-frontend	Downstream reporting/dashboard consumers, data format expectations	§8 Backend Contract & Security Alignment

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

---