Data Profiling Tools: Which One Fits the Job

A data profiling tool reads a table or file and reports what is actually in each column: how many rows, how many nulls, how many distinct values, the minimum and maximum, the type it looks like and how the values are spread. It is the first thing to run on data you did not build, and the last thing to run before you trust a migration or a merge.

Most profilers compute the same core measures. What separates them is where they run, how much data they can take and what they leave behind: a result set, a report you can send to someone, a model in your dbt project or a job on a schedule. So this list is ordered by job, not by a score. Each pick says what it is best for, how to start, what to watch for and where to check its current version and licence, which change faster than any list can.

At a glance

1. DuckDB SUMMARIZE

Best for: A first look at a file or export before it goes anywhere. Runs on: Your laptop: CSV, Parquet or JSON files, or any DuckDB query. You get: One row per column, as a result set.

DuckDB's SUMMARIZE computes a set of aggregates over every column of a table, a query or a file read directly from disk: min, max, approx_unique, avg, std, the quartiles q25, q50 and q75, count and the percentage of nulls, next to each column's name and type. Pointed at a CRM export, it answers the first questions without loading anything into a warehouse.

Pair it with DESCRIBE to see the types DuckDB inferred. A column you expected to be a date that comes back as VARCHAR is usually the first finding of the day.

DESCRIBE SELECT * FROM 'accounts_export.csv';

SUMMARIZE SELECT * FROM 'accounts_export.csv';

Watch for

• Distinct counts and quartiles are approximate, so confirm a key's uniqueness with an exact COUNT(DISTINCT …) before you rely on it.
• No format or pattern checks: a phone column full of placeholders still summarizes neatly.
• It reads the file as DuckDB infers it. Check the inferred types and delimiter before you trust the numbers.

Check the current version, install steps and licence: DuckDB docs: Summarize.

2. Plain SQL in your warehouse

Best for: Profiling tables where they already live, with nothing to install. Runs on: Snowflake, BigQuery, Postgres or any SQL engine. You get: Whatever you select: one query per table or one row per column.

Every profiler on this list is, underneath, running aggregates you can write yourself. In the warehouse that means one pass of conditional aggregates per table: COUNT(*), COUNT(column) for completeness, COUNT(DISTINCT column) for cardinality, MIN and MAX for range, and a SUM(CASE WHEN … THEN 1 ELSE 0 END) for any rule you can state, such as an email that fails a pattern.

It is the most exact option (no sampling unless you add it) and the easiest to put on a schedule, because it is just a query. For wide tables, generate the query from INFORMATION_SCHEMA.COLUMNS instead of typing it. The conditional aggregates walkthrough builds a whole scorecard this way.

SELECT
  COUNT(*)                                        AS row_count,
  COUNT(DISTINCT account_id)                      AS distinct_account_ids,
  1 - COUNT(email) * 1.0 / NULLIF(COUNT(*), 0)    AS email_null_rate,
  COUNT(DISTINCT industry)                        AS industry_cardinality,
  MIN(created_at)                                 AS first_created,
  MAX(created_at)                                 AS last_created
FROM crm.accounts;

Watch for

• You write every measure, so you only see what you thought to ask for.
• Full scans of large tables cost compute; on usage-billed warehouses, check the bytes or credits a query will use first.
• Integer division and null handling differ between engines. Check how yours divides before you read a rate.

Check the current version, install steps and licence: Your warehouse's INFORMATION_SCHEMA reference.

3. pandas describe(), isna() and value_counts()

Best for: A quick check inside a notebook on data that fits in memory. Runs on: Python, on one machine. You get: DataFrames you can keep working with.

If the data is already a DataFrame, pandas covers the basics in three lines. df.describe(include="all") gives count, mean, spread and quartiles for numeric columns and count, unique, top value and its frequency for the rest. df.isna().mean() is the null rate per column, and df.nunique() the distinct count.

value_counts(dropna=False) on a single column is the fastest way to see placeholder values, mixed spellings and the share of blanks, which is most of what cardinality profiling is looking for.

import pandas as pd

df = pd.read_csv("accounts_export.csv")

df.dtypes
df.describe(include="all").T
df.isna().mean().sort_values(ascending=False)
df.nunique().sort_values()
df["industry"].value_counts(dropna=False).head(20)

Watch for

• Everything happens in memory, so a large export may not fit.
• read_csv infers types: IDs with leading zeros become integers unless you pass dtype=str.
• Nothing is saved or shared unless you write it out yourself.

Check the current version, install steps and licence: pandas docs: DataFrame.describe.

4. fg-data-profiling (formerly ydata-profiling)

Best for: A shareable report on one dataset, for someone who will not read code. Runs on: Python, on pandas or Spark DataFrames. You get: An HTML report, JSON, or a notebook widget.

The library long known as pandas-profiling, then ydata-profiling, is now published as fg-data-profiling, imported as data_profiling. One call produces a report with an overview of the dataset (rows, variables, missing cells, duplicate rows), a section per column with statistics and a histogram, correlations, a missing-values view and a list of alerts such as constant columns, high cardinality, skew and missing values.

It is the pick when the profile has to leave your machine: an HTML file a stakeholder can open, or a JSON file a pipeline can read. It can also compare two reports, which suits a before-and-after check on a migration.

import pandas as pd
from data_profiling import ProfileReport

df = pd.read_csv("accounts_export.csv")

profile = ProfileReport(df, title="Accounts export", minimal=True)
profile.to_file("accounts_profile.html")

Watch for

