Data Profile

Describe what is actually in the data, one row per column.

Category: Preparation
Also known as: profiling, statistics, summary, describe, investigate, quality, nulls, distinct, cardinality, min max

What it does

Reads every row and reports on the columns instead of the rows. Each input column becomes one output row: how many values it holds, how many are null or blank, how many are distinct, its smallest and largest value, and for numbers the mean, median, standard deviation and total. Text columns also get their shortest, longest and average length. The last column lists each column's most common values with their counts, which is usually where the surprise is.

Inputs and outputs

Inputs	One.
Outputs	Profile.

When to use it

You've just connected a source and don't yet know what's in it
A column looks wrong and you want to see which values are actually there
Record what the data looked like on each run, so you can spot the day it changed

Examples

Just look at it

source → Data Profile     (leave the output unconnected)

Press Run, click the node, read the Data tab. Nothing is written anywhere.

Profile without disturbing the pipeline

source ─┬─► …the rest of your pipeline
        └─► Data Profile

One output can feed several nodes, so this costs the main line nothing.

Keep a history you can trend

Data Profile → Database output (Append)

Then you can answer 'when did the nulls jump?' months later.

A report to send

Data Profile → Excel Report

Just a few columns

Columns: email, state, signup_date

Tips

Leave the output unconnected and the numbers still appear in the Data tab. This is the normal way to use it.
A Test run only reads 50 rows, so its numbers describe those 50. Use Run for the real figures.
Nulls and blanks are counted separately: a null is a missing value, a blank is an empty or all-spaces string. Files usually give you blanks, databases usually give you nulls.
Most common values are skipped for columns with more than 1000 distinct values: the top five order ids tell you nothing, and skipping them is much faster.
Percentages are left empty when there are no rows at all, rather than reported as 0%, because an empty column is not a clean one.
Every figure is exact except the median, which is a very close approximation so that profiling a huge column can't run out of memory.
It never changes your data. The rows that come out are facts about the input, not the input.