July 09, 2024 By Ryan Peachey

New Inphinity Cloud Changelog Tables (for SQL Destinations)

Share:

  • New Inphinity Cloud Changelog Tables (for SQL Destinations)
    2:10

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).

Inphinity Cloud Changelog enable option

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:

Inphinity Cloud Changelog Tables CREATE OR REPLACE

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:

Inphinity Cloud Changelog Stored Procedure

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!

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

 

Comments

About Author

Ryan Peachey

Ryan’s enthusiasm for F1 mirrors his approach to data analytics – precision, speed and relentless pursuit of excellence. He’s recognised for his analytics prowess, collaborative spirit and commitment to delivering data-driven solutions that drive business growth.

Related Posts

Shawn Rogers webinar recording

Discover key insights from Shawn Rogers' webinar on practical AI adoption for businesses, focusing on data management, productivity enhancements, and leveraging existing AI capabilities.
Go to Post

Geospatial data visualisation: a Qlik...

Learn how to integrate KML with QlikSense for enhanced geospatial data analysis and create interactive maps for better strategic planning. === Main Point Summary: The blog provides a tutorial on...
Go to Post

Be the first to hear about new events and updates