Data Quality Dimensions

The previous lesson measured which records the source leaves outside, and accepted without question the records let inside. This lesson removes that assumption: the row that enters the record can itself be flawed. A reading may be physically impossible, a field may be left empty, two fields may contradict each other, a record may have been written months ago.

The shared name of these four problems is data quality, and it cannot be reduced to a single number. Each one asks for a separate rule, a separate threshold, and a separate number. The measure of the lesson has two layers: a number for each dimension, and how many rows are eliminated when the threshold changes. The second layer is more important than the first, because a cleaned set is a smaller and different set.

• SD15. The set is the 1,400-row subscriber record of the previous lessons, and this lesson adds flaws to it: corrupted readings, empty fields, and a stale tier field. The flaws are drawn after the base fields; the base set does not change. The set is fictional; the model’s true value is 21.31 m³.
• SD16. Accuracy is whether the reading gives the truth; here only physically impossible values can be caught. Completeness is the required fields being filled in.
• SD17. Consistency is two fields agreeing with each other: the tariff tier stored separately in the record must be the same as the tier computed from the reading. Timeliness is the number of days that have passed since the last reading.
• SD18. The accuracy threshold is 120 m³ a month for one meter; this value is given to the model as input. A floating-point reading is tested against a threshold, not for equality, because the measurement is truncated at two decimal places and the result of a comparison with an upper bound should not depend on equality.

Four Dimensions, Four Numbers

// quality.mjs — the same set, this time four quality dimensions are measured. IT IS A MODEL:
// the corruptions are drawn AFTER the base fields, the base set does not change.
export const SEED = 20260218, RECORD_COUNT = 1400, TARIFF = [10, 25, 40], UPPER_LIMIT = 120;
const mix = (x) => { x = Math.imul(x ^ (x >>> 16), 2246822507);
  x = Math.imul(x ^ (x >>> 13), 3266489909); return (x ^ (x >>> 16)) >>> 0; };
const rng = (t) => { let s = mix(t);
  return () => (s = (s * 1664525 + 1013904223) >>> 0) / 4294967296; };
const pick = (u, w) => { let t = 0;
  for (let i = 0; i < w.length; i++) { t += w[i]; if (u < t) return i; } return w.length - 1; };
export const REGION = [
  { name: "north", share: 0.28, base: 21, sat: [0.05, 0.10, 0.25, 0.40, 0.20] },
  { name: "south", share: 0.22, base: 17, sat: [0.02, 0.08, 0.30, 0.45, 0.15] },
  { name: "east", share: 0.18, base: 26, sat: [0.20, 0.08, 0.05, 0.12, 0.55] },
  { name: "west", share: 0.14, base: 14, sat: [0.10, 0.15, 0.25, 0.30, 0.20] },
  { name: "central", share: 0.18, base: 23, sat: [0.06, 0.12, 0.28, 0.34, 0.20] },
];
export const tierOf = (v) => (v < TARIFF[0] ? 1 : v < TARIFF[1] ? 2
  : v < TARIFF[2] ? 3 : 4);
export const RECORDS = Array.from({ length: RECORD_COUNT }, (_, i) => {
  const r = rng(SEED + i);
  const b = REGION[pick(r(), REGION.map((x) => x.share))];
  const t = Math.max(1.2, b.base * Math.exp((r() + r() + r() - 1.5) * 0.62));
  const truth = Math.round(t * 100) / 100;
  const meters = pick(r(), [0.62, 0.24, 0.10, 0.04]) + 1;
  const visits = pick(r(), [0.38, 0.32, 0.16, 0.08, 0.04, 0.02]);
  const satisfaction = pick(r(), b.sat) + 1;
  const temperature = Math.round((6 + r() * 22) * 10) / 10;
  const elapsed = Math.floor(Math.pow(r(), 1.9) * 210);
  // Corruptions. The chance of a missing field and a stale tier rises with consumption:
  // high-consumption meters sit at hard-to-reach points and change tiers more often.
  const fault = r() < 0.035, sign = r() < 0.4;
  const measured = fault ? (sign ? -truth : truth * 10) : truth;
  const regionEmpty = r() < 0.03;
  const satEmpty = r() < 0.04 + 0.0035 * truth;
  const stale = r() < 0.05 + 0.0022 * truth;
  return { subscriber_id: 10001 + i, region: regionEmpty ? null : b.name, consumption_true: truth,
    consumption_m3: measured, corrupted: fault, meter_count: meters,
    visit_count: visits, satisfaction: satEmpty ? null : satisfaction, temperature_c: temperature,
    days_elapsed: elapsed, tier_field: stale ? tierOf(truth * 0.8) : tierOf(truth) };
});
export const mean = (a) => a.reduce((x, y) => x + y, 0) / a.length;
export const TRUTH = mean(RECORDS.map((k) => k.consumption_true));

// Four dimensions, four rules. The floating-point comparison is made with a threshold, not
// equality: UPPER_LIMIT is the monthly upper bound for one meter, and a reading above it is
// not physically possible.
export const accurate = (k) => k.consumption_m3 > 0 && k.consumption_m3 < UPPER_LIMIT * k.meter_count;
export const complete = (k) => k.region !== null && k.satisfaction !== null;
export const consistent = (k) => k.tier_field === tierOf(k.consumption_m3);
export const current = (k, days) => k.days_elapsed <= days;

if (import.meta.url === `file://${process.argv[1]}`) {
  const y = (h) => String(h[0]).padEnd(14) + String(h[1]).padEnd(34)
    + h.slice(2).map((v) => String(v).padStart(10)).join("");
  console.log(`model (seed ${SEED}): ${RECORD_COUNT} records; true value of the model: ` +
    `mean ${TRUTH.toFixed(2)} m3, raw mean ` +
    `${mean(RECORDS.map((k) => k.consumption_m3)).toFixed(2)} m3`);
  console.log("\n" + y(["dimension", "rule", "violations", "rate"]));
  const measures = [
    ["accuracy", `reading from 0 to ${UPPER_LIMIT} x meters`,
      RECORDS.filter((k) => !accurate(k)).length],
    ["completeness", "region and satisfaction filled", RECORDS.filter((k) => !complete(k)).length],
    ["consistency", "tier field matches the reading", RECORDS.filter((k) => !consistent(k)).length],
    ["timeliness", "last reading newer than 90 days", RECORDS.filter((k) => !current(k, 90)).length],
  ];
  for (const [name, rule, n] of measures) {
    console.log(y([name, rule, n, `${(100 * n / RECORD_COUNT).toFixed(1)}%`]));
  }
  const none = RECORDS.filter((k) => accurate(k) && complete(k) && consistent(k)
    && current(k, 90)).length;
  const corrupted = RECORDS.filter((k) => k.corrupted).length;
  console.log(`\ncorrupted readings in the model ${corrupted}, caught by the accuracy rule ` +
    `${RECORDS.filter((k) => !accurate(k)).length}`);
  console.log(`records passing all four rules ${none}, ` +
    `${(100 * none / RECORD_COUNT).toFixed(1)}%`);
  const y2 = (h) => String(h[0]).padEnd(16) + h.slice(1).map((v) => String(v).padStart(11)).join("");
  console.log("\n" + y2(["field", "filled", "empty", "fill rate"]));
  for (const [name, f] of [["region", (k) => k.region], ["satisfaction", (k) => k.satisfaction],
    ["consumption_m3", (k) => k.consumption_m3], ["days_elapsed", (k) => k.days_elapsed]]) {
    const filled = RECORDS.filter((k) => f(k) !== null).length;
    console.log(y2([name, filled, RECORD_COUNT - filled, `${(100 * filled / RECORD_COUNT).toFixed(1)}%`]));
  }
}

model (seed 20260218): 1400 records; true value of the model: mean 21.31 m3, raw mean 24.70 m3

dimension     rule                              violations      rate
accuracy      reading from 0 to 120 x meters            46      3.3%
completeness  region and satisfaction filled           200     14.3%
consistency   tier field matches the reading           104      7.4%
timeliness    last reading newer than 90 days          482     34.4%

corrupted readings in the model 58, caught by the accuracy rule 46
records passing all four rules 730, 52.1%

field                filled      empty  fill rate
region                 1357         43      96.9%
satisfaction           1232        168      88.0%
consumption_m3         1400          0     100.0%
days_elapsed           1400          0     100.0%

The four dimensions gave four separate numbers and none of them replaces another. The largest violation is in timeliness — 482 records, 34.4% — and this number is not a data error: the records are correct, only old. The smallest violation is in accuracy, but its effect is the largest: the 46 corrupted readings push the raw mean from 21.31 up to 24.70 m³. The number of violations and the effect of the violations do not move in the same direction; a quality report does not end with counting violations, it also writes how far the violations moved the reported number.

