Data Warehousing

A data warehouse brings data from several source systems into a store built for analysis. It gives you a stable place to compare history, calculate measures, and answer questions that cross operational boundaries.

The central mental model is a historical measurement system.

operational systems -> ingest -> clean and conform -> warehouse -> analysis
       change data                preserve meaning       read across history

An order system records a sale. A support system records a case. A marketing platform records a campaign response. Each system serves its own process. A warehouse reshapes selected data so analysts can study those processes together.

The warehouse does not replace the source systems. It serves a different workload.

Why a warehouse exists

Operational systems handle current business activity. They accept orders, update account balances, and reserve inventory. These systems favor short, predictable transactions and current state.

Analytical work asks different questions:

• How did revenue change by product and region over three years?
• Which customer segments create the most support demand?
• Did a promotion change unit volume or only shift timing?
• How do actual results compare with targets?

These questions scan, join, group, and summarize many records. They also need consistent definitions across systems and time.

A warehouse separates that analytical workload from operational processing. It integrates selected data, records historical context, and presents structures designed for filtering and aggregation.

The end-to-end system

A warehouse is more than a database. It is a system of sources, movement, transformation, storage, metadata, controls, and consumers.

sources -> landing or staging -> transformation -> dimensional tables
                                                   |
                                                   v
                                      semantic models and reports

Source systems own operational records. They may use different identifiers, names, time zones, and update rules.

Ingestion copies source data into the analytical environment. A batch may run on a schedule. Change data capture may move smaller changes more frequently.

Staging holds data for loading and transformation. It can reduce repeated reads from operational systems and make a failed load easier to restart. Staging data is not the business-facing product.

Transformation combines sources, converts types, applies business rules, resolves identifiers, and checks quality. The result should use consistent meanings.

Warehouse storage keeps the integrated analytical tables. Many relational warehouses use dimensional models.

Serving layers expose governed data to semantic models, reports, dashboards, notebooks, and other analytical tools.

Metadata and governance describe the data, record ownership and lineage, and control access.

Every arrow is a contract. You need to know what moved, when it moved, which rules changed it, and what happened when part of the load failed.

ETL and ELT

Two common loading patterns differ in where transformation happens.

ETL means extract, transform, and load. Data is transformed before it enters the final warehouse tables.

ELT means extract, load, and transform. Data first enters the analytical platform, then the platform performs the transformation.

Both patterns can produce the same dimensional result. The choice depends on platform capabilities, source constraints, scale, security, and operations.

Do not judge a pipeline by the three-letter label. Judge whether it is repeatable, observable, restartable, and able to preserve required history.

Start with a business process

A dimensional model begins with a measurable business process. Sales, shipments, support cases, and inventory snapshots are processes. A vague subject such as company data is not precise enough.

For each process, define the questions, the event or state being measured, and the required history. Then declare the grain.

Grain states exactly what one fact row represents:

One row represents one product line on one completed order.

This sentence controls the model. A measure belongs in the fact table only when it makes sense at that grain. Mixing order totals with order-line facts can duplicate revenue when a query sums the rows.

One fact table should use one consistent grain. Different processes or grains usually need separate fact tables.

Facts and dimensions

A fact table records measurements associated with events or observations. Its rows contain dimension keys and measures.

A dimension table supplies descriptive context. It lets you filter and group facts by product, customer, location, date, or another business concept.

                 Date
                   |
Customer ---- Sales Fact ---- Product
                   |
                 Store

In this sales star, the fact table may contain quantity and net amount. The dimensions supply labels and groupings such as month, customer segment, product category, and region.

The shape is called a star schema because the fact table sits at the center. A warehouse normally contains several stars for different business processes.

Dimensional design makes common analytical intent visible. Facts answer what happened and how much. Dimensions answer who, what, where, when, and how you want to group the result.

Measures and aggregation

Every measure needs a documented aggregation rule.

An additive measure can be summed across every relevant dimension. Line-item sales amount is often additive.

A semi-additive measure can be summed across some dimensions but not all. An account balance can be summed across accounts at one point in time, but summing daily balances across time usually has no useful meaning.

A non-additive measure should not be summed. Ratios and averages often need recomputation from their components.

Store the components needed for correct calculation. A stored average can produce the wrong result when groups have different sizes.

History is a design choice

Source systems change. Customers move. Products change categories. Sales territories are reassigned. The warehouse must decide whether analysis should use the current description or the description that was valid when an event occurred.

A slowly changing dimension defines how a dimension attribute change affects history.

• Type 1 overwrites the existing value. Use it when history is not required or when correcting an error.
• Type 2 adds a new versioned row. Facts remain connected to the historical dimension version.
• Type 3 keeps limited prior state in additional attributes.

