cancel
Showing results for 
Search instead for 
Did you mean: 
Community Platform Discussions
Connect with fellow community members to discuss general topics related to the Databricks platform, industry trends, and best practices. Share experiences, ask questions, and foster collaboration within the community.
cancel
Showing results for 
Search instead for 
Did you mean: 

Left join with merging two streaming tables returns inner join instead

Brammer88
New Contributor III

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

0 REPLIES 0

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group