Build a data warehouse in an afternoon

Nine parts ago, Pondside Coffee had numbers in four tools and answers in none of them. This part is the whole journey in one sitting, as a plan you can follow this afternoon, followed by what it takes to keep a warehouse honest for years.

If you have read the series, this is the checklist you will want to keep. If you are starting here, every step links to the part that covers it in depth. Either way, the shape of the finished thing is simple.

The plan, in six steps

1. Write down the questions first (an hour)

   Before any software, write down the ten questions you most want answered, in plain English, the way you would ask a colleague. Pondside's first three were whether subscribers still visit the café, whether the Saturday rush is growing, and which beans earn the most after cost. Next to each, note which tools hold the answer. That list decides which sources to connect first, which views to build, and how you will know the warehouse is working: when it answers them. (Part one is about why these questions are the reason a warehouse exists at all.)

2. Create the warehouse (ten minutes)

   In DuckHouse, a warehouse is one DuckDB database on its own machine. Your organization needs a card on file before it can create one. Then choose:

   • A size. Start with the smallest; for a business Pondside's size it is plenty, and you can resize later, which restarts the database for a few seconds. (Part two covers why one modest machine goes so far.)
   • A region. US East, unless whatever queries the database most runs somewhere else. The dashboard, the API, MCP and the syncs all run there.
   • A disk. It is sized when the database is created and fixed after that, so leave room to grow. The default is 10 GB, which holds Pondside's sample data many times over.
   • Stop when idle, or not. On, the machine stops when nothing is connected and wakes on the next connection. Off, it is always ready. More on the trade-off below.

3. Connect the sources (an hour, then let it run)

   Add a Connection for each tool on your list. Give each one the least access that works: a database user that can only read, a Stripe key restricted to reading. Choose the tables and a schedule, and the first sync starts straight away. Each source lands in its own schema (square, shopify, stripe), so nothing collides. A large first load works in stages and fills in over a few runs; some sources, Shopify among them, limit how fast anyone may read. (Part four covers full and incremental syncs, deletes, and the traps.)

4. Build the model (an hour)

   Put two thin layers of views on top of the raw tables. A clean layer fixes what every question would otherwise have to fix: cents to dollars, UTC to the shop's own clock, test orders out, text to numbers. A marts layer shapes the answers: one row per day per channel, one row per customer, one row per product. Pondside's whole model is about a dozen views. This is the one most questions start from:

   marts.daily_sales, from part five

   -- One row per day per channel. The table most questions start from.
   CREATE OR REPLACE VIEW marts.daily_sales AS
   SELECT sold_on AS day, 'cafe' AS channel, count(*) AS orders, sum(net_sales) AS revenue
   FROM clean.cafe_sales
   GROUP BY ALL
   UNION ALL
   SELECT ordered_on, 'web', count(*), sum(net_sales)
   FROM clean.web_orders
   GROUP BY ALL
   UNION ALL
   SELECT paid_on, 'subscription', count(*), sum(amount)
   FROM clean.subscription_payments
   GROUP BY ALL;

   (Part five builds every view and explains each decision.)

5. Ask the first questions (half an hour)

   Run your list against the marts in the dashboard's query console, in DuckPlus, or from DuckDB on your own machine. A first question worth asking of any business: where does the money come from?

   Revenue by channel, January to September 2026

   SELECT
     channel,
     round(sum(revenue)) AS revenue,
     round(100 * sum(revenue) / sum(sum(revenue)) OVER (), 1) AS share_pct
   FROM marts.daily_sales
   WHERE day BETWEEN DATE '2026-01-01' AND DATE '2026-09-30'
   GROUP BY channel
   ORDER BY revenue DESC;

   Result, 3 rows

   channel	revenue	share_pct
   cafe	428,916	58.1
   web	191,520	25.9
   subscription	117,812	16

   (Part six is a cookbook of these: growth, cohorts, retention, churn and margin.)

6. Connect an assistant (ten minutes)

   Add DuckHouse to Claude as a custom connector with the address https://duckhouse.co/api/mcp, approve read-only access on the consent screen, and the same warehouse answers questions in English, on your computer and on your phone. (Part seven explains how; part eight puts it in your pocket.)

That is the afternoon. What follows is the part nobody puts in the demo: keeping the numbers right, the bill small and the data safe, month after month.

