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:
create a new normal table with the updated structure of the dynamic table
insert data from the current version of the dynamic table to the normal table as standard ENTITY_RECREATE logic would do.
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
swap the old and new dynamic tables
remove the generated normal table and the swap table
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
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):
STORAGE.DYNAMIC_TABLE_SIMPLE_ENTITY_RECREATE: true
DDL generated by Agile Data Engine:
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):
STORAGE.DYNAMIC_TABLE_SIMPLE_ENTITY_RECREATE: false
DDL generated by Agile Data Engine:
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 (...);