Im trying to merge two streaming tables together with a left (outer) join, but it seems to somehow return all values from the left table that have matching values from the right table, instead of also appending the NULL values if there is no match. If I "just" run the same queries outside of DLT functionality with normal temp views, it does give me the expected result (the same amount of rows of the left sided table bronze_block). Does anybody know what I am doing wrong, or why DLT are behaving the way they do? Here is my code example that I try to run with DLT.
Any help is much appreciated! I am kind of new to this, so I know that I will do something wrong, but I cannot really find good / complicated examples within the databricks documentation so I hope somebody can give me some pointers here. Thanks a lot!
Im running this on a Azure databricks DLT pipeline (Advanced Product edition), using a unity catalog destinationm, cluster policy set to non, 1 max worker with enhanced auto scaling.
CREATE OR REFRESH STREAMING LIVE TABLE bronze_inventdim
, current_timestamp() as CreatedOnUTC
, map("inferSchema", "true")
CREATE STREAMING LIVE VIEW vw_block
CREATE STREAMING LIVE VIEW vw_inventdim
WATERMARK to_timestamp(CreatedOnUTC) AS wm_b DELAY OF INTERVAL 1 HOURS
CREATE OR REFRESH STREAMING LIVE TABLE tst_watermarks
FROM STREAM(LIVE.vw_block) as a
LEFT OUTER JOIN STREAM(LIVE.vw_inventdim) as b ON
a.INVENTDIMID = b.INVENTDIMID
AND a.CreatedOnUTC BETWEEN b.wm_b - INTERVAL 1 HOURS AND b.wm_b + INTERVAL 1 HOURS
Hi @Brammer88, In your code, you have applied the watermark on the
vw_inventdim view but not on the
According to Databricks documentation, watermark and event-time constraints must be specified for left and right outer joins. This is because for generating the NULL results in outer join, the engine must know when an input row is not going to match with anything in the future.
Hence, the watermark + event-time constraints must be specified to generate correct results.
Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections.
Click here to register and join today!