StackLift
All terms

Glossary

Data warehouse

A central database built for reporting and analytics across the whole business.

A data warehouse is a central, structured database designed for analytics and reporting. It collects cleaned data from many source systems, organizes it into consistent models, and answers analytical SQL queries without slowing down the operational apps that produced the data. Cloud examples include Snowflake, Google BigQuery and Amazon Redshift.

Data usually arrives through an ETL or ELT pipeline and is modeled into tables that business users and BI tools can query. The warehouse becomes the shared source for dashboards, finance reports and metrics.

Warehouses are strongest for structured data and governed reporting. Teams with large volumes of raw files, logs or machine learning workloads often add a data lake or move to a lakehouse.

Start in 60 seconds

Put this to work on your project.

Get an AI-assisted, architect-reviewed scope, cost range, and timeline. Free, no login.

The complete guide

Data warehouse: the complete guide

Updated November 18, 2026 ยท Published by , Founder, StackLift AI. Written with AI assistance; how we make content.

Short answer

A data warehouse is a central, structured database built for analytics and reporting. It collects cleaned data from many source systems, organizes it into consistent models, and runs analytical SQL without slowing the apps that produced the data. It is the shared source behind dashboards, finance reports and business metrics.

What is a data warehouse?

Your app database is built for many small reads and writes: place an order, update a profile. A warehouse is built for the opposite: scanning a lot of history to answer questions such as revenue by region over time.

It brings together data from the CRM, billing, product and support tools, so everyone reads the same numbers.

Data warehouse vs. operational database

Two different jobs
PointOperational databaseData warehouse
Main jobRun the applicationAnalyze the business
WorkloadMany small transactionsFewer, larger queries over history
Data shapeNormalized for writesModeled for reading and reporting
UsersThe app and its usersAnalysts, BI tools, finance, data scientists
FreshnessAlways currentAs fresh as the pipeline

Running heavy reports on the operational database can slow the app, which is one reason to add a warehouse.

How is a data warehouse built?

  1. List the questions the business needs answered and who asks them.
  2. Identify sources and their owners.
  3. Move data in with an ETL or ELT pipeline.
  4. Model it into clear tables, often facts such as orders and dimensions such as customers.
  5. Define key metrics once, in code, and test them.
  6. Connect BI tools, set access controls and monitor freshness and cost.

What are the main kinds of data warehouse?

Common options
OptionExamplesGood for
Managed cloud warehouseSnowflake, Google BigQuery, Amazon Redshift, Azure SynapseMost teams: low operations, scales on demand
Warehouse-style engine on a lakehouseDatabricks SQL and similar (see data lakehouse)Mixed data and machine learning
PostgreSQL used as a warehousePostgreSQL, with replicas or extensionsSmall data and a single analyst team

Exact capabilities and pricing models differ by vendor and change often. Compare them against your data size, query patterns and existing cloud.

Data warehouse vs. data lake vs. lakehouse

Where each fits
TypeStoresStrengthLimit
Data warehouseStructured, modeled tablesGoverned reporting, simple SQLLess suited to raw files and unstructured data
Data lakeRaw files of any kindCheap, flexible storageWeak governance and quality without extra layers
Data lakehouseOpen files with a table layerOne copy for BI and machine learningMore to design and operate

The categories are converging, and many warehouses can now read open table formats such as Apache Iceberg. A warehouse is often the simplest choice when data is mostly structured and the main need is reporting.

Common mistakes

  • Loading everything before deciding which questions matter.
  • Letting each team define metrics its own way.
  • No cost controls, so queries run up the bill.
  • Weak access control on sensitive columns.
  • No monitoring of pipeline freshness.

How do you govern and secure a warehouse?

A warehouse holds the whole business in one place, which makes access control important. Decide who may see which tables and columns before the first dashboard is shared.

  • Grant access by role, not by person.
  • Mask or restrict columns that hold personal or financial details.
  • Keep raw, cleaned and reporting layers separate.
  • Set cost limits and review the heaviest queries.
  • Document each metric and who approves changes to it.

