Snowplow users managing large datasets in Amazon Redshift often face the challenge of balancing storage costs and data retention policies. This post explores the trade-offs between the traditional Delete and Vacuum approach and the more efficient Deep Copy method.

Q: What is the Delete and Vacuum approach?

The Delete and Vacuum method involves removing older data from Redshift and reclaiming the freed-up disk space. Here’s how the process typically works:

1. Unload Data to S3: Extract data older than the retention window to S3 for archiving.
   
   

UNLOAD

  (SELECT * FROM table_name WHERE root_tstamp < DATE_ADD('month', -18, CURRENT_DATE))

TO 's3://bucket/location'

CREDENTIALS 'aws_access_key_id=<KEY>;aws_secret_access_key=<SECRET>'

DELIMITER '|' NULL AS 'null' ESCAPE;

2. Delete Data from Redshift: Remove the unloaded data from the table:
   
   

DELETE FROM table_name WHERE root_tstamp < DATE_ADD('month', -18, CURRENT_DATE);

3. Vacuum the Table: Reclaim disk space and re-sort the table:
   
   

VACUUM table_name;

Drawbacks of Delete and Vacuum:

• Time-Consuming: Vacuum can run for hours, especially for large datasets.
  
  
• High Disk Usage: Significant space is required to maintain intermediate states during the vacuum process.
  
  
• Operational Disruption: Long-running vacuum operations can impact query performance.
  
  

Q: What is the Deep Copy approach?

The Deep Copy method leverages a more efficient strategy by creating a new version of the table with only the desired data. This eliminates the need for vacuuming and minimizes runtime.

Steps to Implement Deep Copy:

1. Rename the Original Table:
   
   

ALTER TABLE table_name RENAME TO table_name_old;

2. Create a New Table:
   
   

CREATE TABLE table_name_new AS SELECT * FROM table_name_old WHERE root_tstamp > DATE_ADD('month', -18, CURRENT_DATE);

3. Replace the Old Table:
   
   

ALTER TABLE table_name_new RENAME TO table_name;

DROP TABLE table_name_old;

Benefits of Deep Copy:

• Faster Execution: Deep copy avoids the lengthy vacuum process.
  
  
• Optimized Disk Space: Data is re-sorted automatically, reducing fragmentation.
  
  
• Scalability: Suitable for large datasets and frequent data retention policies.
  
  

Q: How do I automate Deep Copy for multiple tables?

Automating the Deep Copy process involves using a SQL Runner playbook. Here’s a sample playbook structure:

:variables:

  :backupWindow: "18"

  :schema: "atomic"

‍

:steps:

  - :name: table1

    :queries:

      - :name: table1

        :file: sql/table1.sql

  - :name: table2

    :queries:

      - :name: table2

        :file: sql/table2.sql

Considerations for Deep Copy:

• Primary Keys: Drop primary key constraints before executing the copy to avoid conflicts.
  
  
• Disk Space: Ensure at least 50% disk space is free to accommodate the new table version during the copy process.
  
  

Final Thoughts

While the Delete and Vacuum method remains viable for smaller datasets, the Deep Copy approach offers substantial advantages for large Snowplow data tables in Redshift. By optimizing for runtime and disk usage, Deep Copy enhances data management efficiency, ensuring data integrity and reducing operational overhead.

‍