Spot data quality issues before they cost you

Bad data rarely announces itself. A sales lead notices the dashboard does not match the CRM. Finance finds two reports with different revenue for the same month. After a few of these, people stop trusting any report and go back to their own spreadsheets. The direct costs are real too: ad budget moved on double-counted conversions, commissions paid on refunded deals, a price change based on half the orders.

The good news is that most data quality problems fall into five predictable types, and each has a simple check. You do not need a data engineering team to run them.

The five common problems

1. Missing values

Empty fields where there should be a value:

• customers with no industry or company size, which breaks segmentation
• orders with no source or campaign, which leaves channel analysis incomplete
• invoices with no payment date, which shifts revenue into the wrong month

How to catch it: count the empty values in the columns that matter, every week. A sudden jump in the null rate usually means a form changed or an integration broke, not that customers changed.

SELECT COUNT(*) AS orders,
       COUNT(*) FILTER (WHERE utm_source IS NULL OR utm_source = '') AS no_source,
       ROUND(100.0 * COUNT(*) FILTER (WHERE utm_source IS NULL OR utm_source = '') / COUNT(*), 1) AS pct_missing
FROM orders
WHERE created_at >= now() - interval '7 days';

2. Duplicate records

The same thing counted twice. Causes include a customer signing up with two emails, a sync retry writing the same rows again, or a contact entered by hand twice with slightly different names.

How to catch it: group by the field that should be unique and look for counts above one.

SELECT order_id, COUNT(*)
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;

A small worked example shows why this matters. Say a shop has 1,000 real orders at an average of $50, so $50,000 of revenue. A sync retry duplicates 40 of them. Revenue now reads $52,000, average order value stays at $50, and order count reads 1,040. Nothing looks obviously wrong, but every number built on revenue or orders is 4% high, and cost per order is 4% too low.

3. Stale data

Data that stopped updating. A connection whose access expired three days ago, a CRM field nobody maintains, a stock count that reflects yesterday.

How to catch it: know when each source last synced, and check it before any important review. Also check the newest date inside the data, because a sync can succeed and still bring back nothing new.

SELECT MAX(created_at) AS newest_order FROM orders;

4. Wrong values

Values that are present and plausible, but wrong. These are the hardest to find:

• revenue in two currencies added together without conversion
• timestamps in UTC read as local time, which moves late-evening orders to the next day
• a status of "active" on a customer who cancelled last month
• test orders mixed in with real ones

How to catch it: define the ranges that make sense and flag anything outside them. Order quantities are positive. Dates are not in the future. A single order over $10,000 at a shop whose average is $50 gets a second look. These boundary checks catch more than you would expect.

5. Changed columns

The source system renames a field, changes its type or adds a new status value, and reports that depend on it break or quietly go wrong.

How to catch it: watch for columns appearing or disappearing, and for new values in fields that should have a fixed list (status, plan, country).

Three layers of checks

At the source. Freshness (when did it last update?), volume (is today's row count in the normal range?) and completeness (are key fields filled?). A 50% drop in daily orders is far more likely to be a broken connection than a collapse in demand.

At the metric. Is this month's revenue within a plausible range of last month's? Do the parts add up to the total (new + expansion - contraction - churn should equal the change in MRR)? Does the customer count in the CRM roughly match the customer count in billing?

At the business rule. A customer marked as churned with an active subscription, an invoice dated after the contract ended, negative revenue without a refund: each is a conflict someone who knows the business will spot and a generic check will not.

What clariBI does and what it leaves to you

clariBI has no data quality rules feature where you set thresholds and get alerts, but several parts of it help you see problems early:

• Source status and last sync. Settings > Integrations shows each source's status and when it last synced. The configuration panel has Test Connection, Sync Data and a preview of the stored rows. For API connectors it also shows the last sync's summary, including errors and skipped requests.
• Column changes. If a source's columns change, affected dashboards show "Some widgets need column rebinding" and let you pick the new column, instead of charting the wrong one.
• Changes flagged in reports. When a source changed in a way that affects the numbers, such as new categories or a jump in volume, the report notes the dates so a data change is not mistaken for a business trend.
• Numbers checked against the chart data. In AI dashboards and reports, any sentence that quotes a figure the chart data cannot back is removed before the text is saved.
• Failing refreshes stop. After five failed refreshes in a row, a dashboard switches to manual refresh so it does not keep showing half-updated data.

What it cannot do is know that your "revenue" column includes tax, or that the test account should be excluded. Those are rules you write down and check. The AI limitations page lists how missing values, duplicates, mixed formats and small samples affect answers, and the help article on data source errors explains each error message and its fix.

A routine for a small team

You do not need to check every field in every table. Start with the numbers leadership reviews every week.

1. List the three to five metrics that matter most (revenue, orders, new customers, churn) and the sources behind them.
2. For each source, write down who owns it, how often it should update and the known quirks (currency, time zone, test accounts).
3. Every Monday, before the review, check last sync for each of those sources and compare the headline numbers with the source system for one known figure, such as last week's order count.
4. Once a month, run the duplicate and missing-value queries above on the main tables.
5. When something is off, name an owner and a date. A freshness problem usually means reconnecting a source; the help article on sync failures, expired access and rate limits covers the common causes.

Track what you find. A simple log of "date, source, problem, fix" shows within a few months which sources cause most of the trouble.

Prevention beats detection

• Validate at entry. Required fields, fixed lists and type checks in your forms and CRM prevent most missing and wrong values.
• Test new connections against a number you know before anyone relies on them.
• Announce changes. Whoever changes a form, a CRM field or a plan name tells the people who report on it, before the change goes live.
• Document each source. Expected columns, update frequency and quirks, in one shared page. The post on practical data governance for growing companies has a lightweight template.

If you want to put a figure on what bad data is costing you, the cost of bad data has a method for estimating it.

Next step

Run the duplicate query on your orders or deals table today. If it returns rows, find out which sync or process created them before you look at another dashboard built on that table.