.png){kind=logo}
Last month, I watched a senior analyst spend three days hunting down a $47 variance in the trial balance. Three days. For forty-seven dollars.
But here's the thing—I get it. When your books don't tie, everything else falls apart. The close gets delayed, your KPIs start telling lies, and suddenly you're explaining to the CFO why the numbers they presented to the board last week might be wrong.
The real problem isn't the $47. It's that you don't have a system to catch these issues before they multiply into something that keeps you awake at night, wondering what else you missed.
After fifteen years in finance and countless close cycles that felt more like detective work than accounting, I've learned something: most data quality issues fall into three buckets. Miss any of these, and you're playing whack-a-mole with your GL every month.
Missing records are the sneakiest troublemakers. That invoice your AP team swears they entered? Nowhere to be found. The payroll journal entry that should've posted Friday? Still sitting in some processing queue. These gaps create those maddening subledger-to-GL variances that make you question everything.
I once spent a weekend tracing what looked like a $200K revenue shortfall, only to discover that our EDI feed had been dropping transactions with special characters in the description. Twenty-three invoices, all sitting in a failed-processing folder nobody knew existed.
Duplicate records are the shape-shifters. They look just different enough to slip past your standard checks. "Invoice #12345" and "INV-12345" are the same bill, but your system treats them like strangers. Bank feeds re-importing the same ACH batch. Foreign exchange revaluations running twice because someone hit "refresh" at the wrong moment.
The worst part? Duplicates don't just inflate your balances—they create audit trails that make no sense. Try explaining to external auditors why the same vendor payment appears to have processed twice, three days apart.
Misclassified entries are the most frustrating because the money's there—it's just hiding in the wrong place. Software licenses getting coded as meals. Capital expenditures landing in a sales department cost center. VAT calculations posting to the wrong entity.
Individually, these might seem like minor housekeeping. But when you're trying to analyze gross margins or defend a budget variance, misclassifications turn your financial reports into fiction.
Before you start building fancy detection algorithms, you need what I call the "unsexy infrastructure"—the stuff that makes everything else possible.
Your chart of accounts, cost centers, vendor master, customer master—treat these like the critical master data they are. Not just static lists, but living datasets with effective dates, approval workflows, and change logs. Every slight variation of a vendor name ("Apple Inc." vs "Apple, Inc." vs "Apple Computer") is a future headache waiting to happen.
Create transaction IDs that survive system boundaries. I learned this the hard way when we implemented a new ERP and lost the ability to trace transactions from source to GL. Now I use something like {source_system}-{original_key}-{event_date}. Boring? Absolutely. But it turns monthly deduplication from an art project into a SQL query.
Capture multiple timestamps—when the transaction happened, when it posted, when your system processed it. Half the "missing" transactions I've hunted down weren't missing at all—they were just late. There's a difference between a problem and a timing lag, but you can't tell which is which without proper timestamps.
And here's a tip that saved my team countless hours: when you resolve an exception, always record why. "Late posting," "duplicate entry," "mapping correction"—these reason codes become the foundation for better rules tomorrow. Or, if you're feeling ambitious, training data for automated systems that can spot patterns you might miss.
Control totals are your early warning system, but most people implement them wrong. Don't just count records—build expectations based on historical patterns.
I use rolling medians with median absolute deviation to set dynamic tolerance bands. If Mondays typically see 200-250 AP invoices and you're looking at 47, something's broken. The system should flag that automatically, not wait for you to notice during month-end reconciliation.
Look for broken sequences. Where your source systems use sequential numbering, window functions in SQL make gaps obvious instantly. In procure-to-pay workflows, left-join anti-matches reveal the classics: purchase orders without receipts, receipts without invoices, invoices without payments.
The most critical check? Tie-outs between subledgers and GL controls. AP aging should reconcile to AP control accounts. AR aging should tie to AR controls. Fixed asset subledgers should match FA GL balances. When variances exceed materiality thresholds, auto-generate exceptions with enough context to backtrace the issue.
One more thing—distinguish between truly missing data and cutoff timing. Not everything that looks missing is actually missing. Some postings just arrive after period close. Tag these separately so they don't clog your exception queue or trigger false alarms.
Start with the strict matches—vendor ID, invoice number, date, amount. That catches maybe 60% of duplicates. The other 40% are craftier.
Before you get fancy with fuzzy matching, just clean your data. Convert everything to lowercase, strip punctuation and common prefixes ("INV", "#", "REF"), normalize currency symbols. You'd be amazed how many "sophisticated" duplicates are just text formatting issues.
For the truly fuzzy matches, I use token-based similarity scoring on invoice numbers and descriptions, combined with date and amount tolerance windows. Same vendor, invoice numbers that are 90% similar, amounts within pennies, dates within three days—probably the same transaction.
Sometimes duplicates travel in packs. Instead of looking for pairs, cluster similar transactions by vendor and date range. Keep the most complete record (fewest null fields, most recent posting date) and flag the others for removal.
But guard against false positives. Recurring charges—rent, software subscriptions, utility bills—are supposed to look similar month after month. Tag service periods so legitimate recurring transactions don't jam your exception queue.
Listen, I love automation as much as anyone—but don't jump straight to machine learning black boxes. Your auditors won't thank you, and neither will the analyst who has to explain why the model reclassified $50K of legitimate travel expenses as fixed assets.
Start with rules you can defend in a board meeting:
Vendor-based defaults with memo keyword overrides
Tax posting logic that actually makes sense
"Impossible combinations" that should never happen
Then layer in lightweight natural language processing. Tokenize transaction descriptions, compute TF-IDF features, combine with vendor patterns and historical classifications. Random forests or gradient boosted trees give you both performance and explainability—you can actually see why the model made specific suggestions.
Keep automation on a leash. Only auto-apply classifications at high confidence levels (85% or above). Everything else goes to your AP team with a suggested account and a one-line explanation. Prioritize by materiality—a $15 office supply miscoding isn't urgent, but six-figure consulting fees in the wrong cost center definitely is.
Your dashboard should tell a story, not overwhelm with data. Group exceptions by entity, source system, and period. Show lineage so people can trace issues back to their root cause. Track metrics that matter: precision and recall for duplicate detection, percentage of spend requiring reclassification, aging of unresolved exceptions.
Most importantly, throttle your alerts. Real-time notifications for material items and period-end cutoff risks, but batch everything else into daily digests. Your team's attention is finite—use it wisely.
Don't try to solve everything at once. You'll overwhelm your team and probably introduce new problems.
Month 1: Build your foundation. Inventory data sources, implement basic reconciliations, start capturing reason codes. Fold these into your existing close procedures—they shouldn't feel like extra work. Update your month end close checklist to include sequence-gap scans, control total validations, and exception reviews at key milestones.
Month 2: Add smarter detection. Implement fuzzy duplicate matching, introduce rule-based classification checks, start measuring and tuning based on analyst feedback.
Month 3: Carefully introduce automation. Pilot supervised classification models with clear explanations. Keep auto-apply disabled until your metrics stabilize, then enable it conservatively.
By quarter-end, you won't have "solved" data quality—nobody does—but you'll have fewer surprises, faster closes, and a process your team trusts.
You'll know you're winning when close cycles shrink not because you're working longer hours, but because fewer issues slip through. Duplicate payments become rare exceptions instead of monthly fires to fight. Subledger variances stay below materiality thresholds and resolve quickly when they do appear.
Most importantly, your leadership team starts trusting the numbers again. Not because they never see exceptions, but because they understand the system that catches and fixes them.
Clean financial data isn't a destination—it's a discipline. Get the basics right, build reliable detection systems, combine explainable rules with targeted automation, and keep humans focused where judgment matters most.
Do that consistently, and you'll transform month-end from a dreaded fire drill into a smooth, predictable process that delivers numbers everyone can defend.
What's the data quality issue that keeps you up at night? I'd bet it falls into one of these three buckets.
