Data Warehousing: Architecture, Design, and Cloud Options

Data warehousing is the practice of centralizing data from multiple sources for analysis and reporting. A well-designed data warehouse serves as the single source of truth for business intelligence, enabling consistent reporting across the organization. According to Allied Market Research, the global data warehousing market is projected to reach $51 billion by 2031, driven by the growing need for data-driven decision-making and the adoption of cloud-based analytics platforms.

What Is a Data Warehouse?

A data warehouse is a centralized repository optimized for analytical queries. Unlike operational databases (OLTP) designed for high-volume transactions, warehouses are designed for OLAP (Online Analytical Processing) workloads that scan and aggregate large volumes of data across multiple dimensions.

Key characteristics include: optimized for reading large data volumes through columnar storage and compression, designed for complex multi-table joins across fact and dimension tables, support for historical analysis spanning months or years, and consolidation of data from multiple source systems into a consistent schema. Modern cloud warehouses separate compute from storage, allowing independent scaling of each based on workload requirements.

Dimensional Modeling

Dimensional modeling is the standard design approach for data warehouses, originally documented by Ralph Kimball in The Data Warehouse Toolkit. It organizes data into facts (quantitative measures) and dimensions (descriptive context), providing an intuitive structure for business users.

Facts

Fact tables store quantitative measurements of business events. Facts are numeric, additive, and usually immutable — once a transaction fact is recorded, it should not be modified. Common measures include revenue, quantity, count, and duration.

CREATE TABLE fact_sales (
    sale_id INT PRIMARY KEY,
    date_key INT REFERENCES dim_date(date_key),
    product_key INT REFERENCES dim_product(product_key),
    quantity INT NOT NULL,
    revenue DECIMAL(10,2) NOT NULL
);

Dimensions

Dimension tables provide descriptive context for facts. They contain text attributes, hierarchies, and reference data that answer the who, what, when, where, and why of each business event. A well-designed dimension is denormalized, with all related attributes in a single table.

CREATE TABLE dim_product (
    product_key INT PRIMARY KEY,
    product_name VARCHAR(200),
    category VARCHAR(100),
    brand VARCHAR(100)
);

Star Schema

The star schema places one or more fact tables at the center, surrounded by denormalized dimension tables. This structure minimizes the number of joins required for analytical queries. A typical star schema enables queries with 2-5 joins to answer complex business questions, compared to 10-20 joins in a normalized schema. The star schema is the most common warehouse design because it balances query performance with design simplicity and is intuitive for business users.

Snowflake Schema

The snowflake schema normalizes dimensions into sub-dimensions. For example, the product dimension might reference separate category and brand dimension tables via foreign keys. This reduces data redundancy but increases join complexity. In most modern warehouses, the storage savings from normalization are negligible compared to the query performance cost of additional joins, making the star schema the preferred approach for analytical workloads.

Kimball vs Inmon

Two competing methodologies dominate data warehouse design, each with distinct trade-offs:

Kimball (Bottom-Up)

Build dimensional data marts for individual business processes first, then integrate them through conformed dimensions that are shared across marts. This approach delivers value faster by starting with one department or business process. Conformed dimensions ensure that customer, product, and date dimensions are consistent across all data marts, enabling cross-process analysis. The Kimball approach is more common in modern data teams due to its iterative, agile nature.

Inmon (Top-Down)

Build a normalized enterprise data warehouse (3NF) first, then create dimensional data marts from it for specific business functions. This approach ensures a consistent, fully integrated view of data across the entire organization. The trade-off is a significantly longer initial delivery timeline — often 12-18 months before producing any business value. Inmon’s approach suits large enterprises with mature data governance programs and complex cross-functional reporting requirements.

Most modern data teams use a hybrid approach: Kimball-like dimensional marts with conformed dimensions, backed by a data lake or lakehouse for raw data storage. This combines the speed of Kimball with the consistency of Inmon.

ETL vs ELT

ETL (Extract, Transform, Load)

