A pipeline is a responsibility, not just a transfer
Imagine receiving a daily order file from a fictional shop. Moving it into a database is a start. A dependable process also asks whether the file arrived, whether the columns changed, whether an order appeared twice and whether a failed run can be retried without duplicating records.
Data engineers build and maintain these paths between sources and usable datasets. Their work connects producers of information with analysts, applications and other consumers. A successful file copy does not establish that the data is complete or ready for a decision.
Understand records and meaning before adding orchestration
Foundations include SQL, tables, keys, data types and how records relate. Python can help read, transform and inspect data. A data model explains what a row represents: an order, an order line or an event. Confusing those grains can multiply totals when tables are joined.
TWA’s existing direction includes relational databases, modelling, Python, ETL/ELT concepts, quality checks, documentation and cloud storage/warehouse concepts. Exact database, orchestration or cloud products are not confirmed. Apache Airflow’s documentation is industry reading about task dependencies and workflow execution, not a promise that Airflow is included.
Make a retry safe for the sales report.
A shop sends orders A and B to a reporting table. A connection interruption means the same batch may be delivered again.
Your role: Run the same orders twice, inspect the repeated copies, then use stable order labels to prevent duplication in a clean reporting table.
What you’ll learn: Keep a repeated delivery from becoming duplicate sales in a report.
No account needed. Nothing is sent or saved. You can restart at any point.
Read the full guided story without the interactive controls
You can follow the same lesson here without JavaScript.
Load the first batch
Copy orders A=120 and B=80 into the empty reporting table.
What happens and why: Two rows produce a sum of 200 units. Copying received rows to the end of a table is called appending.
Retry the same delivery
Run the same batch again and compare the reporting rows and sum.
What happens and why: Appending the retry creates A, B, A, B and a sum of 400. These are repeated records of the same orders, not new sales.
Design a destination with a stable key
Start a clean destination with order ID as its key. This explicitly rebuilds the toy table; it does not magically repair an existing production table.
What happens and why: An order ID identifies the business record across retries. A run number identifies the attempt, so using it as the key would not recognise a repeated order.
Rerun against the keyed destination
Load and retry using “insert new IDs; update existing IDs.” Watch whether the result changes.
What happens and why: Two rows and 200 units remain after repeated loads. Upsert means insert or update by a defined key. Idempotency means repeating an operation has the same effect as doing it once in this bounded case.
Takeaway: Retries are normal. Define business identity and write semantics so a repeated delivery does not become a new sale. Cleaning existing duplicates and preserving history need separate designs.
Where this is useful: Useful in reporting pipelines, payment-event handling and reliable data ingestion.
A fictional educational example, not a confirmed TWA assignment, assessment or learner outcome. Simplified to explore one idea; real situations need context and authorised procedures.
Optional deeper exploration: open the original technical workspace
Want to explore the technical model?
The guided story above introduces the idea. This original workspace lets you try actions in different orders. It has its own reset and does not inherit your story choices.
- Append
- Add each received row to the end of a destination.
- Stable key
- An identifier that continues to refer to the same business record.
- Upsert
- Insert a new key or update an existing key.
- Idempotency
- Repeating an operation produces the same final effect as doing it once.
Retry without multiplying records.
Learning objective: Use a stable key to make a pipeline rerun idempotent.
A fictional batch contains orders A=120 and B=80. Network failure may cause the same batch to run twice.
Evidence desk
- Batch
- A:120, B:80
- Destination
- Initially empty
- Key
- order_id identifies the same business record
Your workspace
Worked explanation & takeaway — available without JavaScript
Appending twice creates four rows and doubles the reported sum to 400. Upserting by order_id leaves two records and a sum of 200 after either run. Stable keys and defined update semantics matter; this toy model does not implement late arrivals, transactions or history.
Fictional educational model. No live system, personal assessment or confirmed TWA assignment. All actions run locally in your browser.
A pipeline should explain the second run
The two scenarios reveal reliability decisions; no database or live file is connected.
- Ingest
Identify the source and record when it arrived.
- Transform
Apply documented types and field rules.
- Check
Test important expectations and isolate exceptions.
- Serve
Publish an understood dataset with a status.
Use the documented file/record identity and load rule to avoid silently duplicating business events. A replay should have a defined result.
Read both scenarios and explanations
The same file arrives twice
Use the documented file/record identity and load rule to avoid silently duplicating business events. A replay should have a defined result.
An order has no identifier
Treat the missing key as an exception. Record the row and decision rather than inventing an identifier without a justified rule.
An educational illustration using fictional situations. It is not a confirmed TWA assignment, live system, assessment or promise of a particular tool.
Reliability is visible in the failure path
In the illustration below, a source file arrives twice. A carefully defined identifier and loading rule can prevent a replay from becoming an extra sale. A late or invalid row needs an explicit decision: reject it for review, quarantine it or process it under a documented rule. Quietly dropping it hides a business problem.
Data tests can assert expectations about missing values, uniqueness or relationships. dbt’s documentation provides examples of such assertions. A passing check only supports the expectation that was tested; it does not prove that every business assumption is right. Keep the check, result and response understandable to the person relying on the output.
A replay-safe sample ingestion plan
Design a pipeline for a fictional daily order file. Document what should happen when the same file is received twice or a required identifier is missing.
What you could document
- A source/target data model and field dictionary
- A transformation outline in SQL, Python or pseudocode
- Quality checks for identifiers and expected values
- A rerun, failure-response and handover note
What makes the explanation useful?
A reviewer should understand why rerunning does not double-count records, where failed rows go and how a downstream user learns about a delay.
The learning plan determines actual activities and assessment. These deliverables are examples, not contractual curriculum outcomes.
Design for the next person who uses the data
A dataset handover should describe its fields, source, refresh expectation, quality checks and known limitations. It should also identify who responds when a run fails. An analyst should not have to guess whether a quiet dashboard means no activity or a stalled pipeline.
Deeper directions can include data operations, analytics engineering, warehouse design and distributed processing. Start with a small pipeline whose reruns and errors you can explain. Discuss your SQL/programming experience with TWA and compare this work with Data Analysis, where the emphasis is interpreting information rather than building its delivery path.