Most businesses have a Monday-morning ritual. Someone exports orders from the online store, invoices from the accounting system and contacts from the CRM. They paste them into a spreadsheet, fix the dates, remove the duplicates and match customers by eye. By lunchtime there’s a report. Next Monday they do it all again.
That’s data wrangling: getting raw data from wherever it lives into a shape you can analyse. It has always been the bulk of the work in analytics, and it still is.
The tools for it have changed a great deal in the last few years. The hard parts mostly haven’t. Knowing which is which is the difference between a data setup that runs itself and one that needs a hero every Monday.
What’s changed
Getting data out is largely solved
Not long ago, pulling data out of a SaaS tool meant writing and maintaining your own scripts against its API. Today, managed connectors such as Fivetran and Airbyte, and open-source libraries such as dlt, cover most common systems: Shopify, Xero, HubSpot, Stripe, Salesforce, Google Analytics. They handle paging, rate limits, incremental loads and changes to the source’s schema.
For most businesses, extraction is now a choice of tool, not an engineering project. Spend your effort on what comes after.
Load first, transform after
The standard pattern now is ELT: land the raw data in a warehouse untouched, then transform it there. An untouched copy of what each source sent means you can always rebuild, trace a number back to where it came from, or change a business rule without extracting everything again. Warehouse storage is cheap enough, in BigQuery, Snowflake, Databricks or plain Postgres at smaller scale, that there’s rarely a reason not to keep it.
Transformations are code
The spreadsheet steps (rename this, filter that, join these) now live as SQL or Python in version control, usually in layers:
- Raw: exactly what the source sent.
- Staging: cleaned and renamed, with consistent types, time zones and currencies.
- Marts: business entities and facts, joined across sources, ready for dashboards and AI.
Tools like dbt made this pattern mainstream. The benefit isn’t the tool. It’s that every number has a traceable lineage, changes are reviewed before they ship, and the whole thing reruns on a schedule without anyone opening a laptop.
Quality is tested, not eyeballed
Every run checks the data automatically: IDs are unique and never empty, every invoice belongs to a known account, statuses come from an expected list, and the newest data is recent enough. When a check fails, the pipeline stops and someone is told, before a wrong number reaches a dashboard.
Tests are the difference between “the report looks off” on Thursday afternoon and “the accounting sync failed overnight” at 7am.
What hasn’t changed
Identity is still the hard part
The same café buys beans online, gets invoiced for wholesale and sits in the CRM as a lead. It’s “Green Fern Coffee Ltd” in the accounting system, an email address in the online store and “Green Fern” in the CRM, with three different IDs. No connector fixes that for you.
Identity resolution means matching those records into one customer. We build it as a set of rules, each with a confidence: an exact email match is near certain, a matching company domain is strong, a similar name at the same postcode is good but not certain. Records are merged only when the evidence clears a threshold. The details matter. A shared email domain says two people work for the same business, unless the domain is gmail.com.
Then we measure it, on two counts: how often a merge is right (precision) and how many of the true matches we find (recall). One rule in our demo, similar business names in the same city, looked sensible on paper. Measured against the known answer, about one in five of its merges joined two different businesses, so it no longer merges anything on its own. A matcher you haven’t measured is a matcher you can’t trust.
Definitions are business decisions
What counts as revenue? With or without VAT? Before or after refunds and credit notes? On the order date, the invoice date or the payment date? These aren’t technical questions, and the data can’t answer them. Someone in the business has to decide, and the decision has to be written down, in the transformation code and in the documentation, so the dashboard, the finance report and any AI tool all give the same answer.
Time is subtle
Time zones, financial years, late-arriving records and history all cause quiet errors. One we see often: a sale is reported under the customer’s current sales rep rather than the rep at the time. When a rep leaves and their accounts move, last year’s numbers change unless you’ve kept the history. Decide which view each report needs, and model both if you need both.
A lesson from our own pipeline
When we moved our demo from generic CSV files to Shopify, Xero and HubSpot connectors, every number on the dashboard came out the same. That was the point: same business, different source systems.
What we didn’t catch straight away was that every customer ID had changed. Our IDs were derived from the source systems’ own keys, and the new sources had new keys. The AI-written briefings published before the switch referred to customers by their old IDs, so 23 of the 31 charts embedded in them quietly returned no rows. Our automated check passed, because it checked that each page opened, not that each chart had data in it.
We re-published the briefings and changed the check so it fails if any chart comes back empty. Two lessons apply well beyond our demo:
- IDs are a contract. If anything stores your IDs, whether saved reports, CRM fields, exported lists or AI outputs, then either keep the IDs stable when sources change, or know everything that holds them and re-issue it.
- Test outputs for content, not just existence. “The page loads” is not “the page is right”.
Where AI helps, and where it doesn’t
Large language models have become genuinely useful for wrangling:
- Reading unstructured text. Classifying free-text product descriptions, pulling fields out of PDFs and emails, summarising thousands of customer reviews. In our market-intelligence work for an appliance manufacturer, LLMs read the reviews and explain what customers like and dislike about each product.
- Proposing matches and mappings. Suggesting which messy category labels map to your standard list, or which two records are probably the same customer.
- Writing the first draft. Staging models, tests and documentation.
What they shouldn’t be is the system of record. Their output can vary from run to run, they can be confidently wrong, and they’re slow and costly when applied row by row. The pattern that works is hybrid: rules decide wherever rules can, AI handles the genuinely fuzzy remainder, and its suggestions are stored, reviewable and reused rather than generated afresh each run.
A short checklist
- Use a maintained connector before writing your own.
- Land raw data untouched, and transform it in the warehouse.
- Keep transformations in version control, in layers.
- Test uniqueness, relationships, expected values and freshness on every run.
- Resolve identities deliberately, and measure precision and recall.
- Write business definitions down where both the code and the people can see them.
- Treat IDs as a contract, and test outputs for content.
- Use AI for the fuzzy parts, with rules and review around it.
The goal is boring
Good wrangling is invisible. Data arrives, is checked, joined and documented, and the Monday-morning spreadsheet stops existing. That frees people to work on the questions instead of the plumbing, and it’s the foundation that dashboards and AI depend on.
See the result in our live demo, or tell us about your Monday-morning spreadsheet.