The Biggest Data Quality Problems I've Seen in Real Projects
Discover the most common data quality problems I've seen in real projects, from duplicate records and broken joins to time zone errors. Learn how to avoid these pitfalls and build a reliable data foundation for your organization.
In my experience working on data projects, data quality issues often lurk beneath the surface, causing downstream chaos in analytics, reporting, and decision-making. Here are some of the most common pitfalls I’ve encountered and how they can derail even the best-intentioned initiatives.
1. Duplicate Customers
Duplicate customer records are a frequent headache, often arising from inconsistent data entry, merged systems, or poor deduplication logic. For example, a customer might appear twice in the database with slightly different names or addresses. This is a common occurrence when a sales team is manually entering customer details into the CRM and can be alleviated somewhat with better processes during data entry. As long as human data entry is involved though, there will always be mistakes.
This leads to inflated metrics, fragmented customer insights, and wasted resources on redundant outreach. A robust deduplication strategy, including fuzzy matching and centralized identity resolution, is essential. However, care must be taken with these strategies to avoid merging records that are in fact distinct. Using unique identifiers like phone numbers is a safer strategy and don't forget to include monitoring and logging for any automated deduplication so changes can be reverted.
2. Broken Joins
When tables fail to join correctly, it’s a red flag for data quality. This often happens due to mismatched keys (e.g., a customer ID stored as a string in one system and an integer in another) or inconsistent data formats. In one project, a broken join between time series tables led to the under reporting of weekly electricity usage affecting budgets and financial forecasting.
Ensuring schema alignment and enforcing data governance rules can prevent this but this can be challenging when your trying to integrate data from different systems managed by different suppliers. With this kind of architecture taking a data lake approach might be the way to go. You can collect all the data in a central location before processing and storing it with a consistent schema in your data warehouse. This comes with its own challenges however and you may just be shifting the problem of broken joins in your queries to broken ETL pipelines.
3. Time Zone Mistakes
Time zone errors are subtle but devastating. A single misconfigured time zone in a timestamp field can skew event logs, user activity reports, or even fraud detection models.
This needs to be solved on the governance level with a strategy like enforcing all timestamps to be in UTC and/or making sure the time zone data is included in the timestamp field itself. Depending on the system it might make more sense to have local times used, for example you have international customers on your SaaS product and their data stays separate. For an internal reporting system it might be easier to have everything stored as UTC to make queries and downstream reporting easier. Times can then be converted in the reporting layer to reflect the user's local time.
4. Unexpected NULL Values
NULLs in critical fields (e.g. missing email addresses in a customer table or blank dates in a transaction log) can break pipelines and distort analytics. In one case, a NULL in a "last purchase date" field caused a customer retention model to incorrectly flag active users as churned. Proactive data validation and default value strategies (e.g. "unknown" for missing data) help mitigate this. Always ask yourself "should NULL even be allowed in this field?". Removing the possibility from the start can prevent most of these issues that come from data entry.
5. Slowly Changing Dimensions
Slowly changing dimensions (SCDs) occur when data evolves over time, but systems fail to track changes. For example, a customer’s industry might change, but the historical data remains static, leading to inaccurate cohort analysis. Without proper SCD handling (e.g. type 2 slowly changing dimensions with versioning), historical trends become misleading.
6. Poor Source System Validation
Inadequate validation at the source often results in dirty data downstream. If a system accepts invalid data (e.g. negative sales amounts or non-numeric values in a field meant for dates), it cascades into downstream tools. In one project, a lack of source validation led to mislabeled data in a data warehouse, requiring days of cleanup, new data request and backfills. Implementing strict data rules, automated checks, and collaboration with source systems is key.
Why This Matters
Data quality is a technical challenge but it should also be a business priority. If business users can't trust the data then the whole system will fail. Addressing these issues early prevents costly rework, ensures reliable insights, and builds trust in your data. Whether you’re dealing with duplicates, broken joins, or time zone errors, investing in data quality is the foundation of any successful analytics initiative.
Need help improving your data platform?
If you're running analytics workloads on Microsoft Azure and would like an independent review of your ETL pipelines, architecture or data quality processes, I'd be happy to help.
Whether you're planning a platform modernization or simply want greater confidence in your reporting, get in touch to discuss how I can support your team.