MUST USE for data engineering and analysis work — pipelines, ETL/ELT, data quality, SQL optimization, schema evolution, backfills, and reporting. Triggers: ETL, ELT, pipeline, data quality, SQL optimization, backfill, migration, schema drift, validation, batch vs streaming, dashboard-db, sqlite, audit-log-schema, connector-data, 데이터 파이프라인, 데이터 품질, 백필.
Production-grade data engineering patterns for building reliable data systems.
Activates by change surface for data pipelines, analytics, SQL-heavy work, schema evolution, backfills, and reporting.
C0/C1 work (small local patches): See jaw-dev §0.0 Work Classifier + §0.1 Patch Fast-Path before reading references.
jaw-dev is canonical:jaw-dev §0.2 Rule Classes, §3 Verification Gate, and §5 Safety Rules apply to all work governed by this skill.
When to Activate
Building data pipelines or ETL/ELT processes
Processing CSV, JSON, Parquet, or Excel files
Writing analytical SQL, warehouse/lakehouse queries, or transformation models
Setting up data quality checks or validation
Performing data analysis, aggregation, or reporting
Choosing between batch and streaming architectures
Do not activate for plain app CRUD SQL, OLTP query tuning, or transactional schema design. Route those to jaw-dev-backend/references/stacks/database.md. This skill owns analytics, ETL/ELT, pipelines, data quality, and reporting.
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 jaw-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.
Pre-Flight Checklist
Before delivering:
Input contract defined: source, schema, expected columns/types, and owner
Pipeline is idempotent and restartable from the last successful checkpoint
Data-quality checks cover nulls, uniqueness, ranges, freshness, and row counts
Volume and latency justify the chosen engine: pandas, Polars, DuckDB, SQL warehouse, Spark/Flink
Invalid records have a dead-letter/quarantine path with enough context to debug
PII/governance classification is complete or delegated to jaw-dev-security/§7
Output format and downstream contract are explicit
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 at pipeline boundaries immediately. Internal transforms propagate errors; dead-letter queues handle row-level quarantine at the boundary (see §3).
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:
Use a watermark column (e.g., updated_at, id) to track the last processed record.
Store the watermark after successful load. On failure, restart from the last saved watermark.
Process in batches (tune based on source limits and memory), not all-at-once.
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: Quarantine invalid rows under the dataset's access and retention policy; log redacted identifiers and diagnostics, never raw PII. 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
Engine landscape (verified 2026-07-02): dbt Core remains the default; dbt Fusion
is the separately-documented/licensed current engine (check its feature matrix and
license before adopting); SQLMesh is a credible active alternative with plan/apply
workflows. Choose per license posture and team workflow — do not assume Fusion pricing
without a primary source.
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
Quarantine under the dataset’s access/retention policy; retain only authorized evidence and log redacted diagnostics.
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
Changes to a contracted schema require versioning and consumer notification.
5. Analysis & Reporting
Migration and Backfill Sequencing (DATA-MIGRATION-01, DEFAULT)
Treat schema changes and data backfills as separate steps. Production evolution runs
expand → backfill → dual read/write where needed → contract.
Before declaring the migration complete, require three things: a dry run, an
idempotency proof, and reconciliation counts. Idempotency is the one that
matters most in practice — a backfill is the operation most likely to be interrupted and
resumed, so one that is not safe to re-run turns a retry into double-counted data.
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 (Kafka 4.x / KRaft era — no ZooKeeper):
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 (verified 2026-07-02)
Orchestration
Airflow 3.x (standalone DAG processor; SequentialExecutor removed), Prefect 3, Dagster
Lakehouse format: do NOT assume "Iceberg won" — Delta Lake and Apache Iceberg are both
active; choose by ecosystem (engine/vendor support, catalog, existing stack), not by
mindshare claims.
Tool Decision Matrix
Factor
pandas
Polars
DuckDB
Best for
<100MB, exploration, ML prep
>100MB, batch ETL, performance
SQL analytics, ad-hoc queries
Execution
Single-threaded, eager
Multi-threaded Rust, lazy eval
Vectorized, auto disk spill
Speed (groupby/join)
Benchmark on representative input
Depends on expressions, data, and execution mode
Depends on query plan, data, and memory
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 (HEURISTIC — size bands are guidance, not hard cutoffs):
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.
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
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. Query Performance Guidelines
Ownership note: this section covers analytical SQL, warehouse/lakehouse queries, and pipeline transforms. Plain app CRUD SQL, OLTP schema design, and transactional query tuning belong to jaw-dev-backend/references/stacks/database.md.
Start with non-executing EXPLAIN. EXPLAIN ANALYZE executes the statement, including writes and possible external effects; use only with authorized execution, isolated representative data, and a resource budget. Rollback does not prove absence of external side effects.
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 jaw-dev-backend/references/core/observability.md. Instrument pipeline stages as spans, data quality checks as events.
When pipeline errors surface through APIs, follow the existing API error contract (jaw-dev-backend/SKILL.md §3) and map 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 jaw-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:
Companion
When to Consult
Key Sections
jaw-dev-backend
Exposing data via API, response envelope shape, pagination
Pipeline validation, contract tests for data APIs, CI gates
§2 Backend & API Testing, §3 Contract Testing
jaw-dev-frontend
Downstream reporting/dashboard consumers, data format expectations
§15 Backend Contract & Security Alignment
Integration patterns:
Data APIs preserve existing and protocol-native contracts (jaw-dev-backend §5); do not wrap GraphQL, gRPC, SSE, or an established API to match a sample envelope
PII pipelines must classify columns and apply masking per jaw-dev-security guidance before this skill's §7 rules
Data contract changes (§4 Data Contracts) must notify downstream consumers including frontend teams
Data Change Review Checklist (DATA-REVIEW-01, DEFAULT)
Source: sol research (dev-skill reinforcement audit, Euler findings).
When reviewing or implementing changes that affect data pipelines, schemas,
or data stores, check these domain-specific concerns:
Schema Changes
Is the change backward-compatible? (additive fields, optional columns)
Are existing consumers updated or tolerant of the new schema?
Is there a migration path for existing data?
Are destructive changes (DROP, RENAME, type narrowing) reversible?
Is the schema change tested with representative production-scale data?
Pipeline Changes
Are late/out-of-order events handled correctly?
Is the pipeline idempotent for replays?
Are timezone/DST transitions handled (especially for daily aggregations)?
Is numeric precision preserved across transforms (float → decimal)?
Are nondeterministic transforms (sampling, shuffling) reproducible with seeds?
Quality Gates
Is there a before/after reconciliation report (row counts, checksums)?
Are null/missing value rates within expected bounds?
Are downstream consumers notified of schema or semantic changes?
Is the blast radius documented (which dashboards, models, exports break)?
Backfill Safety
Is the backfill cost estimated (compute, I/O, lock duration)?
Is there a rollback plan for partial backfill failure?
Are concurrent writes handled during backfill?
Is the backfill window documented and approved?
1---2name: jaw-dev-data3description: "MUST USE for data engineering and analysis work — pipelines, ETL/ELT, data quality, SQL optimization, schema evolution, backfills, and reporting. Triggers: ETL, ELT, pipeline, data quality, SQL optimization, backfill, migration, schema drift, validation, batch vs streaming, dashboard-db, sqlite, audit-log-schema, connector-data, 데이터 파이프라인, 데이터 품질, 백필."4metadata:5 source: https://skillmd.com/skills/lidge-jun/jaw-dev-data6 short-description: "Data pipelines, ETL/ELT design, quality validation, SQL optimization, and analysis."7 keywords: "ETL, pipeline, data quality, SQL optimization, backfill, streaming, data contracts, dashboard-db, sqlite"8 last-verified: "2026-07-02"9---1011# Dev-Data — Data Engineering & Analysis Guide1213Production-grade data engineering patterns for building reliable data systems.14Activates by change surface for data pipelines, analytics, SQL-heavy work, schema evolution, backfills, and reporting.1516> **C0/C1 work (small local patches):** See `jaw-dev` §0.0 Work Classifier + §0.1 Patch Fast-Path before reading references.1718> **`jaw-dev` is canonical:** `jaw-dev` §0.2 Rule Classes, §3 Verification Gate, and §5 Safety Rules apply to all work governed by this skill.1920## When to Activate2122- Building data pipelines or ETL/ELT processes23- Processing CSV, JSON, Parquet, or Excel files24- Writing analytical SQL, warehouse/lakehouse queries, or transformation models25- Setting up data quality checks or validation26- Performing data analysis, aggregation, or reporting27- Choosing between batch and streaming architectures2829**Do not activate for plain app CRUD SQL, OLTP query tuning, or transactional schema design.** Route those to `jaw-dev-backend/references/stacks/database.md`. This skill owns analytics, ETL/ELT, pipelines, data quality, and reporting.3031## External/current data evidence3233For current external dataset contracts, source freshness, pipeline/tool version34behavior, provider data API changes, or public benchmark/source claims, read the35active `jaw-search` skill and follow its query-rewrite, source-fetch, and36evidence-status rules. Use browser fetch/open/text/get-dom/snapshot only after37candidate URLs exist and the claim needs browser-verifiable source evidence.3839---4041## Pre-Flight Checklist4243Before delivering:44- [ ] Input contract defined: source, schema, expected columns/types, and owner45- [ ] Pipeline is idempotent and restartable from the last successful checkpoint46- [ ] Data-quality checks cover nulls, uniqueness, ranges, freshness, and row counts47- [ ] Volume and latency justify the chosen engine: pandas, Polars, DuckDB, SQL warehouse, Spark/Flink48- [ ] Invalid records have a dead-letter/quarantine path with enough context to debug49- [ ] PII/governance classification is complete or delegated to `jaw-dev-security`/§750- [ ] Output format and downstream contract are explicit5152---5354## 1. Data Processing Principles5556Five rules that apply to every data task:5758| Principle | What It Means |59|-----------|---------------|60| **Pipeline thinking** | Every pipeline is Extract → Transform → Load. Keep each stage as an independent, testable function. |61| **Schema-first** | Define expected columns, types, and constraints BEFORE writing transformation logic. |62| **Defensive parsing** | External data will have nulls, wrong types, extra columns, missing columns, and encoding issues. Assume all of these. |63| **Idempotent operations** | Running the same pipeline twice on the same input must produce the same output. Use upsert patterns, not blind inserts. |64| **Fail fast, fail loud** | Raise errors at pipeline boundaries immediately. Internal transforms propagate errors; dead-letter queues handle row-level quarantine at the boundary (see §3). |6566---6768## 2. Data Ingestion Patterns6970### Format-Specific Guidance7172| Format | Best For | Watch Out For |73|--------|----------|---------------|74| **CSV** | Simple tabular data, human-readable | Encoding (UTF-8 BOM), delimiter ambiguity, multiline values, inconsistent quoting |75| **JSON** | Nested structures, API responses | Large files (stream, don't load all at once), deeply nested objects, encoding |76| **Parquet** | Large analytical datasets, columnar queries | Requires library support, not human-readable, schema evolution |77| **Excel** | Business user handoffs | Multiple sheets, merged cells, formulas vs. values, date formatting |78| **Database** | Production system access | Connection pooling, query timeouts, use read replicas for analytics |7980### Incremental Loading8182For large or frequently updated data sources:83841. Use a **watermark column** (e.g., `updated_at`, `id`) to track the last processed record.852. Store the watermark after successful load. On failure, restart from the last saved watermark.863. Process in batches (tune based on source limits and memory), not all-at-once.874. Validate row counts: `loaded_rows` should equal `source_rows_since_watermark`.8889### Schema Validation on Ingest9091Before any transformation, validate incoming data:9293```94✅ Check: Expected columns exist95✅ Check: Data types match (string, number, date, boolean)96✅ Check: Required fields are not null97✅ Check: Values are within expected ranges98✅ Check: No unexpected duplicate keys99❌ Fail: Quarantine invalid rows under the dataset's access and retention policy; log redacted identifiers and diagnostics, never raw PII. Don't silently drop.100```101102---103104## 3. ETL/ELT Pipeline Design105106### Layer Architecture107108**Rules:**109- **Keep staging immutable.** Copy first, transform in a separate step — this enables replay and debugging.110- **One transformation per step.** Don't combine cleaning + joining + aggregating in one function. Chain separate steps.111- **Incremental processing.** Process only new/changed records when possible. Full reloads only when schema changes.112113### dbt Integration Patterns114115Engine landscape (verified 2026-07-02): **dbt Core** remains the default; **dbt Fusion**116is the separately-documented/licensed current engine (check its feature matrix and117license before adopting); **SQLMesh** is a credible active alternative with plan/apply118workflows. Choose per license posture and team workflow — do not assume Fusion pricing119without a primary source.120121When using dbt for transformations, follow the **staging → intermediate → mart** layer architecture:122123**Rules:**124- **Staging models**: rename, cast, filter NULLs — no joins, no business logic125- **Intermediate models**: joins across staging, deduplication, business transforms126- **Mart models**: aggregations, final business entities consumed by BI/analytics127- Every model has a `schema.yml` with tests (not_null, unique, relationships, custom SQL).128- Run validation tests in CI and after significant changes — treat test failures as pipeline failures.129- Use `dbt source freshness` to monitor upstream data staleness130131### Error Handling in Pipelines132133| Scenario | Pattern |134|----------|---------|135| **Invalid records** | Quarantine under the dataset’s access/retention policy; retain only authorized evidence and log redacted diagnostics. |136| **Source unavailable** | Retry with exponential backoff (1s, 2s, 4s). Alert after 3 failures. |137| **Schema mismatch** | Halt pipeline. Log expected vs. actual schema. Don't attempt partial loads. |138| **Duplicate records** | Use upsert (INSERT ON CONFLICT UPDATE) or deduplicate with window functions. |139140### Orchestration Basics141142When pipelines have multiple steps with dependencies:143144- Define tasks as a **DAG** (Directed Acyclic Graph). Each task depends on its upstream tasks.145- Each task must be **independently retryable**. If step 3 fails, you restart step 3, not step 1.146- Set reasonable retries (2-3) with delay (5 min between attempts).147- Add timeout per task to prevent hung pipelines.148- Alert on failure: email, Slack, or monitoring dashboard.149150---151152## 4. Data Quality153154### Validation Checks155156Run these after every pipeline step, not just at the end:157158| Check | What It Validates | Example |159|-------|-------------------|---------|160| **Not null** | Required fields have values | `WHERE order_id IS NULL` → 0 rows |161| **Unique** | No duplicates on key columns | `COUNT(*) = COUNT(DISTINCT id)` |162| **Range** | Numeric values within bounds | `amount BETWEEN 0 AND 1,000,000` |163| **Categorical** | Values in allowed set | `status IN ('pending', 'active', 'closed')` |164| **Freshness** | Data is recent enough | `MAX(updated_at) > NOW() - INTERVAL '24 hours'` |165| **Row count** | No unexpected data loss or explosion | Within ±10% of previous run |166| **Referential** | Foreign keys point to existing records | `customer_id EXISTS IN customers` |167168### Quality Tool Integration169170Use a **layered quality strategy** — different tools at different pipeline stages:171172| Stage | Tool | Purpose |173|-------|------|---------|174| **Ingest** | Great Expectations | Validate raw data against expectations before staging |175| **Transform** | dbt tests | Assert model-level quality (not_null, unique, relationships, custom SQL) |176| **Production** | Soda / Monte Carlo | Real-time monitoring, anomaly detection, SLA enforcement |177178Validate data dimensions: completeness, uniqueness, range, format, referential integrity, freshness.179180**Rule:** Run validation on every pipeline step — skipping "because the data looks fine" leads to silent downstream corruption.181182### Data Contracts183184For datasets shared between teams, define a contract:185186A data contract must include:187- **name**, **owner**, **version**188- **schema**: column name, type, nullability, uniqueness, allowed values189- **SLA**: freshness threshold, minimum completeness percentage190- **consumers**: list of downstream teams/systems191192Changes to a contracted schema require **versioning and consumer notification**.193194---195196## 5. Analysis & Reporting197198### Migration and Backfill Sequencing (DATA-MIGRATION-01, DEFAULT)199200Treat schema changes and data backfills as **separate steps**. Production evolution runs201expand → backfill → dual read/write where needed → contract.202203Before declaring the migration complete, require three things: a **dry run**, an204**idempotency proof**, and **reconciliation counts**. Idempotency is the one that205matters most in practice — a backfill is the operation most likely to be interrupted and206resumed, so one that is not safe to re-run turns a retry into double-counted data.207208### Always Start with Summary Statistics209210Before any deep analysis, provide:211212| Metric | What to Report |213|--------|----------------|214| Row count | Total records in dataset |215| Column inventory | Name, type, null count per column |216| Numeric summary | min, max, mean, median, std dev |217| Categorical summary | Unique values, top 5 most frequent |218| Time range | Earliest and latest timestamp |219| Data quality | Null percentage, duplicate percentage |220221### Output Formats222223| Format | When to Use |224|--------|-------------|225| **Markdown tables** | Inline reports, ≤50 rows, quick summaries |226| **JSON** | Programmatic consumption, API responses |227| **CSV export** | Handoff to spreadsheet users, large datasets |228| **HTML + charts** | Dashboards, visual reports (Chart.js, Mermaid diagrams) |229230### Statistical Reporting231232When analysis involves statistics:233- State the method used and its assumptions.234- Report confidence intervals, not just point estimates.235- Visualize distributions (histograms, box plots), not just averages.236- Distinguish correlation from causation explicitly.237238---239240## 6. Architecture Decisions241242### Batch vs. Streaming243244| Condition | Choose |245|-----------|--------|246| Real-time insight required (sub-minute latency) | Streaming (Kafka + Flink, Spark Structured Streaming, or Kafka Streams depending on complexity) |247| Exactly-once semantics needed | Kafka transactional producers + Flink/Spark |248| Latency >1 min acceptable, volume >1TB/day | Distributed batch (Spark, Databricks) |249| Latency >1 min acceptable, volume <1TB/day | Single-node batch (SQL, Python, dbt) |250251**Default to batch.** Streaming adds significant complexity in error handling, state management, and debugging. Only use streaming when latency requirements genuinely demand it.252253### Streaming Decision Tiers (heuristic guidance)254255| Latency Requirement | Framework | Complexity |256|---------------------|-----------|------------|257| Sub-100ms, complex stateful | Apache Flink | High (dedicated cluster) |258| Sub-second, existing Spark infra | Spark Structured Streaming | Medium |259| Sub-second, Kafka-centric | Kafka Streams (embedded library) | Low-Medium |260| Minutes acceptable | Batch with frequent scheduling | Low |261262**Kafka essentials for data engineers (Kafka 4.x / KRaft era — no ZooKeeper):**263- Partition by expected throughput — avoid excessive partitions264- Use Schema Registry for backwards-compatible evolution265- Default to at-least-once delivery + idempotent consumers266- Use exactly-once only for financial/billing (transactional producers + consumers)267- Monitor consumer lag via Prometheus/Grafana268269See `references/streaming.md` for Kafka configuration, CDC patterns, and windowing.270271### Storage Selection272273| Need | Choose |274|------|--------|275| SQL analytics, BI dashboards, structured queries | Data warehouse (Snowflake, BigQuery, PostgreSQL) |276| ML training, unstructured data, large-scale storage | Data lake (S3/GCS + Parquet or Delta format) |277| Both SQL and ML needs | Lakehouse (Delta Lake, Apache Iceberg) |278| Real-time key-value lookups, caching | Redis, DynamoDB |279| Graph relationships | Neo4j, Neptune |280281### Tool Selection282283| Category | Options (verified 2026-07-02) |284|----------|---------|285| **Orchestration** | Airflow 3.x (standalone DAG processor; `SequentialExecutor` removed), Prefect 3, Dagster |286| **Transformation** | dbt Core / dbt Fusion / SQLMesh, Spark, plain SQL |287| **Streaming** | Kafka 4.x (KRaft), Kinesis, Pub/Sub |288| **Quality** | GX Core (Great Expectations' OSS library), dbt tests, Soda Core (data contracts), custom validators |289| **Monitoring** | Prometheus, Grafana, Datadog, Monte Carlo (data observability) |290| **Local analysis** | DuckDB (in-process SQL), Polars (fast DataFrame), pandas 3.x (exploration/ML) |291292Lakehouse format: do NOT assume "Iceberg won" — Delta Lake and Apache Iceberg are both293active; choose by ecosystem (engine/vendor support, catalog, existing stack), not by294mindshare claims.295296### Tool Decision Matrix297298| Factor | pandas | Polars | DuckDB |299|--------|--------|--------|--------|300| **Best for** | <100MB, exploration, ML prep | >100MB, batch ETL, performance | SQL analytics, ad-hoc queries |301| **Execution** | Single-threaded, eager | Multi-threaded Rust, lazy eval | Vectorized, auto disk spill |302| **Speed (groupby/join)** | Benchmark on representative input | Depends on expressions, data, and execution mode | Depends on query plan, data, and memory |303| **Memory** | Full load into RAM | Streaming, lazy chains | Spill-to-disk for out-of-core |304| **API style** | DataFrame (imperative) | DataFrame (expression-based) | SQL-first |305| **ML interop** | Excellent (scikit-learn, etc.) | Good (`.to_pandas()`) | Good (`.fetchdf()`) |306| **File format** | CSV, JSON, Excel | CSV, Parquet, Arrow-native | CSV, Parquet, JSON, S3 direct |307308**Decision rule (HEURISTIC — size bands are guidance, not hard cutoffs):**309310| Data size / workflow | Recommended tool |311|----------------------|------------------|312| Small (<100MB), interactive exploration | pandas |313| Medium (100MB-10GB), batch transforms | Polars |314| SQL-first analytics, any size | DuckDB |315| Blended workflow | Polars transforms, DuckDB aggregations (zero-copy via Arrow) |316317See `references/tools.md` for full patterns and code examples.318See `references/ml-pipeline.md` for ML training pipelines, experiment tracking (MLflow 3.x), feature stores (Feast), and data versioning (DVC/Delta Lake).319320---321322## 7. Data Governance & PII323324### Data Classification325326| Level | Examples | Handling |327|-------|----------|---------|328| **Public** | Aggregated metrics, public reports | No restrictions |329| **Internal** | Business KPIs, operational data | Access controls, no external sharing |330| **Confidential** | Customer data, financial records | Encryption at rest, column-level masking |331| **Restricted** | SSN, payment data, health records | Tokenization, row-level security, audit logging |332333### PII Handling Checklist334335Before building any pipeline that touches PII:336- [ ] Classify all columns by sensitivity level337- [ ] Apply masking/tokenization for non-production environments (static masking)338- [ ] Implement dynamic masking for production queries (role-based)339- [ ] Set data retention TTL — don't keep PII longer than needed340- [ ] Support right-to-erasure (GDPR Article 17): cascading delete across all pipeline stages341- [ ] Log all PII access for audit trail342- [ ] Mask raw PII values before logs and traces — use structured logging with redaction343344### GDPR/CCPA Quick Reference345346| Requirement | Engineering Pattern |347|-------------|---------------------|348| Right to erasure | Soft delete → batch purge → propagate to downstream stores including data lake |349| Data minimization | Collect only necessary fields; TTL on non-essential data |350| Consent tracking | Consent event store with versioned preferences; consent-aware pipeline branches |351| Data portability | Standardized export endpoint (JSON/CSV) per user request |352353See `references/governance.md` for detailed implementation patterns, row-level security, and retention policies.354355---356357## 8. Query Performance Guidelines358359Ownership note: this section covers analytical SQL, warehouse/lakehouse queries, and pipeline transforms. Plain app CRUD SQL, OLTP schema design, and transactional query tuning belong to `jaw-dev-backend/references/stacks/database.md`.360361- Start with non-executing EXPLAIN. EXPLAIN ANALYZE executes the statement, including writes and possible external effects; use only with authorized execution, isolated representative data, and a resource budget. Rollback does not prove absence of external side effects.362- Slow query threshold: > 100ms for OLTP, > 5s for OLAP/analytics363- Index strategy: B-tree for equality/range, GIN for array/JSONB, GiST for geo364- Missing index detection: `pg_stat_user_tables` → seq_scan / idx_scan ratio365- Partition tables > 10M rows if query patterns allow time-range or hash partitioning366- Never `SELECT *` in production code — specify columns367368For pipeline observability, follow the OpenTelemetry patterns in `jaw-dev-backend/references/core/observability.md`. Instrument pipeline stages as spans, data quality checks as events.369370When pipeline errors surface through APIs, follow the existing API error contract (`jaw-dev-backend/SKILL.md` §3) and map failures to appropriate HTTP status codes (422 for validation, 502 for upstream failures, 503 for capacity).371372For data API patterns (pagination of large datasets, cursor-based access, streaming responses), see `jaw-dev-backend/references/core/api-design.md`.373374---375376## 9. Companion Skills377378Data engineering does not exist in isolation. Cross-reference these skills when your pipeline connects to other systems:379380| Companion | When to Consult | Key Sections |381|-----------|-----------------|--------------|382| `jaw-dev-backend` | Exposing data via API, response envelope shape, pagination | §5 API Response Contract, §2 Layered Architecture |383| `jaw-dev-security` | PII handling, data classification, access controls, audit logging, input validation policy (per jaw-dev-security §10 ownership matrix) | §1 Input Validation, §4 Secrets, §8 Pre-Flight |384| `jaw-dev-testing` | Pipeline validation, contract tests for data APIs, CI gates | §2 Backend & API Testing, §3 Contract Testing |385| `jaw-dev-frontend` | Downstream reporting/dashboard consumers, data format expectations | §15 Backend Contract & Security Alignment |386387**Integration patterns:**388- Data APIs preserve existing and protocol-native contracts (`jaw-dev-backend` §5); do not wrap GraphQL, gRPC, SSE, or an established API to match a sample envelope389- PII pipelines must classify columns and apply masking per `jaw-dev-security` guidance before this skill's §7 rules390- Data contract changes (§4 Data Contracts) must notify downstream consumers including frontend teams391392---393## Data Change Review Checklist (DATA-REVIEW-01, DEFAULT)394395Source: sol research (dev-skill reinforcement audit, Euler findings).396397When reviewing or implementing changes that affect data pipelines, schemas,398or data stores, check these domain-specific concerns:399400### Schema Changes401- [ ] Is the change backward-compatible? (additive fields, optional columns)402- [ ] Are existing consumers updated or tolerant of the new schema?403- [ ] Is there a migration path for existing data?404- [ ] Are destructive changes (DROP, RENAME, type narrowing) reversible?405- [ ] Is the schema change tested with representative production-scale data?406407### Pipeline Changes408- [ ] Are late/out-of-order events handled correctly?409- [ ] Is the pipeline idempotent for replays?410- [ ] Are timezone/DST transitions handled (especially for daily aggregations)?411- [ ] Is numeric precision preserved across transforms (float → decimal)?412- [ ] Are nondeterministic transforms (sampling, shuffling) reproducible with seeds?413414### Quality Gates415- [ ] Is there a before/after reconciliation report (row counts, checksums)?416- [ ] Are null/missing value rates within expected bounds?417- [ ] Are downstream consumers notified of schema or semantic changes?418- [ ] Is the blast radius documented (which dashboards, models, exports break)?419420### Backfill Safety421- [ ] Is the backfill cost estimated (compute, I/O, lock duration)?422- [ ] Is there a rollback plan for partial backfill failure?423- [ ] Are concurrent writes handled during backfill?424- [ ] Is the backfill window documented and approved?
Run npx get-skill add lidge-jun/jaw-dev-data in your terminal (requires Node.js), paste this page's agent-chat prompt into Claude, Cursor, or any MCP-connected agent, or download the SKILL.md file and copy it into your agent's skills directory.
MUST USE for data engineering and analysis work — pipelines, ETL/ELT, data quality, SQL optimization, schema evolution, backfills, and reporting. Triggers: ETL, ELT, pipeline, data quality, SQL optimization, backfill, migration, schema drift, validation, batch vs streaming, dashboard-db, sqlite, audit-log-schema, connector-data, 데이터 파이프라인, 데이터 품질, 백필. It is listed under DevOps & Infra on SkillMD.
This skill has not completed SkillMD's automated safety review yet. Independent scanners report: SkillSpector: PASS, Skill Scanner: PASS. Capability flags: makes network calls. SkillMD never runs a skill's scripts for you; review the SKILL.md before installing.
This skill is tagged as working with Claude Code, Claude.ai, OpenAI Codex. SKILL.md is an open format, so most agents that read a skills directory can load it too.
Yes. Installing skills from SkillMD is free, and the skill stays under its author's original license.
lidge-jun (@lidge-jun) published this skill. Their other Agent Skills are listed on their SkillMD profile.