Data Cleaning: The Unglamorous Work That Decides Your Answer

Data cleaning is the part of analysis nobody puts in the deck. It happens before the chart, before the model, before the meeting, and it is where most wrong answers are born. A dashboard built on duplicated rows looks identical to a dashboard built on clean ones. The chart renders. The number is bold and centered. It is also wrong, and nothing in the output will tell you.

This article is about the specific failures that survive a casual review: duplicates that are not exact copies, nulls that get counted as zeros, columns whose types quietly change under you, outliers that are real, and the documentation habit that turns a one-time cleanup into something you can rerun next quarter without rediscovering everything. There is a checklist at the end you can run against any new dataset.

Why data cleaning decides the answer before the analysis starts

Every analysis has an implicit contract: the rows mean what you think they mean. Break that contract and the sophistication of what comes after is irrelevant. A weighted average over a table where 4% of rows are duplicated is not a slightly worse weighted average, it is a different number with no error bar attached.

The reason this keeps happening is that broken data usually passes every check a human does by eye. The row count looks plausible. The top ten rows look fine. The total is in the right order of magnitude. The failure mode of dirty data is not an error message, it is a confident answer that is off by an amount you cannot see. Picking the right chart type will not save you here, because a bar chart of bad aggregates is a perfectly good bar chart.

So the discipline is this: before you compute anything, find out what you actually have.

Profile first, fix second

Profiling is a fixed set of questions you ask every table, every time, before you touch it:

• How many rows, and how many did you expect?
• What is the grain? One row per what, exactly? Write the sentence out loud: "one row per order line per shipment."
• Is the key you believe is unique actually unique?
• What percentage of each column is null, and does that match the business meaning?
• How many distinct values does each categorical column have? Twelve country codes or four hundred, including "US", "USA", "us", and " US"?
• What are the min and max of every numeric and date column? Dates in 1899 or 2073 are a schema story, not a business story.

A single pass gets you most of the way:

select
  count(*)                                  as rows,
  count(distinct order_id)                  as distinct_orders,
  count(*) - count(customer_id)             as null_customer_ids,
  count(*) filter (where amount = 0)        as zero_amounts,
  count(*) filter (where amount is null)    as null_amounts,
  min(created_at), max(created_at),
  min(amount), max(amount)
from orders;

Profiling takes ten minutes and routinely saves a day.

Duplicate handling starts with defining "the same"

Deduplication is treated as a mechanical step and it is actually a definitional one. There are three distinct problems hiding under the word "duplicate."

Exact duplicates. Byte-identical rows, usually from a load that ran twice or an export that overlapped a date window. These are the easy case, and even here, dropping them blindly is wrong if your grain legitimately allows identical rows. Two customers can buy the same product for the same price in the same second.

Entity duplicates. The same real-world thing recorded more than once with different values: "Acme Inc.", "Acme, Inc.", and "ACME INCORPORATED" with three different account IDs. No distinct will touch these. They require a matching rule you choose deliberately, usually a normalized key such as lowercased domain from the email address, or a fuzzy match you review by hand for the top matches.

Event duplicates from joins. The most common and least noticed. You join orders to shipments, a multi-row relationship, and every order with three shipments now contributes its revenue three times. Nothing is duplicated in the source. The duplication was created by your query.

Deduplicating without destroying information

select distinct is almost always the wrong tool, because it collapses rows using every column, which means one differing timestamp keeps both copies and one differing column you did not care about silently splits your groups. Prefer an explicit keep rule:

with ranked as (
  select *,
    row_number() over (
      partition by lower(trim(email))
      order by updated_at desc, id desc
    ) as rn
  from contacts
)
select * from ranked where rn = 1;

That query states the definition of identity (normalized email) and the definition of the survivor (most recently updated, ties broken deterministically by id). Both are decisions someone can disagree with, which is exactly why they should be written down rather than implied.

For join fan-out, the fix is to aggregate before you join, not to deduplicate after:

select o.order_id, o.amount, s.shipment_count
from orders o
left join (
  select order_id, count(*) as shipment_count
  from shipments group by order_id
) s on s.order_id = o.order_id;

Rule of thumb: after every join, check that your row count did what you expected. If joining a 10,000 row table to a lookup produces 10,400 rows, you have a duplicate in the lookup, not a bonus.

Nulls versus zeros: the distinction that breaks totals

A field has three possible states and most pipelines only model two. There is a known nonzero value, a known value that happens to be zero, and an unknown. Zero means "we measured, and the answer was nothing." Null means "we do not know." Conflating them produces errors that flow in both directions and are nearly impossible to spot downstream.

Consider average deal size where 30% of deals have a null amount because the field was optional until March. avg(amount) in SQL ignores nulls entirely, so you get the average of the 70% that were filled in. Replace those nulls with zero and the average collapses. Neither number is wrong mechanically. Only one of them answers the question you asked, and which one depends on why the values are missing.

The behaviors worth memorizing:

