What is the difference between an OLTP database and an OLAP data warehouse?

What the interviewer wants to hear

• OLTP handles many small transactional reads and writes
• OLAP handles large analytical queries over many rows
• OLTP is row-oriented and normalised; OLAP is often columnar and denormalised
• Gives example systems for each

Model answer

OLTP stands for online transaction processing. It is the database behind an app, like PostgreSQL or MySQL, handling many small, fast operations: insert an order, update a balance, read one user. It is row-oriented, highly normalised to avoid duplicate data, and tuned for low latency and strong consistency. OLAP, online analytical processing, is for questions like "revenue by region per month for 3 years". These queries scan millions of rows but only a few columns, so warehouses such as BigQuery, Snowflake or Redshift use columnar storage, compression and massively parallel processing. OLAP models are often denormalised star schemas to make queries simpler. We usually do not run heavy analytics on the OLTP database, because it would slow down the app, so we copy data into the warehouse.

More easy Data Engineer questions

• Describe a data pipeline you built or would build, from source to dashboard.
• Explain a star schema and why it is used in data warehouses.
• What data quality checks would you add to a pipeline?
• Why use an orchestration tool like Airflow instead of cron jobs?
• What is a slowly changing dimension, and how do SCD Types 1, 2 and 3 differ?
• Why would you store analytics data as Parquet rather than CSV or JSON?
• What is change data capture (CDC), and why do data teams use it?
• Explain Kafka topics, partitions, offsets and consumer groups to a new teammate.
• What does dbt do in a modern data stack, and what are its main features?