databricks sql watermark syntax

Question

Need some help regarding watermark syntax with DLT sql pipeline setup.

Wanted to load combined data from 2 silver layer steaming table into a single table with watermarking so it can capture late updates but having some syntax error.

SQL query to get combined data

CREATE OR REFRESH STREAMING LIVE TABLE  target_table
AS 
SELECT 
*
FROM STREAM(LIVE.silver_table1)   a
LEFT JOIN STREAM (LIVE.silver_table2) b
      ON a.Id=b.Id

But when try to add watermark logic then getting ParseException error

Syntax used for 1 of the table

FROM STREAM(LIVE.silver_table1)  WATERMARK LastUpdateDateTime DELAY OF INTERVAL 60 SECONDS

Error

Error in SQL statement: ParseException: 
[PARSE_SYNTAX_ERROR] Syntax error at or near 'LastUpdateDateTime'(line 22, pos 54)

Answer 1

The issue is with the placement of the WATERMARK logic in your SQL statement.

Usually, the syntax for using WATERMARK with a streaming source in SQL depends on the database system. But the general format is

FROM STREAM (stream_name) WATERMARK watermark_column_name <DELAY OF> <delay_interval>

Try this.

Syntax:

%sql
CREATE OR REFRESH STREAMING LIVE TABLE target_table
AS
SELECT *
FROM STREAM(LIVE.silver_table1)
WATERMARK LastUpdateDateTime DELAY OF INTERVAL 60 SECONDS a
LEFT JOIN STREAM (LIVE.silver_table2) b ON a.Id = b.Id;