• count(*) counts rows. count(column) counts non-null values. The gap between them is your null count.
• sum, avg, min, and max skip nulls, so a null is not the same as a zero in any aggregate.
• null = null is not true. Comparisons against null yield unknown, so a where status != 'closed' filter silently drops every row where status is null.
• Inner joins drop rows whose key is null. Left joins keep them and produce nulls on the right side, which is a completely different kind of null from the ones in your source.

The "date spine" point deserves emphasis. Missing periods are the most under-detected null problem in reporting, because a month with no rows does not appear as a zero, it does not appear at all. Line charts then connect straight across the gap and imply continuity that never existed. If you are plotting a time series, join to a generated calendar table first, and read the comparison of chart types before choosing a form that hides gaps.

When you do encode these rules in SQL, keep them explicit and readable. coalesce is fine for simple defaults, but branching logic belongs in a documented conditional, and the CASE WHEN patterns and pitfalls guide covers the null-ordering traps that bite people here, especially the fact that a case without an else returns null rather than falling through to a default.

Type coercion and the silent failures it hides

Types are where cleaning bugs go to hide, because a wrong type usually still produces output.

Numbers stored as text. Sorting gives you "10" before "9". Sums either error out or, in loosely typed engines, concatenate. Currency symbols, thousands separators, and trailing spaces all force a column to text on import. Cast explicitly and count the failures rather than letting them coerce to null in silence.

Identifiers treated as numbers. Postal codes, account numbers, and product SKUs with leading zeros lose them the moment a tool decides they look numeric. 01234 becomes 1234 and never joins to anything again. Anything you will not do arithmetic on should be text.

Floating point money. Binary floats cannot represent most decimal fractions exactly, so summing hundreds of thousands of currency values in a float column drifts. Use a decimal or integer minor-unit representation for anything financial.

Dates. The worst offender by volume. 03/04/2026 is March 4 in the United States and April 3 nearly everywhere else, and a mixed-source file can contain both. Timestamps without timezones get reinterpreted as local time on the machine that reads them, which shifts a nontrivial share of events across day boundaries and quietly moves revenue between months. Spreadsheet exports carry serial numbers where a date used to be. Insist on ISO 8601 with an explicit offset at every boundary you control, and store in UTC while displaying in whatever timezone the business reasons in.

Booleans. true, TRUE, T, 1, yes, Y, and empty string all show up in the same column across systems. Normalize once, at ingestion, and treat anything unrecognized as null instead of false.

The general principle: cast on purpose, at a known point, and keep the raw value. A cleaning step that overwrites the original string leaves you unable to diagnose the 0.3% of rows that failed. A cleaning step that adds amount_clean beside amount_raw lets you count and inspect them.

Outliers deserve a decision, not a filter

There are three kinds of extreme values and they call for three different responses.

Errors. A negative age, an order for 4,000,000 units of a product you make 200 of a month, a unit mismatch where one source reports grams and another kilograms. These are wrong and should be corrected or nulled with a documented rule.

Legitimate extremes. One enterprise contract that is 60 times the median deal. Real revenue, real customer. Deleting it makes your average pretty and your forecast useless. The right move is usually to report the median alongside the mean, or to segment the population so the enterprise deals live in their own cohort.

Definition changes. A metric jumps 8x on a Tuesday because tracking started firing on a second page, or a pricing change reclassified a product line. This is not a data quality problem, it is a metadata problem, and the answer is an annotation on the series rather than a change to the values.

The habits that keep this honest: never delete outliers silently, always run the analysis with and without them and report if the conclusion changes, and if you cap values, say the cap out loud in the output ("values above the 99th percentile capped for the trend line, raw values in the appendix"). An outlier you removed without a note is indistinguishable from data you never had.

The documentation habit that makes data cleaning reproducible

The difference between cleaning and hygiene is repeatability. If your cleanup lives in a spreadsheet where someone deleted rows by hand, it is not a process, it is a one-time event that will need to happen again from scratch and will produce different numbers.

Five practices carry almost all the value:

1. Cleaning is code. A script, a SQL model, a notebook, anything that runs top to bottom. If you cannot rerun it on fresh data and get the same transformation, it is not done.
2. Raw stays raw. Land the source untouched, clean into a separate layer. When someone asks why a number changed, you need the before.
3. Every rule has a reason and a date. A one-line comment beside each rule: what it does, why it exists, when it was added, and who decided. "Excluding orders where test_flag = true, added 2026-04-12, per finance, these are QA records from the staging integration."
4. Assert, do not hope. Add checks that fail loudly: the key is unique, the null rate in this column is under 2%, no dates in the future, row count within 20% of the previous run. Cleaning that runs weekly without assertions rots quietly.
5. The dictionary lives with the model. Column meanings, units, grain, and accepted values belong next to the schema, not in a document nobody opens. This is one of the things worth evaluating when you compare data modelling tools, because tools differ enormously in whether definitions and tests are first-class or bolted on.

Write down the grain statement above all else. Most cleaning arguments dissolve the moment two people say out loud what one row is supposed to represent.

A data cleaning checklist you can run on any dataset

