Qlik Replicate: attrep_changes raises storage cost as the image of a dropped table will persist on Snowflake Storage

[dima_etkin]

Over a period of time, attrep_changes tables will significantly contribute to the overall storage cost as the image of a dropped table will persist on Snowflake Storage for the purpose of Time Travel and Fail-Safe (see Working with Temporary and Transient Tables | docs.snowflake.com for details).

Resolution

Create the attrep_changes table as a transient with a limited-to-absent support for Time Travel and Fail Safe. This needs to be done in Snowflake.

1. Create a transient schema (CREATE SCHEMA | docs.snowflake.com) with:
   
   
   • DATA_RETENTION_TIME_IN_DAYS set to 0
     
     
     
     
   • MAX_DATA_EXTENSION_TIME_IN_DAYS set to 0 (Maximum number of days for which Snowflake can extend the data retention period for tables to prevent streams on the tables from becoming stale)
     
     See MAX_DATA_EXTENSION_TIME_IN_DAYS for more information.
     
     
2. In Qlik Replicate, configure the task so that all of the control tables will be created in the transient schema:
   
   
   

Example:

CREATE OR REPLACE TRANSIENT SCHEMA IF NOT EXISTS TRANSIENT_SCHEMA
        DATA_RETENTION_TIME_IN_DAYS = 0
        MAX_DATA_EXTENSION_TIME_IN_DAYS = 0
        COMMENT = 'Transient Schema for Qlik Replicate Control Tables';

No out-of-the-box solution exists in Qlik Replicate for a configurable option to create the attrep_changes table as a transient one. Log an Idea with Qlik if this is a requirement.

Cause

• attrep_changes tables are defined for each task and are dropped or created for each bulk being applied to the target
• attrep_changes tables are created as normal SF tables, which means that they have a Time Travel and Fail-Safe feature enabled (storing old data images and mirroring data)
• The volume of uncompressed data in each instance of attrep_changes table usually varies between 500 MB and 2 GB
• Usually, customers configure a bulk to be dropped into Snowflake every 10 to 20 minutes.
• If we assume that the data is stored uncompressed, then a worst-case scenario would be:
  • 2GB * 6 drops/hour * 24 hours * 90 days (default data retention in enterprise account) = 25,920GB à 26 billable Terabytes after 90 days.
  • Even with a 60% compression ratio, this still produces 9 TB per table after 90 days.

Environment

• Qlik Replicate