Data Warehousing Basics

Introduction

A data warehouse is a separate analytics-optimized DB — star schema, columnar storage, MPP. Snowflake, BigQuery, Redshift and Databricks lead the pack.

Understanding the topic

Core concepts to understand:

• Star schema: fact tables + dimension tables.
• Columnar storage: read only the columns you query.
• MPP — parallel scans across many nodes.
• Loaded via ETL/ELT (Fivetran, dbt, Airbyte).
• SQL is the lingua franca — same queries you've learned.

Syntax reference

Visual workflow / architecture:

OLTP DB ──ETL──▶  Warehouse (Snowflake/BigQuery)
                          │
                ┌─────────┴─────────┐
              fact_orders         fact_events
                  │  joins to       │
              dim_users   dim_products   dim_dates

Real-world use

Every analytics-mature company has a warehouse — even small startups via Snowflake / BigQuery.

Best practices

• Model as star schema.
• Use dbt for SQL transformations.
• Govern access tightly.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Star schema vs OLTP.
• Q2. Columnar vs row store.
• Q3. ETL vs ELT.
• Q4. dbt — what is it?
• Q5. When to introduce a warehouse.