Definition
- Flink SQL – compiles SQL queries to a continuously running dataflow; processes events individually with native event-time, watermarks, and changelog semantics (retract/upsert streams).
- Spark SQL streaming – runs SQL as incremental micro-batches on a trigger interval; unified with batch SQL but adds the interval as a latency floor.
- Changelog – Flink natively models inserts, updates, and deletes; Spark uses append, complete, or update output modes without native retract.
- Trade-offs – Flink wins on latency and changelog semantics; Spark wins on ecosystem unification and operational simplicity.
Processing transactions at the National Bank: Flink SQL is a teller who handles each transaction instantly and tracks running account balances that update in real time, including corrections and reversals. Spark SQL streaming is a teller who processes transaction batches every second and publishes snapshots, which is simpler but does not naturally model mid-batch corrections as first-class events.
Understanding the Concepts
What is the fundamental difference between Flink SQL and Spark SQL for streaming?
Flink SQL compiles queries to a continuously running dataflow graph. Each event flows through operators as it arrives, with no batch boundary. Spark SQL streaming treats the stream as an unbounded table and re-runs an incremental query every trigger interval.
-- Flink SQL: continuous query, processes event-by-event
CREATE TABLE transactions (
txn_id STRING,
account_id STRING,
amount DECIMAL(18,2),
txn_time TIMESTAMP(3),
WATERMARK FOR txn_time AS txn_time - INTERVAL '10' SECOND
) WITH (
'connector' = 'kafka',
'topic' = 'nbi.transactions'
);
SELECT account_id, SUM(amount) AS total
FROM transactions
GROUP BY account_id;
-- This runs CONTINUOUSLY. Each new event updates the aggregate.
-- Results are emitted as changelog (retract + insert).
# Spark SQL streaming: micro-batch query
from pyspark.sql import SparkSession, functions as F
spark = SparkSession.builder.appName("NBI-StreamSQL").getOrCreate()
txn_stream = spark.readStream.format("kafka") \
.option("subscribe", "nbi.transactions").load()
txn_stream.createOrReplaceTempView("transactions")
result = spark.sql("""
SELECT account_id, SUM(amount) AS total
FROM transactions
GROUP BY account_id
""")
result.writeStream \
.trigger(processingTime="1 second") \
.outputMode("update") \
.format("console") \
.start()
# Runs every 1 second as a micro-batch.
How do changelog semantics differ?
Flink natively represents updates as retract streams (retract old value, insert new value) and upsert streams (keyed by primary key). This makes CDC ingestion and update-heavy aggregations first-class. Spark uses output modes: append (new rows only), complete (full result each batch), or update (changed rows).
-- Flink: native changelog for CDC from a database
CREATE TABLE accounts (
account_id STRING,
balance DECIMAL(18,2),
PRIMARY KEY (account_id) NOT ENFORCED
) WITH (
'connector' = 'debezium-json',
'topic' = 'nbi.accounts.cdc'
);
-- Flink understands INSERT, UPDATE, DELETE from the CDC stream.
-- Downstream aggregations automatically handle retractions.
SELECT branch, SUM(balance) AS total_balance
FROM accounts
GROUP BY branch;
-- When a balance changes, Flink retracts the old sum and inserts the new one.
How does latency compare?
Flink SQL processes each event as it arrives: latency is the processing time per event, typically single-digit to tens of milliseconds. Spark SQL streaming adds the trigger interval as a latency floor.
| Engine | Latency | Batch boundary |
|---|---|---|
| Flink SQL | 1-50 ms | None (continuous) |
| Spark SQL (micro-batch) | 100 ms - seconds | Trigger interval |
| Spark SQL (continuous, experimental) | ~10 ms | Per-record (limited features) |
# Spark: minimum practical latency is the trigger interval
# trigger(processingTime="100 milliseconds") → 100ms+ latency
# trigger(processingTime="1 second") → 1s+ latency
# Flink: no trigger interval. Each event processed immediately.
How does state management compare in SQL?
Flink SQL's state is backed by pluggable state backends. For large state, RocksDB spills to disk. State has fine-grained TTL per key. Spark SQL streaming's state store is checkpointed to the file system per micro-batch.
-- Flink: state TTL for streaming aggregation
CREATE TABLE result WITH (
'state.ttl' = '24 h' -- expire state after 24 hours
) AS
SELECT account_id, COUNT(*) AS txn_count
FROM transactions
GROUP BY account_id;
# Spark: state cleanup via watermarks
result = txn_stream \
.withWatermark("txnTime", "24 hours") \
.groupBy("accountId") \
.count()
# State for keys with no events in 24 hours is cleaned up.
How does exactly-once compare?
Both achieve exactly-once, but through different mechanisms.
# Flink: async distributed checkpoints (Chandy-Lamport)
# + transactional sinks (Kafka two-phase commit, JDBC transactions)
# = end-to-end exactly-once with minimal latency impact
# Spark: checkpoint offsets per micro-batch
# + idempotent or transactional sinks
# = end-to-end exactly-once within the micro-batch model
What is Spark's ecosystem advantage for streaming SQL?
Spark SQL is the same for batch and streaming. You write one query and run it on static data or streaming data with minimal changes.
# BATCH: same SQL on static data
batch = spark.read.parquet("s3://nbi-data-lake/transactions/")
batch.createOrReplaceTempView("transactions")
result = spark.sql("SELECT branch, SUM(amount) FROM transactions GROUP BY branch")
# STREAMING: same SQL on live data
stream = spark.readStream.format("kafka") \
.option("subscribe", "nbi.transactions").load()
stream.createOrReplaceTempView("transactions")
result = spark.sql("SELECT branch, SUM(amount) FROM transactions GROUP BY branch")
# One SQL, one engine, batch or stream
When should you choose Flink SQL vs Spark SQL for streaming?
| Requirement | Choose |
|---|---|
| Latency < 50 ms | Flink SQL |
| Native CDC / changelog semantics | Flink SQL |
| Complex event processing with timers | Flink SQL |
| Unified batch + streaming SQL | Spark SQL |
| Team already on Spark / Databricks | Spark SQL |
| Latency of 1-5 seconds acceptable | Spark SQL |
| Delta Lake integration | Spark SQL |
# At the bank:
# Fraud alerts (ms latency, CDC) → Flink SQL
# Dashboard rollups (1s latency, unified stack) → Spark SQL
What should a senior engineer know?
A senior engineer understands that the choice is a trade-off driven by requirements: latency, changelog semantics, state complexity, and ecosystem fit. They do not claim one is universally better.
# Senior decision: choose by requirements, not fashion
# Flink SQL: best-in-class streaming with native changelog
# Spark SQL: best-in-class unified ecosystem with micro-batch
# Using both at the same bank is common and sensible
Visual Overview
Flink SQL execution:
Kafka → [Continuous Dataflow] → Sink
Event-by-event
Changelog: +I, -U, +U, -D
State: RocksDB (large, durable)
Latency: milliseconds
Spark SQL streaming execution:
Kafka → [Micro-batch 1] → Process → Sink
Kafka → [Micro-batch 2] → Process → Sink
Trigger interval = latency floor
Output modes: append, update, complete
State: StateStore (checkpoint per batch)
Latency: 100ms - seconds
Changelog semantics:
Flink: Spark:
+I (insert account sum) Append mode: new rows only
-U (retract old sum) Update mode: changed rows
+U (insert new sum) Complete mode: full result
-D (delete on key removal)
Flink models updates natively. Spark approximates with output modes.
Key Takeaway
Flink SQL and Spark SQL streaming differ fundamentally in their execution model. Flink SQL compiles queries to a continuously running dataflow where each event is processed immediately, with native changelog semantics that model inserts, updates, and deletes as first-class retract and upsert streams. Spark SQL streaming runs micro-batches by default, treating the stream as an unbounded table with a trigger interval as the latency floor.
The trade-offs fall into four areas. On latency, Flink reaches single-digit milliseconds while Spark micro-batch is hundreds of milliseconds to seconds. On semantics, Flink's native changelog makes CDC ingestion and update-heavy aggregations natural, while Spark uses append, complete, and update output modes. On state, Flink offers pluggable backends with fine-grained TTL for large state. On ecosystem, Spark's great strength is one SQL API for both batch and streaming with a larger community and Delta Lake integration.
At the National Bank of India, we used Flink SQL for fraud detection where millisecond latency and native CDC from the accounts database were essential, and Spark SQL streaming for the management dashboard where one-second latency was fine and the team already used Spark for everything.
The senior perspective is that the choice is about requirements, not about one being universally better. Using both at the same bank is common and sensible.
Remember one sentence: Flink SQL is true continuous streaming with native changelog semantics, Spark SQL is micro-batch with a unified ecosystem, and you choose by latency and semantic requirements.
---