1

I want to perform incremental load from Azure Blob storage to Snowflake table using Matillion ETL. There is a JDBC incremental load component but it works for a select few databases and Snowflake isn't one of them. I tried searching the documentation but wasn't able to find anything. Any suggestions ?

EDIT: Under properties of my Table Update component

enter image description here

Coder1990
  • 89
  • 8

1 Answers1

1

The Blob Storage load component does not support update or upsert. To accomplish this, use the Azure Blob storage load component to load to a staging table. Then run a transformation job that reads from the staging table, and uses either the 'Table Output' component with the append option (for Insert), or the 'Table Update' component with the 'include when not matched' option (for Upsert)

enter image description here

peterb
  • 697
  • 3
  • 11
  • Can you tell me how to do the either insert or update condition ? In ADF, we can add a conditional split. Is there a similar functionality here ? Also, how does CDC work in this case ? – Coder1990 Nov 02 '21 at 16:58
  • The full docs are [here](https://documentation.matillion.com/docs/2043155) but basically the Table Update allows you to do the same as an ADF conditional split, determined by a) the unique identifier, which you put in the Join Expression and b) the values you choose for 'When Matched' and 'Include Not Matched'. The unique identifier determines whether every new record gets updated (if it's already present) or inserted (if it's not already present). Internally it runs a single fast pushdown SQL MERGE. CDC is another way of getting hold of source data as an alternative to a Blob Storage load. – 53epo Nov 02 '21 at 19:59
  • I am facing issue in the Table Update component. When I try to keep the when matched as "Case", Update it shows error : SQL compilation error: error line 8 at position 113 invalid identifier '"Case"'. To avoid this error, I tried to keep the join condition as the condition in this field as well, but then it gives this error : Duplicate row detected during DML action. Which means it is clearly not working. Have updated the snapshot for your reference in my question. – Coder1990 Nov 16 '21 at 14:52