Data engineering: applied workshop

Data models and grain

Use data models and grain to make decisions inspectable.

Define keys, fields and relations for orders and order lines.

Teaching example: One row per order differs from one row per item.. Predict the result, independently check the key step, and compare these assumptions with the actual role. Do not use demonstration results as real-world advice.

SQL joins and constraints

Use sql joins and constraints to make decisions inspectable.

Check primary keys, foreign keys and join results on example tables.

Teaching example: Missing foreign-key matches need explicit handling.. Predict the result, independently check the key step, and compare these assumptions with the actual role. Do not use demonstration results as real-world advice.

Extract, transform and load

Use extract, transform and load to make decisions inspectable.

Map a source, transformation and destination table.

Teaching example: Retaining raw inputs helps trace transformation differences.. Predict the result, independently check the key step, and compare these assumptions with the actual role. Do not use demonstration results as real-world advice.

Data quality rules

Use data quality rules to make decisions inspectable.

Specify null, uniqueness, range and total checks.

Teaching example: Duplicate order IDs may require rejection or deduplication.. Predict the result, independently check the key step, and compare these assumptions with the actual role. Do not use demonstration results as real-world advice.

Incremental loads and idempotency

Use incremental loads and idempotency to make decisions inspectable.

Design a rerun that does not duplicate the same batch.

Teaching example: Stable-key updates avoid unconditional duplicate appends.. Predict the result, independently check the key step, and compare these assumptions with the actual role. Do not use demonstration results as real-world advice.

Logs and lineage

Use logs and lineage to make decisions inspectable.

Log input rows, output rows and errors for each batch.

Teaching example: 500 input = 490 successful + 10 failed should reconcile.. Predict the result, independently check the key step, and compare these assumptions with the actual role. Do not use demonstration results as real-world advice.

Delivery and operations notes

Use delivery and operations notes to make decisions inspectable.

Document schema, refresh frequency, reruns and failure recovery.

Teaching example: Data refresh time differs from event time.. Predict the result, independently check the key step, and compare these assumptions with the actual role. Do not use demonstration results as real-world advice.

Worked example · fictional

From a problem to inspectable work

Synthetic batch: 1000 rows, 20 duplicates and ten invalid, with five in both sets. Keep rows that are neither duplicates nor invalid.

1. Exclude the union 20+10−5=25; output 975. Do not subtract overlapping exclusions twice.
2. Further judgment: How avoid duplicates when rerunning after failure?
3. Use stable business keys, idempotent writes and consistent checkpoints

Deliver pipeline design and quality checklist: the calculation reference is 975; the recommendation must respect Use stable business keys, idempotent writes and consistent checkpoints. Separate numbers, inference and unknowns.

Failure case and repair

In Data quality rules, copy tool output without definitions, then retain the conclusion when asked: How avoid duplicates when rerunning after failure?

Even a result of 975 does not explain when Use stable business keys, idempotent writes and consistent checkpoints is supported.

Exclude the union 20+10−5=25; output 975. Do not subtract overlapping exclusions twice. Then test assumptions in Logs and lineage.