3. Profiling structured data sources

Relational databases remain the backbone of enterprise data estates, and they are usually the first source type a profiling pipeline must handle. Unlike flat files, a relational source exposes a formal contract: catalogs, schemas, tables, columns, declared data types, constraints, keys, and indexes. That contract is the starting point for profiling, but it is not the whole story. Declared metadata tells you what the database promises; profiling tells you what the data actually contains. The gap between the two is where most data quality defects live.

This chapter walks through the three interlocking activities of relational profiling: discovering the schema, profiling each column, and extracting metadata that can be stored, compared, and monitored over time.

Schema Discovery: Reading the Contract

Schema discovery is the systematic enumeration of every object that can hold or describe data. On PostgreSQL, the primary entry points are information_schema and the pg_catalog views; on MySQL, information_schema; on SQL Server, INFORMATION_SCHEMA plus sys.objects and sys.columns; on Oracle, ALL_TAB_COLUMNS, ALL_CONSTRAINTS, and ALL_TABLES. A portable discovery query typically joins table, column, and constraint views:

SELECT
  c.table_schema,
  c.table_name,
  c.column_name,
  c.ordinal_position,
  c.data_type,
  c.is_nullable,
  c.character_maximum_length,
  c.numeric_precision,
  c.numeric_scale
FROM information_schema.columns c
JOIN information_schema.tables t
  ON t.table_schema = c.table_schema
 AND t.table_name   = c.table_name
WHERE t.table_type = 'BASE TABLE'
  AND c.table_schema NOT IN ('pg_catalog','information_schema')
ORDER BY c.table_schema, c.table_name, c.ordinal_position;

Three things matter beyond the column list. First, keys and constraints: primary keys, foreign keys, unique constraints, and check constraints define referential integrity and reveal the intended grain of each table. Second, indexes: an index on a column usually signals that the column is used for joins or filtering, which is a strong hint about its business role. Third, views and materialized views: these often encode business logic and should be profiled separately from base tables, because their row counts and nullability can differ from the underlying tables.

A practical discovery step also records the row count estimate from the catalog (for example, pg_class.reltuples or sys.dm_db_partition_stats) so that later profiling can choose between a full scan and a sampled scan. For tables above a threshold — say 10 million rows — sampling is usually the right default.

Column Profiling: Measuring What Is Actually There

Column profiling computes a fixed set of statistics per column. The core metrics are:

• Null count and null ratio — the fraction of rows where the value is NULL.
• Distinct count and cardinality ratio — distinct values divided by non-null rows. A ratio near 1.0 suggests a key or near-key; a ratio near 0 suggests a low-cardinality categorical column.
• Min, max, and quantiles — for numeric and date columns, these reveal range and skew.
• Top-K frequent values — the most common values and their frequencies, which expose dominant categories and potential sentinel values.
• Pattern and length distribution — for string columns, the distribution of lengths and of character-class patterns (for example, ^[A-Z]{2}\d{6}$).

A single profiling query can compute several of these at once. The following example profiles an orders table on PostgreSQL:

SELECT
  COUNT(*)                                   AS total_rows,
  COUNT(customer_id)                         AS non_null_customer_id,
  COUNT(DISTINCT customer_id)                AS distinct_customer_id,
  COUNT(*) - COUNT(customer_id)              AS null_customer_id,
  MIN(order_date)                            AS min_order_date,
  MAX(order_date)                            AS max_order_date,
  COUNT(DISTINCT status)                     AS distinct_status,
  MIN(amount)                                AS min_amount,
  MAX(amount)                                AS max_amount,
  AVG(amount)                                AS avg_amount
FROM orders;

For top-K values, a separate query is clearer:

SELECT status, COUNT(*) AS freq
FROM orders
GROUP BY status
ORDER BY freq DESC
LIMIT 10;

When the table is large, replace the full scan with a deterministic sample. On PostgreSQL, TABLESAMPLE SYSTEM (1) gives a block-level sample; on SQL Server, TABLESAMPLE (1 PERCENT). Sampling changes the interpretation of distinct counts: a sampled distinct count is a lower bound, not the true cardinality. For high-cardinality columns, use a sketch such as HyperLogLog or APPROX_COUNT_DISTINCT (available in Oracle and SQL Server) instead of exact COUNT(DISTINCT ...).

Metadata Extraction: Turning Profiles into a Persistent Record

Profiling results are only useful if they are stored in a structured, queryable form. The standard approach is a metadata repository — a set of tables that hold the discovered schema, the computed statistics, and the run history. A minimal schema looks like this:

With this repository in place, drift detection becomes a simple join between two runs. For example, to find columns whose null ratio increased by more than 10 percentage points between run 101 and run 102:

SELECT
  a.schema, a.table_name, a.column_name,
  a.null_ratio AS prev_null_ratio,
  b.null_ratio AS curr_null_ratio,
  b.null_ratio - a.null_ratio AS delta
FROM column_profile a
JOIN column_profile b
  ON a.schema = b.schema
 AND a.table_name = b.table_name
 AND a.column_name = b.column_name
WHERE a.run_id = 101
  AND b.run_id = 102
  AND b.null_ratio - a.null_ratio > 0.10
ORDER BY delta DESC;

This query is the operational heart of profiling: it converts a one-off measurement into a monitoring signal. The same pattern applies to distinct-count collapse (a categorical column losing values), range shifts (a date column's max moving backward), and pattern violations (a column that was 99% ^[A-Z]{2}\d{6}$ dropping to 60%).

Putting It Together: A Profiling Workflow

The three activities form a natural pipeline. Discovery enumerates objects and records the declared schema. Profiling computes statistics per column, using sampling or sketches where the table is large. Extraction writes both the declared schema and the computed statistics into the metadata repository, keyed by run ID. The diagram below shows this flow and the key artifacts at each stage.

The pipeline is deliberately linear at the top and feedback-driven at the bottom. Discovery feeds profiling, profiling feeds extraction, and extraction feeds drift detection. Each stage produces a distinct artifact — declared schema, computed statistics, persistent record — and each artifact is independently useful. A team that only has the declared schema can still detect structural drift; a team that only has column statistics can still detect value drift; a team that has both can correlate the two and answer questions like "did the null ratio spike because a new nullable column was added, or because an existing column started receiving NULLs?"

With the schema discovered, the columns profiled, and the metadata persisted, the relational source is no longer a black box. The next step is to apply the same discipline to semi-structured and streaming sources, where the contract is weaker and the profiling must adapt.