• The full report computes a lot; on large data, start with minimal=True or a sample, as its large-dataset guide suggests.
• Code still importing ydata_profiling uses the old package name, which its maintainers say will not get further updates. Check its migration guide.
• A sampled report describes the sample. Note the scope next to any number you quote from it.

Check the current version, install steps and licence: fg-data-profiling on GitHub.

5. dbt-profiler

Best for: Teams who already model in dbt and want profiles in the project. Runs on: Your dbt project, on the warehouse it targets. You get: A SQL model, a printed table, or column meta in schema.yml.

dbt-profiler is a dbt package that profiles any model or source: for each column, the share of non-null values, distinct count and share, whether it is unique, min and max for numbers and dates, and average, median and standard deviation for numbers. Because the profile is a macro, it can be a model of its own, refreshed with the rest of the project.

Its print_profile_schema and print_profile_docs macros write the profile into your schema files and dbt docs, so the people reading the docs see what the column holds, not only what it is meant to hold.

# print a profile of one relation
dbt run-operation print_profile --args '{"relation_name": "customers"}'

# or keep it as a model: models/customers_profile.sql
# {{ dbt_profiler.get_profile(relation=ref("customers")) }}

Watch for

• It supports a list of adapters and dbt versions; check yours is on it before you install.
• No pattern or format measures: pair it with dbt tests for the rules you know.
• Profiling every model on every run adds warehouse cost. Profile the tables you are worried about.

Check the current version, install steps and licence: dbt-profiler on GitHub.

6. Deequ and PyDeequ

Best for: Tables too large for one machine, profiled where Spark already runs. Runs on: Apache Spark (Scala, or Python through PyDeequ). You get: Column profiles, suggested checks, and stored metrics over time.

Deequ is a library from AWS Labs, built on Spark, for measuring data quality in large datasets. Its column profiler computes completeness, approximate distinct counts and the inferred data type for every column, plus statistics for numeric ones, as a distributed job. PyDeequ is its Python API.

The same library goes on from the profile: it can suggest constraints from what it found, verify them on every run and keep the metrics in a repository so you can compare runs, which is where profiling turns into distribution shift detection. AWS has said AWS Glue Data Quality is built on Deequ.

import os
os.environ["SPARK_VERSION"] = "3.5"  # match your Spark runtime

from pydeequ.profiles import ColumnProfilerRunner

result = ColumnProfilerRunner(spark).onData(df).run()

for column, profile in result.profiles.items():
    print(profile)

Watch for

• PyDeequ needs SPARK_VERSION set and the matching Deequ JAR on the cluster; each release supports particular Spark versions.
• Distinct counts are approximate by design at this scale.
• It is a library, not a report: you decide where the results go.

Check the current version, install steps and licence: PyDeequ on GitHub.

7. OpenRefine

Best for: Messy text columns in a one-off file, and people who would rather click than code. Runs on: A desktop app on your machine, used in the browser. You get: Facets and clusters you can fix in place, then export.

OpenRefine profiles by facet: pick a column and it lists every value with how often it appears, or plots a numeric range. That is the quickest way to see that an industry field holds forty spellings of five industries, or that a country column mixes codes and names.

Its clustering goes a step further than the other picks. Using key-collision and nearest-neighbour methods, it groups values that are probably the same thing written differently, such as Acme Inc, ACME, Inc. and Acme Incorporated, so you can review and merge them before the import.

Watch for

• Clustering matches characters, not meaning: two different companies with similar names will be proposed together, so review every cluster.
• It works on a project in memory on your machine, so it suits files more than warehouse tables.
• Edits happen in OpenRefine; to repeat them on the next export, save and reapply the operation history.

Check the current version, install steps and licence: OpenRefine docs: clustering in depth.

How to choose a data profiling tool

Four questions settle it faster than a feature list.

• Where does the data live? A file on your laptop points to DuckDB, pandas or OpenRefine. A warehouse table points to SQL or dbt-profiler, so the data does not move. A Spark lake points to Deequ.
• Is this once or every run? A one-off look before a migration needs speed. A profile that has to catch drift needs to run on a schedule and keep its results, which favours SQL, dbt-profiler or Deequ.
• Who reads the result? If it is you, a result set is enough. If it is a stakeholder or an auditor, a report they can open, such as fg-data-profiling's HTML, saves a meeting.
• How big is it? In-memory tools stop where your machine's memory does. Past that, push the work to the warehouse or to Spark, or profile a sample and say so.

What to profile first, whichever tool you use

• Row count and key uniqueness. If the key you plan to join on repeats, every later number is suspect. See duplicate record detection before aggregation.
• Null rates on the columns the next step needs. A field that is mandatory downstream and half-empty upstream is a blocker, not a warning. See null rate analysis across warehouse tables.
• Cardinality of categorical fields. Too many distinct values in a picklist means free text got in. See cardinality profiling for categorical columns.
• Declared type against stored values. Numbers in strings and dates in three formats break joins and sorts quietly. See data type mismatch auditing in Snowflake and BigQuery.
• Ranges and outliers. Minimums and maximums surface sentinel dates and negative quantities. See detecting statistical outliers in numeric columns.

What no profiler will tell you

A profile describes what is in the data, not whether it is right. A column can be fully populated, perfectly unique and in range, and still hold the wrong customer's email. Profilers also look at one table at a time: whether an order points at an account that still exists is a relationship check, covered in referential integrity spot-checks without foreign key constraints.

And a profile is only as wide as what it scanned. Record whether each number came from a sample or a full scan; the walkthrough on profiling an unfamiliar data warehouse before integration explains why that matters most on keys, money and dates.