Type 2 requires a surrogate key because one natural business key can have several versions. Validity dates and a current-row flag identify when each version applies.

Do not apply one history rule to an entire dimension without thought. A corrected spelling may use Type 1 while a territory assignment uses Type 2.

Conformed dimensions connect processes

A conformed dimension uses consistent meaning across fact tables. If Sales and Inventory share a conformed Product dimension, both processes group products by the same identifiers and attributes.

Conformance is a semantic promise. Matching column names are not enough. Values, data types, definitions, and change rules must agree.

Conformed dimensions let you compare separate business processes without joining fact tables directly. You summarize each process by shared dimensions, then compare the results at a compatible grain.

Physical design follows workload

Logical dimensional design and physical storage solve different problems.

Columnar storage keeps values from the same column together. Analytical queries often read a few columns across many rows, so columnar execution can reduce input and output work and improve compression.

Partitioning divides a large table into manageable sections. A date partition can help loads replace a period and can let a filtered query skip unrelated partitions. Too many small partitions can hurt performance, so partitioning needs evidence from load and query patterns.

Aggregate tables or materialized results can accelerate repeated summaries. They add refresh work and another result that must remain consistent with detailed facts.

Modern cloud warehouses may separate compute from storage. That changes scaling and cost controls, but it does not remove the need for clear grain, trusted transformations, and workload management.

Loading safely

A reliable load is more than a successful insert.

For each pipeline, define:

• the source extraction boundary;
• the business key and warehouse surrogate key;
• how inserts, updates, deletes, and late records behave;
• which quality checks reject, quarantine, or warn;
• how retries avoid duplicate facts;
• when data becomes visible to consumers;
• how to reconcile warehouse totals with a source control total;
• which metadata records the run and transformation version.

Load dimensions before facts when facts need dimension keys. Decide how to represent an unknown or late dimension member. Silent inner joins can drop facts and make totals look plausible while incomplete.

Test the pipeline with replays, partial failures, corrected source data, and out-of-order arrivals. A pipeline that works only for the first full load is not ready for production.

Quality, security, and observability

A warehouse can standardize data, but it cannot make an unsupported business definition true.

Quality checks should cover structure and meaning. Test required fields, uniqueness, referential integrity, accepted ranges, freshness, volume changes, and reconciled measures. Record failures where operators can act on them.

Security applies from source extraction through reports. Limit sensitive data to a justified purpose. Control access to staging and warehouse layers. Protect credentials, audit access, and define retention.

Observability answers operational questions:

• Did the load run?
• Which source interval did it process?
• How many rows were read, rejected, inserted, and updated?
• Is the published data fresh enough for its stated use?
• Which downstream assets depend on a changed table?

Trust comes from visible definitions and evidence, not from a database name.

When a warehouse fits

A warehouse is a strong fit when you need:

• integrated analysis across several systems;
• repeatable metrics with governed definitions;
• historical analysis across business changes;
• large scans, joins, and aggregations;
• stable data products for reports and semantic models.

A warehouse is a poor primary fit for low-latency application transactions. It is also not automatically the best home for large volumes of raw, unstructured data or exploratory workloads that need the original data shape.

Warehouses, lakes, and lakehouses can coexist. The useful question is not which label wins. Ask which system owns raw retention, governed analytical tables, operational state, and each consumer contract.

Limits and failure modes

A warehouse copies data, so it introduces latency. A report can be internally correct and still be too stale for an operational decision.

Integration also creates governance work. Two source systems may disagree about customer identity or revenue timing. Transformation code cannot settle that conflict without an owned definition.

Dimensional models favor known analytical patterns. They can evolve, but every new process, measure, and history rule requires design and testing.

Performance features carry operational costs. Partitions, aggregates, clustering, and materialized results need monitoring and maintenance. Apply them to measured bottlenecks.

Finally, a warehouse can centralize sensitive history. That concentration increases the importance of access control, minimization, audit, and retention.

A practical learning path

1. Compare analytical and transactional workloads.
2. Trace one source record through ingestion, staging, transformation, and serving.
3. Define a business process and write its fact-table grain.
4. Design facts, dimensions, keys, and aggregation rules.
5. Model historical changes with Type 1 and Type 2 dimensions.
6. Connect two processes through conformed dimensions.
7. Build repeatable incremental loads with quality and reconciliation checks.
8. Measure query behavior before adding partitions or aggregates.
9. Add lineage, access policy, freshness targets, and operational alerts.
10. Compare warehouse, lake, and lakehouse designs for a real workload.