Query delta lake from Snowflake to read latest version

user_2198720 0
Viewed 211

I want to load data from a Delta Table on Databricks into a table on Snowflake using the MERGE INTO command.

The goal is that the numbers of records that are at the Delta Table on Databricks look the same as the records at the table on Snowflake.

The problem that is happening is that Snowflake queries duplicate records due to the fact that Delta Lake(S3 path) has multiple versions.

What can I do to read only the latest version of the delta lake?

MERGE INTO myTable as target USING (
    SELECT
    $1:DAY::TEXT AS DAY,
    $1:CHANNEL_CATEGORY::TEXT AS CHANNEL_CATEGORY,
    $1:SOURCE::TEXT AS SOURCE,
    $1:PLATFORM::TEXT AS PLATFROM,
    $1:LOB::TEXT AS LOB
    FROM @StageFilePathDeltaLake
    (FILE_FORMAT => 'sf_parquet_format')
  ) as src 
        ON target.CHANNEL_CATEGORY = src.CHANNEL_CATEGORY 
        AND target.SOURCE = src.SOURCE 
WHEN MATCHED THEN
UPDATE SET 
 DAY= src.DAY
,PLATFORM= src.PLATFORM
,LOB= src.LOB
WHEN NOT MATCHED THEN
INSERT  (
      DAY,
      CHANNEL_CATEGORY,
      SOURCE,
      PLATFORM,
      LOB
) VALUES  (
  src.DAY,
  src.CHANNEL_CATEGORY,
  src.SOURCE,
  src.PLATFORM,
  src.LOB
);

The sf_parque_format was created with these details:

create or replace file format sf_parquet_format
    type = 'parquet'
    compression = auto;
delta-lake snowflake-cloud-data-platform
2 Answers
user_109704780

The problem above is that Snowflake is reading this as a Parquet file, and not as Delta.

The solution is easy, be explicit about its Delta nature: Create an external table using the above stage and table_format = delta and query from external table instead of querying directly from the stage.

create external table …
 location=@mystage/daily/
 refresh_on_create =  false
 auto_refresh = false
 file_format = (type = parquet)
 table_format = delta; -- this one

As an additional note from my teammates: If you want to see the latest data (after creating the external table if Delta Lake was updated), you will need to perform an ALTER EXTERNAL TABLE {name} REFRESH. Also you can create an insert only stream on top of the external table, but would only get populated after a REFRESH occurs.

user_1324380
Related