Backing up the application tables

In the scenario where the application is uninstalled, all of the data for the upload logs, and transfer configuration will be lost. To mitigate this an automated backup of the reporting views should be established. This could be handled in a number of different ways, below is a method using Snowflake Tasks and a CTA that will backup the table every hour.

use role accountadmin;

use database ta_verify;
create schema backup;

    
CREATE TASK transfer_app_logs_backup
  WAREHOUSE = transfer_app_wh
  SCHEDULE = 'USING CRON 0 * * * * America/Los_Angeles'
  AS
    CREATE OR REPLACE TABLE backup.transfer_logs
      AS 
        SELECT * FROM transfer_app.reporting.transfer_logs;


    
CREATE TASK transfer_app_transfer_definitions_history_backup
  WAREHOUSE = transfer_app_wh
  SCHEDULE = 'USING CRON 0 * * * * America/Los_Angeles'
  AS
    CREATE OR REPLACE TABLE backup.transfer_definitions_history
      AS
         SELECT * FROM transfer_app.reporting.transfer_definitions_history;

show tasks;

Last updated