The note under the accuracy row shows the real limit. In the model 58 readings are corrupted and the rule catches 46 of them. The 12 records that escape belong to households with more than one meter: because the threshold is 120 m³ per meter, a corrupted reading of 210 m³ in a household with two meters stays under the limit and looks valid. An accuracy rule catches only the impossible; a value that is wrong but possible cannot be told apart by any rule. The only way to catch the 12 escaped records is to compare against a second source, and the previous lesson showed that source carries a bias of its own.

The field fill table says completeness is measured per field. The region field is 96.9% filled, satisfaction is 88.0%; consumption_m3 is never empty. Looked at per record, what remains is 730 rows that pass all four rules — only 52.1% of the set. The dimensions are measured separately but applied together, and the intersection is always smaller than even the smallest dimension.

As the Threshold Tightens

// threshold.mjs — how many rows are eliminated as the quality threshold tightens, and which
// way the reported number moves. Each dimension is applied alone first, then together.
import { RECORDS, RECORD_COUNT, TRUTH, mean, accurate, complete, consistent, current, tierOf }
  from "./quality.mjs";
const y = (h) => String(h[0]).padEnd(28) + h.slice(1).map((v) => String(v).padStart(11)).join("");
const show = (name, l) => {
  const o = mean(l.map((k) => k.consumption_m3));
  console.log(y([name, l.length, `${(100 * l.length / RECORD_COUNT).toFixed(1)}%`, o.toFixed(2),
    (o - TRUTH >= 0 ? "+" : "") + (o - TRUTH).toFixed(2)]));
};
console.log(`true value of the model ${TRUTH.toFixed(2)} m3; every mean below is computed ` +
  `from the measured field`);
console.log("\neach dimension alone");
console.log(y(["rule", "kept", "kept %", "mean", "deviation"]));
show("no filter", RECORDS);
show("accuracy only", RECORDS.filter(accurate));
show("completeness only", RECORDS.filter(complete));
show("consistency only", RECORDS.filter(consistent));
show("timeliness only (90 days)", RECORDS.filter((k) => current(k, 90)));

const PROFILE = [
  ["P0 no filter", () => true],
  ["P1 accuracy", (k) => accurate(k)],
  ["P2 + completeness, 90 days", (k) => accurate(k) && complete(k) && current(k, 90)],
  ["P3 + consistency, 60 days", (k) => accurate(k) && complete(k) && consistent(k) && current(k, 60)],
  ["P4 same rules, 30 days", (k) => accurate(k) && complete(k) && consistent(k) && current(k, 30)],
];
console.log("\nas the threshold tightens");
console.log(y(["profile", "kept", "kept %", "mean", "deviation"]));
const kept = PROFILE.map(([name, f]) => { const l = RECORDS.filter(f); show(name, l); return l; });

console.log("\nelimination rate per tier (by the true reading)");
const y2 = (h) => String(h[0]).padEnd(28) + h.slice(1).map((v) => String(v).padStart(13)).join("");
const tier = (b) => RECORDS.filter((k) => tierOf(k.consumption_true) === b);
console.log(y2(["profile", "eliminated", ...[1, 2, 3, 4].map((b) => `tier ${b} (${tier(b).length})`)]));
PROFILE.forEach(([name, f]) => {
  const d = [1, 2, 3, 4].map((b) =>
    `${(100 * tier(b).filter((k) => !f(k)).length / tier(b).length).toFixed(1)}%`);
  console.log(y2([name, RECORDS.filter((k) => !f(k)).length, ...d]));
});
const p2 = kept[2], p4 = kept[4];
console.log(`\n${p2.length - p4.length} more rows were eliminated between P2 and P4, ` +
  `the mean moved ${(mean(p4.map((k) => k.consumption_m3)) -
    mean(p2.map((k) => k.consumption_m3))).toFixed(2)} m3`);
console.log(`the cleanest set (P4) is ${p4.length} rows and deviates ` +
  `${(mean(p4.map((k) => k.consumption_m3)) - TRUTH).toFixed(2)} m3 from the true value of the model`);

true value of the model 21.31 m3; every mean below is computed from the measured field

each dimension alone
rule                               kept     kept %       mean  deviation
no filter                          1400     100.0%      24.70      +3.39
accuracy only                      1354      96.7%      22.71      +1.41
completeness only                  1200      85.7%      24.32      +3.01
consistency only                   1296      92.6%      21.47      +0.17
timeliness only (90 days)           918      65.6%      24.01      +2.70

