Data warehouses: what they are and when you need one

"We should get a data warehouse" comes up in most growing companies at some point, usually after a meeting where three people brought three different revenue numbers. Sometimes a warehouse is the answer. Often a simpler step solves the problem for another year or two. This guide explains what a warehouse is and how to tell which situation you are in.

What a data warehouse is

A data warehouse is a database designed for analysis. It collects data from your operational systems (billing, CRM, e-commerce, support, ads) into one place, in a shape that makes questions across months and systems fast to answer.

The databases behind your applications are built for a different job: handling many small reads and writes quickly. Think of them as the tills in a shop, recording one sale at a time. A warehouse is the back office where someone spreads out a year of receipts and looks for patterns.

Warehouses usually have four traits:

• Organized by subject: customers, orders, products, rather than by which app produced the data.
• Integrated: the same customer has the same ID and the same name, whichever system the data came from.
• Historical: it keeps how things were, not only how they are now.
• Stable: loaded data is not edited in place, so a report run twice gives the same answer.

Database, warehouse, data lake

The lines have blurred. PostgreSQL handles a surprising amount of analysis at small scale, and modern warehouses can query files in a lake directly. Snowflake vs. BigQuery vs. Postgres compares three common choices in more detail.

How data gets in: ETL and ELT

ETL (extract, transform, load) pulls data from each source, cleans and reshapes it, then loads it into the warehouse. ELT loads the raw data first and does the transformation inside the warehouse, usually with SQL managed in a tool such as dbt. ELT has become the common approach because cloud warehouses have plenty of computing power and raw data stays available if a rule needs to change.

Either way, the work is the same in substance:

1. Extract from each system on a schedule, often nightly or hourly.
2. Transform: rename fields consistently ("client" and "customer" become one thing), fix types, handle missing values, and encode business rules such as how revenue is counted.
3. Load into tables built for questions: fact tables for events (orders, payments, sessions) and dimension tables for descriptions (customers, products, dates).

Signs you need a warehouse

Reports slow down the app. Analysts run heavy queries at night so customers do not notice. You need a separate place for analysis.

Key numbers disagree. Finance, sales and marketing each have a revenue figure. A warehouse with agreed rules produces one, though the agreement is the hard work, not the technology. See the single source of truth playbook.

Monthly reporting is a spreadsheet assembly line. If someone exports from five tools every month and joins them with lookups, that is a manual warehouse.

You need history the source systems do not keep. Many apps store only the current state. If you want to see how a customer's plan changed over time, you have to capture it yourself.

More than one or two people analyze data. Shared tables, version-controlled definitions and access control start to matter.

Signs you do not need one yet

• Most questions come from one system. Query it directly, or a read replica of it.
• The data is small. A few million rows is comfortable for PostgreSQL with sensible indexes.
• Nobody would own it. A warehouse without someone to maintain the pipelines goes stale within months.
• Your business model is still changing. The schema will change every quarter. Wait until it settles.

Cheaper steps before a warehouse

A read replica. A copy of your production database, kept up to date by the database itself. Analysts query it without slowing the app. This is often the single most useful first step.

Database views. A handful of SQL views that join and pre-aggregate the tables people ask about (daily revenue by product, active customers by month) give you much of the benefit of a modeled warehouse. If SQL is new to you, SQL for business analysts covers the basics.

A BI tool that connects to the sources directly. For a small team whose data lives in SaaS tools, connecting those tools to a BI tool can replace the spreadsheet assembly line without any pipeline to maintain.

Local analytics engines. Tools such as DuckDB run fast analytical SQL over CSV and Parquet files on a laptop.

If you do build one

• Start with two or three sources that answer the most pressing questions, such as billing and CRM. Add more once those are trusted.
• Write the definitions first. What counts as revenue, what counts as an active customer. A warehouse whose numbers do not match expectations is ignored.
• Add checks to every pipeline. If today's load has 90% fewer orders than yesterday's, stop and alert someone.
• Plan for access. A warehouse only pays off if people can get answers from it.

Where clariBI fits

clariBI is not a warehouse, and it does not need one to be useful.

• No warehouse yet: connect business apps directly (Stripe, HubSpot, GA4, Google Ads, Meta Ads, API connectors and MCP apps), upload files, or connect a PostgreSQL, MySQL, SQL Server or Oracle database with a read-only user. The database connection guide covers the setup, and the database docs list the fields for each engine. Questions can combine sources by date or by a shared column.
• BigQuery: the BigQuery connector reads a table or the result of a SQL query you write, so you can aggregate in BigQuery and send clariBI the summary. See BigQuery and other data stores for Snowflake and other warehouses, which today go through a file export.
• Row limits: each database sync copies up to a plan-dependent number of rows per table. That is another reason to point clariBI at views with daily or weekly totals rather than raw event tables.

A practical next step: write down the three questions your team argues about most often, then check which systems hold the data for each. If the answer is one or two systems, start with views and a direct connection. If it is five systems and a spreadsheet, it is time to plan a warehouse.