What’s the difference between OLTP and OLAP, and how does that affect schema design?
OLTP (online transaction processing) systems handle many small, frequent reads and writes, a single order being placed, a single row updated, and they’re optimized for that: normalized schemas that avoid data duplication, so a write only has to touch one place. OLAP (online analytical processing) systems handle the opposite pattern, infrequent but massive queries that scan and aggregate huge volumes of historical data, “total revenue by region by month for the last three years,” and they’re optimized for that instead.
Why the same schema doesn’t work for both
A normalized OLTP schema, correct and efficient for transactional writes, becomes painfully slow for OLAP-style analytical queries because answering “total revenue by region” requires joining across many normalized tables at massive scale, which is expensive. OLAP systems instead favor denormalized structures, star or snowflake schemas with a central fact table (the transactions) surrounded by dimension tables (region, time, product), explicitly trading storage space and write complexity for dramatically faster read/aggregation performance.
Best practice
Don’t try to run heavy analytical queries directly against your OLTP production database, it’ll contend for the same resources transactional writes need and can degrade the actual application. Instead, extract data via ETL/ELT into a separate OLAP-oriented warehouse (BigQuery, Snowflake, Redshift) built with a schema optimized for the analytical access pattern, keeping the two workloads physically and structurally separate.
Edge case interviewers probe for
Ask how you’d handle a need for near-real-time analytics without waiting for a nightly ETL batch. This is where change-data-capture (streaming changes from the OLTP database as they happen) into a real-time OLAP system, or a hybrid HTAP (hybrid transactional/analytical processing) database, comes in, a more advanced tradeoff than the simple “batch ETL overnight” default.
Common mistake
Treating “normalize everything” as universally correct database design advice, when it’s specifically an OLTP optimization; applying the same rule to an analytical warehouse schema actively hurts query performance.
What the interviewer is checking
Whether you understand that schema design decisions follow from actual access patterns (many small transactions versus few, large aggregations), not a single “correct” universal design.
Think of OLTP like a cash register at a busy store: it needs to ring up one sale at a time, fast, correctly, constantly, all day long. Think of OLAP like the store’s end-of-year accountant, who doesn’t care about ringing up individual sales but instead needs to answer big-picture questions like “what were total sales by region for each month this year,” which means looking across everything at once rather than one transaction at a time.
If you made the cash register work exactly like the accountant’s spreadsheet (storing everything spread out and cross-referenced for easy big-picture summarizing), ringing up a single sale would become slow, since it’d have to update information scattered in many different places. If you made the accountant work directly off the cash register’s format (organized purely for speed of one transaction at a time), answering “total sales by region” would mean manually piecing together thousands of individual receipts, painfully slow. Each job needs its own organization, which is exactly why companies keep a separate analytical warehouse instead of running big reports directly against the live transaction system.
Why interviewers ask this
It’s a foundational data engineering concept that reveals whether you understand schema design as access-pattern-driven rather than a single universal “best practice.”
What a strong answer signals
That you know when normalization helps versus hurts, and understand why analytical and transactional workloads need physically separate systems.
Common follow-ups
- What’s a star schema versus a snowflake schema?
- How would you design an ETL pipeline to keep the warehouse in sync?
- What is HTAP and when would you actually reach for it?
Advanced variation
“How would you handle slowly changing dimensions in a star schema,” which expects knowledge of SCD types (like tracking historical changes to a customer’s address for accurate historical reporting).
An e-commerce team initially ran monthly revenue reports directly against their production PostgreSQL database, which used a fully normalized OLTP schema. As the business grew, these reports started taking hours and noticeably slowing down checkout during the query window. Moving to a nightly ETL pipeline that denormalized the data into a star schema (a central orders fact table joined to date, product, and region dimension tables) in a separate BigQuery warehouse cut report time from hours to seconds and completely removed the load from the production database that customers were actively using to check out.
- 1OLTP handles many small, frequent transactions; OLAP handles infrequent, massive analytical queries.
- 2Normalized schemas suit OLTP writes; denormalized star/snowflake schemas suit OLAP reads.
- 3Running heavy analytical queries directly against a production OLTP database risks degrading the live application.
- 4ETL/ELT pipelines move data from OLTP into a separate, purpose-built OLAP warehouse.
- 5Schema design should follow the actual access pattern, not a single universal “always normalize” rule.