Analytics

Analytics#

Analytical workloads (big aggregations across many rows, with low-latency interactive responses) benefit from columnar storage, parallel execution, and separation of compute from storage. The category includes warehouses, lakehouses, embedded analytical engines, and the open table formats they share.

Row vs. Column#

  • Row-oriented stores (Postgres, MySQL) write each row’s bytes together. Best for transactional workloads that read and write individual rows.

  • Column-oriented stores write each column’s bytes together. Reading a few columns over many rows (the analytical access pattern) is dramatically faster.

Modern engines blur the line; many “OLTP” databases now ship columnar indexes for analytics within the same engine.

Cloud Warehouses#

The managed analytical databases that anchor most enterprise data stacks. BigQuery is GCP-native; Snowflake spans clouds; Redshift is the AWS pick; Databricks SQL sits on Delta Lake; Microsoft Fabric is the Azure stack. Each competes on price/performance, ecosystem, and integration depth.

Managed, serverless or near-serverless analytical databases.

These compete on price/performance, ecosystem, and how well they integrate with the rest of your stack.

Open-Source / Self-Hosted#

The non-cloud options. ClickHouse leads for self-hosted analytical workloads; DuckDB is SQLite-shaped for analytics; Druid and Pinot serve real-time low-latency queries; StarRocks and Doris are modern MPP databases. Choosing here usually trades cloud convenience for cost and control.

  • ClickHouse, column-oriented, extremely fast, increasingly the default for self-hosted analytics.

  • DuckDB, in-process columnar; SQLite for analytics. Runs on a laptop or in a serverless function.

  • Apache Druid, real-time analytics on streaming data.

  • Apache Pinot, low-latency analytical; similar niche to Druid.

  • StarRocks / Doris modern MPP databases.

Lakehouse#

The architecture that won the open-data-platform debate. Keep raw Parquet files in object storage; layer Iceberg or Delta or Hudi as the metadata format; query with Trino, Spark, or Dremio. The combination decouples storage from compute and breaks vendor lock-in.

The pattern: keep raw data as files in object storage; layer a metadata catalog and a query engine on top.

Query engines on top.

The combination (object storage + Iceberg + Trino) has become the default open architecture for new data platforms.

Streaming Analytics#

The kind of analytics that runs continuously over event streams instead of batch tables. Flink is the standard processor; ksqlDB and Kafka Streams sit on Kafka; Materialize incrementally maintains views; RisingWave wraps streaming in a Postgres-compatible database.

Continuous queries over event streams.

Streaming analytics blurs into real-time databases (Druid, Pinot, Tinybird, ClickHouse with Kafka Connect).

DuckDB#

Worth its own section. DuckDB has become the analytical default for single-machine work in 2026.

$ duckdb my.db

-- Read parquet directly
SELECT user_id, SUM(amount)
FROM   read_parquet('s3://bucket/orders/*.parquet')
GROUP  BY user_id;

-- Read CSV / JSON / Excel
SELECT * FROM 'data.csv';
SELECT * FROM 'data.json';

-- Read Postgres directly
ATTACH 'postgres://user@host/db' AS pg (TYPE postgres);
SELECT * FROM pg.public.orders;

DuckDB embeds in Python, R, Node, Go, Rust, Java; it runs on laptops, inside Lambda / Cloud Functions, in browsers via WebAssembly.

ETL / ELT#

The architectural question every data team answers. ETL transforms in flight before loading; ELT lands raw data and transforms with SQL inside the warehouse. Modern stacks default to ELT with dbt or SQLMesh and managed extract/load connectors like Fivetran and Airbyte.

The pipeline-form question.

  • ETL, Extract, Transform, then Load. Old pattern; transform in flight.

  • ELT, Extract, Load, then Transform. Modern pattern; let the warehouse do the work with SQL.

Tooling.

Data Modeling#

The forms of warehouse table design. Star schemas split facts from dimensions; one-big-table denormalizes for columnar friendliness; Kimball-style dimensional modeling remains the reference; Data Vault buys flexibility at the cost of weight. Each suits a different governance posture.

  • Star schema, one fact table per metric, surrounded by dimension tables. The classic warehouse model.

  • One Big Table (OBT), denormalized, columnar-friendly. Increasingly common.

  • Dimensional modeling (Kimball), still the most-cited reference for warehouse design.

  • Data Vault, enterprise modeling for source-system flexibility; heavier.

Choosing#

The decision tree for picking an analytical engine. DuckDB for solo work, cloud warehouses for team scale, ClickHouse for self-hosted speed, lakehouse stacks for openness, Druid or Pinot for real-time. Most products start by needing none of this, and add only when row-store analytics struggle.

  • Single machine, one analyst, ad-hoc work, DuckDB.

  • Team-scale, on a cloud, BigQuery / Snowflake / Redshift.

  • Self-hosted, fast queries on lots of data, ClickHouse.

  • Open data architecture, Iceberg + Trino on object storage.

  • Real-time, Druid / Pinot / ClickHouse with streaming ingestion.

Most products don’t need any of this until “the database is slow on analytical queries that hold a lock”. Start there; expand when the data volume or query latency demands it.

Contents