Season 5 Ep 3 — If Ep 1 was storage and Ep 2 was flow, Ep 3 is query. The era where "one engine does everything" is over, and engine placement has become a new design domain.
- Prologue — "The engine is a tool, the problem is the workload"
- Chapter 1 · Classifying OLAP Engines
- Chapter 2 · DuckDB — "The revolution of single-node OLAP"
- Chapter 3 · ClickHouse — "The king of real-time OLAP"
- Chapter 4 · Snowflake and BigQuery — "The managed giants"
- Chapter 5 · StarRocks and Doris — "MPP real-time BI"
- Chapter 6 · Apache Pinot and Druid — "Ultra-low-latency OLAP"
- Chapter 7 · Trino and Presto — "Federated queries"
- Chapter 8 · Databricks SQL and Redshift
- Chapter 9 · The Traps in Benchmarks
- Chapter 10 · Engine Placement Patterns
- Chapter 11 · A Selection Guide for Korean Companies
- Chapter 12 · Universal Principles of Performance Tuning
- Chapter 13 · Ten Antipatterns
- 13.1 "Everything with one engine"
- 13.2 Choosing on benchmarks alone
- 13.3 Approving a PoC without production load
- 13.4 Ignoring the star schema
- 13.5 Over-partitioning
- 13.6 No management of materialized views
- 13.7 Self-hosting without on-call readiness
- 13.8 Going all in on SaaS while ignoring lock-in
- 13.9 Scaling up instead of tuning queries
- 13.10 No user quotas or guardrails
- Chapter 14 · Checklist — 12 Things Before Adopting an OLAP Engine
- Chapter 15 · Next Up — Season 5 Ep 4: "dbt, SQLMesh, Dagster, Airflow, Prefect"
Prologue — "The engine is a tool, the problem is the workload"
The 2015–2020 OLAP debate was "which engine is fastest." 2025 is different:
- Benchmarks are marketing tools (the interpretation of TPC-H and TPC-DS is conditional)
- Each engine has an overwhelming strength in a specific workload
- It is normal for one company to use 2–4 engines
- "Right engine in the right place" determines cost, performance, and team capability all at once
This post lays out the realistic strengths and weaknesses of the major 2025 OLAP engines, and the placement strategy that follows.
Chapter 1 · Classifying OLAP Engines
1.1 Four Axes
- Deployment: single node / distributed MPP / serverless
- Workload: BI (complex joins) / real-time (low latency) / ad-hoc (exploration)
- Managed vs self-hosted: SaaS / open source plus self-operation
- Data location: built-in storage / Lakehouse integration
1.2 Classifying the Major Engines
| Engine | Deployment | Primary workload | Model |
|---|---|---|---|
| DuckDB | Single | Ad-hoc, embedded | Open |
| ClickHouse | Distributed | Real-time, logs | Open + Cloud |
| Snowflake | Serverless | BI, ELT | SaaS |
| BigQuery | Serverless | BI, ELT | SaaS |
| Databricks SQL | Distributed | BI + ML | Managed |
| Redshift | Distributed | BI | Managed |
| StarRocks | Distributed | Real-time BI | Open + SaaS |
| Apache Doris | Distributed | Real-time BI | Open |
| Apache Pinot | Distributed | Ultra-low latency | Open |
| Apache Druid | Distributed | Time series, streams | Open |
| Trino/Presto | Distributed | Federated queries | Open |
Chapter 2 · DuckDB — "The revolution of single-node OLAP"
2.1 Identity
- Started at CWI in 2019
- Embedded OLAP: just as SQLite brought OLTP to the laptop, DuckDB brings OLAP to the laptop
- Queries Parquet/CSV/JSON directly, with extensions for reading Iceberg and Delta
2.2 Strengths
- Installs in 5 seconds, a single binary
- Python/R/Node/Go/Rust bindings
- Can analyze billions of rows on 8–16GB of RAM (columnar plus vectorization)
- Queries Parquet "with native SQL"
2.3 2024–2025 Momentum
- MotherDuck: DuckDB extended into the cloud
- Iceberg and Delta read support formalized
- In-browser OLAP with DuckDB WASM
- dbt-duckdb as the standard for local development and CI
2.4 Limits
- Single node (vertical scaling only)
- Concurrency is at the in-app scale
- Not a replacement for a large server cluster
2.5 Where It Is Used
- Local development for analysts
- Data validation in CI
- OLAP backend for small and mid-sized projects
- SQL engine for notebooks and Python scripts
Chapter 3 · ClickHouse — "The king of real-time OLAP"
3.1 Identity
- Internal at Yandex in 2009 → open sourced in 2016 → ClickHouse Inc. in 2021
- Distributed columnar, built on the MergeTree engine
- Real-time insertion of billions of rows plus second-scale queries
3.2 Strengths
- Overwhelming insertion and query performance (on specific workloads)
- The de facto standard for real-time dashboards
- Direct consumer via the Kafka/Kinesis Engine
- Cost efficient (when self-operated)
3.3 2024–2025 Trends
- ClickHouse Cloud (SaaS) growing rapidly
- Expanded Iceberg reads
- More advanced materialized views and dictionaries
- Many observability platforms (parts of Datadog, Axiom, Tinybird, PostHog) are built on ClickHouse
3.4 Limits
- JOIN optimization is weaker than some engines (greatly improved recently)
- Complex to operate (when self-hosted)
- Some differences in SQL compatibility
3.5 Where It Is Used
- Log and event analysis
- Real-time dashboards
- Ads and marketing analytics
- APM/observability backends
Chapter 4 · Snowflake and BigQuery — "The managed giants"
4.1 Snowflake
- Serverless, compute and storage separated
- Multi-cluster warehouse (automatic scaling)
- Native Iceberg tables (2024)
- Cortex AI (LLM and ML features integrated)
- Data Sharing
4.2 BigQuery
- Google Cloud serverless
- Slot-based compute, fast ad-hoc
- Iceberg External plus BigLake
- Gemini integration (natural language to query SQL)
- BI Engine (in-memory acceleration)
4.3 Strengths
- Zero operational burden
- Initial setup in a few days
- Security, auditing, and backup built in
- Overwhelming share among large enterprises
4.4 Limits
- Cost: at scale, tens of thousands to hundreds of thousands of dollars per month
- Lock-in (being eased by Iceberg support)
- Unsuitable for some workloads (ultra-low latency)
4.5 Strategy for 2025
- Query external data in the lake via Iceberg External Tables
- Hot/cold separation: hot in native, cold in Iceberg
- dbt, Airflow, and Dagster integration is the standard
Chapter 5 · StarRocks and Doris — "MPP real-time BI"
5.1 What They Share
- Real-time plus BI plus Lakehouse support
- Iceberg/Hudi/Delta reads
- MySQL protocol compatible (BI friendly)
- Chinese in origin, expanding globally
5.2 StarRocks
- Forked from Apache DorisDB → became independent
- CelerData provides commercial support
- Fast growth in 2024–2025
- Mature cost-based optimizer
5.3 Apache Doris
- An Apache Foundation project
- Originated at Baidu
- Routine Load (direct from Kafka)
- Strong for real-time dashboards
5.4 Strengths
- Strong at both real-time and complex joins
- Dual operation of Lakehouse plus their own storage
- JOIN advantage over ClickHouse
5.5 Where They Are Used
- Real-time operational BI
- Complementing a Lakehouse engine
- Growing adoption in China, Korea, and Japan
Chapter 6 · Apache Pinot and Druid — "Ultra-low-latency OLAP"
6.1 Apache Pinot
- Originated at LinkedIn → Apache
- Targets query latency under 100ms
- Upsert support (2023–)
- StarTree (SaaS) available
6.2 Apache Druid
- Originated at Metamarkets → Imply commercializes it
- Specialized for time series and streams
- Kafka indexing service
6.3 Where They Are Used
- Ultra-low-latency analytics at LinkedIn, Uber, Netflix and others
- Customer-facing dashboards
- Real-time recommendation and anomaly detection
6.4 Operational Difficulty
- Complex setup, requires operations engineers
- SaaS (StarTree/Imply) eases the burden
6.5 Differences from ClickHouse and StarRocks
- Pinot/Druid: specialized for ultra-low latency under 100ms
- ClickHouse: broad workload coverage plus excellent price-performance
- StarRocks: complex BI joins plus Lakehouse friendliness
Chapter 7 · Trino and Presto — "Federated queries"
7.1 Identity
- Facebook Presto → Trino (2020 fork, by the original developers)
- Dozens of connectors: Iceberg/Delta/Hudi, Hive, MySQL, Postgres, Kafka and more
- Multiple data sources through a single SQL statement
7.2 Strengths
- Multi-source JOINs
- Among the best performers for Iceberg reads and writes
- Can be self-hosted
- Serves as the "central query engine" inside a company
7.3 Starburst
- Commercial Trino distribution, with enterprise features and support
- Gateway, cache, and security integrated
7.4 Limits
- Stronger for ad-hoc and ELT than for dashboards
- Complex to operate (managing coordinators and workers)
- Read-centric rather than real-time ingestion
7.5 Presto vs Trino
- Presto: maintained by Meta/PrestoDB
- Trino: effectively the community standard
- Choosing Trino is the norm for new projects in 2025
Chapter 8 · Databricks SQL and Redshift
8.1 Databricks SQL
- SQL warehouse built on Delta plus Spark
- Photon engine (vectorized)
- Iceberg UniForm support in 2024
- ML plus BI integration is the strength
8.2 Redshift
- The traditional AWS DW
- Compute and storage separated via RA3 plus AQUA
- S3 Iceberg access via Spectrum
- Deeply integrated with the AWS ecosystem
8.3 Where They Are Used
- Databricks SQL: teams already using Databricks
- Redshift: AWS-centric companies, connecting SAP and Salesforce
8.4 Strategy for 2025
- Databricks: Iceberg compatibility plus Mosaic AI
- Redshift: stronger serverless and AI integration
- Both are moving to ease lock-in
Chapter 9 · The Traps in Benchmarks
9.1 TPC-H and TPC-DS
- Industry standards, but different from real workloads
- Engine vendors promote results they tuned themselves
- Query plans, data scale, and concurrency settings sway the results heavily
9.2 ClickBench
- Led by the ClickHouse team (there are fairness disputes)
- Still, an open benchmark that allows comparing a variety of engines
9.3 StarSchema and JOB
- Complex-join benchmarks, closer to reality
- Useful for exposing the gaps between engines
9.4 A Practical Evaluation Protocol
- Sample your own data (10–100GB)
- Take 5–15 of your frequent queries
- Simulate concurrency of 10–100
- Calculate performance against cost
- Assess operational complexity (infrastructure, monitoring, on-call)
9.5 "Price-performance ratio" Is the Real Metric
- Not raw milliseconds but $/query or $/TB scanned
- Concurrent user counts and latency SLAs belong in there too
Chapter 10 · Engine Placement Patterns
10.1 The "one engine" Antipattern
- Everything on Snowflake alone → ultra-low latency fails, costs explode
- Everything on ClickHouse alone → complex BI joins and management burden
- Everything on Trino alone → real-time is impossible
10.2 The Realistic "2–4 engines" Pattern
Pattern A: Startup (small scale)
- DuckDB: development and CI
- Snowflake/BigQuery: production BI
Pattern B: SaaS (centered on real-time dashboards)
- ClickHouse: production analytics and customer-facing
- DuckDB: local analysis
- Snowflake: internal BI plus ELT
Pattern C: Enterprise (large company)
- Snowflake/Databricks: company-wide BI
- Trino: Lakehouse federated queries
- ClickHouse/Pinot: specific high-performance apps
- DuckDB: analyst development
Pattern D: High-performance customer-facing
- Pinot/Druid: millisecond UX
- ClickHouse: admin dashboards
- BigQuery/Snowflake: executive and strategic BI
10.3 Data Sharing
- Concentrate the source of truth in Iceberg
- Each engine reads Iceberg, replicating into its own storage when needed
- Keep the metadata catalog common (Polaris/Unity/Glue)
Chapter 11 · A Selection Guide for Korean Companies
11.1 The Current State
- Finance and public sector: Teradata/Oracle plus Hadoop → migrating to Snowflake/Databricks
- Commerce and gaming: a mix of ClickHouse plus BigQuery and Snowflake
- Startups: DuckDB plus Snowflake/BigQuery rising sharply
11.2 Korean Particulars
- Network separation requirements: on-premise engines (ClickHouse, StarRocks, Trino) preferred
- Korean-language SQL tool compatibility (Metabase, Redash, Superset and other Korean screens)
- Large-volume log and game analytics: ClickHouse has the price-performance edge
- Financial BI: Snowflake and Databricks adoption rising sharply
11.3 A Practical Recommendation Matrix
| Scenario | First choice | Secondary |
|---|---|---|
| Company-wide BI (large company) | Snowflake/BigQuery | DuckDB, Trino |
| Real-time logs and APM | ClickHouse | Druid/Pinot |
| Customer-facing dashboards | Pinot/StarTree | ClickHouse |
| ML plus BI integration | Databricks SQL | Snowflake |
| Lakehouse federation | Trino | DuckDB |
| A startup first DW | BigQuery/Snowflake | DuckDB |
| On-premise and finance | StarRocks/Doris | ClickHouse |
Chapter 12 · Universal Principles of Performance Tuning
12.1 Schema Design
- The star schema (fact plus dimension) is still powerful
- Tune the level of denormalization per engine and workload
- Optimize column types (Int32 vs Int64 and so on)
12.2 Partitioning and Sorting
- Time partitioning by default
- Base the sort key on WHERE and JOIN patterns
- ClickHouse: primary key = sort key
12.3 Indexes and Projections
- ClickHouse: skip index, projection
- StarRocks: bitmap index, MV
- Snowflake: Search Optimization Service
- Iceberg: Puffin statistics
12.4 Caching
- Result cache (query results)
- Metadata cache
- Query plan cache
- CDN/edge cache (dashboards)
12.5 Materialized Views
- Precompute frequently used aggregations
- Incremental updates are the crux
- ClickHouse, Snowflake, and Materialize are strong here
Chapter 13 · Ten Antipatterns
13.1 "Everything with one engine"
Guaranteed failure. Split by workload.
13.2 Choosing on benchmarks alone
Re-evaluation with your own data and queries is mandatory.
13.3 Approving a PoC without production load
Insufficient concurrency and SLA testing.
13.4 Ignoring the star schema
Excess denormalization → a maintenance nightmare.
13.5 Over-partitioning
Tens of thousands of small files → planning hell.
13.6 No management of materialized views
They go stale, and only the cost piles up.
13.7 Self-hosting without on-call readiness
ClickHouse, Trino, and Druid carry a heavy on-call burden.
13.8 Going all in on SaaS while ignoring lock-in
Data sovereignty plus cost risk.
13.9 Scaling up instead of tuning queries
Costs rise and nothing is fundamentally solved.
13.10 No user quotas or guardrails
Invites "one person causes downtime for everyone."
Chapter 14 · Checklist — 12 Things Before Adopting an OLAP Engine
- Decompose the workload (BI/real-time/ad-hoc/customer-facing)
- Benchmark 3–5 candidate engines yourself
- Price-performance matrix
- Assess operational complexity (managed vs self)
- Security, audit, and RBAC requirements
- Lakehouse compatibility (Iceberg/Delta/Hudi)
- BI tool and ETL tool compatibility
- Data sharing and catalog strategy
- Disaster recovery and backup
- Scalability simulation (10–100x)
- Staffing and training plan
- A final engine placement diagram
Chapter 15 · Next Up — Season 5 Ep 4: "dbt, SQLMesh, Dagster, Airflow, Prefect"
If engines query the data, orchestrators govern the pipelines. Ep 4 covers the tooling ecosystem of data transformation and orchestration.
- The standardization of dbt and its limits
- The arrival of SQLMesh: an alternative to dbt, or a complement
- Dagster: asset-centric orchestration
- The evolution of Airflow 2.x and 3.0
- The rebirth of Prefect 3.0
- The boundary with Temporal
- Data contracts and schema evolution
- CI/CD for data pipelines
- Observability plus alerting
- Stack selection at Korean companies
- Balancing "one tool does it all" against "a tool chain"
"CI/CD for data pipelines" is the real front line of data engineering in 2025.
See you in the next post.
Summary: OLAP in 2025 has escaped the illusion of "everything with one engine" and become the era of engine placement by workload. DuckDB takes single node plus development and CI, ClickHouse takes real-time analytics, Snowflake, BigQuery, and Databricks SQL take managed BI, StarRocks and Doris take real-time BI plus Lakehouse, Pinot and Druid take ultra-low-latency customer-facing work, and Trino takes federated queries. Benchmarks are a starting point, evaluating your own workload is mandatory, and a placement of 2–4 engines is the realistic dominant pattern. Korean companies should design their engine mix while accounting for network separation, Korean-language BI tools, gaming, and financial particulars. "Engine placement is the core of data platform design" is the lesson of 2025.