New Inphinity Cloud Changelog Tables (for SQL Destinations)

There has been some great new additions in the Inphinity v13 release. One that may have gone under the radar however, is the introduction of the SQL writeback changelog setup. Previously, you needed additional setup in your database via triggers or streams, such as in Snowflake, to enable change history, but now you no longer need that, all you do need is a copy of the writeback table.

When creating a new Inphinity writeback destination in cloud, simply enable the changelog option. This will tell Inphinity when changing records, you want to keep the changes in a separate table suffixed '_changelog' (lower case only).

So what’s now required in Snowflake now?

Previously you would need to ‘CREATE STREAM’ On the Inphinity table that was already setup, and this would be a snowflake side cost that’s out of your control to manage as this doesn’t run on your setup warehouses. Now, you only need to do the following:

This will create a duplicate of the table (without the primary key as required by Inphinity), allowing Inphinity to land the previous records for you to process. So, what landed here you may ask? If you update a row via the Inphinity form in a Qlik app the following will happen:

1. The Old Record is moved from the ‘Live’ Table into the '_changelog' Table
2. The New Record is updated in the ‘Live’ Table as per normal
3. I will still have the ‘ChangeBy’ and ‘ChangeDate’ Fields to see who’s done what and when

This can be simplified by creating a stored procedure to convert tables in Snowflake into writeback destinations with change logging:

The procedure is relatively small, and take 3 parameters:

• The Table name to ‘upgrade’
• The Key field of that table to make sure if it is a Primary key
• Logging Boolean, if true, will create the '_changelog' table

For me this is much simpler -  dealing with change data in the same format as the source enables you to audit and trace changes back more easily. We hope this helps with setting up your writeback tables in your SQL destination of choice!

Follow Ometis on LinkedIn to keep up-to-date with developments in Qlik and all things data.