10.5.2 – Streams vs Tables in ksqlDB

Module: Section 10 – Kafka Ecosystem & Tools
Section: 10.5 – ksqlDB Fundamentals
Goal: Understand the fundamental difference between streams and tables in ksqlDB

Introduction

The stream vs table duality is the core concept in ksqlDB. Mastering this distinction is essential for effective stream processing.

Why This Module Matters: Choosing the wrong abstraction (stream vs table) leads to incorrect results and poor performance.

Streams: Unbounded Event Log

Definition

Stream: Immutable, append-only sequence of events.

Characteristics:

• Unbounded (never-ending)
• Insert-only (no updates or deletes)
• Events are independent
• No primary key required

Analogy: Log file, clickstream, sensor readings

Example: User Clicks

CREATE STREAM user_clicks (
  click_id VARCHAR,
  user_id BIGINT,
  page VARCHAR,
  timestamp BIGINT
) WITH (
  KAFKA_TOPIC='clicks',
  VALUE_FORMAT='JSON'
);

Data:

click_id | user_id | page     | timestamp
---------|---------|----------|----------
c1       | 123     | home     | 10001
c2       | 456     | checkout | 10002
c3       | 123     | product  | 10003
c4       | 123     | checkout | 10004

Key Point: All events are independent. User 123 appears 3 times (3 separate clicks).

Querying Streams

-- All clicks (continuous)
SELECT * FROM user_clicks EMIT CHANGES;

-- Filter specific page
SELECT * FROM user_clicks 
WHERE page = 'checkout'
EMIT CHANGES;

-- Count clicks per user (windowed)
SELECT 
  user_id,
  COUNT(*) AS click_count
FROM user_clicks
WINDOW TUMBLING (SIZE 1 HOUR)
GROUP BY user_id
EMIT CHANGES;

Tables: Current State

Definition

Table: Mutable, changelog stream where latest value per key represents current state.

Characteristics:

• Bounded (current state only)
• Updates and deletes allowed
• One record per key (latest wins)
• Primary key required

Analogy: Database table, user profile, account balance

Example: User Profiles

CREATE TABLE users (
  user_id BIGINT PRIMARY KEY,
  username VARCHAR,
  email VARCHAR,
  created_at BIGINT
) WITH (
  KAFKA_TOPIC='users',
  VALUE_FORMAT='AVRO'
);

Data (Kafka topic messages):

Key: 123 → {"user_id": 123, "username": "alice", "email": "alice@example.com"}
Key: 456 → {"user_id": 456, "username": "bob", "email": "bob@example.com"}
Key: 123 → {"user_id": 123, "username": "alice", "email": "alice@newdomain.com"}

Current State (Table view):

user_id | username | email
--------|----------|----------------------
123     | alice    | alice@newdomain.com
456     | bob      | bob@example.com

Key Point: Only latest value for user 123 is visible.

Querying Tables

-- Current state for all users (pull query)
SELECT * FROM users;

-- Specific user (pull query)
SELECT username, email 
FROM users 
WHERE user_id = 123;

-- Continuous updates (push query)
SELECT * FROM users EMIT CHANGES;

Stream vs Table: Core Differences

Stream-Table Duality

Every Table is a Stream

Concept: A table is a materialized view of a stream.

Example: User profile updates.

Stream (changelog):

user_id | username | email                 | timestamp
--------|----------|-----------------------|----------
123     | alice    | alice@example.com     | 10001
456     | bob      | bob@example.com       | 10002
123     | alice    | alice@newdomain.com   | 10003

Table (current state at t=10003):

user_id | username | email
--------|----------|----------------------
123     | alice    | alice@newdomain.com
456     | bob      | bob@example.com

Every Stream Can Become a Table

Aggregating stream into table:

-- Stream
CREATE STREAM page_views (
  user_id BIGINT,
  page VARCHAR
) WITH (...);

-- Table (materialized aggregate)
CREATE TABLE user_page_counts AS
SELECT
  user_id,
  COUNT(*) AS total_views
FROM page_views
GROUP BY user_id
EMIT CHANGES;

Result: Table with latest count per user.

Use Case Patterns

Pattern 1: Stream for Events

Use Case: Track all user actions.

CREATE STREAM user_actions (
  action_id VARCHAR,
  user_id BIGINT,
  action_type VARCHAR,
  timestamp BIGINT
) WITH (...);

Why Stream?: Need complete history of all actions.

Pattern 2: Table for Current State

Use Case: User profiles, product catalog.

CREATE TABLE products (
  product_id VARCHAR PRIMARY KEY,
  name VARCHAR,
  price DOUBLE,
  stock INT
) WITH (...);

