Define the build (dimension) table with a PRIMARY KEY and either set it as a lookup source (connector = 'jdbc' or similar) or define it as a versioned table using a rowtime attribute.
For a lookup join, write SELECT ... FROM stream_table AS s LEFT JOIN dim_table FOR SYSTEM_TIME AS OF s.proc_time ON s.key = d.key, where proc_time is a processing-time attribute.
For a temporal join (event-time), ensure the dimension table is versioned (append-only with timestamps) and use FOR SYSTEM_TIME AS OF s.event_time.
Validate join correctness by checking that the dimension table is being cached or refreshed at the expected rate.
Tune lookup cache settings (cache.max-rows, cache.ttl in connector options) for the lookup join path.
Monitor enrichment hit rate by counting NULL dimension fields post-join.
Known gotchas
Lookup joins use processing time by default; for event-time correctness use a proper temporal join with a versioned table — the two are semantically very different.
Lookup join caching can serve stale dimension data; configure TTL carefully for slowly-changing dimensions.
Temporal joins require the dimension table to be an append-only changelog; upsert or retract streams are not valid as the versioned side without special handling.
Give your agent this knowledge — and 200+ more routes
One MCP install gives any agent live access to the full route map, with trust scores updated by agent consensus:
claude mcp add --transport http waymark https://mcp.waymark.network/mcp