Data is extracted from sources, transformed in a staging environment, then loaded into the warehouse. ETL requires dedicated transformation infrastructure and ensures only clean, structured data enters the warehouse. It is common in on-premises environments and with legacy tools like Informatica, Talend, and SSIS. ETL is appropriate when the transformation logic is complex and requires external processing capabilities not available in the warehouse.

ELT (Extract, Load, Transform)

Data is extracted from sources, loaded raw into the warehouse, then transformed using the warehouse’s compute power through SQL transformations. ELT is simpler to implement and leverages the warehouse’s scalability — you can run transformations that process terabytes of data without provisioning dedicated transformation infrastructure. It is the dominant approach for cloud warehouses like Snowflake, BigQuery, and Redshift.

ELT enables data exploration of raw data before transformation rules are finalized, supporting iterative development. However, loading raw data into the warehouse raises governance and privacy concerns — sensitive data in raw sources must be identified and protected before or immediately after loading.

Cloud Warehouse Options

Snowflake’s separation of compute and storage is particularly powerful. You can create multiple virtual warehouses (compute clusters) that all query the same shared data layer without contention. Compute can be paused when not in use, and storage costs are billed separately based on compressed data volume. This architecture enables organizations to support many concurrent workloads — ad-hoc analytics, scheduled reporting, data loading — with isolated compute resources sharing a single copy of data.

Warehouse vs Data Lake

The lakehouse architecture combines both: raw data in object storage with ACID transactions and warehouse-quality queries via open table formats like Apache Iceberg and Delta Lake. This eliminates the need to maintain separate systems for raw data storage and structured analytics.

Building a Data Warehouse

1. Identify business processes: Which activities generate data worth analyzing? Prioritize processes with clear business impact and data availability
2. Select the grain: What does one row in each fact table represent? The grain must be granular enough for analysis without being overly detailed
3. Identify dimensions: Who, what, when, where for each process — typically 4-8 dimensions per fact table
4. Identify facts: Quantifiable measures for each process — ensure all measures are consistent with the declared grain
5. Design slowly changing dimensions: How to handle attribute changes over time using appropriate SCD strategies

Start with one business process, validate the model with stakeholders, and expand incrementally. Over-engineering the initial design delays value delivery and often requires revision as requirements evolve. The Data Warehouse Toolkit recommends delivering a new data mart every 3-4 months when building out a warehouse program.

Frequently Asked Questions

What is the difference between a data warehouse and a database? A database (OLTP) is optimized for transactional workloads — many small, concurrent read and write operations with strict ACID guarantees. A data warehouse (OLAP) is optimized for analytical workloads — large scans, complex joins, aggregations, and window functions across millions to billions of rows. Most organizations use both: a transactional database for their application and a warehouse for analytics and reporting.

Should I use ETL or ELT? ELT is generally preferred for cloud warehouses because it is simpler, leverages the warehouse’s scalable compute, and enables data exploration before transformation rules are finalized. Use ETL when you need to clean data before it enters the warehouse, when working with legacy on-premises systems that cannot scale compute, or when your warehouse compute costs make heavy transformation uneconomical.

How often should I update my data warehouse? Daily updates are sufficient for most strategic reporting needs. Hourly or near-real-time updates are needed for operational dashboards and time-sensitive decisions such as inventory management or fraud detection. Match the update frequency to business requirements rather than technical capabilities — more frequent updates increase costs and complexity without proportional business value.

What is a data mart? A data mart is a subset of the warehouse focused on a specific business function — sales, finance, marketing, operations. Data marts are typically dimensional, denormalized, and optimized for a specific BI tool or department. In the Kimball approach, the warehouse is the union of all conformed data marts, each sharing common dimension tables.

How do I handle slowly changing dimensions? Use Type 2 (add new row with effective dates) for dimensions where historical accuracy matters — customer address for shipment analysis, product category for trend reporting. Use Type 1 (overwrite) for error corrections where the old value had no analytical value. The choice depends on whether analysts need to see what the attribute was at the time of each historical fact.

ETL Pipelines Guide — Data Engineering Overview — Data Modeling Guide