---
title: "Data profiling"
description: "Data profiling is the process of examining a dataset to understand its structure, content, and quality — things like data types, value distributions, null rates, and cardinality — before using it for analysis or building pipelines on top of it."
canonical: "https://motherduck.com/glossary/data-profiling/"
related:
  - title: "DuckLake | MotherDuck Docs"
    url: "https://motherduck.com/docs/concepts/ducklake/"
  - title: "Claudeception: Inside the Mind of an Analytics Agent"
    url: "https://motherduck.com/blog/claudeception-inside-the-mind-of-an-analytics-agent/"
  - title: "Analyze JSON Data Using SQL and DuckDB"
    url: "https://motherduck.com/blog/analyze-json-data-using-sql/"
---

# Data profiling

> Data profiling is the process of examining a dataset to understand its structure, content, and quality — things like data types, value distributions, null rates, and cardinality — before using it for analysis or building pipelines on top of it.

## 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:

```sql
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:

```sql
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:

```sql
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.