Data Warehousing Fundamentals: A Practical Intro for Engineers
A beginner's guide to data warehousing for engineers — what a data warehouse is, how it differs from a transactional database, key concepts, and how to choose one.
Reports may need to combine application records, event logs, and third-party analytics. A warehouse provides a separate place to store and query that data without making the application database serve every analytical workload.
This guide covers the core concepts of data warehousing for engineers who are just starting to build a data stack or who want to understand why a separate warehouse is worth the complexity.
What a Data Warehouse Is
A data warehouse is a database optimized for analytical queries — aggregations, multi-table joins, reporting, and historical analysis — rather than for transactional operations.
Your application database (Postgres, MySQL, MongoDB) is optimized for OLTP: fast reads and writes of individual rows, low-latency lookups, concurrent user requests, and strong transactional guarantees. It is designed to serve your application in real time.
A data warehouse is optimized for OLAP: reading large volumes of data, joining many tables, computing aggregations across millions or billions of rows. It is designed to serve analytical queries efficiently.
Heavy analytical queries can compete with application requests for CPU, memory, and I/O. A separate warehouse isolates those workloads when the reporting requirements justify the extra pipeline and storage.
How Warehouses Are Structured Differently
Cloud warehouses such as Snowflake, BigQuery, and Redshift use columnar storage. DuckDB is an embedded analytical database that also uses columnar techniques. In row storage, a record’s fields are stored together; column storage groups values by column.
Columnar storage can reduce the data read by analytical queries. For example, SELECT SUM(revenue) FROM orders WHERE date > '2025-01-01' needs the revenue and date columns rather than every field in each order.
Grouping similar values can improve compression and reduce the data read from storage. The benefit depends on the dataset and query.
Key Concepts
Schemas and Layers
Data warehouses typically organize data into layers:
- Raw or staging layer — data as ingested from source systems, with minimal transformation. This is the landing zone for your ETL or ELT pipeline.
- Intermediate or transformation layer — cleaned, joined, and shaped data. Business logic is applied here.
- Mart or presentation layer — purpose-built tables for specific use cases: analytics dashboards, executive reporting, product feature data.
This layered approach separates concerns. If a transformation has a bug, you can re-run only that layer without re-ingesting raw data.
Star and Snowflake Schemas
Traditional data warehouse design uses dimensional modeling. The core idea: separate facts (measurable events — orders, page views, payments) from dimensions (the entities that describe those facts — customers, products, dates).
A star schema has a central fact table surrounded by dimension tables. The fact table contains foreign keys to each dimension and the numeric measures (quantities, amounts). Joins are simple and queries are fast.
A snowflake schema normalizes dimension tables further, breaking them into sub-dimensions. This reduces redundancy but adds join complexity.
Choose a mart’s shape around its queries and maintenance needs. Dimensional models can make shared definitions easier to manage; wider tables can simplify particular reports.
Partitioning and Clustering
Large tables (billions of rows) benefit from partitioning — dividing the table into smaller segments based on a column, typically a date. A query that filters on the partition column reads only the relevant partition rather than the full table.
Clustering and sort keys organize data to help queries skip irrelevant blocks. Their configuration and behavior differ across warehouses; evaluate them with the filters used by your workload.
Compute and Storage Separation
Many cloud warehouses price storage and query processing separately. Check the selected service’s on-demand, reserved-capacity, and minimum billing rules when estimating cost.
Separating storage and compute can allow query capacity to change without moving the underlying data. Whether idle compute can be suspended, and what charges remain, depends on the service.
Choosing a Cloud Warehouse
BigQuery — a managed service with on-demand and capacity-based compute options. Evaluate it alongside existing Google Cloud data and access requirements.
Snowflake — separates compute through virtual warehouses and is available on multiple cloud providers. Assess region availability, integrations, and workload costs.
Redshift — integrates with AWS data services and offers provisioned and serverless options. Evaluate the operational and cost model that fits the workload.
DuckDB — an embedded analytical database for local analysis and applications that do not need a managed warehouse service.
Compare warehouse options using representative queries, expected data volume, existing cloud services, and operating cost.
The Relationship Between Warehouses and ETL Pipelines
A data warehouse without pipelines is an empty destination. Pipelines are how data gets from your application databases, SaaS tools, and event streams into the warehouse. The warehouse is the destination; the pipeline is the mechanism.
This is why ETL and ELT are discussed in the context of warehouses. The "L" (load) in both patterns is loading into the warehouse. The "T" (transform) in ELT happens inside the warehouse using SQL tools like dbt.
Design ingestion and transformation around the warehouse’s query needs, supported data types, and operating costs.