FIT5137 Chap.1 Data Engineering and Warehouse Architecture
Data Engineering and Warehouse Architecture
Data Engineering and Warehouse Architecture explains how operational records become reliable analytical data. The chapter begins with the decision to be supported, then works backwards through measures, grain, dimensions, source fields, transformations, controls and presentation.
It distinguishes operational databases, which capture current events, from warehouses, which preserve selected history for comparison and analysis. The central skill is traceability: every dashboard value should be followed through its warehouse row and transformation rule to an operational event. Layer boundaries matter because they localise defects and make corrections auditable.
A useful architecture does not merely name products; it specifies what each stage receives, changes, validates and publishes. The chapter also shows how late corrections, rejected rows and changing labels affect the pipeline, so the first successful load is treated as the start of engineering rather than proof that the design is complete.
What this chapter covers
- 01
Start with the business decision and analytical question
- 02
Separate operational capture from analytical storage
- 03
Define measures, grain and descriptive dimensions
- 04
Map source fields into transformation responsibilities
- 05
Preserve rejected rows and transformation lineage
- 06
Reconcile warehouse outputs to source control totals
- 07
Handle late corrections with repeatable reload rules
- 08
Publish results with scope, freshness and definitions
Worked example · free
Reconcile a maintenance-delay pipeline
- 2Begin with 125 extracted rows and identify 12 earlier snapshots using stable event identifiers plus status time.
- 2Archive the 12 superseded snapshots rather than counting them as separate delays. The candidate event set is 125 minus 12, which equals 113.
- 2Quarantine the 3 rows with negative duration because the value violates the stated domain and no correction is supported. Accepted events equal 113 minus 3, which equals 110.
- 1Reconcile all outcomes: 110 accepted plus 12 archived plus 3 rejected equals the original 125 rows.
- 1Report that analytical delay measures cover 110 accepted events and disclose the 3 rejected events separately.
Key terms
- Operational database
- A database organised to capture and update current business events reliably.
- Data warehouse
- An analytical store that integrates selected data and history for comparison and decision support.
- Transformation rule
- A documented operation that maps, cleans, derives or filters data between pipeline stages.
- Data lineage
- The traceable path from an analytical result through transformations to its originating record.
- Control total
- An independently calculated count or measure used to reconcile pipeline inputs and outputs.
- Late arrival
- A valid event or correction received after the related analytical period was first processed.
Data Engineering and Warehouse Architecture FAQ
Why should a warehouse start with a question?
The question determines the measure, event grain, dimensions, history and refresh timing that matter. Starting from available tables can create a technically neat warehouse that cannot answer the intended decision. Working backwards also exposes missing source evidence before implementation becomes expensive.
Is a warehouse just a copy of the operational database?
No. Operational systems organise data for current transactions and updates. A warehouse selects, cleans, integrates and reshapes data for historical analysis. It should preserve lineage to the operational event, but its fact and dimension structures reflect analytical grain rather than copying source tables unchanged.
What makes a transformation auditable?
An auditable transformation has declared inputs and outputs, a readable rule, affected-row evidence, exception handling, version or run context and reconciliation. A reviewer should be able to identify which records changed, why they changed and how the output still relates to the source population.
How should late corrections be handled?
Define a repeatable window or change-capture method, determine whether prior rows are replaced or versioned, and ensure reruns do not duplicate unchanged events. Reconcile the revised period and label its freshness. The policy must match the required historical interpretation.
Assessment move
Draw the architecture as responsibilities rather than products. For any scenario, write the question, measure, row grain, dimensions, source fields, transformation rules, controls and presentation in that order. Then trace one attribute from the report back to its operational origin. Practise failure cases: a renamed depot, a late event, a rejected duration and an incomplete source feed.
For each, identify the earliest layer that could prevent or expose the defect. Finish every design answer with a causal sentence explaining how the chosen architecture protects totals, history or traceability. Build a compact evidence ledger while revising. In one column record the claim a report makes; in the next record the warehouse object and rule supporting it; then add a control that could falsify the claim.
Rotate through count, duration, category and date examples so the method is not tied to one measure. Rehearse a first load and a correction load separately. The correction run should show which period is reopened, how superseded output is identified, why unchanged events remain single and how users learn that a result was restated. Finally, explain the architecture aloud without naming any software.
If the logic becomes impossible to describe once product names are removed, the design probably relies on labels instead of responsibilities. Close the session by writing a one-paragraph incident note for a failed control. Name the affected population, earliest trustworthy stage, temporary containment and evidence required before republishing.
This exercise connects architecture to operational judgement and exposes whether lineage is detailed enough to support a safe recovery. Compare that incident note with the normal pipeline diagram and mark every control that provided usable evidence. Missing controls become the next revision targets.
