Data Warehouse vs. Database: Understanding the Differences and When to Use Each

Learning Objectives

• Define what a transactional database is and list its typical use cases
• Describe what a data warehouse is and why it exists
• Compare and contrast their architectures, workloads, and performance patterns
• Follow a step-by-step workflow to move data from a database into a warehouse
• Use Galaxy to explore and query both systems efficiently

1. Foundations: What Is a Database?

A database is an organized collection of data designed for real-time transaction processing. Think of the tables powering your SaaS application—users, orders, payments, feature flags. These tables are created, read, updated, and deleted (the famous CRUD operations) thousands of times per minute.

1.1 OLTP Workloads

Databases such as PostgreSQL, MySQL, and SQL Server are optimized for OLTP (Online Transaction Processing). Core characteristics include:

• Row-oriented storage—speeds up single-row inserts/updates
• Normalized schema—minimizes duplicate data, ensuring consistency
• ACID guarantees—protect data integrity in concurrent environments
• Indexes tuned for fast point-lookups and small range scans

1.2 Real-World Example

Consider an e-commerce platform. When Alice checks out, her purchase triggers several quick database writes:

BEGIN;
INSERT INTO orders (user_id, total) VALUES (123, 49.99);
INSERT INTO order_items (order_id, sku, qty) VALUES (456, 'TSHIRT', 2);
COMMIT; -- total latency target < 100 ms


The design priorities are low latency and high transactional throughput.

2. Foundations: What Is a Data Warehouse?

A data warehouse is a central store of consolidated, historized data built for large-scale analytics, reporting, and machine learning. Technologies include Snowflake, Amazon Redshift, Google BigQuery, and ClickHouse.

2.1 OLAP Workloads

Warehouses excel at OLAP (Online Analytical Processing):

• Columnar storage—scans only the columns referenced, saving I/O
• Denormalized or star schemas—reduce joins for faster aggregations
• Massively parallel processing (MPP)—distributes large queries across many nodes
• Immutable data loads—append rather than update, simplifying history tracking

2.2 Real-World Example

Jane in business intelligence runs a monthly revenue cohort analysis:

SELECT
DATE_TRUNC('month', order_date) AS cohort_month,
DATE_DIFF('month', cohort_month, CURRENT_DATE) AS months_since_first,
SUM(total) AS revenue
FROM warehouse.fact_orders
GROUP BY 1, 2
ORDER BY 1, 2;


This query might scan billions of rows, but the warehouse returns results in seconds by leveraging column pruning, clustering, and parallelism.

3. Key Differences at a Glance

CriterionDatabase (OLTP)Data Warehouse (OLAP) Primary PurposeReal-time transactionsHistorical analytics Typical UsersApplication code, microservicesAnalysts, data scientists, execs Schema StyleHighly normalized (3NF)Denormalized (star/snowflake) Storage OrientationRow-basedColumn-based Query PatternsPoint lookups, short range scansLarge scans, aggregates, joins ConcurrencyThousands of short queries/secDozens of heavy queries/min Latency Goals< 100 msSeconds to minutes

4. Why You Need Both

Modern organizations rarely choose one over the other—they run both:

1. OLTP DB collects operational data.
2. ETL/ELT pipeline copies and transforms that data into the warehouse.
3. Analytics layer (BI tools, notebooks, Galaxy) queries the warehouse for insights.

5. Step-by-Step: From Database to Warehouse

1. Extract – Use tools like Airbyte, Fivetran, or custom scripts to stream CDC (Change Data Capture) logs from the source DB.
2. Load – Land raw tables into a raw_ schema in the warehouse.
3. Transform – With dbt or SQL scripts, build cleaned stg_ tables, then dim_ensions and fact_ tables.
4. Orchestrate – Schedule the pipeline via Airflow or Dagster.
5. Query & Visualize – Analysts explore using Galaxy, Looker, or dashboards.

Hands-On Exercise

Goal: Move a users table from Postgres to Snowflake and count sign-ups by day.

1. Create a Snowflake stage and copy CSV dump:

1. Load into raw table:

1. Transform into cleaned stage:

1. Aggregate sign-ups:

Tip: Paste each statement into Galaxy, link your Postgres and Snowflake connections, and use the split-pane editor to compare results side-by-side.

6. Common Misconceptions & Pitfalls

• “I can run dashboards straight on my production DB.”
  That works at 10 users, but analytical joins will eventually saturate CPU and lock transactional tables.
• “A warehouse is just a bigger database.”
  Size matters, but architecture matters more—columnar storage and MPP make analytics fast.
• “Normalization always beats denormalization.”
  Not for OLAP. Joining 15 tables for every query kills performance; star schemas trade some redundancy for speed.
• “ETL must run nightly.”
  Modern tools allow near-real-time ELT, letting analysts work on freshness measured in minutes.

7. Using Galaxy to Explore Both Systems

7.1 Switching Contexts Instantly

In Galaxy you can open multiple connections—say, prod-postgres and analytics-snowflake. Tab groups let you:

• Inspect row counts in Postgres (SELECT COUNT(*) FROM orders;)
• Run the transformed version in Snowflake (SELECT COUNT(*) FROM fact_orders;)

Side-by-side results help validate ETL correctness.

7.2 AI Copilot for Schema Discovery

Type a prompt like “show me how to join stg_users to fact_orders by user”. Galaxy’s AI copilot reads table metadata from both systems and proposes a correct query—no docs hunting required.

7.3 Collections & Endorsements

Create a “Warehouse Quality Checks” Collection. Save queries that reconcile DB and warehouse counts, then endorse them so teammates can rerun on demand.

8. Best Practices Checklist

• Use separate warehouses for prod vs. staging analytics to avoid costly accidental queries.
• Partition fact tables by date and cluster by high-cardinality columns (e.g., user_id).
• Document data lineage with dbt docs generate and surface it via Galaxy’s metadata panes.
• Automate backfills; never hand-edit warehouse tables unless absolutely necessary.
• Monitor query_history (Snowflake) or STL_QUERY (Redshift) for slow, costly queries.

9. Practice Challenges

1. Star-Schema Design: Given orders, users, and products tables in your OLTP DB, design dimension and fact tables for the warehouse.
2. Slow Query Hunt: In the warehouse, identify the top 5 most expensive queries last week and propose optimizations.
3. Freshness Alert: Write a query that flags any table whose max(load_ts) is > 2 hours old.
4. Galaxy Bonus: Save each answer in a Collection, endorse it, and invite a peer to leave comments.

Key Takeaways

• Databases are for transactions; warehouses are for analytics.
• Architectural choices (row vs. column, OLTP vs. OLAP) dictate performance.
• Most organizations need both and should invest in robust ELT pipelines.
• Tools like Galaxy make it easier to query, validate, and govern data across the entire stack.

Next Steps

1. Spin up a free Snowflake trial or use BigQuery’s free tier.
2. Connect both your operational DB and warehouse to Galaxy.
3. Implement one practice challenge and share it with your team.
4. Read further on dimensional modeling (Kimball methodology) and dbt best practices.