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
