azureunix-timestampepochwatermarkincremental-load

Azure Delta Load won't recognize Epoch timestamp (ms) as Watermark Column Name

I am trying to perform a delta load (incremental load) with Azure Data Factory from SQL Server to Blob Storage. My tables have an updateStamp column that is Epoch Time in milliseconds, numeric(19,0) data type. When I look to select the Watermark column name in the configuration section of the Copy Data tool in ADF, it is not one of the options and it does not let me manually enter the column name. It looks like it only wants a datetime data type or key integer data type. I have tried with the Metadata-driven copy task and the Delta copy from Database template with no luck. Is there a work around or way of converting the max and using that (instead of adding another column to hundreds of millions of rows). Any help or guidance is appreciated.

I'm expecting to be able to use a data type that indicates a point in time as the watermark for an incremental load, even though that data type is not datetime.

Solution

  • I have tried to repro this in my environment using Delta copy from a database template in adf for the watermark column with epoch timestamp type. Below are the steps.

    enter image description here

    Create PROCEDURE update_watermark @LastModifyDate numeric(19,0)
AS
BEGIN
    UPDATE watermarktable
    SET [WatermarkValue] = @LastModifyDate
END

    enter image description here

    enter image description here

    Sink File:

    enter image description here

    enter image description here

    Delta records are copied to sink when epoch timestamp is given as watermark column.

    Reference: MS doc on Delta copy from a database template.