Data Quality, Cleansing and Master Data

Users forgive a slow dashboard. They never forgive one that was wrong in a board meeting, and they'll bring it up for years. A warehouse is only as trusted as its worst number, so data quality can't be a final polish. It's a design discipline. Know the ways data goes bad, check for them automatically on every load, and fix the root causes where you can. The related discipline of master data management answers the hardest quality question of all. Which of the five versions of this customer is the real one?

Six dimensions of data quality

Dimension	Question	Typical failure	Automated check
Completeness	Is anything missing?	8 percent of customers have no city	Null rate per column against a threshold
Accuracy	Is it true?	Unit price 0.00, quantity -3	Range rules; reconcile totals to the general ledger or bank
Consistency	Same thing, same value everywhere?	LHR, Lahore, lahore, Lahore City	Distinct value lists; cross-system comparisons
Uniqueness	Stored once?	Receipt TX-00981 loaded twice	COUNT vs COUNT DISTINCT of the business key
Validity	Correct format and allowed values?	Phone without leading 0, CNIC with 12 digits	Regex, lookup lists, type casts
Timeliness	Fresh enough?	Last POS file is two days old	Freshness SLA per source; alert when late

Where cleansing happens

Cleansing lives in the transform step between staging and core (Lesson 3). Staging keeps the ugly original so you can prove what the source sent. The core holds the cleaned value. A small rejects or quarantine table holds rows that failed rules, with the reason. Never silently drop a row. Never "fix" data in the source without telling its owners. And prefer fixing the source (a mandatory city field in the POS) over fixing the symptom forever in the pipeline.

Typical cleansing operations, in the order they usually run:

1. Standardise. Trim, upper/lower case, remove non-digits from phone numbers, normalise date formats.
2. Validate. Apply format and range rules; route failures to quarantine.
3. Deduplicate. Keep one row per business key, choosing the latest or most complete.
4. Conform. Map source codes to warehouse codes via mapping tables (LHR-DHA to store_key 17; "Lawn" and "LAWN SUIT" to "Lawn suit").
5. Enrich. Add derived values (city from postcode, fiscal period from date).

Quality also needs owners. A data steward on the business side (the merchandising manager for products, the CRM lead for customers) decides what a valid value is and resolves the cases the rules can't. The warehouse team builds the checks and reports the scorecard. It shouldn't be inventing business rules alone at 3 am when a load fails, and we say that as people who have done exactly that. Agree thresholds in advance ("up to 2 percent invalid phones is acceptable, any duplicate receipt is not") and a quality failure becomes a procedure instead of an argument.

Quality checks as SQL

-- A checks table the orchestrator reads after every load
CREATE TABLE ops.dq_results (
  run_date DATE, check_name TEXT, actual NUMERIC, threshold NUMERIC, passed BOOLEAN
);

-- 1. Uniqueness: duplicate receipt lines in staging
INSERT INTO ops.dq_results
SELECT CURRENT_DATE, 'stg_pos_sales_duplicates',
       COUNT(*) - COUNT(DISTINCT (txn_no, line_no)), 0,
       COUNT(*) = COUNT(DISTINCT (txn_no, line_no))
FROM stg.pos_sales;

-- 2. Validity: Pakistani mobile numbers (03xx-xxxxxxx after cleaning)
INSERT INTO ops.dq_results
SELECT CURRENT_DATE, 'invalid_phone_pct',
       ROUND(100.0 * SUM(CASE WHEN REGEXP_REPLACE(cust_ph, '\D', '', 'g') !~ '^03[0-9]{9}$'
                              THEN 1 ELSE 0 END) / COUNT(*), 2),
       2.0,
       100.0 * SUM(CASE WHEN REGEXP_REPLACE(cust_ph, '\D', '', 'g') !~ '^03[0-9]{9}$'
                        THEN 1 ELSE 0 END) / COUNT(*) <= 2.0
FROM stg.pos_sales WHERE cust_ph IS NOT NULL;

-- 3. Accuracy: warehouse total must match the POS day-end report within 1 rupee
INSERT INTO ops.dq_results
SELECT CURRENT_DATE, 'sales_total_vs_pos',
       ABS(w.total - p.total), 1,
       ABS(w.total - p.total) <= 1
FROM (SELECT SUM(net_amount) AS total FROM dw.fact_sales WHERE date_key = 20250814) w,
     (SELECT SUM(REPLACE(amt, ',', '')::NUMERIC) AS total FROM stg.pos_sales
      WHERE TO_DATE(dt, 'DD/MM/YYYY HH24:MI') = DATE '2025-08-14') p;

-- 4. Timeliness: newest staged row must be from yesterday or today
INSERT INTO ops.dq_results
SELECT CURRENT_DATE, 'pos_freshness_days',
       CURRENT_DATE - MAX(_loaded_at::DATE), 1,
       CURRENT_DATE - MAX(_loaded_at::DATE) <= 1
FROM stg.pos_sales;

-- Orchestrator: stop the BI refresh if anything failed today
SELECT COUNT(*) AS failures FROM ops.dq_results
WHERE run_date = CURRENT_DATE AND NOT passed;

Tools such as Great Expectations, dbt tests and Soda wrap this pattern in configuration, but the idea is the same. Every load produces a scorecard, and a failed critical check blocks publication instead of letting a wrong number reach a dashboard. Check number 3, the reconciliation to the POS day-end report, is the one we'd keep if we could only keep one.

Master data and the golden record

Master data is the slowly changing reference data shared across the business. Customers, products, suppliers, employees, locations. Each system holds its own copy with its own key and its own mistakes. Master data management (MDM) builds one golden record per real-world entity:

• Matching. Decide which records are the same person. Deterministic rules first (same CNIC, same normalised phone), then fuzzy rules (similar name plus same city plus similar email). Every match rule has a confidence, and low-confidence pairs go to a human steward.
• Survivorship. Decide which value wins for each attribute. Most recent address, CRM as the trusted source for CNIC, keep the first email seen, longest name string.
• Cross-reference. Store the mapping from every source key to the master ID, so facts from any channel roll up to the same customer.

The master ID becomes the natural key of the conformed dimension (Lesson 9). Without MDM, "how many customers do we have?" has as many answers as you have systems.

31,000 customers who were really 18,400: a Daraz seller in Lahore with a Shopify store, a WhatsApp order book and a Daraz shop counted 31,000 customers. After phone-number normalisation (strip +92, spaces and dashes, add the leading 0) and CNIC matching for cash-on-delivery customers, the golden record count was 18,400. Repeat-purchase rate jumped from a reported 12 percent to a real 34 percent, which moved the marketing budget from acquisition ads to a loyalty scheme. The phone rule alone did most of the work, and it's the first thing we'd write on any Pakistani customer dataset.

What we'd want you to remember

Check completeness, accuracy, consistency, uniqueness, validity and timeliness on every load, automatically. Cleanse between staging and core, keep the originals, quarantine failures with a reason, and never drop rows silently. Reconcile totals to an independent source and block publication when a critical check fails. Fix root causes in the source where you can. MDM builds one golden record per entity through matching, survivorship and cross-reference, and its ID keys the conformed dimension.

Tasks for this week

1. Write the phone-number standardisation as a single SQL expression that turns "+92 300-1234567", "0300 1234567" and "3001234567" into "03001234567".
2. For a school register with columns (name, father_name, dob, class, phone), propose two deterministic match rules and one fuzzy rule for finding duplicate students.
3. Design three DQ checks for a fact_bills table at K-Electric (units, amount, due_date, consumer_no) and state the threshold and severity of each.