Shape / inspect · All operations

Profile a table

Summary statistics, distinct counts, null counts.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
a table describing the input rather than holding its rows
Level
one clause of SQL

The DuckDB step

Per-column summary statistics

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
CREATE TEMP TABLE result AS
SELECT column_name, approx_unique, null_percentage, "min", "max", count FROM (SUMMARIZE orders);
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(*) = 8 AND max(null_percentage) FILTER (WHERE column_name = 'amount') = 10 AND max("max") FILTER (WHERE column_name = 'order_id') = '10' AND max(count) = 10 FROM result)) THEN 'as expected' ELSE error('the profile does not report 10 rows with a tenth of amounts missing') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the profile does not report 10 rows with a tenth of amounts missing”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Classify columns by what they hold

Label each column with a semantic or personal-data type from the share of values matching each pattern.

Count distinct values

Count the distinct values of each column, or the distinct rows, exactly or approximately.

Count missing values in every column

Count the blank or null cells, or the filled ones, in each column.

Profile other kinds of content

Profile text, images or behaviour segments rather than table columns.

Score each column for quality

Score columns for missing share, stability, entropy, ID-ness or overall health.

Count the rows or test for an empty table

Return the number of rows, or whether there are none, as one value.

Summarise every column

Give one row per column with its type, counts, nulls, distinct values, minimum, maximum, mean and quartiles.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (6)
  • Basic Data Profileread 2026-09-27

    Output per-column metadata and summary statistics for further analysis

  • Compare Behaviorread 2026-09-27

    Analyse one set of behaviour profiles against another

  • Data Healthread 2026-09-27

    Score data health from missing values, outliers and sparsity

  • Detail Fieldsread 2026-09-27

    Return cluster- or group-level detail fields for a behaviour profile

  • Field Summaryread 2026-09-27

    Report descriptive statistics per column with data-handling recommendations

  • Image Profileread 2026-09-27

    Extract size, channels, Exif metadata and per-channel metrics from images

Apache DataFusion (1)
  • describeread 2026-09-27

    DataFrame method: Return summary statistics of the DataFrame

Apache NiFi (1)
  • CountTextread 2026-09-27

    Count text metrics of the content and record them as attributes; content is unchanged

AWS Glue (3)
  • AllStatisticsread 2026-09-27

    Analyzer that gathers row count, completeness, uniqueness and length or value metrics of a column

  • Entropyread 2026-09-27

    Check the entropy of a column

  • countread 2026-09-27

    Number of rows in the frame

DuckDB (2)
  • SUMMARIZEread 2026-09-27

    Per column: type, min, max, distinct count, mean, deviation, quartiles, nulls

  • summaryread 2026-09-27

    summary table function

Duckle (3)
  • Classify / PII Detectread 2026-09-25

    classify each column by semantic or PII type from regex match rates

  • Column Profileread 2026-09-25

    per-column count, nulls, distinct, min, max and quartiles

  • Column Profile (Advanced)read 2026-09-25

    single-column profile with null share, approx distinct and pattern match rates

KNIME Analytics Platform (2)
  • Statisticsread 2026-09-27

    Compute moments, quantiles, missing counts, histograms and nominal value counts per column

  • Statistics Viewread 2026-09-27

    Show summary statistics of numeric and text columns in a view

mlpack (1)
OpenRefine (4)
Pentaho Data Integration (1)
Polars (5)
Power Query M (4)
  • Table.ApproximateRowCountread 2026-09-27

    Return an approximate row count without a full scan, where the source supports it

  • Table.IsEmptyread 2026-09-27

    Report whether the table has no rows

  • Table.Profileread 2026-09-27

    Return per-column statistics: min, max, average, standard deviation, count, null count, distinct count

  • Table.RowCountread 2026-09-27

    Return the number of rows

RapidMiner Studio (3)
  • Extract Statisticsread 2026-09-27

    Output the per-column statistics shown in the results view as a table

  • Quality Measuresread 2026-09-27

    Score each column for ID-ness, stability, missing share and text-ness

  • Statisticsread 2026-09-27

    Compute basic statistics and value distributions for every column

VisiData (3)
  • describe-allread 2026-09-23

    Open Describe Sheet for visible columns of all sheets on the stack

  • describe-selectedread 2026-09-23

    Open Describe Sheet for visible columns of the selected sheets

  • describe-sheetread 2026-09-23

    Open Describe Sheet: errors, nulls, distinct, mode, min, max, sum, median, mean, stdev

