Data retention time in days

 DATA_RETENTION_TIME_IN_DAYS :

The DATA_RETENTION_TIME_IN_DAYS parameter in Snowflake is used to set the automatic time-based data retention policy for a table or a secure view. It determines how long the historical data is retained in the table or view or database before it is automatically deleted.

we can apply this on database , views, or tables based on the requirement.

CREATE OR REPLACE DATABASE my_temp_db 
DATA_RETENTION_TIME_IN_DAYS  = 5;

CREATE OR REPLACE SCHEMA my_temp_db.MY_TEMP_SCHEMA
DATA_RETENTION_TIME_IN_DAYS  = 5;

CREATE OR REPLACE TABLE my_temp_db.MY_TEMP_SCHEMA_2.emp (
"id" NUMBER(38,0) NOT NULL,
NAME VARCHAR(16777216) NOT NULL,
    sal double    
)
DATA_RETENTION_TIME_IN_DAYS  = 1;

INSERT INTO my_temp_db.MY_TEMP_SCHEMA.emp
VALUES 
  (0, 'Murali', 10000.50),
  (1, 'John Doe', 1000.50),
  (2, 'Jane Smith', 2000.75),
  (3, 'Bob Johnson', 1500.25);

The use case for DATA_RETENTION_TIME_IN_DAYS is to manage data retention policies and ensure compliance with data privacy regulations, such as the General Data Protection Regulation (GDPR) or other legal requirements. By specifying a retention time, organizations can automatically enforce the deletion of data after a specified period.

use cases for DATA_RETENTION_TIME_IN_DAYS:

Compliance with data privacy regulations: Organizations may need to comply with regulations that require them to delete sensitive data after a specific retention period. By setting DATA_RETENTION_TIME_IN_DAYS, organizations can ensure that data is automatically deleted when it reaches the defined retention period.

Data archival and cleanup: In some cases, organizations may choose to retain data for a specific period and then delete it to optimize storage costs. By setting the DATA_RETENTION_TIME_IN_DAYS, data that is no longer needed can be automatically cleaned up, reducing storage consumption.

Temporary data management: Tables or views that store temporary data, such as session-specific or transient data, can have a shorter retention period defined. This helps ensure that temporary data is automatically deleted after it is no longer needed.

It's important to note that DATA_RETENTION_TIME_IN_DAYS is applicable only to historical data in the table or view. It does not affect real-time or current data that is actively being inserted, updated, or queried.

Point to be noted  :

The DATA_RETENTION_TIME_IN_DAYS option in Snowflake does not delete the entire database. It only affects the retention policy for data within specific tables or secure views.

When you set the DATA_RETENTION_TIME_IN_DAYS parameter for a table or secure view, it determines the retention period for the historical data stored in that particular object. Once the data reaches the specified retention period, it is automatically deleted.

Other objects and data within the database are not affected by this setting. The database structure, other tables, views, and data outside of the specified objects will remain intact.

It's important to understand that the DATA_RETENTION_TIME_IN_DAYS parameter is specific to individual tables or secure views and allows you to manage the retention policy for data within those objects, rather than affecting the entire database.