Data Types and Data Quality

Before Margriet de Wit's KPI pack can say anything about tariffs, someone has to check what is actually in the data. This page uses the Kestrel sample data (kestrel.sql), which contains six deliberate problems of the kind every real export has. The queries run in DuckDB 1.5 (as of October 2026).

Structured, semi-structured and unstructured data

Structured data has a fixed shape that is known in advance: rows and named columns, each column with one type. Kestrel's daily_readings table is structured: every row has a meter, a date and two numbers. Databases, spreadsheets and CSV exports hold structured data, and SQL can query it directly.

Semi-structured data carries its structure with it, as labels inside the data, but the shape can vary from record to record. A JSON message from meter-api names its fields, but one message may have an export field and the next may not, and values can be nested. JSON, XML, YAML and most log formats are semi-structured. Its structure is applied when the data is read rather than when it is stored (see schema on read).

Unstructured data has no predefined data model: free text in emails and ticket notes, PDF contracts, scanned meter photos, recorded phone calls. Much of an organization's data is of this kind. To count anything in it you first have to extract structure, for example by tagging the reason in each customer email, which is where text analytics and AI models come in.

Levels of measurement

A column of numbers is not automatically something you can add up. The psychologist S. S. Stevens described four scales of measurement in 1946 ("On the Theory of Scales of Measurement", Science), and they are still the quickest way to decide which calculations make sense. The level of measurement of a variable is the kind of scale its values are on, and each level allows everything the level before it allows, plus more.

from collections import Counter
from statistics import mean, median

tariffs = ["fixed", "dynamic", "fixed", "day_night", "variable", "fixed"]  # nominal
satisfaction = [5, 4, 4, 2, 5, 1, 4]  # ordinal: 1 = very unhappy, 5 = very happy
temperature_c = [10.0, 20.0]          # interval
kwh = [4.5, 9.0]                      # ratio

print("most common tariff:", Counter(tariffs).most_common(1)[0][0])
print("median satisfaction:", median(satisfaction))
print("mean satisfaction:", round(mean(satisfaction), 2), "(assumes equal steps)")
print("20 C / 10 C =", temperature_c[1] / temperature_c[0], "but not twice as warm")
kelvin = [t + 273.15 for t in temperature_c]
print("in kelvin:", round(kelvin[1] / kelvin[0], 3))
print("9.0 kWh / 4.5 kWh =", kwh[1] / kwh[0], "and really twice as much energy")
print("mean of postcodes 2511 and 3011:", mean([2511, 3011]), "(meaningless)")

most common tariff: fixed
median satisfaction: 4
mean satisfaction: 3.57 (assumes equal steps)
20 C / 10 C = 2.0 but not twice as warm
in kelvin: 1.035
9.0 kWh / 4.5 kWh = 2.0 and really twice as much energy
mean of postcodes 2511 and 3011: 2761 (meaningless)

Python happily computes every line; the computer does not know the scale. The mean of a satisfaction score is widely reported, but it quietly assumes that the step from 1 to 2 is as big as the step from 4 to 5, so report the median or the share of 4s and 5s alongside it. On the kelvin scale, which has a true zero, 20 °C is only about 3.5% higher than 10 °C: 0 °C is not "no temperature". And a postcode or a customer number that happens to consist of digits is still a name.

What data quality means

Data quality is the degree to which data is fit for the purpose it is used for. The definition is deliberately relative: readings that are a day late are perfect for a monthly KPI pack and useless for balancing the grid every quarter of an hour. So "is this data good?" always needs the follow-up "good enough for what?"

Dimensions of data quality

To make quality measurable it is split into a data quality dimension each: one aspect that can be checked with a rule and expressed as a number, such as "99.7% of meters have a reading for every day". The six most quoted dimensions come from a 2013 white paper by DAMA UK (the UK chapter of the Data Management Association), not from the DAMA body of knowledge itself, which lists more. The international standard ISO/IEC 25012 defines a data quality model with 15 characteristics. The six are a practical start:

