Data profiling

Overview

Data profiling is the systematic analysis of a dataset's structure and content: column types, value ranges, null and distinct counts, patterns, and distributions. It's usually the first step before writing transformation logic, designing a schema, or trusting a new data source, because it surfaces problems (missing values, inconsistent formats, unexpected outliers) before they become bugs downstream.

Profiling can be manual (running a handful of COUNT, MIN/MAX, and COUNT(DISTINCT ...) queries) or automated with dedicated tooling. Either way, the goal is the same: build a quick statistical picture of every column so you know what you're actually working with, not just what the schema or documentation claims.

What a profile typically includes

• Type and format: is a "date" column actually parseable as a date, or a mix of formats?
• Completeness: null counts and null percentage per column
• Cardinality: number of distinct values, useful for spotting near-constant columns or high-cardinality keys
• Distribution: min, max, mean, standard deviation, and quantiles for numeric columns
• Value patterns: most frequent values, sample values, and outliers

Profiling with DuckDB

DuckDB has a built-in SUMMARIZE command that computes most of this in one shot, over a table or any query:

Copy code
SUMMARIZE orders;

-- or summarize the result of a query
SUMMARIZE SELECT * FROM read_parquet('s3://bucket/orders/*.parquet');

SUMMARIZE returns one row per column with column_name, column_type, min, max, approx_unique, avg, std, q25, q50, q75, count, and null_percentage. Because DuckDB can query CSV, Parquet, and JSON files directly, this works equally well against a local file, an S3 path, or an existing table — so profiling a brand-new dataset is often just one line:

Copy code
SUMMARIZE read_csv('data/raw/customers.csv');

For a targeted check, you can still write explicit queries, e.g. finding the null rate for one column:

Copy code
SELECT
  COUNT(*) AS total_rows,
  COUNT(*) - COUNT(email) AS null_emails,
  ROUND(100.0 * (COUNT(*) - COUNT(email)) / COUNT(*), 2) AS null_pct
FROM customers;

Why it matters

Profiling early catches issues that are expensive to discover later: a "unique" ID column that isn't actually unique, a numeric column stored as text, or a date field with a handful of malformed rows that would silently break a join. It also informs decisions like which columns need data cleansing, what constraints to enforce, and where to add data quality tests in a pipeline.