Kindly fill up the following to try out our sandbox experience. We will get back to you at the earliest.
The 8 column statistics a data profiler computes, what a bad value looks like for each, when to run one instead of a monitor, and the limit it cannot cross.
Updated on
Data profiling is the process of running statistics over a dataset, column by column, to find out what the data actually contains before anyone builds on it. A profiling run reads a table and returns, for every column, how many values are missing, how many different values there are, what the smallest and largest values are, what formats the values take and how those values are distributed. The output is a description of the data as it stands, not a verdict on it.
That distinction is the whole point of the discipline. Profiling tells you a customer email column is 18 percent empty and holds three different address formats. It does not tell you whether those emails belong to the right customers. Everything else in this article follows from that boundary.
Profiling belongs to the first stage of any data work: before a migration, before a new pipeline, before a model is trained, before a dashboard is trusted. It is the input to both data quality management and to the governance work that decides who owns a dataset and how it may be used.
Every profiling tool worth running computes some version of the eight statistics below. The names vary between vendors. The measurements do not. Read the table first, then the sections that follow for what each one catches and what to do when it looks wrong.
| Statistic | What it measures | What a bad value looks like | What you do about it |
|---|---|---|---|
| 1. Null rate | Share of rows where the column is NULL or an empty string | Anything above 0 percent in a column documented as mandatory, or a jump from 2 percent last month to 40 percent this month | Add a null rate monitor with the threshold set just above the historical value, and fix the source rather than filling the gap downstream |
| 2. Distinct and unique count | How many different values appear, and how many appear exactly once | Distinct count below row count on a column you treat as a key; 4,000 distinct values in a status column whose enum has six | Stop joining on it until you know why, and add an allowed values check on the enum |
| 3. Cardinality ratio | Distinct count divided by row count | A customer identifier at 0.30 when you expected 1.0; a country column at 0.90 where you expected an enum of about 200 values | A low ratio on a supposed key means duplication. A high ratio on a supposed category means free text has been typed into it |
| 4. Minimum and maximum | The smallest and largest value, and the range between them | Negative quantities, a date of birth in 1900 or 2087, a maximum equal to a sentinel such as 9999-12-31, timestamps in the future | Add a range check on the column, and treat sentinel values as nulls in every downstream calculation |
| 5. Pattern and format distribution | How often each format signature occurs, such as three digits, a dash, four digits | More than one dominant pattern in a column that should have one, or a date column stored as text holding both 2026-09-06 and 06/09/2026 | Pick the target format, add a regular expression check, and parse the rest at the source rather than in every query that reads it |
| 6. Length distribution | Minimum, maximum and average character length of the values | A maximum length equal to the declared column width, which usually means silent truncation; a minimum of 0 where an empty string is standing in for NULL | Widen the column at the source and reload, and normalize empty strings to NULL so the null rate stops lying to you |
| 7. Duplicate rate on the business key | Share of rows that share a business key with at least one other row | Anything above 0 percent on a key you join or aggregate on | Find whether it is a genuine duplicate or a versioned record, then either deduplicate at load time or add the version column to the join |
| 8. Referential overlap | Share of foreign key values with no matching row in the parent table, sometimes called the orphan rate | Anything above 0 percent, and it is more common than teams expect because most cloud warehouses do not enforce foreign keys | Add an orphan check as a scheduled test, because the database is not going to raise this one for you |
The null rate is the share of rows where a column holds NULL or an empty string. It is the first number to read because it is the one that silently changes the meaning of every average, count and join downstream. A column that is 18 percent empty makes an average that is computed over 82 percent of the data while being reported as if it covered all of it.
The bad value is not a fixed number. On a nullable comment field, 60 percent empty is normal. On an order date, anything above zero is a defect. What matters more than the absolute level is the movement: a column that has sat at 2 percent for a year and is now at 40 percent has a broken upstream job, and that is visible only if you kept the previous profile to compare against.
These are two different numbers and they are routinely confused. Microsoft states the difference plainly in its Power Query data profiling documentation: distinct refers to the overall number of different values in a column, while unique refers to values that have only a single instance. A column of 10,000 rows with 9,998 distinct values and 9,996 unique values has two values that each appear twice.
That gap is the whole finding. If the column is supposed to be a key, those repeats will fan out the next join you write and double a revenue figure without raising an error. If the column is a category, a distinct count far above the documented list means free text has been entered where an enumeration was expected, and every filter written against it is now missing rows.
Cardinality ratio turns the distinct count into something comparable across tables of different sizes. A ratio at or very near 1.0 means every row holds a different value, which is what you expect from a key, a timestamp or a free text field. A ratio near zero means a small set of repeated values, which is what you expect from a status, a country or a product category.
The bad value is a ratio that contradicts what the column is supposed to be. A customer identifier at 0.30 is not a key, it is a foreign key you have mislabeled or a table with three rows per customer. A country column at 0.90 is not a category, it is free text. Both findings change the query you were about to write, and both are invisible from a schema definition.
The minimum and maximum are the cheapest checks in profiling and they catch the errors that embarrass a team in front of a customer. Negative quantities on an order line. A discount above 100 percent. A date of birth in 1900 that came from a form default, or in 2087 that came from a typo. A maximum of 9999-12-31 on an end date, which is a sentinel meaning open ended rather than a real date, and which will produce a customer lifetime of eight thousand years in the next cohort report.
Write down the legitimate range for the column, add a check that enforces it, and treat sentinel values as nulls rather than letting them flow into arithmetic.
A pattern profile groups the values of a column by their format signature and counts how often each shape occurs. A phone column might come back as 92 percent matching a three digit, three digit, four digit pattern, 6 percent carrying a country prefix and 2 percent holding text such as "n/a". A healthy column has one dominant pattern. A column with three patterns above 10 percent each is a column that was populated by three different systems that never agreed on a format.
This is the statistic that finds dates stored as strings, identifiers that changed format after a migration, and postcodes that lost their leading zero in a spreadsheet. Fix it once at the source with a parse and a regular expression check, rather than writing the same repair into every query that reads the column.
Length profiling reports the minimum, maximum and average character length of the values in a column. Two findings come out of it that nothing else catches. The first is silent truncation: when the maximum observed length is exactly equal to the declared width of the column, the data was almost certainly cut off on load and nobody was told. The second is the empty string masquerading as a null, which shows up as a minimum length of zero while the null rate reads as clean.
It also catches mixed standards. A country code column with lengths of both two and three characters holds both ISO alpha 2 and alpha 3 codes, and any join against a reference table will match only half the rows.
The duplicate rate is the share of rows that share a business key with at least one other row. It is deliberately measured on the business key rather than on the whole row, because a row that differs only in its load timestamp is still a duplicate for every reporting purpose. Two order rows with the same order number will double the revenue total whether or not the rest of the columns differ.
Above zero percent on a key you aggregate on is a defect until proven otherwise. The question to settle before fixing it is whether these are genuine duplicates or versioned records that were loaded as a history. If they are versions, the fix is to add the version or effective date column to the join rather than to delete rows.
Referential overlap measures how many values in a foreign key column have a matching row in the parent table, and the inverse, the orphan rate, is the number that matters. An order table with 3 percent of its customer identifiers pointing at customers who do not exist will quietly drop 3 percent of revenue out of every inner join written against it, and inflate every average computed from what remains.
This is the statistic that has changed most in the last few years. In an operational database the engine refuses to insert an orphan. Most cloud warehouses accept foreign key constraints as documentation and do not enforce them, so the check that used to be free is now a test somebody has to write. Profiling a foreign key column against its parent is how you find out whether anyone did.
The eight statistics above are grouped by most tools into three techniques. The grouping is useful because it tells you what you can profile with one table in front of you and what needs more than one.
Most teams stop after structure and content because those two can be run by one person against one table in an afternoon. Relationship profiling is where the expensive defects hide, because a join that silently drops rows produces a number that looks reasonable and is wrong.
A profiling number is only as good as the rows it was computed over, and most default settings do not read every row. This is the part of profiling that is almost never written down, and it is the reason two people profiling the same table get different answers.
Microsoft documents the behavior for Power Query in its data profiling tools reference, last updated 28 August 2025: by default Power Query performs its profiling over the first 1,000 rows of the data, and you have to select the "Column profiling based on top 1000 rows" message in the lower left of the editor to switch it to the entire dataset. A null rate read from that default is a null rate for the first thousand rows, which on a table loaded in date order means the oldest thousand rows.
Databases sample too, for their own reasons. The PostgreSQL query planning configuration reference sets default_statistics_target to 100, which is the level of detail ANALYZE collects per column unless a column specific target is set with ALTER TABLE SET STATISTICS. Raising it improves the planner estimates and lengthens the ANALYZE run. Those statistics exist to plan queries rather than to audit data, so they are a useful cross check and never a substitute for a real profile.
Three rules follow from this. Record whether a profile was sampled or full before you quote a number from it. Run full scans on the columns that carry money, identity or dates, and sample everything else. When a profile disagrees with what a person tells you about a table, check the sample size before you check the person.
These two get treated as the same activity and they answer different questions. Profiling asks what does this data look like right now. Monitoring asks whether it has moved from what it looked like before. You need both, and running the wrong one is how teams end up with either a stale document nobody reads or an alert channel nobody opens.
| Question | Data profiling | Data quality monitoring |
|---|---|---|
| When it runs | On demand, before you build on a dataset or change it, plus on a schedule per dataset | Continuously, on every load or on a fixed cadence |
| What it answers | What does this data look like right now | Has this data moved away from what it looked like |
| What it produces | A set of statistics per column | An incident when a statistic crosses a threshold |
| Who reads the output | The engineer or analyst who is about to use the table | Whoever is on call for the pipeline |
| Does it need a threshold | No | Yes |
| What it costs | A full or sampled scan of the table on each run | A small repeated query, plus the cost of every false alarm |
| How it usually fails | It is run once during onboarding and never again, so the document ages out | Thresholds are set by guesswork, the channel fills with noise and people mute it |
The link between the two is the useful part. A profile is where a threshold comes from. If a column has held a null rate between 1.8 and 2.4 percent for six months, the monitor threshold is 3 percent, not a number somebody picked in a meeting. This is the practical difference between data quality and data observability, and it is why a profiling run that is never compared against the previous one wastes most of its value.
The video shows the handover in practice: the assistant reads the profiling results for a table, finds two columns holding negative values with no monitor watching them, proposes a monitor with a test type and a threshold, and creates it only after an explicit approval. That sequence, profile first, threshold from the profile, monitor after approval, is the working pattern this whole section describes.
A profile measures shape. It cannot measure truth. A price column where every value was multiplied by one hundred by a broken currency conversion upstream will profile as perfectly healthy: no nulls, sensible range, one clean pattern, high cardinality. Every statistic passes and every number is wrong.
Three classes of defect are invisible to profiling, and knowing them stops a team trusting a green profile more than it deserves.
This is where profiling hands over to the rest of the discipline. Rules that assert what a value must be belong to data validation, row count and freshness behavior belong to data observability, and meaning belongs to a catalog with a named owner. Profiling is the measurement that tells the other three where to look.
Profiling earns its place for three reasons, each better stated as what it prevents. It improves data quality by finding missing, inaccurate and inconsistent values before anything is built on them: a 40 percent null rate found in a profiling run costs an hour, and the same rate found in a board report costs the credibility of every report beside it.
It gives teams an understanding of their own data that no schema provides. Formats, relationships and dependencies are properties of the values, not of the column definitions, and they are what data governance decisions actually rest on: who owns this dataset, which columns hold personal data, and which downstream systems break if it changes.
It saves time by automating discovery. The alternative is exploratory queries written by hand, one analyst at a time, with the results kept in nobody in particular head. A stored profile replaces that, and the saving compounds with every new joiner.
The process runs in six steps. The names below are the ones already used on this page. What each step now carries is the specific thing to do inside it.
Steps 4 and 6 are the two that separate a profiling habit from a profiling exercise. Everything before them can be automated. Neither of them can.
Five technologies changed what a profiling tool can do, and they are the reason a modern profiler looks nothing like the SQL scripts it replaced. Machine learning turns the history of previous runs into an expected value for the next one, so a tool can flag a shift rather than just report a number. Data visualization turns a distribution into a chart a business owner can read without SQL. Natural language processing extends profiling to text columns such as reviews and support tickets, where a pattern signature means nothing. Cloud computing made full table scans on large tables affordable enough to schedule. Data governance software is where the results are stored, so a profile becomes part of the record of a dataset instead of a file on somebody laptop.
What matters when choosing between tools is narrower than the feature lists suggest. Six requirements decide whether a profiling tool gets used after the first month.
| What the tool has to do | Why it matters | What to ask before you buy |
|---|---|---|
| Profile without writing SQL | The people who know whether a value is wrong are usually not the people who write SQL | Can an analyst run a profile from the catalog page for a table, without a ticket |
| Keep the history of every run | A single profile is a snapshot. The finding is in the difference between two of them | How long are previous runs kept, and can two runs be compared side by side |
| Cover every source, including the ones with no connector | The uncovered systems are usually the legacy ones holding the worst data | What happens to a source with no native connector, and can it still be documented and profiled |
| Turn a profile into a monitor | A finding that does not become a check is a finding you will make again next quarter | Can a threshold be created directly from a profiling result, and who has to approve it |
| Respect masking and classification | Profiling reads real values, including personal data, and a profile can leak what a query would not | Are classified columns masked in the profile output, and is the masking driven by policy rather than by a setting |
| Say whether the run was sampled or full | A sampled null rate quoted as a full one is how a bad number becomes an agreed number | Does the output state the sample size on every statistic |
Decube is number one on that list because it does all six in one place, which is the point of buying a platform rather than assembling one. Profiling runs from the asset page in the catalog with no SQL, the statistics tab carries minimum and maximum, distinct rows and null counts per column, previous runs are kept so two can be compared, sensitive columns are masked in the output according to the classification policy, and a result can be turned into a quality monitor without leaving the page. Pricing is published: Starter is 175 US dollars per user per month from 21,000 a year with a minimum of ten users, and Growth is 225 US dollars per user per month from 54,000 a year with a minimum of twenty users. If you want to see it against your own tables, request a demo.
Five things make profiling harder than the tooling suggests. Each one has a practical answer.
Seven practices, each stated as something to do rather than something to believe. The Panoply guide to data profiling best practices and tools covers similar ground from a warehouse loading angle and is worth reading alongside this.
These sit inside the wider set of data quality management best practices rather than replacing them. Profiling is the measurement step; the rest of the program decides what happens when the measurement comes back badly.
Profiling earns its budget by moving the discovery of a defect from the point where it costs an hour to the point where it costs a quarter. A null rate found in a profiling run is a ticket. The same null rate found in a regulatory return is an incident, a correction and a conversation with a regulator.
What a team gets from doing this consistently is narrow and real: fewer failed migrations, fewer joins that silently drop rows, monitor thresholds that came from evidence rather than from a meeting, and a shared description of every important dataset that new people can read instead of asking for.
Start with one table that matters. Run the eight statistics across every column, write down the expectation for each, store the result where the table lives, and reprofile in a month. The second run is where profiling starts paying.
Data profiling is the process of running statistics over a dataset, column by column, to find out what the data contains before anyone builds on it. A profiling run returns, for every column, how many values are missing, how many different values there are, the smallest and largest values, the formats the values take and how they are distributed. It describes the data as it stands and does not judge whether the data is correct.
Point a profiler at the source system rather than at an extract, run the statistics across every column rather than a chosen few, read each statistic against a written expectation for that column, store the full result where the dataset lives, fix what you find at the source, and tell the people who depend on the dataset what changed. Then reprofile on a schedule, because the comparison between two runs is where most findings come from.
There are three. Structure profiling reads the shape of the data: data types, lengths and format patterns. Content profiling reads the values: null rates, distinct and unique counts, cardinality and duplicates. Relationship profiling reads across tables to find which columns join to which and where a foreign key points at a row that does not exist. Structure and content need one table. Relationship profiling needs at least two.
Profiling a customer table returns, for the email column, a null rate of 18 percent, 9,998 distinct values across 10,000 rows, a dominant format pattern covering 92 percent of the values and a maximum length exactly equal to the declared column width. Those four numbers say the column is nearly a fifth empty, holds two repeated addresses, carries a second format in 8 percent of rows, and has probably been truncated on load.
Profiling asks what this data looks like right now and produces a set of statistics. Monitoring asks whether the data has moved away from what it looked like before and produces an incident when a statistic crosses a threshold. Profiling runs on demand before you build on a dataset. Monitoring runs continuously afterwards. The link between them is that a profile is where a sensible monitoring threshold comes from.
Profiling measures what is in the data without asserting what should be there. Validation asserts a rule and fails the records that break it, such as requiring an order date to be present and to fall in the past. Profiling is how you find out which validation rules are worth writing, and validation is how you stop the same defect arriving again.
They usually mean the same activity. Database profiling normally refers to profiling tables inside a relational database, while data profiling covers the same statistics applied to any source, including files, streams and warehouse tables. Note that some database vendors also use profiling to mean query performance profiling, which is a different subject entirely.
The ones that can hand an agent both the data and the context needed to judge it: a catalog with a named owner and a description for every asset, column level lineage so the agent can trace where a value came from, profiling statistics and quality monitors so it can tell a healthy table from a broken one, and classification policies so sensitive columns are masked before they reach the model. Decube covers all four in one platform, which is why profiling results, lineage and monitors can be read together rather than assembled from separate tools.
Gartner publishes its evaluations behind a paywall and we do not restate a ranking we cannot read and verify, so no list here is presented as theirs. What is verifiable is the set of requirements a governance tool has to meet: a catalog covering every source including those with no native connector, column level lineage, profiling and quality monitoring in the same place as the catalog, classification and masking driven by policy, and published pricing. Evaluate against those requirements on your own data rather than against a ranking.
The same answer applies: we do not restate a Gartner ranking we cannot read and verify. For observability specifically, the requirements to test are coverage of freshness, volume, schema and field level health, monitors whose thresholds are derived from profiling history rather than set by hand, incident routing to a named owner, and lineage good enough to show what breaks downstream when a check fails. Decube provides all of those alongside the catalog, so a quality incident is visible on the asset page where people already work.
First step to AI Readiness
Table of Contents
Sneak peek from the data world.
Bring lineage, quality, governance, and AI together in one platform—so your teams and AI agents can act with confidence.
_For%20light%20backgrounds.svg)
.svg){kind=small}
.webp)