What is the difference between OLTP and OLAP databases?
Why Interviewers Ask This
This tests whether you can apply MySQL / SQL knowledge to real-world scenarios. Interviewers are looking for clarity of thought and evidence that you've encountered this in production code.
Answer
OLTP (Online Transaction Processing) databases are optimized for high-volume, short, fast transactions — typical operational database workloads. Characteristics: many concurrent users; queries touch few rows at a time (lookup by PK or small ranges); primarily INSERT/UPDATE/DELETE with reads; highly normalized schema (3NF+) to avoid update anomalies; optimized for write throughput and low latency; examples: MySQL, PostgreSQL, Oracle — production databases powering web applications. OLAP (Online Analytical Processing) databases are optimized for complex analytical queries over large amounts of historical data — business intelligence and reporting workloads. Characteristics: few users running complex queries; queries scan millions of rows with aggregations; primarily read-only; denormalized schema (star or snowflake schema) with dimension and fact tables for query performance; optimized for read throughput and analytical query speed; examples: Snowflake, Google BigQuery, Amazon Redshift, ClickHouse, Apache Druid. Key differences: OLTP has many small transactions; OLAP has few large scans. OLTP is normalized; OLAP is denormalized. Row-based storage vs. columnar storage. A common architecture: operational MySQL database → ETL process → data warehouse for analytics.
Pro Tip
If you're unsure about a detail, say so honestly and explain your reasoning. Interviewers respect candidates who can think through uncertainty rather than bluffing.