What is a data warehouse, in one sentence?
A data warehouse is a database built for query and analysis instead of for processing transactions. Oracle's documentation puts it this way: it exists to support business intelligence, and it usually holds historical data taken from transaction systems, plus data from other sources when needed.
That definition carries the whole subject. An order system is tuned to record one sale at a time. A warehouse is tuned to answer questions about all the sales, across years, regions and products, without slowing down the systems that take the orders. Databricks describes the same purpose in current terms: the warehouse stores and organizes current and historical data from multiple sources in a business-friendly manner. Every concept on this page exists to serve that goal.
What are the four classic characteristics of a data warehouse?
The classic framework comes from Bill Inmon and lists four characteristics. Oracle's introduction to data warehousing uses the same four and credits them to Inmon.
- Subject oriented: the warehouse is organized around a business subject such as sales, claims or inventory, not around one application.
- Integrated: data from different sources is put into one consistent format. That means fixing naming conflicts and mismatched units of measure before anyone queries the result.
- Nonvolatile: once data is loaded it should not change, because the point is to analyze what already happened. New loads add rows; they do not rewrite last month.
- Time variant: the warehouse keeps long history so trends and patterns show up. Transaction systems often move old records to an archive to stay fast, which is the opposite habit.
A quick test for any design: if it lets users overwrite history directly, or keeps only the current state, it has drifted away from a warehouse and toward an operational database.
How is a data warehouse different from an OLTP database?
OLTP stands for online transaction processing, the kind of database an application writes to all day. The contrast below follows Oracle's comparison of the two environments.
| Aspect | OLTP database | Data warehouse |
|---|---|---|
| Workload | Predefined operations, tuned in advance | Ad hoc queries and open-ended analysis |
| Typical query | Touches a handful of records | Scans thousands or millions of rows |
| How data changes | Users issue individual inserts and updates | Bulk loads by the ETL process, often nightly or weekly |
| Schema | Fully normalized to protect writes | Partially denormalized to speed up reads |
| History kept | A few weeks or months | Many months or years |
The two are partners, not rivals. The OLTP system is the source; the warehouse is where its history goes to be analyzed.
What do ETL and ELT mean in data warehousing?
ETL stands for extract, transform, load. Microsoft's architecture guide defines it as a data integration process that pulls data from diverse sources into one unified store. During the transform step, business rules are applied by a separate engine, often with staging tables that hold data temporarily. Typical work includes filtering, sorting, aggregating, joining, cleaning, deduplicating and validating.
ELT stands for extract, load, transform. The same guide says it differs from ETL only in where the transformation happens: inside the target data store, using that store's own processing power. Removing the separate engine simplifies the pipeline, so ELT fits best when the warehouse itself has the compute to spare.
The staging area is the landing zone for raw extracts. Oracle notes that most data warehouses use one because it simplifies cleansing and consolidation when data arrives from many source systems.
What is dimensional modeling, and why does grain come first?
Dimensional modeling splits data into two kinds of tables. A fact table holds the numeric measures from a real business event, such as quantity and amount on an order line, plus a foreign key to each related dimension. A dimension table holds the descriptive context: product names, customer segments, store regions, calendar attributes. Dimension tables are usually wide and flat, and their columns become the filters and labels on reports.
Arrange one fact table in the middle with its dimensions around it and you get a star schema. Microsoft calls it a mature modeling approach widely adopted by relational data warehouses. If you normalize the dimension hierarchies into extra tables, you get a snowflake schema. The Kimball Group advises against snowflaking because users find it harder to read and it can slow queries, while a flat dimension holds exactly the same information.
The Kimball Group's design process has four decisions in a fixed order: select the business process, declare the grain, identify the dimensions, identify the facts. Grain means exactly what one fact row represents, for example one line on one receipt. It is declared before anything else because every dimension and fact must match it. Starting at the lowest, atomic grain keeps the model able to answer questions nobody has asked yet.
Facts also differ in how they add up. Additive facts, such as sales amount, can be summed across every dimension. Semi-additive facts, such as account balances, can be summed across everything except time. Non-additive facts, such as ratios, should be stored as their additive parts and calculated at the end.
Which fact table and dimension patterns should you know?
The Kimball Group names three basic fact table types:
- Transaction: one row per measurement event at a point in time, such as each scan at a register.
- Periodic snapshot: one row per period, such as a day or month, summarizing everything in that period. A row is usually written even when nothing happened.
- Accumulating snapshot: one row per process instance, such as an order moving through fulfillment, with a date key for each milestone. It is the only type whose rows are routinely updated.
Dimensions change too, and how you handle that is called a slowly changing dimension (SCD) technique. Type 1 overwrites the old value and loses history. Type 2 adds a new row for each change, with at least three extra columns: an effective date, an expiration date and a current row flag. Type 3 adds a column to keep the prior value next to the current one.
Type 2 is why warehouses use surrogate keys, simple integers assigned in sequence, instead of the source system's natural key. One customer can have several dimension rows over time, so the natural key alone cannot be unique. Finally, conformed dimensions share the same column names and values across fact tables, which lets one report combine sales and returns on the same rows.
How do storage concepts make warehouse queries fast?
Modeling decides what the tables mean. Physical design decides how quickly they answer. Four concepts appear in nearly every modern platform.
- Columnar storage: each block holds values from one column for many rows. Amazon Redshift's documentation gives the example of a 100-column table where a query using five columns needs to read only about five percent of the data.
- Partitioning: a large table is split into segments, often by date. Google BigQuery can scan only the partitions that match a filter and skip the rest, a process it calls pruning, which reduces bytes read and cost.
- Clustering or sort keys: data inside storage blocks is ordered by chosen columns so filters on those columns can skip blocks.
- Materialized views: Snowflake describes these as pre-computed results of a query, stored for later use, so repeated heavy queries run faster than hitting the base table; in Snowflake they require Enterprise Edition.
Where do data marts, lakehouses and medallion layers fit?
A data mart is a smaller store built for one line of business, such as finance or marketing. Oracle shows it as a layer added on top of the warehouse and staging area so each group gets a focused view. Conformed dimensions are what keep several marts consistent with each other.
A lakehouse combines features of data lakes and data warehouses on one platform, holding both raw and prepared data. Databricks organizes lakehouse data with the medallion architecture: bronze tables hold raw data as it arrived, silver tables hold data that has been matched, merged, conformed and cleansed, and gold tables hold curated, business-level data. The gold layer uses denormalized, read-optimized models with fewer joins, and Databricks notes that Kimball-style star schemas and Inmon-style data marts both fit there.
The labels change from vendor to vendor. The underlying ideas do not: integrate the sources, keep history, model around business events, and store data in the shape your queries need.