cancel
Showing results for 
Search instead for 
Did you mean: 

Left join with merging two streaming tables returns inner join instead

Brammer88
New Contributor

Hi all, 

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
()
USING DELTA
AS

SELECT *
, current_timestamp() as CreatedOnUTC
FROM cloud_files(
'${pipeline.storage_path}/INVENTDIM'
, 'parquet'
, map("inferSchema", "true")
)


CREATE STREAMING LIVE VIEW vw_block
AS
SELECT *
FROM STREAM(LIVE.bronze_block)


CREATE STREAMING LIVE VIEW vw_inventdim
AS
SELECT *
FROM STREAM(LIVE.bronze_inventdim)
WATERMARK to_timestamp(CreatedOnUTC) AS wm_b DELAY OF INTERVAL 1 HOURS


CREATE OR REFRESH STREAMING LIVE TABLE tst_watermarks
USING DELTA
AS

SELECT
a.*
, b.INVENTBATCHID
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

1 REPLY 1

Kaniz
Community Manager
Community Manager

Hi @Brammer88In your code, you have applied the watermark on the vw_inventdim view but not on the vw_block view.

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.

Welcome to Databricks Community: Lets learn, network and celebrate together

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! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.