Validity and accuracy are easy to confuse. A valid value is possible: 91.300 kWh in a day is a valid number for a household meter. An accurate value is true, and only a comparison with reality (the meter's own register, a second source) can prove that. Rules can check validity; for accuracy they can only flag suspects.

Finding the problems in Kestrel's readings

The count trap

The first check most people make is a row count. Twelve meters times thirty days of September should be 360 readings:

SELECT count(*) AS readings,
       count(DISTINCT meter_id) AS meters,
       count(DISTINCT reading_date) AS days,
       count(DISTINCT meter_id) * count(DISTINCT reading_date) AS expected
FROM daily_readings;

SELECT meter_id, count(*) AS days
FROM daily_readings
GROUP BY meter_id
HAVING count(*) <> 30
ORDER BY meter_id;

┌──────────┬────────┬───────┬──────────┐
│ readings │ meters │ days  │ expected │
│  int64   │ int64  │ int64 │  int64   │
├──────────┼────────┼───────┼──────────┤
│      360 │     12 │    30 │      360 │
└──────────┴────────┴───────┴──────────┘
┌──────────┬───────┐
│ meter_id │ days  │
│ varchar  │ int64 │
├──────────┼───────┤
│ M-30005  │    31 │
│ M-30010  │    29 │
└──────────┴───────┘

The total is exactly right, and the data is still wrong in two directions: one meter has a reading too many and another one too few. Totals hide errors that cancel out. Check at the level the data is supposed to be unique at, here one row per meter per day.

A data-quality report

Each dimension becomes a rule, and each rule a query that counts the rows that break it. Collected in one result, the rules form a small report that can run after every load:

SELECT 1 AS nr, 'uniqueness' AS dimension,
       'one reading per meter per day' AS rule, count(*) AS failing
FROM (SELECT meter_id, reading_date FROM daily_readings
      GROUP BY meter_id, reading_date HAVING count(*) > 1)
UNION ALL
SELECT 2, 'completeness', 'a reading for every meter and day', count(*)
FROM (SELECT DISTINCT meter_id FROM daily_readings)
CROSS JOIN (SELECT DISTINCT reading_date FROM daily_readings)
ANTI JOIN daily_readings USING (meter_id, reading_date)
UNION ALL
SELECT 3, 'validity', 'kwh_import is not negative', count(*)
FROM daily_readings WHERE kwh_import < 0
UNION ALL
SELECT 4, 'accuracy', 'at most 3x the meter median', count(*)
FROM daily_readings
JOIN (SELECT meter_id, median(kwh_import) AS med
      FROM daily_readings GROUP BY meter_id) USING (meter_id)
WHERE kwh_import > 3 * med
UNION ALL
SELECT 5, 'consistency', 'one spelling per city', count(*)
FROM (SELECT lower(city) FROM customers
      GROUP BY lower(city) HAVING count(DISTINCT city) > 1)
UNION ALL
SELECT 6, 'consistency', 'every contract has a customer', count(*)
FROM contracts ANTI JOIN customers USING (customer_id)
ORDER BY nr;

┌───────┬──────────────┬───────────────────────────────────┬─────────┐
│  nr   │  dimension   │               rule                │ failing │
│ int32 │   varchar    │              varchar              │  int64  │
├───────┼──────────────┼───────────────────────────────────┼─────────┤
│     1 │ uniqueness   │ one reading per meter per day     │       1 │
│     2 │ completeness │ a reading for every meter and day │       1 │
│     3 │ validity     │ kwh_import is not negative        │       1 │
│     4 │ accuracy     │ at most 3x the meter median       │       1 │
│     5 │ consistency  │ one spelling per city             │       1 │
│     6 │ consistency  │ every contract has a customer     │       1 │
└───────┴──────────────┴───────────────────────────────────┴─────────┘

A few details in the query are worth knowing:

• The completeness rule builds every combination of meter and day with a CROSS JOIN, then keeps the combinations that have no reading. ANTI JOIN is DuckDB shorthand for "rows with no match"; in standard SQL you write NOT EXISTS or a left join with WHERE … IS NULL.
• The accuracy rule compares each reading with the meter's own median, not with a fixed limit: a bakery and a flat have very different normal days. Three times the median is a judgment call that flags the 91.300 kWh day of M-30001 (median 8.626) and nothing else. It finds a suspect; a person or the source system confirms it. Medians and outliers are explained on Statistics for IT.
• The orphan contract got in because nothing enforced a foreign key, the database feature that would have refused it (see foreign keys). Many exports and data lakes carry no constraints at all, so the checks have to be done afterward.

Missing values and NULL

A missing value is a field that holds no data. In SQL it is NULL; in pandas it shows as NaN or NaT; in a CSV file it is an empty field, or a placeholder such as -, n/a or 9999 that you have to recognize yourself. "Missing" can mean very different things:

• Not applicable: a meter without solar panels has no export reading. Kestrel stores NULL there, not 0, because nothing was measured.
• Not yet delivered: M-30010's reading for 17 September may still arrive from the grid operator.
• Unknown: a customer who did not give a phone number.

Which one it is decides how to treat it. SQL's aggregate functions skip NULL, which is right for some questions and wrong for others:

SELECT round(avg(kwh_export), 3) AS avg_export,
       round(avg(coalesce(kwh_export, 0)), 3) AS avg_null_as_zero,
       count(kwh_export) AS with_value,
       count(*) - count(kwh_export) AS null_rows
FROM daily_readings;

┌────────────┬──────────────────┬────────────┬───────────┐
│ avg_export │ avg_null_as_zero │ with_value │ null_rows │
│   double   │      double      │   int64    │   int64   │
├────────────┼──────────────────┼────────────┼───────────┤
│      7.025 │            2.927 │        150 │       210 │
└────────────┴──────────────────┴────────────┴───────────┘

Both numbers are correct, for different questions. 7.025 kWh is the average daily export of a meter with solar panels, because avg() only looked at the 150 rows that have a value. 2.927 kWh is the average daily export per meter across all meters, counting the ones without panels as exporting nothing. Writing coalesce(kwh_export, 0) (see COALESCE) is a decision about meaning, so make it on purpose and say so next to the number.

Data cleaning

Data cleaning is detecting and correcting, removing or flagging data that is incomplete, incorrect, duplicated or inconsistent, so that analysis can rely on it. The usual steps:

1. Standardize codes: trim spaces, fix case, map variants to one official spelling.
2. Remove duplicates on the business key (meter and day), not only on completely identical rows.
3. Validate types and ranges: dates that are dates, amounts that are numbers, no negative imports.
4. Decide on outliers case by case: a decimal slip is corrected, a real peak (a bakery's oven running all weekend) stays.
5. Record every change with a reason, so the clean data can be rebuilt from the raw data and anyone can see what was changed.

Standardizing codes

Inconsistent spelling is the most common problem in anything people type. In pandas (see Data Analysis with Python), the cities from a sign-up form show how many ways one city can be written:

import pandas as pd

# City as typed into the sign-up form
cities = pd.Series(
    ["Den Haag", "Rotterdam", "den haag", "Utrecht", "Den Haag ", "Zwolle", "Utreht"],
    name="city",
)
print(cities.value_counts().sort_index())

# A reference list of official spellings decides, not the data itself
official = ["Den Haag", "Eindhoven", "Groningen", "Rotterdam", "Utrecht", "Zwolle"]
lookup = {name.lower(): name for name in official}

clean = cities.str.strip().str.lower().map(lookup)
print(clean.value_counts().sort_index())
print("needs a person:", cities[clean.isna()].tolist())

city
Den Haag     1
Den Haag     1
Rotterdam    1
Utrecht      1
Utreht       1
Zwolle       1
den haag     1
Name: count, dtype: int64
city
Den Haag     3
Rotterdam    1
Utrecht      1
Zwolle       1
Name: count, dtype: int64
needs a person: ['Utreht']

The first count shows "Den Haag" twice: the second one ends with a space you cannot see. After trimming and lowercasing, each value is looked up in a list of official names, and anything that does not match becomes missing instead of being guessed. The typo "Utreht" is handed to a person. A shortcut such as .str.title() looks tempting but breaks real names: it turns 's-Hertogenbosch into 'S-Hertogenbosch.

Cleaning with a log

For the readings, Joost Bakker checked the four suspect rows against KBS and wrote each decision down. The clean table is then built from the raw table and that log, so it can be rebuilt at any time and every changed value has a reason next to it:

-- Every decision is a row in the log, with a reason
CREATE TABLE cleaning_log (
  meter_id     VARCHAR,
  reading_date DATE,
  action       VARCHAR,
  new_value    DECIMAL(8,3),
  reason       VARCHAR
);
INSERT INTO cleaning_log VALUES
  ('M-30005', '2026-09-12', 'deduplicate', NULL, 'identical row delivered twice'),
  ('M-30009', '2026-09-21', 'set_null',    NULL, 'negative import is impossible'),
  ('M-30001', '2026-09-08', 'correct',    9.130, 'decimal slip, confirmed in KBS'),
  ('M-30010', '2026-09-17', 'keep_gap',    NULL, 'requested from the grid operator');

-- The clean table is built from the raw table plus the log
CREATE TABLE readings_clean AS
SELECT DISTINCT
  r.meter_id,
  r.reading_date,
  CASE l.action
    WHEN 'set_null' THEN NULL
    WHEN 'correct'  THEN l.new_value
    ELSE r.kwh_import
  END AS kwh_import,
  r.kwh_export
FROM daily_readings r
LEFT JOIN cleaning_log l USING (meter_id, reading_date);

SELECT 'raw' AS version, count(*) AS row_count, count(kwh_import) AS with_kwh,
       sum(kwh_import) AS kwh_import
FROM daily_readings
UNION ALL
SELECT 'clean', count(*), count(kwh_import), sum(kwh_import)
FROM readings_clean
ORDER BY version DESC;

┌─────────┬───────────┬──────────┬───────────────┐
│ version │ row_count │ with_kwh │  kwh_import   │
│ varchar │   int64   │  int64   │ decimal(38,3) │
├─────────┼───────────┼──────────┼───────────────┤
│ raw     │       360 │      360 │      5557.595 │
│ clean   │       359 │      358 │      5471.328 │
└─────────┴───────────┴──────────┴───────────────┘

SELECT DISTINCT removes the duplicate row (it is identical in every column; a duplicate with different values would need a rule for which one wins). The negative reading becomes NULL and is counted as missing, the decimal slip is corrected, and the missing day stays missing. September's total drops by 86 kWh, which is the size of the error the KPI pack would otherwise have reported. The CREATE and INSERT statements print nothing in DuckDB.

Fix problems where they start

Cleaning in the analysis repairs the symptom. The orphan contract CT-20014 and the duplicate reading came from somewhere, and they will come again next month unless the cause is fixed. Lucía Romero could correct 'den haag' in her spreadsheet every month; better is a drop-down list of cities in the customer form, and a foreign key or a check in KBS that refuses a contract for a customer that does not exist (see constraints in table design).

• Report it to the owner. Each source has an owner (for KBS, Joost Bakker) who can fix the cause. Femke Jansen, as data owner for billing data, decides which quality rules the KPI pack must meet; see Data Governance and Privacy.
• Never fix numbers in the report. A total typed over in the final spreadsheet cannot be reproduced next month and disagrees with every other report.
• Measure quality over time. The six-rule report above, run every night, turns "the data is a bit messy" into a trend someone can act on.

Data quality matters even more when data trains a model, because a model learns the errors as if they were facts; see data quality for AI. For the governance side (rules, ownership and definitions) see data quality and governance in a warehouse.