Description

Stream processing looks deceptively approachable right up until you try to join two live data streams. Unlike static database tables, streaming data arrives continuously, out of order, and never quite "done" — and that fundamental reality exposes the limits of traditional SQL thinking fast. This episode of Automatic digs into the engineering challenges behind real-time joins, why they trip up even experienced teams, and what a disciplined approach to building them actually looks like in production.

The episode covers the full arc of designing, operating, and surviving real-time joins at scale, including:

• Why SQL struggles with streams — static query planners were built for complete, bounded datasets, not infinite flows with late-arriving and out-of-order events.
• Keys, windows, and watermarks — the three core decisions that define the behavior of any real-time join, and why getting each one wrong has outsized downstream consequences.
• Streaming vs. microbatch trade-offs — how each model handles state and windowing differently, and why understanding which one you're operating in changes your failure modes entirely.
• State management and data skew — the hidden operational costs of holding partial matches in memory, how hot join keys can silently degrade performance, and practical remedies like salted partitioning.
• When to skip the live join altogether — making the case for precomputed, denormalized views when relationships are stable, and reserving real-time joins for truly time-sensitive logic.
• Observability, governance, and on-call culture — why real-time systems demand metrics that tell a story, privacy-conscious key design, idempotent outputs, and an engineering mindset comfortable reasoning in windows and watermarks.

The episode also tackles the less glamorous but critical operational details: throttling backfills to avoid collisions with live traffic, schema validation at ingress, versioned topics, and the importance of testing restore procedures against realistic data cardinality rather than toy examples. The recurring theme is that real-time joins reward deliberate, unglamorous engineering far more than clever tricks.

For more from the show, check out LLMs Behind Closed Doors: Building Secure, In-House AI Models, which explores another high-stakes infrastructure challenge — keeping powerful AI models inside your own walls.

Automatic