# 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. - **Published:** 2025-05-19 - **Author:** Clixo - **Reading time:** 6 min read - **Tags:** data-warehouse, data-engineering, beginner, snowflake, bigquery - **Canonical URL:** https://clixo.sh/blog/data-warehousing-fundamentals-for-engineers You have application data in Postgres, events in a logging system, and user behavior in a third-party analytics tool. Someone asks for a report that joins all three. You try to run the query against the Postgres application database, and it times out. You have just encountered the problem that data warehouses solve. 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. The fundamental reason for the separation: running heavy analytical queries against your production application database affects application performance. Analytical queries scan large amounts of data and run for minutes. Application queries look up individual rows and run in milliseconds. They do not coexist well on the same database. ## How Warehouses Are Structured Differently Modern cloud warehouses (Snowflake, BigQuery, Redshift, DuckDB) use columnar storage rather than row storage. In a row-based database, all columns for a given row are stored together. In a columnar database, all values for a given column are stored together. This matters enormously for analytical queries. A query like `SELECT SUM(revenue) FROM orders WHERE date > '2025-01-01'` only needs two columns from the orders table. In a columnar store, the database reads only those two columns. In a row store, it reads every column for every matching row, even the ones not needed. Columnar storage also compresses extremely well. Similar values stored adjacently compress efficiently, reducing storage costs and the amount of data read from disk for any given 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. ```mermaid flowchart LR A["App Database"] --> P["ETL or ELT Pipeline"] B["SaaS Tools"] --> P C["Event Streams"] --> P P --> R["Raw Layer"] R --> T["Transformation Layer"] T --> M["Mart Layer"] M --> DA["Analytics Dashboards"] M --> PF["Product Features"] ``` ### 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. Modern cloud warehouses have enough compute that the strict normalization requirements of traditional dimensional modeling are less critical than they once were. Many teams use a simpler flat table approach for marts and reserve dimensional modeling for high-volume fact tables. ### 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 (Snowflake) and sort keys (Redshift) provide further organization within partitions. BigQuery's clustering achieves a similar result. These features are relevant once tables are large enough that scan time is a bottleneck. ### Compute and Storage Separation Cloud warehouses decouple compute from storage. You pay for storage based on how much data you store. You pay for compute based on how much query processing you run. Snowflake's virtual warehouses, BigQuery's slot-based pricing, and Redshift Serverless all reflect this model. This separation means you can pause compute when no queries are running (cutting costs to storage-only) and scale compute up for heavy batch workloads without changing storage. It is the primary reason cloud warehouses replaced on-premise data warehouse appliances. ## Choosing a Cloud Warehouse **BigQuery** — serverless, query-on-demand pricing (pay per TB scanned), no infrastructure to manage. Best choice for Google Cloud shops and teams that want zero operational overhead. Query caching and partitioning are essential for cost control. **Snowflake** — virtual warehouse model, wide cloud provider support (AWS, GCP, Azure), strong ecosystem of third-party tools. Good choice for multi-cloud environments or teams that want a consistent experience regardless of cloud provider. **Redshift** — AWS-native, tight integration with S3, EMR, and Glue. A strong choice for teams already deeply invested in the AWS ecosystem. Redshift Serverless reduces the provisioning burden for variable workloads. **DuckDB** — an embedded analytical database that runs on a laptop or within a container. Not a cloud warehouse, but excellent for smaller datasets, local development, and pipelines that do not need a managed service. For most product teams starting a data stack, BigQuery or Snowflake is the right answer. Both have generous free tiers for exploration and managed infrastructure that eliminates database administration. ## 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. Understanding the warehouse model — how it is structured, how it is priced, what it is optimized for — is the foundation for designing effective pipelines that load data into it efficiently. If your team is at the stage of setting up a first data warehouse or rearchitecting an existing data stack, [Clixo](https://clixo.sh/#contact) helps product and engineering teams design and build data infrastructure that scales with the product. --- Clixo · 1141 W Bryn Mawr Ave, Itasca, IL 60143, US · [hello@clixo.sh](mailto:hello@clixo.sh) [Start a build](https://clixo.sh/#contact) · [All services](https://clixo.sh/services) · [Agent guide (llms.txt)](https://clixo.sh/llms.txt)