Back to course overview
Module 3Data quality & profiling 13 min

Profiling columns

Before you can defend data quality you have to measure it: null rates, distinct ratios, ranges, and distributions — the fingerprint of every column.

You cannot write good quality checks for data you haven't measured. Profiling is the measurement step: for every column, compute a small statistical fingerprint — and suddenly a table you 'know' becomes a table you know. Every serious data-quality practice, and every data-observability product including our own Vigil, starts exactly here.

The fingerprint

  • Null rate — share of missing values. The single most informative number about a column. A null rate that moves matters more than its absolute level: email going from 30% to 55% null overnight means an upstream form broke, even though both numbers 'look bad.'
  • Distinct ratio — distinct values ÷ rows. Near 1.0 → the column behaves like an identifier. Near 0.0 → a category. An 'identifier' at 0.97 is a smell: something is duplicating. This one ratio is how tools infer which columns should be unique.
  • Range & percentiles — min, max, p50, p95 for numbers and dates. unit_price max of 9,999 says someone fat-fingered; ordered_at max in the future says a timezone bug.
  • Top values — the five most frequent values and their shares. For categorical columns this is the distribution: status should be roughly 80/10/10 across completed/cancelled/refunded. A new value ('complete', no 'd') appearing at 2% is a contract change nobody announced.
profile one column in plain SQLsql
SELECT
  count(*)                                        AS rows,
  count(*) - count(email)                         AS nulls,
  round(1.0 - count(email) / count(*), 3)         AS null_rate,
  round(count(DISTINCT email) * 1.0 / count(*),3) AS distinct_ratio
FROM stg_pos_customers;
-- DuckDB shortcut for the whole table at once: SUMMARIZE stg_pos_customers;

Profile per day, not just per table

A single profile tells you what the data is; a daily profile series tells you what the data is doing. Bucket the fact table by order_date and compute the fingerprint per day, and quality stops being a snapshot and becomes a signal: row counts per day form a volume baseline, daily null rates expose the exact date a source started misbehaving, daily top-values expose category drift. Module 3's lab stores profiles in a table for precisely this reason — and it's the same design choice inside Vigil.

Profile before you promise

The professional move when inheriting any dataset: profile first, promise second. Twenty minutes of profiling tells you which quality guarantees you can actually make — and hands you the thresholds for the checks you'll write in the next lesson, based on evidence instead of optimism.

PreviousLab: Build the Harbor Lane pipelineNext Constraints & tests