STORAGE.ENTITY_RECREATE_AS_EMPTY_TABLE

STORAGE.ENTITY_RECREATE_AS_EMPTY_TABLE is used to force entity to be recreated without previous data.

See also:

• system.deployment.entity_deletion.method

• system.deployment.entity_deletion.method.{physical_type}

Usage

STORAGE.ENTITY_RECREATE_AS_EMPTY_TABLE: true

Notes

Entity recreate as empty table can be used with TABLE , DYNAMIC_TABLE and ICEBERG_TABLE.

The previous version of the entity will be backed up based on the selected system.deployment.entity_deletion.method and after this the entity will be created as empty.

Examples

1. Setting up the STORAGE.ENTITY_RECREATE_AS_EMPTY_TABLE for a TABLE when entity_deletion.method environment variable is as default

Example case:

Physical option set:

STORAGE.ENTITY_RECREATE_AS_EMPTY_TABLE = true

Snowflake

DDL generated by Agile Data Engine:

ALTER TABLE publish.F_TRIP RENAME TO publish.Z_AAAA_BBBB_CCCC_DDDD_BACKUP_6789;

CREATE TABLE publish.F_TRIP
  ...
;

Databricks, Google BigQuery, Amazon Redshift, DuckLake, PostgreSQL

ALTER TABLE publish.F_TRIP RENAME TO Z_AAAA_BBBB_CCCC_DDDD_BACKUP_6789;

CREATE TABLE publish.F_TRIP
  ...
;

Azure SQL Database

EXEC sp_rename 'publish.F_TRIP', 'Z_AAAA_BBBB_CCCC_DDDD_BACKUP_6789';

CREATE TABLE [publish].[F_TRIP]
  ...
;

Azure Synapse SQL

RENAME OBJECT [publish].[F_TRIP] TO [Z_AAAA_BBBB_CCCC_DDDD_BACKUP_6789];

CREATE TABLE [publish].[F_TRIP]
  ...
;

Microsoft Fabric

CREATE TABLE [publish].[Z_AAAA_BBBB_CCCC_DDDD_BACKUP_6789]
AS CLONE OF [publish].[F_TRIP];

DROP TABLE [publish].[F_TRIP];

CREATE TABLE [publish].[F_TRIP]
  ...
;

2. Setting up the STORAGE.ENTITY_RECREATE_AS_EMPTY_TABLE for a TABLE when entity_deletion.method environment variable is set as DROP

Example case:

Physical option set:

STORAGE.ENTITY_RECREATE_AS_EMPTY_TABLE = true

Environment variable set:

system.deployment.entity_deletion.method = DROP

Snowflake, Databricks, Google BigQuery, Amazon Redshift, DuckLake, PostgreSQL

DDL generated by Agile Data Engine:

DROP TABLE publish.F_TRIP;

CREATE TABLE publish.F_TRIP
  ...
;

Azure SQL Database, Azure Synapse SQL, Microsoft Fabric

DROP TABLE [publish].[F_TRIP];

CREATE TABLE [publish].[F_TRIP]
  ...
;

3. Setting up the STORAGE.ENTITY_RECREATE_AS_EMPTY_TABLE for a DYNAMIC_TABLE

Example case:

Physical option set:

STORAGE.ENTITY_RECREATE_AS_EMPTY_TABLE = true

Snowflake

DDL generated by Agile Data Engine:

DROP DYNAMIC TABLE publish.F_TRIP;

CREATE DYNAMIC TABLE publish.F_TRIP
  ...
;

4. Setting up the STORAGE.ENTITY_RECREATE_AS_EMPTY_TABLE for a ICEBERG_TABLE

Example case:

Physical option set:

STORAGE.ENTITY_RECREATE_AS_EMPTY_TABLE = true

Snowflake

DDL generated by Agile Data Engine:

DROP ICEBERG TABLE publish.F_TRIP;

CREATE ICEBERG TABLE publish.F_TRIP
  ...
;

5. Setting up the STORAGE.ENTITY_RECREATE_AS_EMPTY_TABLE for a ICEBERG_TABLE when system.deployment.entity_deletion.method.iceberg_table environment variable is set as RENAME_AS_BACKUP_BY_RECREATE

Example case:

Physical option set:

STORAGE.ENTITY_RECREATE_AS_EMPTY_TABLE = true

Environment variable set:

system.deployment.entity_deletion.method = RENAME_AS_BACKUP_BY_RECREATE

Snowflake

DDL generated by Agile Data Engine:

CREATE ICEBERG TABLE publish.Z_AAAA_BBBB_CCCC_DDDD_BACKUP_6789
  ...
AS SELECT ... FROM publish.F_TRIP;

CREATE OR REPLACE ICEBERG TABLE publish.F_TRIP
  ...
;