A regulation may restrict how certain data is stored and shared. The plan should cover what it requires, and your compliance owner should confirm the specifics.

How StackLift approaches warehouse work

StackLift scopes warehouse projects as deliverables: sources, pipelines, models, metrics, access and dashboards. Estimates are architect-reviewed and priced by complexity, with observability from sprint one. See the data dashboard sprint, the dashboard cost guide and the free estimate.

What is data modeling in a warehouse?

Modeling is the design of the tables. A good model turns scattered system data into concepts the business already uses. It is the difference between a warehouse people trust and one they avoid.

Common modeling approaches
ApproachIdeaGood for
Star schemaCentral fact tables, such as orders, linked to dimension tables, such as customers and productsReporting and BI; the most widely taught pattern
Snowflake schemaDimensions split into further tablesReducing duplication in large dimensions
Data vaultSeparate hubs, links and satellites that track historyMany sources and strong audit needs
One big tableA wide, pre-joined tableSimple, fast dashboards

Most teams begin with a star schema and a layered flow from raw to staging to business-ready tables. Tools like dbt keep that logic as reviewed, tested code.

What are data warehouses used for?

  • Executive and board reporting: one agreed view of revenue, margin and pipeline across systems.
  • Finance: consolidation, reconciliation and month-end reporting from several ledgers.
  • Marketing: channel performance combined with sales outcomes to see what actually earns money.
  • Product analytics: usage events tied to accounts and plans, to understand retention.
  • Operations: inventory, fulfillment and service data in one place for planning.
  • AI and machine learning: clean history to train forecasts or to feed RAG and assistants that answer data questions.

A warehouse also supports a data dashboard, which is often the first visible result.

What does a data warehouse mean for cost and timeline?

The platform bill depends on how much data you store and how much compute your queries use. The larger cost is usually the work: connecting sources, cleaning data, modeling it and agreeing on definitions. Effort rises with the number of source systems, the quality of their data, security requirements and how many reports you need at launch.

A sensible path starts with one subject area, such as revenue, and expands. Read the data and analytics cost page, compare it with the dashboard cost guide, and scope your own case with the free estimate.

What is data modeling in a warehouse?

Modeling is the design of the tables. A good model turns scattered system data into concepts the business already uses, which is the difference between a warehouse people trust and one they avoid.

Common modeling approaches
ApproachIdeaGood for
Star schemaCentral fact tables, such as orders, linked to dimension tables, such as customers and productsReporting and BI; the most widely used pattern
Snowflake schemaDimensions split into further tablesReducing duplication in large dimensions
Data vaultSeparate hubs, links and satellites that track historyMany sources and strong audit needs
One big tableA wide, pre-joined tableSimple, fast dashboards

Most teams begin with a star schema and a layered flow from raw to staging to business-ready tables. Tools like dbt keep that logic as reviewed, tested code.

Why do metric definitions matter so much?

The most common complaint about analytics is not slow queries. It is two reports showing different numbers for the same thing. One counts a customer when they sign up, another when they pay, and a third excludes refunds. A warehouse only fixes this if definitions are written down and built once.

  1. List the ten or so metrics leaders use to run the business.
  2. For each, write the exact definition, the source, the owner and the date it applies from.
  3. Build it once in the modeling layer or a semantic layer, and point every report at it.
  4. Review changes like code, so a new definition is a visible decision.
  5. Publish a short glossary beside the dashboards.

How does data get into and through a warehouse?

Most modern warehouses use ELT: connectors load raw data first, and SQL models then transform it inside the warehouse. This keeps the raw copy for audit and lets analysts change logic without re-extracting data. A data pipeline or an ETL job handles the loading.

Layers in a typical warehouse
LayerContentsRule
Raw or stagingSource data as it arrivedNever edited by hand
CleanedRenamed, typed and deduplicated tablesOne cleaned table per source object
Business modelsCustomers, orders, subscriptions, revenueThe place for shared logic
MartsSubsets for one team, such as finance or salesBuilt from business models only

Which tools connect to a data warehouse?

