Manual weekly checks, errors found too late
A mid-scale mining & logistics operation managed daily vessel berthing and cargo data entirely through manual spreadsheet entry. Data quality checks were conducted weekly by hand — a process taking approximately four hours per cycle, prone to reviewer fatigue, limited to format-level checks, and lacking any statistical baseline or audit trail. New errors only became visible after reports had already reached management.
How an error travelled before the system existed
Four problems with the old way
Every weekly check took about half a working day.
Repetitive manual review is prone to missed errors.
Checks stopped at format; logic and anomalies went unchecked.
No statistical reference for “normal”, and no record of what was checked.
A system that checks the data on its own
Designing and building a rule-based data quality system capable of:
Seven steps, from rulebook to dashboard
From rulebook to dashboard
How the automated data quality program was built, step by step. Tap a step to jump to it.
All numbers and findings are computed by rule-based logic — AI is used only for readable summaries, eliminating hallucination risk on the data itself.
Reference lists and business thresholds live in an external master-data source, so non-technical updates don’t require code changes.
Metrics that naturally scale with an uncontrolled factor (e.g. duration) are compared as normalized ratios, not raw totals — avoiding false positives from bias.
Compiling a tiered validation rulebook
A tiered rulebook (Critical / High / Info severity) covering data completeness, chronological order, cross-column reconciliation, and reference data integrity — 80+ rules in total, developed rule by rule with domain stakeholders before a single line of code was written.
Three severity levels
Four kinds of checks
Building a statistical outlier detection layer
Using the 3-sigma method, segmented by operational context (e.g., terminal × cargo type × vessel class) instead of fixed thresholds.
In plain words: a value is flagged when it sits more than three standard deviations from the average of comparable records, so “normal” is learned from the data, not set by hand.
The 3-sigma band
Each segment gets its own baseline (example from the article)
Refining the statistical model through tuning iterations
Including fixing a real bias where downtime metrics naturally scaled with operation duration; by restating it as a ratio against total time, false positives were eliminated without losing detection sensitivity.
Try it: switch the metric
Concept illustration with made-up numbers, not production data. The 3-sigma line is calculated live from the points shown.
Externalizing all reference data and thresholds
Into a master data workbook, eliminating hardcoded business logic so non-technical stakeholders can adjust rules without code changes.
Orchestrating the entire pipeline
Within a low-code automation platform: scheduled daily trigger → rule engine execution → AI-assisted narrative summary (with deterministic fallback for resilience when AI capacity is unavailable) → severity-based email alerts → growing audit log (append-only).
Tap each stage
Expanding the architecture to a second data domain
Designing a scalable data model (append-only run history + constantly refreshed findings details) so the same pipeline pattern could serve a growing portfolio of datasets without redesign.
Designing and building a two-tiered Power BI dashboard
An executive summary aggregating data readiness scores across monitored domains, and a per-domain drill-down view showing the most frequently failing rules, impacted records, and data quality trends over time.
Schematic of the two tiers, not a screenshot.
From four hours a week to zero
Reduced manual QA effort from ~4 hours/week to zero ongoing manual work.
Built an auditable and repeatable rule catalog that successfully uncovered systemic data issues previously unseen through manual spot-checks.
Produced a reusable blueprint that was successfully replicated to a second operational domain in a matter of days, not months.
Provided management with an immediate, comprehensive view of data quality trends across the portfolio — a previously nonexistent capability.
Impact summary
Three rules behind the build
Deterministic core, AI for narrative only
All numbers and findings are calculated by rule-based logic. AI is solely used to generate human-readable summaries, eliminating hallucination risks on the underlying data.
Numbers come from rules; AI only words them.
Zero hardcoded thresholds
Reference lists and business thresholds are stored in an external master data source, so operational adjustments do not require code changes or redeployments.
Statistically sound outliers
Metrics that naturally scale with uncontrolled factors are compared as normalized ratios, not raw numbers — avoiding bias against larger or longer-duration records.
Two questions, two layers
The dashboard was designed following a simple hierarchy of questions: is the portfolio healthy? (executive summary) followed by what exactly went wrong, and where? (per-dataset drill-down). The dataset scorecard pattern allows new domains to join the program just by adding a single row — rather than a redesign.
- Data readiness scores
- Across all monitored domains
- Most frequently failing rules
- Impacted records
- Data quality trends over time
A new domain is just one more row
The dataset scorecard pattern means a new domain joins the program by adding a single row, rather than through a redesign.
CO
Data Quality — Executive Summary
Portfolio view across all monitored operational datasets
| Dataset | Readiness | Crit | High | 30-day trend | Status |
|---|---|---|---|---|---|
Fleet OperationsVessel berthing & cargo | 96.2% | 22 | 38 | Live | |
Stockyard InventoryYard & material tracking | 48.4% | 36 | 108 | Live | |
Equipment UtilizationFleet burn-rate (planned) | — | — | — | no data yet | Planned |
Maintenance RecordsM&R records (planned) | — | — | — | no data yet | Planned |
Invoice RegisterBilling QC (planned) | — | — | — | no data yet | Planned |
Back to the weekly four hours
The data now gets checked every day, automatically.
80+ rules and a 3-sigma layer run on a daily schedule, findings go out as severity-based email alerts, every run is kept in an append-only audit log, and management sees the whole portfolio on one dashboard. The weekly manual check is no longer needed.