A warehouse is built in an afternoon and earned over years, one trustworthy number at a time.

What it costs to run

A DuckHouse warehouse is billed for compute while its machine runs, metered in five-minute increments, plus storage. There is no subscription and no minimum. These are the shared-CPU sizes, with what each would cost running every hour of a month:

Storage is the disk, at $0.225 per GB-month, so the default 10 GB comes to $2.25 a month; plus the snapshots in object storage at $0.02 per GB-month. The pricing page has every size and rate.

The biggest lever is stop when idle. A warehouse that is asked a few questions a day and stopped the rest of the time costs a fraction of the “all month” column, starting from $0.0117 for each hour it actually runs. Two things to know before you switch it on:

• Waking takes about five seconds. The first query after a quiet spell will notice; the ones after it will not. Leave it off for a database behind a dashboard that people open all day.
• Every sync wakes it. A Connection that syncs every few minutes keeps the machine awake nearly all the time. If cost matters more than freshness, sync less often; hourly is plenty for most questions about yesterday.

You can also stop a database by hand. A database you stopped stays stopped, and no Connection will wake it, until you press Start: useful for a long holiday, or when you want to be certain nothing is running up compute.

Keeping the data safe

Here is exactly what DuckHouse does with a warehouse's data, from the docs:

• The primary copy is on an encrypted disk attached to the warehouse's machine, and DuckDB's writes are transactional.
• Every five minutes, and again whenever the database stops, a consistent snapshot is written to object storage, in a bucket that belongs to that database alone.
• If the machine's disk is ever lost, the database restores itself from its latest snapshot when it next starts. Writes since that snapshot, at most about five minutes' worth, would be lost.
• Every connection to it is encrypted with TLS.

Read the shape of that carefully. Snapshots protect against hardware failure. They are not a history you can browse, and there is no point-in-time restore yet, so do not count on them to undo a mistake. For a warehouse fed by syncs that is less frightening than it sounds: the raw data can be synced again from the sources, and the model is a dozen or so views you can recreate from their SQL. What you cannot recreate is anything that exists only in the warehouse, such as an uploaded CSV or a hand-made table of targets. Keep your own copy of those, and of your view definitions.

ATTACH 'quack:your-database.fly.dev:443' AS wh (TOKEN 'dh_your_token');

-- A table in the main schema, straight to Parquet
COPY (FROM wh.targets) TO 'targets.parquet';

-- Anything in another schema goes through wh.query()
COPY (FROM wh.query('SELECT * FROM stripe.invoices')) TO 'stripe_invoices.parquet';

Keeping access tidy

Two kinds of credential open a DuckHouse warehouse, and they are not interchangeable.

• Give every person and every tool its own credential, named for what it is. Revoking one then disturbs nothing else.
• Anything that only reads should be read-only. Database tokens cannot be: DuckDB's protocol has no read-only user yet. A read-scoped API key can, and the server enforces it by parsing every statement. Use one for dashboards and agents.
• Review connected applications now and then under Settings → Profile, and disconnect anything you no longer use. Disconnecting ends its tokens at once.

The tokens and API keys page has the details.

Checking the numbers

Syncs fail quietly. A password changes, an API starts rate-limiting, a source adds a column, and the warehouse keeps answering questions with numbers that are a few days stale. Nobody notices until a decision has been made on them. A handful of checks, run every Monday, catch nearly all of it. Here are four, run against Pondside as of its last sync.

Is anything stale? The newest record from each source, and how far behind the sync time it is. (On a live warehouse, swap the fixed timestamp for now().)

SELECT
  source,
  latest_record AT TIME ZONE 'America/Chicago' AS latest_record,
  date_diff('hour', latest_record, TIMESTAMPTZ '2026-10-01 06:00:00+00') AS hours_behind
FROM (
  SELECT 'square.payments' AS source, max(created_at) AS latest_record FROM square.payments
  UNION ALL
  SELECT 'shopify.orders', max(created_at) FROM shopify.orders
  UNION ALL
  SELECT 'stripe.invoices', max(created) FROM stripe.invoices
)
ORDER BY source;

Nine hours behind sounds alarming until you remember that the café closes at five. A freshness check has to be read against when the business actually happens; a web shop that has taken no order for nine hours on a weekday afternoon is a different story.

