Query Engines & OLAP
A dashboard that loaded in two seconds now takes thirty, and “just add an index” is no answer for an analytical query scanning a billion rows.
This stratum covers how analytical query engines actually run: parsing and planning, the optimizer and its cost model, join ordering, and vectorized columnar execution — the foundations that transfer directly to ClickHouse, Trino, and DuckDB.
What you'll learn
- Follow a query from SQL text through parse, plan, and optimize to execution
- Explain why columnar plus vectorized execution wins for analytics
- Reason about cost-based optimization and join ordering
- Build a mental model that transfers across ClickHouse, Trino, and DuckDB
Tracks & courses
Full navigation is in the sidebar. Here's what each track gives you and the courses inside it.
Query Engine Foundations
Engine-agnostic theory: execution models, MPP, optimization, joins, pushdown, memory.
DuckDB & Embedded OLAP
A real query engine with the cluster removed. Vectorized execution, open-format reads, Arrow zero-copy, the single-writer model, Quack networking, and the honest decision rule for when one process beats a cluster.
ClickHouse Internals
A single-node-first engine that grew into a cluster. On-disk layout choices - parts, granules, marks, the sparse primary index, skip indexes, codecs - that show up in every query plan. The first S6 engine where storage design and query design are inseparable.
Real-Time OLAP: Druid & Pinot
The latency-extreme corner of OLAP: streaming ingest plus sub-second user-facing queries. Role-based clusters, segments as the shared primitive, Druid bitmaps vs Pinot's index portfolio, ingest-time rollup, Pinot upserts and the star-tree index, tiered storage, and the Druid-vs-Pinot decision framework.
Trino & Federated Query
The most general query engine in S6: SQL over anything, owns no storage, decides everything at query time. The connector model, query lifecycle, the cost-based optimizer fed by connector stats, dynamic filtering, pushdown, the Hive/Iceberg/Delta connectors, federation and its real costs, resource groups, and fault-tolerant execution.
Query Engine Operations
The on-call layer. Earn trust in the engines from 6.2-6.5 in production conditions: EXPLAIN literacy across engines, profiling a slow query methodically, memory and spill, concurrency and admission control, multi-tenancy, caching, autoscaling, capacity planning for p99, cost observability, honest benchmarking, upgrades, and the recurring incident patterns with runbook starting points.
Designing OLAP Systems
The cross-engine design course. With vocabulary installed, four engines internalized, and ops trust earned, the remaining decisions are architectural: pre-aggregation vs raw, aggregation granularity, materialized-view strategies, denormalization on columnar engines, late-arriving data, upsert architectures, federation design, real-time design, multi-engine patterns, and cost-vs-latency curves. Capstone: same workload, three valid designs.
Related topics
Start Query Engines & OLAP free
The first chapters of every course are free to read — no account needed.