What a data warehouse is, and why you might finally need one

Every business ends up with the same problem. The numbers are all there, somewhere. The answer to the question you actually have is in none of them.

Take Pondside Coffee. It is a made-up roaster, and we will follow it through all ten parts of this series. It runs a café that takes payments on Square, sells bags of beans through a Shopify store, and ships a monthly subscription called Pond Club, billed through Stripe. What the green coffee costs lives in a Google Sheet that the owner updates when a shipment lands. Each of those tools comes with its own dashboard, and each dashboard is good at describing that one tool.

Now ask Pondside three questions:

• Do Pond Club subscribers still come into the café, or did the subscription quietly replace their visits? Stripe knows who subscribes. Square knows who buys a flat white. Neither knows about the other.
• Is the Saturday rush actually growing, or is it just summer? Square can show you last Saturday. To tell growth from season you need this September beside last September, hour by hour.
• Which beans earn the most once you account for what they cost? Sales are split between the café and the web shop, and costs are in a spreadsheet. Three systems for one answer.

None of these are exotic. They are the questions any owner, manager or analyst asks in their first month. And the usual way to answer them is the same everywhere: export a CSV from each tool, paste them into a spreadsheet, match rows up by hand, and do it again next month. The answer is out of date by the time it is finished, and it depends on who did the exporting.

That is the problem a data warehouse solves.

What a data warehouse is

A data warehouse is a database that holds a copy of the data from all the systems a business runs on, kept up to date and kept for good, and arranged so that asking questions of it is easy and fast. It is not where the business happens. Square still takes the payments and Shopify still takes the orders. The warehouse is where the business is understood.

The idea is older than most of the tools it now draws from. Bill Inmon, who wrote one of the first books on the subject in 1992, described a warehouse by four properties. They have held up well, and in plain English they come to this:

Subject-oriented

Organized around the things the business cares about, such as customers, orders and products, rather than around whichever app produced the data.

Integrated

The same customer is the same customer everywhere. A person who shops online and buys coffee in the café can be recognized as one person.

Time-variant

History is kept. You can ask what was true last March, not only what is true now.

Non-volatile

Data is added, not overwritten in place by the day-to-day running of the business. Yesterday’s numbers do not drift.

Notice what is not in that list: a size. Nothing says a warehouse has to hold petabytes or cost a fortune. A warehouse is defined by what it is for, and a two-person coffee roaster has the same need for one as a bank, just with fewer rows.

Why not just query the database you already have?

If your business runs on its own application, there is already a database behind it, usually Postgres or MySQL. It is tempting to point your reports at that and call it done. For a while it works. Then it does not, for three reasons.

The first difference is the deepest. Databases that run applications are tuned for what database people call OLTP, online transaction processing: find this order, update that balance, insert one row, and do it fast for many users at once. Warehouses are tuned for OLAP, online analytical processing: read a whole year of orders and add them up by week. Those are opposite workloads, and the best layout for one is the worst for the other. A transactional database stores each row together, so fetching one order is a single read. An analytical database stores each column together, so summing one column never touches the others. That one decision is behind most of the speed difference, and part three takes it apart properly.

The second is safety. A question like “revenue by customer, by month, for two years” reads every row it can find. Run it on the database your checkout uses and your checkout slows down while it runs. A copy, kept somewhere else, can be asked anything without risk.

The third is reach. Your app's database knows nothing about Stripe, your ad spend or the spreadsheet with your costs. The interesting questions are almost always the ones that cross those lines.

What goes into one

Strip away the vendor diagrams and every warehouse is made of the same four things.

1. The sources

The tools that run the business: a payments system, a store, a CRM, the app's own database, analytics, ad platforms, the spreadsheet everyone pretends is temporary. Each holds part of the picture.

2. A way to copy the data in

Something has to read each source and write what changed into the warehouse, on a schedule, without missing records or counting them twice. This is the unglamorous part, and the part that decides whether anyone trusts the numbers. Part four covers how it is done well: full and incremental syncs, handling deletes, rate limits, and the trap of trusting a column called updated_at.

3. The warehouse database

The engine that stores the copies and answers the questions. For a long time this meant a large cloud service priced for large companies. Today, for most businesses, it can be a single DuckDB database, which is what part two is about.

4. Models, and the ways in

Raw copies of fifteen tables are not answers yet. A thin layer of clean, well-named views turns them into things like daily_sales and customers that everyone can use the same way (part five). On top of that sit the ways people ask: SQL for the curious (part six), dashboards for the numbers you check every day, and now an AI assistant that writes the SQL for you, connected through the Model Context Protocol (part seven).