Why Table?: Only care about current price/stock.

Pattern 3: Stream + Table for Enrichment

Use Case: Enrich orders with user information.

-- Stream: Order events
CREATE STREAM orders (
  order_id VARCHAR,
  user_id BIGINT,
  amount DOUBLE
) WITH (...);

-- Table: User profiles
CREATE TABLE users (
  user_id BIGINT PRIMARY KEY,
  username VARCHAR,
  tier VARCHAR
) WITH (...);

-- Enriched stream
CREATE STREAM enriched_orders AS
SELECT
  o.order_id,
  o.amount,
  u.username,
  u.tier
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
EMIT CHANGES;

Why?: Stream for events, table for lookups.

Converting Between Streams and Tables

Stream → Table

Aggregate stream into table:

CREATE TABLE hourly_sales AS
SELECT
  TIMESTAMPTOSTRING(WINDOWSTART, 'yyyy-MM-dd HH:mm') AS hour,
  SUM(amount) AS total_sales
FROM orders
WINDOW TUMBLING (SIZE 1 HOUR)
GROUP BY 1
EMIT CHANGES;

Table → Stream

Convert table changes to stream:

CREATE STREAM user_changes
WITH (KAFKA_TOPIC='users-changelog', VALUE_FORMAT='AVRO') AS
SELECT * FROM users EMIT CHANGES;

Result: Stream of all user profile changes.

Push vs Pull Queries

Push Queries (Streams & Tables)

Continuous, never-ending:

-- Stream
SELECT * FROM user_clicks EMIT CHANGES;

-- Table
SELECT * FROM users EMIT CHANGES;

Use Case: Real-time dashboards, alerting.

Pull Queries (Tables Only)

Point-in-time snapshot:

-- Get current user
SELECT username, email 
FROM users 
WHERE user_id = 123;

-- Get current total sales
SELECT total_sales 
FROM hourly_sales 
WHERE hour = '2025-02-01 14:00';

Use Case: REST API lookups, serving layer.

Real-World Example

Scenario: E-Commerce Order Processing

Stream: Order Events

CREATE STREAM orders (
  order_id VARCHAR,
  user_id BIGINT,
  product_id VARCHAR,
  quantity INT,
  amount DOUBLE,
  timestamp BIGINT
) WITH (...);

Table: User Profiles

CREATE TABLE users (
  user_id BIGINT PRIMARY KEY,
  username VARCHAR,
  email VARCHAR,
  tier VARCHAR
) WITH (...);

Table: Product Inventory

CREATE TABLE products (
  product_id VARCHAR PRIMARY KEY,
  name VARCHAR,
  price DOUBLE,
  stock INT
) WITH (...);

Enriched Orders (Stream)

CREATE STREAM enriched_orders AS
SELECT
  o.order_id,
  o.quantity,
  o.amount,
  u.username,
  u.tier,
  p.name AS product_name,
  p.price
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
LEFT JOIN products p ON o.product_id = p.product_id
EMIT CHANGES;

Daily Sales (Table)

CREATE TABLE daily_sales AS
SELECT
  TIMESTAMPTOSTRING(WINDOWSTART, 'yyyy-MM-dd') AS date,
  SUM(amount) AS total_revenue,
  COUNT(*) AS order_count
FROM orders
WINDOW TUMBLING (SIZE 1 DAY)
GROUP BY 1
EMIT CHANGES;

Key Takeaways

1. Stream: Immutable event log (insert-only, all history).

2. Table: Mutable current state (latest value per key).

3. Stream-Table Duality: Every table is a stream, every stream can become a table.

4. Use Streams For: Events, logs, clickstreams, transactions.

5. Use Tables For: Current state, profiles, inventory, balances.

6. Push Queries: Continuous (EMIT CHANGES), works for both streams and tables.

7. Pull Queries: Point-in-time snapshot, works for tables only.

8. Enrichment Pattern: Stream (events) + Table (lookups) = Enriched stream.

9. Aggregation: Stream → Table (via GROUP BY).

10. Changelog: Table → Stream (via EMIT CHANGES).

What's Next?

Next Module: 10.5.3 – ksqlDB Architecture

What We Covered:

• ✅ Streams vs tables
• ✅ Stream-table duality
• ✅ Push vs pull queries
• ✅ Use case patterns

What's Coming:

• ❌ ksqlDB internal architecture
• ❌ State stores and processing

References:

• Introduction → 10.5.1: Introduction to ksqlDB
• Creating streams/tables → 10.6.1: Creating Streams & Tables
• Joins → 10.7: Joins & Enrichment