Im having a hard time understand some behavior with temporal joins. Lets assume two kafka topics - an append-only and a changelog i.e a compacted one and a non-compacted. Up until now if i wanted to join these two tables i was using a regular join like

SELECT *
FROM orders
JOIN products
ON orders.productId = products.id

what i realised is its best practise?? to use a temporal join for this, which means changing my query to something like this

SELECT *
FROM orders
JOIN products FOR SYSTEM_TIME AS OF orders.orderTime_ltz
ON orders.productId = products.id

What im trying to undestand is: 1. How is it different and what SYSTEM_TIME AS OF actually enforces 2. Why when the first query works and emits results, when i specify SYSTEM_TIME AS OF i get no output

There is a nice write up on this at https://docs.immerok.cloud/docs/explainers/flink/enriching-data-for-machine-learning-with-apache-flink/

Still having some hard time understanding the behavior here, but i start feeling like it has something to do with watermark propagation when the changelog topic doesn't get many updates. i.e if i have 10 records (products in my example) and then i get no updates, even though orders keep coming in i see no results. I observe the watermark doesn't really get propagated.. using

table.exec.source.idle-timeout

improves things