Databases & data warehouses

When two systems give different answers to the same question, the first thing to check is whether they mean the same thing. “Customer” can mean anyone who has ever bought something in one and anyone with an active contract in the other. Neither number is wrong, but both are useless until someone decides which definition applies. The longer that question goes unanswered, the more reports and integrations get built on their own version.

One source per field

Every piece of data needs an owner: one system where it may be changed, and that the others read from. It sounds obvious, and it is in everyday work that it slips. Finance changes the customer’s address in the ERP, the salesperson changes it in the CRM, and which one counts depends on who is asking. We map where each piece of data may be changed and turn the other copies into exactly that: copies.

Keep operations and analysis apart

A report query that runs through a year of orders competes for processor, memory and locks with the users placing new orders in the same database right now. Users notice the system getting sluggish without being able to see why. A separate data warehouse for analysis removes that competition. Whether it is worth the cost depends on how often it happens and what the sluggishness costs, and that can be measured before you decide. We have built data warehouses in Azure Synapse, but the right platform depends on what you already run.

What a data warehouse needs to handle

• A data model where every field has an owner, a source and a definition.
• Analysis data refreshed at the pace decisions are made.
• History you can query back in time, not just today’s state.
• Schema changes planned so the system can keep running meanwhile, with a way back decided before they run.
• Indexes and query tuning where measurements show response times hurt, not everywhere.

A messy data model is technical debt paid by whoever builds on it: the next report, the next integration, the next new hire trying to understand why there are three customer tables. Once the model is in place, data analysis and reporting has something solid to count on.