Keep daily snapshot from source tables

Dremio University
1

Hi,

I’m facing a challenges , as the source database table just keep the current image, and we plan to automate a daily job in dremio to keep track of daily image , plan to use CTAS to export daily snapshot into PDS, and then use MERGE INTO to keep track of the CDC data change by comparing current and previous image , however seems PDS not allow run the DML (insert, update, delete) . pls suggest any alternative way to solve the issue, can i convert source data into iceberg table and do the SCD? Many Thanks in advance

2

@crazyisjen

Few questions,

  • What version of Dremio is this?
  • Is the CTAS format Iceberg?
  • If the CTAS format is Iceberg are you still not able to insert into?
3

thx for reply,

my dremio is community version.19
may I know how to convert to CTAS by sql instead of UI function
Could you advise how to automate the increment load by script? thanks

4

@crazyisjen Insert into is only an option in v23.x and copy into in v24.x

In 19.x you can do CTAS to PARQUET by doing the below

CREATE TABLE <Full_Path_To_Folder>.<Table_name> AS
SELECT ...