Are any days missing? Build a calendar and look for days with no sales at all:

SELECT day::DATE AS missing_day
FROM range(DATE '2025-01-01', DATE '2026-10-01', INTERVAL 1 DAY) AS calendar(day)
WHERE day::DATE NOT IN (SELECT day FROM marts.daily_sales WHERE channel = 'cafe')
ORDER BY missing_day;

Two gaps: Thanksgiving and Christmas Day, when the café was closed. Any other date on that list would be a sync that skipped a day.

Did anything arrive twice? Every source record has an id, and an id should appear once:

SELECT 'square.payments' AS table_name, count(*) - count(DISTINCT id) AS duplicate_ids FROM square.payments
UNION ALL
SELECT 'shopify.orders', count(*) - count(DISTINCT id) FROM shopify.orders
UNION ALL
SELECT 'stripe.invoices', count(*) - count(DISTINCT id) FROM stripe.invoices
ORDER BY table_name;

Does it match the source? The most convincing check of all: pick one number the source's own dashboard shows, and compute it the same way from the raw tables. For the café, that is a month's gross sales and tips, in the café's own month:

SELECT
  count(*) AS payments,
  sum(amount) / 100 AS gross_sales,
  sum(tip_amount) / 100 AS tips
FROM square.payments
WHERE status = 'COMPLETED'
  AND created_at >= TIMESTAMP '2026-09-01 00:00' AT TIME ZONE 'America/Chicago'
  AND created_at <  TIMESTAMP '2026-10-01 00:00' AT TIME ZONE 'America/Chicago';

If Square's sales summary for September says the same, the pipeline is sound. Notice that marts.daily_sales says about $49,120 for the café that month, a little less. The difference is refunds, which the clean layer subtracts and Square's gross figure does not. Being able to explain why two numbers differ, in one sentence, is what trusting a warehouse actually feels like.

Writing it down

The warehouse will outlive your memory of why net_sales subtracts refunds but not tips. Write that down where it will be found:

• In the names. A column called net_sales or revenue_after_refunds needs less explaining than one called amount.
• In the warehouse. COMMENT ON attaches a note to a table, view or column, and the marts.readme view from part eight puts the important notes where an AI assistant will read them.
• In a file you keep. The SQL for every view, in a folder or a repository, is both documentation and your recovery plan.

When one machine is not enough

A DuckDB warehouse is one database on one machine, and that is its strength: the data sits on a fast local disk beside the engine that reads it. It also sets the limits. The disk is fixed when the database is created, the machine lives in one region, and a query's working memory is bounded by the size you chose (larger queries spill to disk and slow down).

Most small businesses will not reach those limits for years. If yours grows into them, or you want a full history of every change to every table, there is a second shape: DuckLake, which keeps tables as Parquet files in object storage, with a catalog in an ordinary database, and lets the data grow as far as it needs. Part nine explains how it works, what it costs in latency, and when to choose it.

The whole series, in ten lines

• What a data warehouse is: one copy of everything, arranged for questions.
• DuckDB: why one machine now handles more than most businesses will ever have.
• Inside the engine: columns, compression and vectors, or how to read less.
• Getting your data in: syncs you can trust, and the traps in between.
• Modeling: clean views and marts that people and AI can read.
• The SQL cookbook: the dozen queries behind most business questions.
• MCP, explained: how an assistant turns a question into SQL, safely.
• Your warehouse, in your pocket: the same questions, from a phone.
• DuckLake: when the data keeps growing.
• And this one: building it in an afternoon, and keeping it honest after.

Pondside, a season later

It is a grey Monday in January. Pondside's owner opens a phone over the first coffee of the day and asks the warehouse to run the checks. Everything is fresh, nothing is missing, nothing arrived twice. Then a real question: which coffees sold best over the holidays, at the café and online together, and did the gift subscriptions from December renew?

The answer takes a few seconds and arrives with the SQL behind it. A year ago the same question meant three exports and an evening with a spreadsheet, and the honest answer was usually “I think so”. Now it is a number, with its working shown, and the evening is free.

That is all a data warehouse is for. Not the technology, which is the easy part now, but the moment a question stops being a project and becomes a sentence. We hope this series helps you get there. If you would like to build yours on DuckHouse, the quickstart takes about two minutes, and the sample data from this series is one download away.