Yggdrasil Decision Forests (2)
  • infer_dataspecread 2026-09-27

    Scan a dataset and write each column's name, type, dictionary and statistics

  • show_dataspecread 2026-09-27

    Print a dataspec file as readable text: column types, missing counts, top values

Background

Data profiling examines the data in an existing source, such as a database or a file, and collects statistics or informative summaries about it.[1] Data profiling draws on descriptive statistics such as minimum, maximum, mean and percentiles, and on metadata such as data type, uniqueness and null values.[1] In DuckDB, the SUMMARIZE statement returns summary statistics for a table, a view or a query.[2]

When to use it

Data profiling may be used to find out whether existing data can easily be used for other purposes, and to assess its data quality.[1] The metadata gathered by profiling can be used to discover problems such as illegal values, misspellings, missing values, varying value representation and duplicates.[1] Profiling may also discover metadata of a source database, such as value patterns and distributions, key candidates, foreign-key candidates and functional dependencies.[1]

In data warehouse development, a light profiling assessment is advised immediately after candidate source systems have been identified, to clarify early whether the correct data is available at the appropriate level of detail.[1] Profiling may also be run after data has been loaded into staging or data marts, to help ensure that cleaning and transformations were done correctly.[1] Profile a table before writing anything that depends on it, and again after loading it, so a surprise shows up while it is still cheap to fix. (Meridian's view)

Checking what should be the key of a table is especially helpful, because duplicate instances of the key are either serious errors or reflect a business rule the extract, transform and load design has not taken in.[3] In DuckDB, the quantiles and percentiles that SUMMARIZE reports are approximate values.[4]

In pandas, DataFrame.describe summarises each analysed column's distribution excluding NaN values, and by default analyses only numeric columns.[5]

In Polars, the output of DataFrame.describe is not guaranteed to be stable, and using it programmatically rather than for interactive exploration is not recommended.[6] Use a profile to look at the data, and write the checks a pipeline relies on as explicit queries whose output you control. (Meridian's view)

The method

The computational complexity of profiling increases from single-column to single-table to cross-table profiling.[1] At the level of single fields, profiling checks that contents agree with their basic data definitions and domain declarations, and it is especially valuable to see how many rows have null values.[3]

In DuckDB, SUMMARIZE launches a query that computes min, max, approx_unique, avg, std, q25, q50, q75 and count over all columns, and returns them with the column name, the column type and the percentage of NULL values.[4] In DuckDB, SUMMARIZE can be used as a subquery, which allows a table to be created from the summary.[4]

Calculating the exact number of distinct elements of a multiset requires memory proportional to that number, which is impractical for very large data sets.[7] HyperLogLog is an algorithm that approximates the number of distinct elements in a multiset, using significantly less memory than an exact count.[7] HyperLogLog applies a hash function to each element and estimates the count from the maximum number of leading zeros in the binary representation of the hashed values.[7] HyperLogLog reduces the variance of that estimate by splitting the multiset into numerous subsets and combining the subsets' estimates with a harmonic mean.[7] HyperLogLog can estimate cardinalities of more than 10^9 with a typical standard error of 2%, using 1.5 kB of memory.[7] HyperLogLog is an extension of the earlier LogLog algorithm, which itself derives from the 1984 Flajolet–Martin algorithm.[7] In DuckDB, approx_count_distinct calculates the approximate count of distinct elements using HyperLogLog, and approx_quantile calculates an approximate quantile using T-Digest.[8]

Compared with the alternatives

In Amazon Web Services (AWS) Glue Data Quality, analyzers gather statistics about data but cannot evaluate and assert a condition, while rules can.[9] In AWS Glue Data Quality, the rule RowCount > 5 uses a row count analyzer to find the number of rows and compares that count with the expression > 5.[9] Profile a table to learn what it holds, then turn what you learn into checks that pass or fail. (Meridian's view)

Sources

  1. [1]Data profilingsecondary source
  2. [2]SUMMARIZE Statementdocumentation
  3. [3]Kimball Design Tip #59: Surprising Value of Data Profilingsecondary source
  4. [4]Summarizedocumentation
  5. [5]pandas.DataFrame.describe — pandas 3.1.1 documentationdocumentation
  6. [6]polars.DataFrame.describe — Polars documentationdocumentation
  7. [7]HyperLogLogsecondary source
  8. [8]Aggregate Functionsdocumentation
  9. [9]Data Quality Definition Language (DQDL) referencedocumentation