as the threshold tightens
profile                            kept     kept %       mean  deviation
P0 no filter                       1400     100.0%      24.70      +3.39
P1 accuracy                        1354      96.7%      22.71      +1.41
P2 + completeness, 90 days          761      54.4%      22.37      +1.07
P3 + consistency, 60 days           608      43.4%      21.05      -0.25
P4 same rules, 30 days              422      30.1%      20.83      -0.48

elimination rate per tier (by the true reading)
profile                        eliminated  tier 1 (48) tier 2 (970) tier 3 (348)  tier 4 (34)
P0 no filter                            0         0.0%         0.0%         0.0%         0.0%
P1 accuracy                            46         4.2%         3.5%         2.6%         2.9%
P2 + completeness, 90 days            639        45.8%        44.4%        48.0%        55.9%
P3 + consistency, 60 days             792        54.2%        55.4%        59.2%        67.6%
P4 same rules, 30 days                978        66.7%        69.0%        71.0%        88.2%

339 more rows were eliminated between P2 and P4, the mean moved -1.55 m3
the cleanest set (P4) is 422 rows and deviates -0.48 m3 from the true value of the model

The first table measures the dimensions one by one and writes the difference in their effectiveness. The accuracy rule throws out 46 rows and brings the deviation from 3.39 down to 1.41 m³. The timeliness rule throws out 482 rows and brings the deviation from 3.39 down to only 2.70 m³. There is no link between the number of rows thrown out and the accuracy gained; the rule that throws out ten times more rows closes only about a fifth of the deviation. The consistency rule, on the other hand, throws out 104 rows and brings the deviation down to 0.17 m³, because most corrupted readings also contradict the tier field: two dimensions jointly catch part of the same rows, and the dimensions are not independent of each other.

The second table tightens the threshold step by step, and the reported mean falls from 24.70 to 20.83 m³. On the way it crosses the model’s true value of 21.31: 22.37 at P2, 21.05 at P3. The number closest to the truth is not in the cleanest set but somewhere in the middle, and only someone who knows the truth can see it. A real analysis has no such column; all it has is the “kept” and “mean” columns, and a person looking at them sees the number falling as the threshold tightens, not approaching the truth.

The third table writes the reason for the fall. The elimination rate rises as the tier rises: in the P4 profile, 66.7% of the first tier is eliminated and 88.2% of the fourth. The eliminated rows are not random. Because in the model the chance of a missing field and a stale tier rises with consumption, every rule that raises quality eliminates the high consumers disproportionately and pulls the mean down. As the set gets cleaned it becomes cleaner but more biased.

Two records follow from this, and a quality report should contain both. The first is how many rows were eliminated: from 1,400 to 422, 30.1%. The second is in which breakdown the eliminated rows concentrate; a one-line note saying “data cleaned” does not carry this information. A threshold is not a tuning knob but a decision that redefines the sample: the 20.83 m³ of P4 is not the mean of 1,400 subscribers but of the 422 subscribers whose records are recent and complete enough, and these two sentences are not the same thing.

Summary

• Data quality is measured with four separate dimensions, and each asks for its own rule, threshold, and number: 46 impossible readings, 200 incomplete records, 104 inconsistent tiers, 482 old records.
• The number of violations and the effect of the violations do not move in the same direction: 46 corrupted readings pull the deviation from 3.39 to 1.41 m³, while 482 old records pull it only to 2.70.
• An accuracy rule catches only the impossible: of the 58 corrupted readings in the model, 12 stay under the threshold and look valid.
• As the threshold tightens the set falls from 1,400 rows to 422 and the mean drops from 24.70 to 20.83 m³, crossing the model’s true value once.
• The eliminated rows are not random: 88.2% of the top tariff tier is eliminated, so the cleaned set is cleaner and at the same time more biased.

Next Step

These four lessons asked what kind of thing the data is, which scale it stands on, where it comes from, and how reliable it is. Their shared assumption was this: the question is a question that can be asked, and the whole matter is finding the right answer. Each of the remaining 422 rows belongs to a household, and these records report many things, from consumption habits to the days the house stood empty. When a breakdown is thinned enough, the row is no longer a sample but a single person. The next lesson takes a set of questions and separates those that the data can answer from those that should not be asked; it counts how many people a breakdown falls down to and shows, with a measurable threshold, where the analysis should stop.