Stage 2 · Lesson 4 of 9

OLTP and OLAP

There are two kinds of database work. , online transaction processing, runs the application itself. It reads and writes a few rows at a time, quickly and safely, like saving one order.

Its tables are , meaning each fact is stored once in one place. OLAP, online analytical processing, answers questions across millions of rows: totals, trends and comparisons. OLAP systems favour and table designs that are easy to add up, even if some data is repeated.

copy data from the transactional database into an analytical database, so reports do not slow down the app. This is why a warehouse model looks different from the app's database.

Common traprunning heavy reports directly on the production database, which slows it for real users and locks tables at the worst time.

A question to check yourself, an AI practice session and 1 cheat sheet entry come with the full roadmap. Unlock

Resources for this stage

Start with

CourseFree
SQL Tutorial for Data Analysis (formerly Mode)
ThoughtSpot · Beginner
Its advanced section covers window functions, performance tuning and pivoting on realistic data

Practise

LeetCode SQL 50
Fifty graded problems; the window function and aggregation ones match what pipeline SQL needs
Select Star SQL
An interactive book on one real dataset that ends with window functions and self joins

Keep handy

Kimball Dimensional Modeling Techniques
The original definitions of grain, conformed dimensions and SCD types, straight from the source
roadmap.sh: SQL
Check which SQL topics you still lack before moving on

Also worth your time

Data Warehouse Design: Schemas, Steps, and Best Practices (opens in a new tab)Read
Star and snowflake schemas, facts and dimensions, and the steps of a warehouse design in one read