Context

At companies like StripeShop, the same data often supports both application transactions and reporting. Interviewers ask this question to test whether you can connect schema design choices to workload patterns, query behavior, and maintenance trade-offs.

Core Question

Explain how you would choose between a normalized schema and a denormalized schema for transactional workloads versus analytics workloads. In your answer, address:

1. What normalization and denormalization mean in practice
2. Why OLTP systems usually favor normalized models
3. Why analytics systems often use denormalized models such as star schemas or wide reporting tables
4. The trade-offs in storage, write performance, read performance, consistency, and query complexity
5. Situations where a hybrid approach is better than choosing only one design

Scope Guidance

The interviewer expects a practical database-design explanation, not just textbook definitions. Focus on how schema shape affects joins, aggregations, update behavior, and operational reliability in PostgreSQL-based systems.

Normalization

Normalization organizes data into related tables to reduce redundancy and improve consistency. It is especially useful when the same business entity is updated frequently and must remain accurate across the system.

SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c
  ON o.customer_id = c.customer_id;

Denormalization

Denormalization intentionally stores repeated or pre-joined data to speed up reads and simplify reporting queries. It is common in analytical systems where large scans and aggregations matter more than minimizing duplicate data.

SELECT order_date, customer_region, SUM(order_amount) AS revenue
FROM order_facts
GROUP BY order_date, customer_region;

Transactional Workloads (OLTP)

OLTP workloads involve many small inserts, updates, and deletes, usually affecting a small number of rows at a time. These systems benefit from normalized schemas because they reduce update anomalies and keep writes targeted and consistent.

UPDATE customers
SET email = 'new_email@example.com'
WHERE customer_id = 101;

Analytical Workloads (OLAP)

OLAP workloads involve large scans, aggregations, and historical reporting across many rows. Denormalized schemas reduce join overhead and make business metrics easier to compute and understand.

SELECT product_category, DATE_TRUNC('month', order_date) AS month, SUM(order_amount) AS revenue
FROM sales_fact
GROUP BY product_category, DATE_TRUNC('month', order_date);

Hybrid Modeling

Many real systems use normalized tables for operational data capture and denormalized tables or marts for reporting. This approach separates write-optimized storage from read-optimized analytics while preserving data quality in the source system.

CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT DATE_TRUNC('month', order_date) AS month, customer_region, SUM(order_amount) AS revenue
FROM order_facts
GROUP BY DATE_TRUNC('month', order_date), customer_region;