Skip to main content
Skip table of contents

STORAGE.DYNAMIC_TABLE_SIMPLE_ENTITY_RECREATE

Target database: Snowflake

STORAGE.DYNAMIC_TABLE_SIMPLE_ENTITY_RECREATE gives an option to choose the entity recreation process:

When set TRUE, Dynamic table will be created with CREATE or REPLACE - logic. This will clear the dynamic table and the state needs to be queried with the query to get the state up-to-date.

When set FALSE, ADE will do following:

  1. create a new normal table with the updated structure of the dynamic table

  2. insert data from the current version of the dynamic table to the normal table as standard ENTITY_RECREATE logic would do.

  3. create a new dynamic table with the updated structure, set entire content as immutable and backfill table to point to the freshly generated normal table

  4. swap the old and new dynamic tables

  5. remove the generated normal table and the swap table

  6. set the immutable rule to the user-specified value or remove it

If user has specified custom backfill table or if the initialization method has been set to be done via scheduled action, the backfill logic will not be triggered.


See also:


Usage

OPTION
STORAGE.DYNAMIC_TABLE_SIMPLE_ENTITY_RECREATE: Boolean

Default: false


Notes

Supports only Snowflake Dynamic table


Examples

Snowflake: Set the STORAGE.DYNAMIC_TABLE_SIMPLE_ENTITY_RECREATE as true

Setting STORAGE.DYNAMIC_TABLE_SIMPLE_ENTITY_RECREATE for an Dynamic table (e.g. publish.F_TRIP):

OPTION
STORAGE.DYNAMIC_TABLE_SIMPLE_ENTITY_RECREATE: true

DDL generated by Agile Data Engine:

SQL
CREATE OR REPLACE DYNAMIC TABLE publish.F_TRIP (
  ...
)
...
AS
  SELECT ...;

Snowflake: Set the STORAGE.DYNAMIC_TABLE_SIMPLE_ENTITY_RECREATE as false

Setting STORAGE.DYNAMIC_TABLE_SIMPLE_ENTITY_RECREATE for an Dynamic table (e.g. publish.F_TRIP):

OPTION
STORAGE.DYNAMIC_TABLE_SIMPLE_ENTITY_RECREATE: false

DDL generated by Agile Data Engine:

SQL
CREATE TABLE publish.F_TRIP_123_ALTER (
  ...
);

INSERT INTO publish.F_TRIP_123_ALTER (...) 
SELECT
  ...
FROM publish.F_TRIP;

CREATE DYNAMIC TABLE publish.F_TRIP_123_SWAP (...) 
IMMUTABLE WHERE (1=1)
BACKFILL_FROM = publish.F_TRIP_123_ALTER
AS
SELECT ...;

ALTER DYNAMIC TABLE publish.F_TRIP_123_SWAP SWAP WITH publish.F_TRIP;

DROP TABLE publish.F_TRIP_123_ALTER;

DROP DYNAMIC TABLE publish.F_TRIP_123_SWAP;

ALTER DYNAMIC TABLE publish.F_TRIP SET IMMUTABLE WHERE (...);

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.