Signs you are ready for a warehouse

You do not need a warehouse because a blog post says so. You need one when some of these are true:

• Someone exports CSVs every week to combine numbers from two or more tools, and the result is a spreadsheet nobody else can reproduce.
• Two people get two different answers to the same question, because they pulled from different places or on different days.
• A tool forgets. Many tools hand over only recent history. Shopify, for example, gives an app the last 60 days of orders unless it has been granted access to all of them. If you want to compare this year with last, someone has to have kept last year.
• Reports are slowing the app down, or you have been asked not to run them during business hours.
• You want to ask an AI about your business and find yourself pasting exports into a chat window, one file at a time, hoping the numbers are current.

And the honest other side: if one tool really does answer every question you have, or you look at your numbers twice a year, a warehouse is more than you need. Use the tool's own reports and come back when the questions outgrow them.

Why this used to be hard, and is not now

For about twenty years, “we need a data warehouse” meant a project. You would sign up for a cloud warehouse built to spread queries across many machines, buy a pipeline tool to fill it, buy a BI tool to read it, and hire someone to hold the three together. That stack exists for companies with genuinely enormous data, and for them it is the right answer. For everyone else it was a large, recurring bill for capacity they never touched.

Most data was never big. The tools were built for the few who had big data, and everyone else paid for it.

That is not just a hunch. Jordan Tigani, who worked on Google BigQuery for years before founding MotherDuck, wrote in his 2023 essay Big Data is Dead that “the vast majority of customers had less than a terabyte of data in total data storage.” Meanwhile ordinary machines kept growing: a single server today has dozens of cores and more memory than many companies have data.

Three things changed at about the same time:

• Single-machine engines got very good. DuckDB, an open-source analytical database, can scan and aggregate hundreds of millions of rows on one machine, on its own. No cluster required.
• Syncing became a service. Copying Stripe or Shopify into a warehouse is a solved problem you can switch on, instead of code you maintain.
• AI assistants learned to use tools. With the Model Context Protocol, an assistant like Claude can read a warehouse's schema, write the SQL, run it and explain the answer. The person asking no longer has to know SQL at all.

Put together, a warehouse for a business like Pondside is now an afternoon of setup and a small monthly bill. For a sense of scale: a DuckHouse warehouse is a dedicated machine running DuckDB, priced from $0.0117 an hour while it runs, and it can stop itself when nothing is connected (the pricing page has the full table).

A first look at the answer

Here is the first of Pondside's three questions, asked of a warehouse holding copies of Stripe and Square. Do not worry about reading the SQL yet. The point is how little of it there is once the data sits in one place.

SELECT
  count(*) AS subscribers,
  count(*) FILTER (
    WHERE email IN (
      SELECT c.email_address
      FROM square.payments p
      JOIN square.customers c ON c.id = p.customer_id
      WHERE p.status = 'COMPLETED'
    )
  ) AS also_buy_at_the_cafe
FROM stripe.customers;

Two systems, matched on email, in one statement. Of Pondside's 1,190 subscribers, 227 have also bought something in the café as a customer Square recognizes. That is the beginning of an answer, and a better question follows from it straight away: are those 227 visiting more often or less since they subscribed? We will get there. The tables are named for the tool they came from, stripe.customers and square.payments, because that is how a warehouse fed by syncs is laid out: one schema per source.

Where this series goes

The Pocket Warehouse follows Pondside from scattered numbers to a warehouse it can question from anywhere. Each part stands on its own, and together they are the whole job:

• DuckDB, and why small data got fast: the engine, and why one machine is enough.
• Inside the engine: columns, compression and vectors, for the curious.
• Getting your data in: syncs that can be trusted.
• Shape it once: modeling that people and AI can read.
• The SQL behind every business question: a cookbook.
• MCP, explained: how AI assistants reach your data.
• Your warehouse, in your pocket: asking from your phone.
• DuckLake, explained (part 9, out October 27): for when the data keeps growing.
• Build a data warehouse in an afternoon (part 10, out November 10): all of it, start to finish.

The destination is a simple one. Pondside's owner, standing at a farmers' market stall on a Saturday morning, takes out a phone and asks: how does this morning compare with the same Saturday last year? And gets a real answer, from real numbers, in the time it takes to hand over a bag of coffee. Everything between here and there is what makes that answer right.