role-data-engineer

Provides: Pipeline design workflow, grain and idempotency analysis, data quality check selection, and backfill planning.

Quick Reference

Golden Rule: Data jobs must be reproducible, observable, and safe to rerun

Default Stance:

• Define grain and source of truth before schema details
• Prefer idempotent writes and checkpointed progress
• Make data quality failures visible before consumers find them
• Treat backfills as production changes

Reference: skill:standards-database for schema, index, and migration mechanics

Design Workflow

1. Establish ownership

Name the source system, the owning team, the downstream consumers, the freshness expectation, and the retention period. A pipeline without a named consumer is a pipeline nobody will notice breaking.

2. Define the grain

State exactly what one row or one event represents. This is the single most common source of silent data bugs: a table whose grain is "one row per order" quietly becoming "one row per order line" doubles every revenue metric downstream.

Write the grain down in the schema documentation, then verify it with a uniqueness check on the key that expresses it.

3. Specify the schema

Decide keys, timestamps, nullability, enums, and versioning. Distinguish the timestamps explicitly:

• Event time: when the thing happened in the source system.
• Ingestion time: when the pipeline received it.
• Processing time: when the pipeline wrote it.

Late-arriving data makes these diverge, and aggregations built on the wrong one will silently drift.

4. Plan ingestion

Decide how the job handles ordering, deduplication, retries, checkpoints, and late data before writing transformation logic:

• What is the deduplication key, and is it stable across retries?
• Where does progress get checkpointed, and what happens on resume?
• How late can a record arrive and still be counted correctly?
• What happens to records that arrive out of order?

5. Add data quality checks

Cover the four categories that catch most real failures:

• Volume: row and event counts against an expected range, not just non-zero.
• Uniqueness: the grain key has no duplicates.
• Referential integrity: foreign keys resolve to existing dimension rows.
• Ranges and nullability: values fall inside plausible bounds; required fields are populated.

Decide per check whether a failure should block downstream consumers or only alert. Silently publishing bad data is worse than publishing nothing.

6. Plan backfill and replay

Before shipping, know how to reprocess history: the batch boundaries, the resume point, the expected runtime, and how the backfill interacts with the ongoing incremental job.

7. Add observability

Emit lag, volume, error rate, freshness, and cost per run. Freshness is the metric consumers actually care about, and it is the one most often missing.

Pipeline Review

• Ingestion is idempotent, or has a stable deduplication key
• Partial failure resumes without duplicating or losing records
• Partitioning matches both query patterns and backfill boundaries
• Late, missing, duplicated, and out-of-order records are handled explicitly
• Schema changes are additive or versioned, with a consumer migration plan
• PII is classified, with masking, retention, and access controls defined
• Reconciliation exists for financial, user-facing, or business-critical metrics

Backfill Safety

Treat a backfill like a production deploy, because it writes to production data.

1. Bound it. Process a date range or partition at a time, never the full history in one transaction.
2. Dry run. Compute the output for one partition and compare against the existing values before writing anything.
3. Make it resumable. Record which partitions completed so an interrupted run does not restart from zero.
4. Watch the load. A backfill competing with the incremental job for the same warehouse slots can delay fresh data for consumers.
5. Know the correction path. If the backfill writes wrong values, state in advance how to detect it and how to revert.

Do not run an unbounded backfill against a table that consumers are reading live without an explicit decision to accept that risk.

Checklist

Before finalizing data work:

• Grain, source of truth, freshness, and retention are explicit
• Idempotency, retries, checkpoints, and late data are handled
• Quality checks cover volume, uniqueness, ranges, and relationships
• Each quality check has a defined failure action: block or alert
• Backfills are bounded, resumable, observable, and have a correction plan
• Observability covers lag, volume, error rate, freshness, and cost

Integration with Other Skills

With standards-database: design schemas, indexes, constraints, and migrations.

With standards-observability: instrument pipeline metrics, logs, and alerting.

With role-site-reliability-engineer: assess production backfill risk and warehouse capacity.

With standards-security: handle PII classification, access control, retention, and data sharing.

With standards-api-design: design event payloads and schema versioning for published streams.