Before you touch anything

• State the grain in one sentence.
• Record the row count and compare it to what you expected.
• Confirm the source, extraction window, and whether the extract is complete or partial.

Structure

• Verify the primary key is actually unique.
• Check for exact duplicate rows.
• Check for entity duplicates using a normalized key.
• Re-count rows after every join and confirm the change was intended.

Types

• Cast every column explicitly and count cast failures.
• Ensure identifiers are text, not numeric.
• Confirm money is decimal or integer minor units, never float.
• Confirm dates parse unambiguously and carry a timezone.
• Normalize booleans and trim and case-fold categorical strings.

Missing values

• Compute null rate per column.
• Classify each null as absent, unknown, or structural.
• Convert sentinel values to real nulls.
• Join to a complete date spine so empty periods appear as zeros.

Values

• Review min, max, and percentiles on every numeric column.
• Classify extremes as error, legitimate, or definition change.
• Check units across every source contributing to the same column.
• Confirm categorical values match the expected set.

Reproducibility

• Cleaning exists as rerunnable code.
• Raw data is preserved unmodified.
• Every rule carries a reason and a date.
• Assertions fail the run when violated.
• Row counts logged at each stage.

Where automation helps with data cleaning, and where it does not

Automation is good at the part of this that is monitoring: noticing that a null rate jumped, that a duplicate appeared, that a sync did not land. It is not a substitute for the judgment calls above, because those are business decisions wearing technical clothing.

Skopx fits the monitoring and answering half of that. You can query PostgreSQL, MySQL, and MongoDB directly in chat, alongside data pulled through nearly 1,000 integrations, and get answers that cite where they came from. In practice that means the profiling questions from earlier stop requiring a context switch:

In our Postgres orders table, for each month this year show the row count, how many rows have a null amount, and how many have amount exactly zero.

It returns the counts month by month with the query it ran, which is usually enough to see the exact week an optional field became required or an integration stopped writing.

The recurring checks fit workflows, which you build by describing them in chat rather than dragging boxes around:

Every weekday at 7am, check the contacts created in our CRM in the last 24 hours for records whose normalized email domain already exists on another account, and post the matches to the #data-quality Slack channel with the account IDs.

That becomes a scheduled run with integration steps and a condition, and each run is inspectable step by step so you can see what it found and what it skipped. The limits are worth stating: workflows are acyclic, capped at 20 steps, have no human-approval step and no custom code step, and any AI step runs on your own provider key. The minimum schedule interval is 15 minutes.

What Skopx is not is an ETL platform, a warehouse, or a transformation framework. Heavy cleaning belongs in SQL models in your warehouse or a dedicated transformation tool, and dashboards belong in a BI tool. Skopx catches what falls between your tools: the check nobody scheduled, the drift nobody noticed until a number looked strange in a meeting. It is a paid product with no free tier, at $5 per month for Solo and $16 per seat per month for Team, with details on the pricing page.

Frequently asked questions

How much time should data cleaning take?

There is no honest universal ratio, and the ones quoted online are folklore. What is reliable: the first time you work with a source, budget more time for profiling and cleaning than for the analysis itself, and expect that number to fall sharply on later runs if you wrote the cleaning as code and added assertions. If it does not fall, your cleaning is not reproducible.

Should I replace nulls with zeros?

Only when zero is the true value. If a customer genuinely had no orders in March, zero is correct and should come from joining to a complete calendar rather than from a blanket replacement. If the value is unknown because a field was optional or a sync failed, leaving it null is the honest choice, and the analysis should either exclude those rows or state the coverage. Blanket coalesce(x, 0) is the single most common way a good pipeline starts lying.

Is it ever acceptable to delete outliers?

Delete errors, not extremes. If you can articulate why a value is impossible, such as a negative quantity or a timestamp before the system existed, remove or null it with a documented rule. If the value is merely large and real, keep it and change how you summarize instead: report the median, segment the population, or show the distribution. Any removal that changes the conclusion must be disclosed alongside the conclusion.

How do I find duplicates that are not exact copies?

Build a normalized match key and group on it. For people, lowercase and trim the email, or strip subaddressing. For companies, use the email domain or a normalized name with legal suffixes and punctuation removed. Group by the key, count rows per group, and review the largest groups by hand before you automate anything. Fuzzy string matching helps on the long tail, but it needs a human review pass on the borderline scores, because a false merge is far more damaging than a missed one.

What is the minimum documentation worth keeping?

Four things: the grain statement, the list of cleaning rules with a reason and a date on each, the assertions that must pass, and the row count at each stage of the pipeline. That is enough for someone else to understand what the data means, why it looks the way it does, and whether today's run behaved like last week's.

Does data cleaning matter if I am only making a quick chart?

Especially then. Quick charts are the ones that end up in decks and get quoted for months, and they are the ones nobody re-derives. The profiling pass at the top of this article takes ten minutes and catches the failures that actually change conclusions: fan-out from a join, a missing period, and a null rate that means the column was not collected for half the window.