Stream-Static & Stream-Stream Joins

Apache Spark · Structured Streaming

Theory

A stream-static join enriches each streaming row with a static table, such as adding customer city to every event. It is stateless: each micro-batch joins its new rows with the static side, and if the static side is a table format like Delta, newer versions are picked up in later batches. Inner and left outer joins (stream on the left) are supported; a full outer join is not.

A stream-stream join matches rows from two streams, for example orders with their payments. Because a match can arrive later on either side, Spark buffers past rows of both streams in state. To keep that state bounded you add a watermark on both sides and a time condition in the join, such as payment_ts BETWEEN order_ts AND order_ts + INTERVAL 15 MINUTES. Inner joins work without these (with unbounded state); left/right outer and semi joins require them, because Spark must know when a row will never match before emitting it with nulls.

Why we use it

Most streaming pipelines need context from other data: reference data for enrichment, or a second event stream to correlate (order vs payment, impression vs click). Knowing which join type is supported and how state is bounded avoids failed queries and runaway memory.

When to use it

Use stream-static for enrichment with dimension tables, broadcasting the static side if it is small. Use stream-stream joins to correlate two event streams within a time range, always with watermarks and a time bound. If the other side changes rarely, a stream-static join is far cheaper.

How it works

  1. Streaming rows need data from another source
  2. Is the other side a table that changes slowly? Yes: Stream-static join (stateless). No: Stream-stream join.
  3. Add withWatermark on both streams
  4. Add a time-range condition to the join
  5. Spark buffers rows in state and drops those too old to match
  6. Joined rows are emitted as matches are found

Syntax

enriched = events_stream.join(customers, events_stream.user_id == customers.customer_id, "left")
o = orders_s.withWatermark("order_ts", "10 minutes")
p = pays_s.withWatermark("pay_ts", "10 minutes")
o.join(p, F.expr("o_id = p_order_id AND pay_ts BETWEEN order_ts AND order_ts + INTERVAL 15 MINUTES"))

Example

from pyspark.sql import SparkSession, functions as F

spark = SparkSession.builder.appName("techtown-spark").getOrCreate()

customers = spark.createDataFrame([
    (101, "Asha",  "Jaipur", "Retail"),
    (102, "Ravi",  "Pune",   "Corporate"),
    (103, "Meera", "Delhi",  "Retail"),
    (104, "Karan", "Jaipur", "SMB"),
    (105, "Priya", "Mumbai", "Corporate"),
], "customer_id INT, name STRING, city STRING, segment STRING")

import tempfile, os, json

base = tempfile.mkdtemp()                     # a real job would use cloud storage paths
src = os.path.join(base, "events_in"); os.makedirs(src)

def drop_file(name, rows):                    # simulate a new file landing in the folder
    with open(os.path.join(src, name), "w") as f:
        for r in rows:
            f.write(json.dumps(dict(zip(["user_id", "event_type", "event_ts"], r))) + "\n")

schema = "user_id INT, event_type STRING, event_ts TIMESTAMP"   # streams need a schema up front

drop_file("e1.json", [(101, "login", "2026-01-05 09:00:00"), (103, "purchase", "2026-01-05 09:07:00"),
                      (109, "login", "2026-01-05 09:08:00")])          # 109 is not a known customer

# 1. Stream-static: enrich events with the customers table (left join keeps unknown users)
events = spark.readStream.schema(schema).json(src)
enriched = (events.join(customers, events.user_id == customers.customer_id, "left")
                  .select("user_id", "event_type", "name", "city"))
q = (enriched.writeStream.format("memory").queryName("enriched_events")
        .option("checkpointLocation", os.path.join(base, "chk_ss"))
        .trigger(availableNow=True).start())
q.awaitTermination()
spark.sql("SELECT * FROM enriched_events ORDER BY user_id").show()

# 2. Stream-stream: orders with payments made within 15 minutes
o_dir, p_dir = os.path.join(base, "orders_in"), os.path.join(base, "payments_in")
spark.createDataFrame([(1, 101, "2026-01-05 09:00:00"), (2, 102, "2026-01-05 09:05:00")],
                      "order_id INT, customer_id INT, order_ts STRING").write.json(o_dir)
spark.createDataFrame([(1, 55000.0, "2026-01-05 09:06:00"), (2, 30000.0, "2026-01-05 09:40:00")],
                      "pay_order_id INT, paid DOUBLE, pay_ts STRING").write.json(p_dir)

orders_s = (spark.readStream.schema("order_id INT, customer_id INT, order_ts TIMESTAMP").json(o_dir)
                 .withWatermark("order_ts", "10 minutes"))
pays_s = (spark.readStream.schema("pay_order_id INT, paid DOUBLE, pay_ts TIMESTAMP").json(p_dir)
               .withWatermark("pay_ts", "10 minutes"))
matched = orders_s.join(pays_s, F.expr(
    "order_id = pay_order_id AND pay_ts BETWEEN order_ts AND order_ts + INTERVAL 15 MINUTES"))
q2 = (matched.writeStream.format("memory").queryName("paid_orders")
         .option("checkpointLocation", os.path.join(base, "chk_join"))
         .trigger(availableNow=True).start())
q2.awaitTermination()
spark.sql("SELECT order_id, customer_id, paid, order_ts, pay_ts FROM paid_orders").show()
# order 2 was paid after 35 minutes, outside the 15-minute bound, so it does not match

# Output:
# +-------+----------+-----+------+
# |user_id|event_type| name|  city|
# +-------+----------+-----+------+
# |    101|     login| Asha|Jaipur|
# |    103|  purchase|Meera| Delhi|
# |    109|     login| NULL|  NULL|
# +-------+----------+-----+------+
#
# +--------+-----------+-------+-------------------+-------------------+
# |order_id|customer_id|   paid|           order_ts|             pay_ts|
# +--------+-----------+-------+-------------------+-------------------+
# |       1|        101|55000.0|2026-01-05 09:00:00|2026-01-05 09:06:00|
# +--------+-----------+-------+-------------------+-------------------+

Key points

Apache Spark topics

Spark Fundamentals

Spark Architecture

RDDs

DataFrames

Aggregations & Joins

Spark SQL

Window Functions

Cleaning & Complex Types

UDFs

Performance Tuning

Structured Streaming

MLlib & Ecosystem

Interview Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Apache Spark

PySpark and Spark SQL from the ground up: DataFrames, joins, window functions, partitions and performance tuning, Structured Streaming and more. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice PySpark Write real PySpark — DataFrames, joins, window functions and Spark SQL — in a live editor with hidden tests and instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Spark  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Try the same API on Practice PySpark Online right in your browser, or run it locally after pip install pyspark (needs Java 17+), or paste it into a Databricks Free Edition notebook.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic