---
title: "Common Data Quality Issues and How to Fix Them"
canonical: "https://www.owox.com/blog/articles/common-data-quality-issues-how-to-overcome"
updated: "2026-10-14"
---

# Common data quality issues and how to fix them

The common data quality issues, how to spot each one, and the kind no checklist names: a join that drops and repeats rows at once. With a run on demo data.

[Data Quality](/blog/topics/data-quality) · Updated October 14, 2026 · [Vadym Kramarenko](/team/vadym-kramarenko) · 7 min read

![Common data quality issues and how to fix them](https://cdn.owox.com/www/content-marketing-article/common-data-quality-issues-how-to-overcome/thumbnail/1.png/public)

**The common data quality issues are duplicates, missing values, inconsistent formats, invalid values, outdated data, schema changes and data spread across silos.** Each has a known symptom and a known check. The issues that cost analysts the most are the ones that do not look like issues: the query runs, the total is plausible, and it is wrong.

This page lists the common issues with how to catch and fix each one. Then it covers the less obvious kind, with a run on demo data in which a single join understated a quarter of ad spend by 24% without producing one duplicate or one NULL.

**_Note:_** _Originally published in December 2021. Fully updated in October 2026 with a join-grain run on the \[Demo\] E-commerce data, new fixes and current checks._

## The common data quality issues at a glance

| Issue | What it looks like | How to catch it | Fix |
| --- | --- | --- | --- |
| Duplicates | The same order, user or event counted twice | Count rows per key that should be unique | Deduplicate on a declared key at load |
| Missing values | NULL country, empty campaign, no customer ID | NULL share per field you group or join by | Fix at the source, or map to an explicit “unknown” |
| Inconsistent formats | “US”, “USA”, “United States”; dates in three styles | Distinct values per field, compared with a list | One mapping table, applied once |
| Invalid values | Negative quantity, a date in the future | Range rules per field | Reject or quarantine the row |
| Outdated data | A feed that stopped on Friday | Latest date in the table vs today | Freshness threshold and an alert |
| Schema changes | A column renamed or retyped upstream | Compare columns and types with the last load | Contract with the source, or a staging view |
| Silos | Ad spend in one place, orders in another | Two totals that should match and do not | One warehouse, joined on purpose |

All of these are worth checking every day, and none is new. [Data quality checks on every Data Mart](/blog/articles/data-quality-checks-data-mart) gives runnable SQL for six of them and what they found on demo data.

## The issues that look like correct numbers

A duplicate or a NULL can be found by a rule that looks at one table. The next issues cannot. **Each table is clean on its own, and the problem only exists in how they are combined or defined.** These are the ones behind most “why does your number differ from mine?” conversations.

### A join at the wrong grain

Ad spend arrives as one row per campaign, platform and day. Orders arrive as one row per order. A natural way to get cost per order by campaign is to join the two on date and campaign and add up the spend.

We ran exactly that on the \[Demo\] E-commerce Data Marts (`owox-demo.ecommerce`, an online shop’s orders, sessions and ad spend) for July to September 2026:

|  | Spend |
| --- | --- |
| Spend in the ad table | **$49,062.54** |
| Spend rows with no order for that campaign that day: dropped by the join | −$24,412.76 |
| Spend rows matched by 2 to 6 orders: repeated by the join | +$12,484.25 |
| **Spend after the join** | **$37,134.03** |

**976 of the 1,500 spend rows vanished, 161 were counted up to six times, and the two errors partly cancelled.** The result is 24% low. Divided by the same 573 orders, spend per order reads $64.81, where the unjoined spend gives $85.62. Nothing errors, and no row in either table is a duplicate.

```
SELECT
  ROUND(SUM(a.spend), 2) AS spend_after_join,
  COUNT(DISTINCT o.order_id) AS orders
FROM `owox-demo.ecommerce.ad_performance` AS a
JOIN `owox-demo.ecommerce.orders` AS o
  ON o.order_date = a.date
JOIN `owox-demo.ecommerce.sessions` AS s
  ON s.session_id = o.session_id
  AND s.campaign = a.campaign
WHERE a.date BETWEEN '2026-07-01' AND '2026-09-30'
```

The usual warning about joins is fan-out: rows multiply and totals go up. That is half of it. **When one side is sparse, an inner join also drops rows, and the net can go either way.** A total that is too low by a quarter is far more believable than one that doubles.

Per campaign, the same join goes both ways:

| Campaign | Real spend | After the join |
| --- | --- | --- |
| brand awareness - us | $8,677 | $11,873 (+37%) |
| (campaign not reported) | $6,069 | $2,472 (−59%) |
| retargeting - cart abandoners | $5,390 | $5,107 (−5%) |
| search - competitor terms | $2,984 | $1,055 (−65%) |

The busiest campaign has orders on most days, so its spend repeats. Quieter campaigns have days with no orders, so their spend disappears. **The ranking changes too:** “(campaign not reported)” falls from the second-largest spender to the seventh. Anyone reallocating budget from this table would move money for the wrong reason.

The fix is to bring both sides to the same grain before joining, and to keep rows that have no partner:

```
WITH daily_spend AS (
  SELECT date, campaign, SUM(spend) AS spend
  FROM `owox-demo.ecommerce.ad_performance`
  WHERE date BETWEEN '2026-07-01' AND '2026-09-30'
  GROUP BY date, campaign
),
daily_orders AS (
  SELECT o.order_date AS date, s.campaign,
    COUNT(*) AS orders
  FROM `owox-demo.ecommerce.orders` AS o
  JOIN `owox-demo.ecommerce.sessions` AS s
    USING (session_id)
  WHERE o.order_date BETWEEN '2026-07-01' AND '2026-09-30'
  GROUP BY date, campaign
)
SELECT
  ROUND(SUM(spend), 2) AS spend,
  SUM(orders) AS orders
FROM daily_spend
FULL OUTER JOIN daily_orders USING (date, campaign)
```

It returns the full $49,062.54 and all 1,942 orders of the quarter. Name the CTEs differently from their columns: in BigQuery, a CTE called `spend` with a column called `spend` makes `SUM(spend)` read the whole row and fail.

**The check that catches this in any query: compare each side’s total before and after the join.** If spend after the join is not the spend in the source table, the join changed the number. The [JOIN vs UNION guide](/blog/articles/bigquery-join-and-union-difference) covers fan-out in more depth.

### One name, two definitions

“Revenue” with cancelled and returned orders, and “revenue” without them, are both correct and both called revenue. On the same demo quarter they differ by about $149,000. Nothing in either table is wrong. The issue is that the definition lives in each person’s query instead of in one place. [One source of truth for your numbers](/blog/articles/source-of-truth-your-numbers-answer-to) walks through that case.

### A partial day that passes every check

Yesterday’s load arrived, so a freshness check passes. It arrived with half the rows, so the total is low. Only a comparison with the usual volume for that weekday catches it. The same applies when one source is current and another stops: any ratio between them drifts with no error. The [checks article](/blog/articles/data-quality-checks-data-mart) shows both on the demo data.

### Mixed currencies and time zones

A sum over order lines in two currencies is a number with no unit. A day boundary in UTC and a day boundary in the shop’s local time put late-evening orders on different dates. Both show up as small, persistent gaps between two reports, never as an error. Write the currency and the time zone of every money and date field next to the data.

## The Turning Point

OWOX Data Marts

See your first report built in real time. _15 minutes._

1.  Connect your data warehouse
2.  Pick your metrics
3.  Get a live Google Sheets report

In the time it takes to write a ticket. Then imagine never writing that ticket again.

[Book a Demo](/book-a-call)

We'll use your actual use case

## Where the issues come from

Most issues enter at one of four points, and the fix belongs at the same point:

1.  **Collection.** A tag fires twice, a form accepts free text, a source changes its export. Fix it at the source if you can; otherwise, record the rule you use to clean it.
2.  **Loading.** A connector fails halfway, a schema changes, a backfill overlaps a daily load. Check volume and freshness after every load.
3.  **Modelling.** Joins at the wrong grain, a filter someone forgot, a key that is not unique. Test totals before and after each join.
4.  **Reporting.** Each report re-implements the metric, so two dashboards drift apart. Define the metric once, where every report reads it.

The first three are engineering problems with known tools. **The fourth is an ownership problem**, and it is the one that produces most of the disagreements people notice. [Who is responsible for data quality](/blog/articles/who-is-responsible-for-data-quality) covers who should own each stage.

## How to stop the same issue coming back

Fixing a number once is a reply to one question. Stopping it from returning takes three things:

*   **A declared key for every table.** Write down what one row is. Uniqueness checks depend on it, and so does every join.
*   **Joins defined once.** If every analyst joins spend to orders in their own query, each picks a different grain. Define the relationship once and reuse it.
*   **One definition per metric, with an owner.** The owner is the person who answers when the number is questioned.

In OWOX Data Marts, an analyst writes each table’s SQL once as a Data Mart in your warehouse, declares its key and its relationships to other Data Marts, and the relationships form the [data model](/product/data-modeling) reports are built on. Business users then pick fields from joined Data Marts in Google Sheets and get the join the analyst defined, not one they improvise. [Data quality](/product/data-quality) checks run against those same Data Marts, on demand or on a schedule, and each result comes with the SQL that ran.

That does not remove the need to think about grain. It moves the thinking to one place, where it can be reviewed once instead of rediscovered in every report.

You can [try OWOX Data Marts](https://www.owox.com/app-signup) on your own warehouse.

## Your New Normal

Turn your data into decisions.

Governed data marts give you the clean foundation ML needs to actually work.

*   No AI hallucinations
*   Analyst-governed definitions
*   Every number traces to SQL

[Get started free](https://www.owox.com/app-signup)

## FAQ

## Frequently Asked Questions

How does data profiling help data quality?

Profiling shows what a table actually contains: row counts, distinct values, NULL shares, minimums and maximums per column. It is how you find the rules to check. A field with three spellings of one country, or a quantity that is sometimes negative, shows up in a profile long before it shows up in a report.

Why automate data quality checks?

Because a check that runs only when someone remembers misses the day the data breaks. Scheduled checks on the reported tables catch a stale feed, a half-loaded day or a broken join before the number reaches a meeting, and leave a dated record of what was checked.

Is master data management needed for data quality?

Large organisations use master data management to keep one record per customer or product across systems. Most analytics teams need a smaller version of the same idea: one mapping table per entity, one declared key, and one definition per metric, applied once in the warehouse rather than in every report.

How do you check data quality?

Write each rule as a query that returns rows only when something is wrong: duplicate keys, NULLs in fields you join or group by, values out of range, a latest date older than expected, a day with far fewer rows than usual. Run the checks on the tables people actually report from, every day, and compare totals before and after every join.

How can I avoid data duplication?

Declare what one row is for every table, deduplicate on that key when data is loaded, and count rows per key as a daily check. Most duplicates in reports are not in the source at all: they come from joining a table to another that has several rows per key. Bring both sides to the same grain before joining.

What are the most common data quality issues?

Duplicates, missing values, inconsistent formats, invalid values, outdated data, schema changes and data split across silos. Less visible but often more costly: joins at the wrong grain, one metric name with two definitions, partially loaded days, and mixed currencies or time zones. These produce numbers that look right and are not.

## Who wrote this

![Vadym Kramarenko](https://cdn.owox.com/www/webflow/6798e703afdf787334941cde_Vadym-Kramarenko.png/public)

[Vadym Kramarenko](/team/vadym-kramarenko) · Growth Marketing Manager

Vadym Kramarenko is a Growth Marketing Manager at OWOX, where he drives user acquisition and product-led growth strategies. He hosts the OWOX podcast, interviewing analytics professionals about data-driven marketing, attribution, and reporting best practices. Vadym specializes in turning complex analytics concepts into practical, actionable marketing frameworks.

[LinkedIn](https://www.linkedin.com/in/vadim-kramarenko/) · [All articles](/team/vadym-kramarenko)

[Data Quality](/blog/topics/data-quality)

## Related articles

[Reporting · What is Ad Hoc Reporting and Why It's Important · November 18, 2024](/blog/articles/ad-hoc-reporting)

[Data Quality · Who is Responsible for Data Quality? A RACI for Analytics Teams · October 14, 2026](/blog/articles/who-is-responsible-for-data-quality)

[Data Quality · What is Data Freshness? How to Measure It and How Fresh a Decision Needs · October 14, 2026](/blog/articles/data-freshness-and-business-decision-making)

[See all articles →](/blog/articles)

## Book a call

You are booked. The invitation is on its way to , with the calendar invite and the meeting link.

[Reschedule](#)

[Cancel](#)

## Schedule a meeting

You are booked. The invitation is on its way to , with the calendar invite and the meeting link.

[Reschedule](#)

[Cancel](#)

## Links

Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link.

- [Data Quality](https://www.owox.com/blog/topics/data-quality) — /blog/topics/data-quality.md
- [Vadym Kramarenko](https://www.owox.com/team/vadym-kramarenko) — /team/vadym-kramarenko.md
- [Data quality checks on every Data Mart](https://www.owox.com/blog/articles/data-quality-checks-data-mart) — /blog/articles/data-quality-checks-data-mart.md
- [JOIN vs UNION guide](https://www.owox.com/blog/articles/bigquery-join-and-union-difference) — /blog/articles/bigquery-join-and-union-difference.md
- [One source of truth for your numbers](https://www.owox.com/blog/articles/source-of-truth-your-numbers-answer-to) — /blog/articles/source-of-truth-your-numbers-answer-to.md
- [Book a Demo](https://www.owox.com/book-a-call) — /book-a-call.md
- [Who is responsible for data quality](https://www.owox.com/blog/articles/who-is-responsible-for-data-quality) — /blog/articles/who-is-responsible-for-data-quality.md
- [data model](https://www.owox.com/product/data-modeling) — /product/data-modeling.md
- [Data quality](https://www.owox.com/product/data-quality) — /product/data-quality.md
- [try OWOX Data Marts](https://www.owox.com/app-signup)
- [Reporting · What is Ad Hoc Reporting and Why It's Important · November 18, 2024](https://www.owox.com/blog/articles/ad-hoc-reporting) — /blog/articles/ad-hoc-reporting.md
- [Data Quality · What is Data Freshness? How to Measure It and How Fresh a Decision Needs · October…](https://www.owox.com/blog/articles/data-freshness-and-business-decision-making) — /blog/articles/data-freshness-and-business-decision-making.md
- [See all articles →](https://www.owox.com/blog/articles) — /blog/articles.md