Analytical questions, such as which genres sell best where, read BookNest's sample orders very differently from the app that wrote them. This chapter covers the engines and SQL built for them, from column-store internals and PostgreSQL 18 1,289 through DuckDB 61,228 , dimensional modeling and dbt 37,942 to cloud warehouses and ClickHouse 29,491 .
LAMP Stack Development taught SQL in depth with MySQL 524 ; this chapter assumes it and teaches only what analytics adds. Everything runs on the 4-CPU WSL2 6 host; paid cloud warehouses are described from documentation, "not run here."
What you will learn:
How OLTP and OLAP differ, and how column stores, compression and vectorization serve analytics
PostgreSQL 18's analytical SQL, materialized views, partitioning and parallel query
DuckDB on files, dimensional modeling, slowly changing dimensions and dbt
Cloud warehouses, ClickHouse, semantic layers and warehouse security compared
Sections
- OLTP vs OLAP
- Column Stores Under the Hood
- PostgreSQL 18 for Analytics
- LATERAL, CTEs, generate_series
- Views, Partitions, Parallelism
- DuckDB: In-Process OLAP
- Querying Files with DuckDB
- DuckDB Extensions vs Postgres
- Dimensional Modeling
- Slowly Changing Dimensions
- Transforming Data with dbt
- dbt Fusion and SQLMesh
- Cloud Data Warehouses
- ClickHouse
- Performance and Semantics
- Securing the Warehouse
- Choosing an Analytical Stack
- Test Yourself!