The usual stack
NeedExamples
WarehouseSnowflake, Google BigQuery, Amazon Redshift, Azure Synapse, Databricks SQL
LoadingFivetran, Airbyte, Kafka, custom pipelines
Transformationdbt, SQL scheduled in the warehouse
Dashboards and BIPower BI, Tableau, Looker, Metabase, Apache Superset
Reverse syncTools that send modeled data back to CRM or marketing systems
Qualitydbt tests, Great Expectations

Choose by the skills of the people who will run it, the cloud you already use and the security you need, not by popularity. All the main platforms can do the job.

How do you keep warehouse costs under control?

Cloud warehouses charge for storage and for compute, so a careless query can cost real money. Cost control is a habit, not a setting.

  • Set budgets and alerts per team or project.
  • Turn compute off or let it pause when nobody is querying.
  • Pre-build common aggregates instead of recomputing them for every dashboard.
  • Select only the columns you need and filter early.
  • Partition or cluster big tables on the fields people filter by.
  • Drop or archive data that no longer has a use.
  • Review the most expensive queries each month and fix the worst.

When do you not need a data warehouse yet?

Warehouses are valuable once data lives in several systems. Before that, they can be more machinery than you need.

  • You have one main system and a few reports. A read-only copy of its database may be enough.
  • Your questions are simple and spreadsheets answer them reliably.
  • Nobody can own the models, tests and access rules.
  • You have not yet decided which decisions the data should drive.

A small dashboard on a single source can prove the value first. A dashboard sprint is a low-risk way to learn what the business will actually use.

What does a first warehouse project look like in practice?

A first warehouse should answer a handful of questions well, not hold every table you own. Starting small keeps cost low and gets people using the numbers sooner.

  1. Pick one decision that data should improve, such as weekly revenue by product line.
  2. Identify the two or three source systems that hold the facts.
  3. Load raw copies through a data pipeline with freshness and count checks.
  4. Model the business tables and write down each metric definition.
  5. Build one dashboard or report, and compare it with the figures finance already trusts.
  6. Collect feedback, fix definitions and then add the next subject area.

The comparison with trusted figures is the step teams skip. If the warehouse and finance disagree, find out why before anyone sees the dashboard, since early distrust is hard to undo.

Can a data warehouse support AI assistants that answer data questions?

Yes, with care. An assistant can translate a plain-language question into SQL and run it against modeled tables. This works well when the tables are clean, well named and documented, and badly when they are raw and ambiguous. The warehouse model is the assistant's map.

  • Give the assistant business-ready tables, not raw staging data.
  • Describe columns and metrics in plain language alongside the schema.
  • Use read-only access limited to what the asker may see.
  • Log each generated query, and let a person review sensitive ones.
  • Test with a set of known questions and answers, as in agent evaluation.

This is one reason clear metric definitions pay off twice: they help people and they help AI. See AI observability for monitoring such features.

Questions people ask

Do I need a data warehouse?

If reports require combining several systems and are slow or built by hand, probably. If one app database answers your questions, you may not need one yet.

What is the difference between a data warehouse and a database?

A database is any organized data store. A warehouse is a database designed for analytics across sources, rather than for running an application.

Is a data warehouse the same as a data lake?

No. A warehouse stores modeled, structured data. A lake stores raw files of any type. A lakehouse combines parts of both.

What is a data mart?

A smaller, focused slice of warehouse data for one team, such as finance or marketing.

Can a warehouse support AI features?

Yes, as a source of clean business data. For documents and text, you may add a vector database or search index.

Is Snowflake a data warehouse?

Yes. Snowflake is a cloud data platform best known as a warehouse. Google BigQuery, Amazon Redshift and Azure Synapse are other widely used options.

Do I need a data warehouse if I have a BI tool?

A BI tool draws charts, but it needs reliable data underneath. If your data sits in one system, the BI tool may be enough. Once you combine several systems, a warehouse keeps the numbers consistent.

What is a star schema?

A star schema is a table design with a central fact table, such as orders, surrounded by dimension tables, such as customers, products and dates. It makes reports easy to write and quick to run.

Sources

Sources and further reading

The standards, rules, research and official documentation this page draws on.