Data Pipeline — Airflow, dbt & Superset BI

Data Quality

Data cleaning, scenario by scenario

Thirteen distinct fault classes were injected into the source data — fourteen counting the regional date-format trap — and every one is handled in the dbt staging layer. Two principles govern all of it: the ground-truth manifest is only ever used to score the cleaning, never to perform it, and cleaning never destroys information — every correction leaves a was_* or is_* flag behind, so data quality itself is reportable on the dashboards.

• Duplicate sales — POS retries write the same sale twice (~0.5% of rows). Staging deduplicates on the natural key, keeping the latest batch's survivor, and a uniqueness test proves it stays fixed.
• Partial re-sends and outage days — one day was first delivered 60% complete, then re-sent in full; the later batch supersedes the first. Days that arrive late in bulk are flagged by the volume-anomaly monitor (arrival counts vs a trailing 28-day median).
• Late-arriving facts — roughly 2% of sales land one to three days after the event. Watermarked extraction picks them up whenever they arrive, and each row carries an is_late_arriving flag rather than being silently merged.
• Late-arriving dimensions — seasonal menu items and replacement staff appear in sales before their master record syncs. SCD2 effective-date joins resolve those early sales once the master lands, so no fact is orphaned.
• Slowly changing dimensions — menu price revisions, annual raises, and quarterly ingredient contracts become valid_from / valid_to version ranges, so historical margins are costed with the prices that were true at the time.
• Schema drift — the delivery_channel column only exists from October 2025, and a gift_card tender appears in January 2026. Typed, nullable pass-throughs admit the new values without breaking a single older model.
• Malformed values — totals arriving as strings with currency symbols and whitespace, null or inconsistently cased payment methods. Regex clean-up and typed casts yield a decimal total on every row, verified by a not-null test and an exact revenue reconciliation.
• Regional date formats — the legacy billing feed writes dates as DD-MM in India, MM/DD in New York, and DD/MM in Perth and London: genuinely ambiguous for any day up to the 12th. Dates are parsed by the store's country, never guessed from the value, and a dedicated test fails on any mixup.
• Timezone traps — two months of Perth sales carry naive local timestamps with no UTC at all. The pipeline reconstructs UTC from the store's fixed +08:00 offset — 7,281 rows repaired and verified against ground truth.
• Refunds and voids — statuses are normalised and only completed sales count as revenue, while refund and void counts still flow through to the daily marts, because they are business signal, not noise.
• Restatements — utility bills revised the following month and monthly stocktake adjustments: the latest revision wins, and a was_restated flag preserves the audit trail.
• Referential breaks — sales recorded against a just-delisted item (a stale POS cache) resolve through the dimension's previous version and are flagged, not dropped.
• Unit inconsistency — three months of Bangalore goods receipts reported produce in kilograms against gram-denominated masters. Quantities are normalised back to grams, with a was_kg_reported flag keeping the trail.
• Orphan goods receipts — emergency purchases with no purchase order behind them are tolerated by design, flagged, and excluded from supplier on-time metrics, where they would be meaningless.

Beneath the scenarios sit two cross-cutting rules: every staging model deduplicates on its natural key — the guard that makes crash-retry re-extraction safe — and every event is re-anchored to a canonical UTC + local + offset triple, with business dates on the store's local trading day. Seven of the scenarios are scored exactly against the manifest and pass in all five stores; the rest are either corrected with the trail preserved or deliberately surfaced instead of erased.