The last hop. Streams built by M.39-M.42 need to become queryable — user-facing dashboards, live analytics, sub-second aggregations. Two families answer this: streaming SQL (Materialize, RisingWave) maintains a query result up-to-date incrementally; real-time OLAP (Pinot, Druid, ClickHouse) does columnar storage with heavy pre-aggregation over streaming ingestion. Both are wrong choices for the workload the other one is designed for. Which one you need depends on the query shape — and the answer isn't always which one is faster.
Every module in Phase H has been about processing streams correctly — batching them (M.39), windowing them (M.40), making them stateful (M.41), sourcing them from operational databases (M.42). What we haven't discussed until now is what streams are ultimately for. In a modern data platform, the answer is user-facing: dashboards that update in real-time as events flow through, analytics APIs that return sub-second query responses over live data, materialized views that stay fresh without periodic rebuilds. The streaming pipeline exists to feed something that a human or an application queries. And that final hop — from "stream flowing into a system" to "query returning a result" — has its own class of technology and its own class of tradeoffs, distinct from everything Phase H covered before. This module is that final hop.
The specific difficulty is that "fast writes" and "fast reads" are usually in tension. Systems optimized for OLTP writes (Postgres, MySQL) use row-oriented storage with B-tree indexes that are cheap to update but expensive to scan for analytics. Systems optimized for OLAP reads (Snowflake, BigQuery, Redshift) use columnar storage with heavy compression and pre-computed aggregates that are cheap to query but expensive to update — the whole ingestion pipeline is batch-oriented, taking minutes to hours to load new data. Real-time analytics platforms sit in the gap: they accept sub-second ingest of individual events (like OLTP) while also serving sub-second aggregation queries over billions of rows (like OLAP). The two families of solutions to this gap take radically different approaches, and understanding when to use each is the specific engineering intuition M.43 is trying to build.
Each earlier attempt fails at either freshness (batch is too slow), isolation (Postgres-direct hurts OLTP), or incrementality (refresh cycles are the wrong shape). Real-time analytics platforms are the specific class of system that hits all three simultaneously — continuously ingesting events, incrementally maintaining query results or columnar aggregations, and serving arbitrary queries at sub-second latencies. The two families (streaming SQL vs real-time OLAP) trade off differently, but both close the gap in ways batch systems fundamentally can't.
The historical arc tells a specific story. Real-time OLAP came first, from ad-tech scale problems around 2011-2013: Metamarkets built Druid to serve real-time advertising analytics; LinkedIn built Pinot for the same class of internal dashboards; both were open-sourced around 2014-2015. Both took the "columnar + pre-aggregation + streaming ingest" approach that made ad-tech dashboards viable. Streaming SQL emerged separately, from a different theoretical foundation: Frank McSherry's differential dataflow work at Microsoft Research (published 2013) formalized incremental view maintenance in a general form; Materialize (2019) built a production system on it; RisingWave (2021) followed with a Rust reimplementation. The two families didn't converge from the same starting point — they're solutions to overlapping problems from different intellectual traditions. The workload types they excel at reflect their origins: real-time OLAP wins on high-cardinality dashboards and time-series aggregation (its ad-tech heritage); streaming SQL wins on complex joins and transactional consistency (its dataflow heritage). Understanding this history helps understand why the tools have different strengths — they were solving different problems first, and only later got compared as alternatives.
The two families of real-time analytics platforms take radically different approaches to the same class of problem. Getting the distinction right is the first architectural decision: pick the wrong family for your workload and you'll fight the system's grain for the entire deployment. The two families aren't competitors that do the same thing at different quality — they're different tools optimized for different query shapes, with overlap in the middle. The choice depends on what your queries actually look like.
Mental model: "I write a SELECT query. The system maintains its result continuously as inputs change. When I ask for the answer, I get it immediately — it's already computed."
How it works: Incremental View Maintenance via differential dataflow. Input changes propagate through the dataflow graph; only affected output rows change. The result of a JOIN is maintained rather than recomputed.
Wins at: Complex multi-table joins, transactional consistency, small-to-medium result sets, SQL-native semantics. Great for dashboards where the query IS the definition of what you want to see.
Loses at: Very high cardinality group-bys, TB-scale historical depth, ad-hoc queries over huge time ranges (materialized views are precomputed for specific queries).
Mental model: "Events land in columnar segments with pre-computed aggregations. Queries hit the segments with heavy indexing — bitmaps, star trees, bloom filters. Any dimensional slice returns fast."
How it works: Ingestion writes to columnar segments (Parquet-like); segments are indexed for fast filter and aggregation; queries scatter across segments and gather results.
Wins at: High-cardinality dashboards (millions of dimensions), time-series aggregations (roll-up-heavy), ad-hoc slicing across huge event streams. Great for "count/sum/percentile over a big table filtered by many dimensions."
Loses at: Complex joins (limited support or slow), transactional consistency (eventual only), stream state that requires ordered processing (Materialize's territory).
The mental model difference is the important part. In streaming SQL, you write the query first and the system figures out how to maintain it incrementally — your artifact is the SQL statement. In real-time OLAP, you configure how data is ingested (partitioning, rollups, indices) and then queries hit the columnar store — your artifact is the schema and ingestion config. These are two different engineering skills. A team fluent in one is often clumsy with the other on their first project. Being deliberate about which family you're choosing, and why, is what separates senior real-time analytics engineering from cargo-culting.
The middle ground and boundary cases matter too. ClickHouse straddles the boundary — technically columnar OLAP but with strong SQL support including some joins; often used where teams want "one system that handles both" and are willing to trade optimality for versatility. Flink SQL sits between streaming SQL and stream processing — you can write SELECT queries against streaming inputs, and it can emit results to sinks, but it doesn't maintain a queryable materialized view the way Materialize does. Snowflake and BigQuery are strictly batch (minutes-to-hours ingestion latency) but with strong SQL and joins — they're the batch competitor to streaming SQL, not to real-time OLAP. Placing a new tool on this map takes ~10 minutes of reading the docs: look at ingestion latency, query latency, join support, and cardinality ceiling; each metric points toward one family or the other.
The end-to-end pattern that closes Phase H is worth naming explicitly: Postgres → Debezium (M.42) → Kafka (M.39) → Flink (M.40, M.41) → Materialize or Pinot or Druid (M.43) → user-facing dashboards or APIs. Every hop in this pipeline has been covered by a specific module. The specific insight is that this pipeline is not a single system — it's five independently-scalable, independently-operable components with well-defined interfaces between them. Each does one thing well; the composition is what makes it viable. The alternative — building all of this into a single monolithic "analytics database" — has been tried repeatedly and always fails, because the specialization at each hop is what makes each hop tractable. The multi-component pipeline IS the modern real-time analytics architecture. M.43's final answer to "how do we do real-time analytics" is: build this pipeline, and use the right analytics engine for the query shape at the end.
The specific mechanism that makes streaming SQL work — and the one worth understanding in depth because it maps to the general "how do we do continuous computation efficiently?" problem — is incremental view maintenance (IVM). The idea: given a query and a stream of input changes, compute how the query's output changes without recomputing the entire query. Classical databases have supported materialized views for decades, but refreshing them was periodic and expensive — full recomputation of the query on a schedule. IVM makes the maintenance continuous and proportional to the change size: an input change that affects one row of the output causes exactly one row of the output to be updated, in microseconds. This is the specific property that Materialize built its business on and RisingWave replicated.
+row for INSERT, -row for DELETE, or a pair for UPDATE). Each operator processes the change locally: JOIN produces the incremental join contribution; GROUP BY updates the affected aggregate. The final operator's output is stored as the materialized view. Work is proportional to the change, not to the total input size — an INSERT that adds one order row causes one update to one region's aggregate, taking microseconds, regardless of whether the orders table has 1M or 1B rows. Point queries against the view are O(1) key lookups.Input changes represented as (row, +1) for INSERT and (row, -1) for DELETE; UPDATE is (old, -1) then (new, +1). This algebra makes every operator's response to changes uniform: apply the delta to the local state, propagate the resulting delta downstream. Frank McSherry's differential dataflow (2013) is the theoretical foundation.
SQL query compiles to a DAG of operators. JOIN, GROUP BY, FILTER, PROJECT, UNION are all differential operators — each knows how to translate an input delta into an output delta. The graph is fixed at query-registration time; deltas flow through it continuously as inputs change.
Some operators (JOIN, GROUP BY) maintain internal state to handle deltas correctly. The JOIN operator maintains both input relations indexed by join key so that a new left-row can be matched against all existing right-rows. State is checkpointed to durable storage — the exact discipline from M.41.
The final output of the dataflow graph is stored as a queryable relation. SELECT * FROM view is a point read against the materialized state — microseconds, regardless of the underlying query's complexity. This is the property that makes streaming SQL viable for user-facing dashboards.
The work-proportional-to-change property is the specific reason streaming SQL platforms can maintain complex queries continuously without falling behind. A traditional database materialized view refresh recomputes the whole query — an ANALYZE command over billions of rows. An IVM-maintained view processes only the rows that changed since last update. For workloads where input change rate is much smaller than total data size (which is nearly every production workload), the two are orders of magnitude apart in cost. A view that would take 20 minutes to refresh in Postgres via REFRESH MATERIALIZED VIEW is maintained continuously in Materialize with sub-second update latency and no scheduled refresh at all. The tradeoff is that you commit to specific queries at view-definition time — arbitrary ad-hoc queries aren't supported in the same way; only the queries you've defined as views get the IVM treatment.
The connection to Flink and M.41 is worth naming. Flink itself supports streaming SQL — Flink SQL — using its own stream-processing engine as the substrate. The mechanism is similar (SQL compiles to a dataflow graph; state is checkpointed via the same discipline from M.41), but Flink's model is "process streams and emit to sinks" rather than "maintain a queryable materialized view." Materialize's specific innovation was making the maintained view itself the primary interface: your applications query the view directly (via a Postgres-compatible SQL interface), rather than receiving events emitted by the streaming system. This shifts the mental model from "stream processing that also happens to query" to "database that also happens to stream." Both work; the choice depends on whether your consumers want to poll a query or subscribe to a change stream. Materialize won the dashboard use case; Flink SQL is more commonly used when the output feeds another streaming system.
Below: each of the three canonical real-time analytics systems (Materialize · Pinot · Druid) evaluated against three different workload profiles (complex SQL joins · high-cardinality dashboards · time-series aggregations). Watch the diagonal — each system has one workload it was built for, and one it will actively fight you on. The 9 combinations illustrate why "which real-time analytics system?" is a decision about query shape, not vendor preference.
The failure modes of real-time analytics platforms are especially treacherous because the platform silently degrades before it visibly breaks. Queries return results that are ~30 seconds stale instead of ~500ms; ingestion falls behind by minutes without an alert; the dashboard shows numbers that seem plausible but aren't correct. Diagnosing these requires understanding the specific failure modes each family has — and building the monitoring discipline that catches them before users do.
Postgres was designed for OLTP: row-oriented storage, B-tree indexes for point queries, MVCC for concurrent transactions. These properties are actively bad for analytics: analytical queries do full-scans that saturate the buffer pool, evicting OLTP working set; complex GROUP BYs hold long transactions that block VACUUM and cause table bloat; dashboards put steady read load on the primary. The fix: separate the analytics workload onto a dedicated system. For real-time needs, that's a streaming pipeline (M.42 CDC → Kafka → Materialize/Pinot/Druid); for batch needs, an EL pipeline to Snowflake/BigQuery. Every serious data platform migrates off Postgres-as-analytics before scaling — the only question is whether you migrate proactively or after an outage.
Real-time analytics platforms process events as they arrive, which means late-arriving events (M.40's watermark territory) modify historical aggregates. A revenue event that gets stuck in a client-side buffer and arrives 24 hours late will retroactively update the aggregate for its actual event-time hour. This is correct behavior — the aggregate should include all events — but it's confusing to users who see "yesterday's revenue" change today. The fix: (a) allowed-lateness discipline (M.40) — configure the view to reject events beyond N hours late; (b) sealed-window semantics — mark historical windows as immutable after a grace period; (c) UI communication — show "revenue as of <timestamp> · may include late events" instead of a static number. Late-arriving data is a real-time-analytics-specific application of M.40's watermark and lateness discipline.
Real-time OLAP systems pre-compute indices for each dimension; high-cardinality dimensions (user_id, session_id, request_id — millions to billions of unique values) blow up index size and merge cost. Druid's dictionary encoding, which is efficient for low-cardinality dimensions, becomes memory-prohibitive for high-cardinality ones. The fix: (a) don't put high-cardinality columns as dimensions — treat them as metric filters instead; (b) if you need per-user aggregation, pre-aggregate upstream (Flink can do this) and store per-user only for hot users, using cardinality-aware storage (HyperLogLog for count-distinct, T-Digest for percentiles); (c) or move that specific workload to Materialize/streaming-SQL, which handles high-cardinality group-by more gracefully via IVM (proportional to change, not to cardinality). Cardinality is a first-class OLAP tuning knob — treating it as an afterthought is anti-pattern §05.iii's specific failure mode.
Real-time OLAP platforms are fast because they've done the aggregation work in advance — rollups at ingest time, pre-computed indices, star trees for common query patterns. When teams don't invest in this pre-aggregation strategy, queries scan raw events at query time; cost scales linearly with data volume, not with query complexity. The solution isn't more hardware; it's understanding which queries are hot and building appropriate rollups and indices for them. The fix: (a) profile query patterns — the top 10 queries are usually 90% of load; (b) design ingestion rollups for those specific patterns (Druid's granularity: HOUR pre-aggregates events per hour at ingest); (c) build star trees or bloom filters (Pinot) for common filter dimensions; (d) accept that some ad-hoc queries will be slow, and route them to a separate cluster or a batch warehouse. Pre-aggregation is a design decision, not an accident; failing to design it is what makes clusters grow indefinitely.
Real-time analytics platforms are shared infrastructure — one bad query can affect every dashboard. Without query-time governance, a single unbounded query, cross-join, or high-cardinality group-by can consume the whole cluster's resources. The failure mode is that queries which look reasonable in isolation are catastrophic in aggregate — no filter on a time column means scanning years of data; no LIMIT means returning millions of rows; joins without indexed keys mean CROSS JOINs. The fix: (a) query cost limits at the platform level (max scan bytes, max rows returned, max execution time); (b) mandatory query review for new dashboards and BI reports; (c) per-user or per-team quotas so one team can't monopolize; (d) query classification (interactive vs batch) with different resource pools. Query governance is the operational discipline that keeps real-time analytics platforms operationally viable as user counts grow — every mature deployment builds it in as a hard requirement, not as a "we'll add it later."
The composite pattern across all five is that real-time analytics operational discipline is different from batch analytics discipline. Batch systems (Snowflake, BigQuery) have well-understood cost models — per-query billing, autoscaling, mature query optimizers. Real-time analytics platforms are cluster-based (you provision N nodes), workload-sensitive (one bad query hurts the whole cluster), and design-dependent (pre-aggregation strategies determine performance). Mature real-time analytics deployments invest in ingestion design, query governance, and workload isolation from day one; deployments that skip these three discover their absence through outages.
Phase H ends here. The five modules — M.39 (batch vs stream), M.40 (windowing), M.41 (stateful streams), M.42 (CDC), M.43 (real-time analytics) — cover the end-to-end streaming discipline: how to think about latency, how to handle event time, how to persist state, how to source from operational databases, how to make the resulting streams user-facing. The composite skill is what mature data platform engineers spend years building. Every module you've completed is a specific piece of that skill; the Phase H completion banner below marks the composite.
The terms that show up in every "should we use Materialize or Pinot?" architecture review, every "why is our Druid query slow?" postmortem, every "should we pre-aggregate at ingest?" design doc.
CREATE MATERIALIZED VIEW ... AS SELECT ...; the system maintains the result incrementally as CDC inputs change. Point queries against the view return in microseconds. Founded 2019, based on differential dataflow.granularity: HOUR config means events within the same hour get aggregated on ingest — one row per hour instead of one per event. Trades detail (can't see individual events) for query speed (drastically less data to scan). Anti-pattern §05.iv is the failure to design this.Test the vocabulary. Click an answer; explanation drops in instantly.
Perfect. Incremental view maintenance, columnar OLAP, rollups, star trees, late-arriving data — the vocabulary that makes real-time analytics deployments viable at multi-year scale. Phase H is closed; Phase I (capstones) is next.
The discipline that makes real-time analytics platforms trustworthy at production scale.
Streaming SQL (Materialize, RisingWave) maintains query results incrementally via differential dataflow — you write SELECT, the system keeps it up-to-date. Real-time OLAP (Pinot, Druid) does columnar storage with heavy pre-aggregation and streaming ingestion. Different intellectual traditions, different query-shape strengths. Picking the wrong family means fighting the system's grain.
Incremental view maintenance is what makes streaming SQL scale. An input change affecting one row of output causes exactly one row of output to update — regardless of underlying data size. Materialize maintains complex JOIN + GROUP BY views in microseconds after each input delta. Traditional materialized-view refreshes were periodic and expensive; IVM makes maintenance continuous and cheap.
Real-time analytics platforms are cluster-based, workload-sensitive, and design-dependent. Pre-aggregation strategies, query governance, cardinality management, workload isolation — these are day-one requirements, not "we'll add them later." Mature deployments invest in them from the start; skipping them produces the anti-pattern failure modes in §05.
Five modules on streams and data pipelines: batching latency, event-time semantics, stateful processing, sourcing from operational databases, real-time analytics. The composite is the modern streaming discipline — enough to design, build, and operate a production streaming platform end-to-end.
The latency spectrum. When to use which.
Tumbling, sliding, session. Event time, watermarks, lateness.
Checkpoints, barriers, state backends, exactly-once.
Streams sourced from operational databases. Debezium, WAL, replication slots.
The last hop. Streaming SQL vs real-time OLAP. Dashboards.
Phase H's arc is a single narrative. M.39 established that streaming is a latency band, not a technology — you use it when the latency requirement makes batch unviable. M.40 established that event-time semantics are non-negotiable at scale — clocks disagree, events arrive late, and pretending otherwise produces silently-wrong results. M.41 established that operational state has to be checkpointed properly if the pipeline is going to survive real failures — Chandy-Lamport's algorithm, adapted for streaming, is what makes exactly-once semantics possible. M.42 established that streams have to come from somewhere — usually a relational database, and log-based CDC via Debezium is the mechanism that satisfies the four requirements (complete, ordered, low-impact, recoverable). M.43 established that the streams have to end somewhere too — either as continuously-maintained materialized views (Materialize) or as columnar segments queryable at sub-second latencies (Pinot, Druid).
The composite pipeline — Postgres → Debezium → Kafka → Flink → Materialize/Pinot/Druid → dashboards — is the modern streaming data platform. Every hop is specialized; every hop has its own operational discipline; the composition is what makes it viable. Teams that get one hop wrong (skimping on CDC, ignoring watermarks, using the wrong state backend, choosing the wrong analytics family) build fragile pipelines that break in ways that are hard to diagnose. Teams that build all five hops with the discipline from Phase H build streaming platforms that survive years of operational reality.
This is the mainstream production shape as of 2026, and understanding each hop deeply is what senior data platform engineering looks like today.