Upserting Data in Matillion

Data

Upserting Data in Matillion

In my last blog, I showed you how to iterate through a variable to bring multiple tables into the raw layer of your data warehouse. Today, I’m going to build on that concept by creating a DIM table and performing an UPSERT. Now, to start, I won’t go over the process of building the DIM table today, as that will be in my next blog, so stay tuned. Today, I will simply concentrate on the process of doing an upsert because it’s simple, but vital.

The first step in doing an upsert is to read in the table you’re updating and connect your new data and existing data via the Detect Changes tool (pictured below). Set your existing table as the Master Table and your SK column (or whatever your Primary Key is) as the Match Key. The next step in setting up the Detect Changes tool is to bring in all columns that are not in the key field into the Compare Columns section.

A side note on the SK column: I will teach you how to create that in next week’s blog.

Table showing standard naming conventions for tables in the Detect Changes tool

Finally, I simply need to include the master and compare columns in Output Columns Mapping section of the Detect Changes tool:

Table showing master and compare columns in "output columns mapping" section of the "detect changes" tool

The next tool we need is the Filter tool. We need to filter for only our new data and changes by filtering on the Indicator column created in the previous step for any value not equal to D or I:

Using the Filter Tool for new data during upserting into Matillion

Now, I will bring in the Table Update tool, but don’t worry, I will break this down step-by-step:

Using the Table Update tool to Upsert data into Matillion

As you can see above, I need to select my Warehouse, Database and Schema, then name my table. For the Target Alias and Source Alias sections, just leave the defaults. Now, I just need to create my join expression. Make sure not to forget that we only want to join on the C or Compare indicator:

Table showing how to create join expression

Next, I will use the SK field and the compare columns to map to the appropriate columns in the Update Mapping section of the Update Table tool.

Table showing how to use the SK field and compare columns to appropriate columns

In the When Matched section of the tool, I will need the formula below. The Case column reads 1=1 and the Operation column reads Update. This tells Matillion to update each column when the keys are matched:

The final step is to do the Insert Mapping. This is basically a repeat of the Update Mapping:

Table showing the Insert Mapping

That’s all there is to it! Stay tuned next week when we discuss creating keys in your tables, and I’ll throw in a few shortcut hints as well.

More About the Author

Scott Perry

Data Engineer
Books for Your Business: The Goal: A Process of Ongoing Improvement While you might expect that this series would concentrate on technology books (after all, I am a tech consultant), this week we are ...
Creating MD5 Hash Keys in Snowflake via Matillion In my last blog, I discussed how to do an Upsert in Snowflake using Matillion. Using the same workflow, I will discuss how we got the ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072