Module 3 Summary - Data Warehousing & BigQuery

Source: Dev.to

Understanding Data Warehouses & OLAP vs OLTP 🏢

Why Do We Need Data Warehouses? 🤔

Imagine you run an online store. Your website has a database that handles:

• Customer sign‑ups
• Product orders
• Payment processing
• Inventory updates

This database needs to be FAST because customers are waiting. Every millisecond counts!

Now, your boss asks: “What were our top‑selling products last year by region, and how did that compare to the year before?”

Running that query on your production database would:

• Slow down your website (bad for customers!)
• Take forever because the database isn’t designed for such complex analysis
• Potentially crash things if the query is too heavy

This is exactly why data warehouses exist! They’re a separate place to store your data, specifically designed for answering complex analytical questions without affecting your live applications.

OLTP vs OLAP – The Two Worlds of Databases

Real-World Example 🛒

OLTP scenario (your app database)

-- A customer places an order – needs to be FAST
INSERT INTO orders (customer_id, product_id, quantity, price) 
VALUES (123, 456, 2, 29.99);

OLAP scenario (data warehouse)

-- Your CEO wants to know Q4 performance – can take a minute, that’s fine
SELECT 
    region,
    product_category,
    SUM(revenue) AS total_revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM sales_data
WHERE order_date BETWEEN '2023-10-01' AND '2023-12-31'
GROUP BY region, product_category
ORDER BY total_revenue DESC;

💡 Key insight: OLTP is like a cashier at a store – fast, handles one customer at a time. OLAP is like the accounting department – takes time to analyze all the receipts and produce reports.

What Exactly is a Data Warehouse? 🏗️

A data warehouse is a centralized repository where you collect data from all your different systems and store it in a way that’s optimized for analysis.

Think of it like this:

Imagine a company with multiple departments:

• Sales team uses Salesforce
• Marketing uses HubSpot
• Website runs on PostgreSQL
• Inventory managed in SAP

Each system has its own database, but your CEO wants a report combining data from all of them. This is where a data warehouse comes in!

┌─────────────┐   ┌─────────────┐   ┌─────────────┐
│  Salesforce │   │   HubSpot   │   │  PostgreSQL │
│   (Sales)   │   │ (Marketing) │   │  (Website)  │
└──────┬──────┘   └──────┬──────┘   └──────┬──────┘
       │                 │                 │
       │    ETL/ELT      │                 │
       │   (Extract,     │                 │
       │   Transform,   │                 │
       │    Load)        │                 │
       ▼                 ▼                 ▼
┌──────────────────────────────────────────────────┐
│              DATA WAREHOUSE (BigQuery)           │
│   All your data, cleaned, organized, ready       │
│   for analysis!                                   │
└──────────────────────────────────────────────────┘
                        │
                        ▼
         ┌──────────────────────────┐
         │  Reports, Dashboards,    │
         │  Machine Learning, etc. │
         └──────────────────────────┘

Key characteristics of a data warehouse

• 📊 Subject‑oriented – Organized by business topics (sales, customers, products)
• 🔗 Integrated – Data from multiple sources combined together
• 📅 Time‑variant – Keeps historical data (years worth!)
• 🔒 Non‑volatile – Data doesn’t change once loaded (it’s a historical record)

Modern Cloud Data Warehouses ☁️

Traditional data warehouses (like Oracle, Teradata) required:

• Buying expensive hardware
• Hiring DBAs to manage servers
• Months of setup time
• Huge upfront costs

Modern cloud data warehouses (BigQuery, Snowflake, Redshift) changed everything:

• ✅ No servers to manage (serverless)
• ✅ Pay only for what you use
• ✅ Scales automatically
• ✅ Set up in minutes
• ✅ Access from anywhere