Uso de restrições de imutabilidade¶

Exemplo: Como impedir a recomputação quando uma tabela de dimensões for alterada¶

Ao atualizar uma linha em uma tabela de dimensões, apenas os fatos do período imutável devem ser processados novamente:

CREATE DYNAMIC TABLE joined_data
  TARGET_LAG = '1 minute'
  WAREHOUSE = mywh
  IMMUTABLE WHERE (timestamp_col < CURRENT_TIMESTAMP() - INTERVAL '1 day')
AS
  SELECT F.primary_key primary_key, F.timestamp_col timestamp_col, D.value dim_value
  FROM fact_table F
  LEFT OUTER JOIN dimension_table D USING (primary_key);

Exemplo: Reter dados por mais tempo que a tabela de origem¶

Crie uma tabela dinâmica que retenha dados analisados por mais tempo do que a tabela de preparação e exclua os dados de preparação antigos com uma tarefa:

CREATE TABLE staging_data (raw TEXT, ts TIMESTAMP);

CREATE DYNAMIC TABLE parsed_data
  TARGET_LAG = '1 minute'
  WAREHOUSE = mywh
  IMMUTABLE WHERE (ts < CURRENT_TIMESTAMP() - INTERVAL '7 days')
AS
  SELECT
    parse_json(raw):event_id::string event_id,
    parse_json(raw):name::string name,
    parse_json(raw):region::string region,
    ts
  FROM staging_data
  WHERE region = 'US';

CREATE TASK delete_old_staging_data
  WAREHOUSE = mywh
  SCHEDULE = '24 hours'
AS
  DELETE FROM staging_data WHERE ts < CURRENT_TIMESTAMP() - INTERVAL '30 days';

Exemplo: Deixar as tabelas downstream usarem a atualização incremental de uma tabela de atualização completa¶

Algumas construções de consulta (como funções de tabela definidas pelo usuário em Python) exigem o modo de atualização completa. As restrições de imutabilidade permitem que tabelas downstream ainda usem a atualização incremental:

CREATE DYNAMIC TABLE udtf_dt
  TARGET_LAG = '1 hour'
  WAREHOUSE = mywh
  REFRESH_MODE = FULL
  IMMUTABLE WHERE (ts < current_timestamp() - interval '1 day')
AS
  SELECT ts, data, output, join_key
  FROM input_table, TABLE(my_udtf(data));

CREATE DYNAMIC TABLE incremental_join_dt
  TARGET_LAG = '1 hour'
  WAREHOUSE = mywh
  REFRESH_MODE = INCREMENTAL
  IMMUTABLE WHERE (ts < current_timestamp() - interval '1 day')
AS
  SELECT * FROM udtf_dt JOIN dim_table USING (join_key);

Exemplo: Preenchimento de uma parte da tabela¶

O exemplo a seguir preenche a região imutável de my_dynamic_table de my_backfill_table e a região mutável da definição da tabela dinâmica.

Quando você reinicia esta tabela dinâmica:

• Modo de atualização incremental: o Snowflake exclui todas as linhas mutáveis e preenche de novo somente a região variável.

• Modo de atualização completa: o Snowflake realiza uma atualização completa com o mesmo efeito.

CREATE DYNAMIC TABLE my_dynamic_table (day TIMESTAMP, totalSales NUMBER)
  IMMUTABLE WHERE (day < '2025-01-01')
  BACKFILL FROM my_backfill_table
  TARGET_LAG = '20 minutes'
  WAREHOUSE = 'mywh'
  AS SELECT DATE_TRUNC('day', ts) AS day, sum(price)
    FROM my_base_table
    GROUP BY day;

Exemplo: Usar o preenchimento para recuperar ou modificar dados em uma tabela dinâmica¶

Você não pode editar diretamente os dados ou a definição de uma tabela dinâmica. Para recuperar ou corrigir dados, execute as seguintes etapas:

1. Clone a tabela dinâmica em uma tabela regular.

2. Modifique a tabela clonada conforme necessário.

3. Preencha a tabela editada em uma nova tabela dinâmica.

No exemplo a seguir, my_dynamic_table agrega dados de vendas diárias da tabela base sales:

CREATE OR REPLACE TABLE sales(item_id INT, ts TIMESTAMP, sales_price FLOAT);

INSERT INTO sales VALUES (1, '2025-05-01 01:00:00', 10.0), (1, '2025-05-01 02:00:00', 15.0), (1, '2025-05-01 03:00:00', 11.0);
INSERT INTO sales VALUES (1, '2025-05-02 00:00:00', 11.0), (1, '2025-05-02 05:00:00', 13.0);


CREATE DYNAMIC TABLE my_dynamic_table
  TARGET_LAG = 'DOWNSTREAM'
  WAREHOUSE = mywh
  INITIALIZE = on_create
  IMMUTABLE WHERE (day <= '2025-05-01')
  AS
    SELECT item_id, date_trunc('DAY', ts) day, count(sales_price) AS sales_count FROM sales
    GROUP BY item_id, day;

SELECT item_id, to_char(day, 'YYYY-MM-DD') AS day, sales_count FROM my_dynamic_table;

+---------+------------+-------------+
| ITEM_ID | DAY        | SALES_COUNT |
|---------+------------+-------------|
| 1       | 2025-05-01 | 3           |
| 1       | 2025-05-02 | 2           |
+---------+------------+-------------+

Opcionalmente, você pode arquivar os dados antigos para economizar custos de armazenamento:

DELETE FROM sales WHERE ts < '2025-05-02';

ALTER DYNAMIC TABLE my_dynamic_table REFRESH;

SELECT item_id, to_char(day, 'YYYY-MM-DD') AS day, sales_count FROM my_dynamic_table;

Mais tarde, você encontra um erro de vendas em 2025-05-01, em que sales_count deve ser 2. Para corrigir isso:

1. Clone my_dynamic_table para uma tabela regular:

   CREATE OR REPLACE TABLE my_dt_clone_table CLONE my_dynamic_table;
   

   Copy

2. Atualize a tabela clonada:

   UPDATE my_dt_clone_table SET
     sales_count = 2
     WHERE day = '2025-05-01';
   
   SELECT item_id, to_char(day, 'YYYY-MM-DD') AS day, sales_count FROM my_dt_clone_table;
   

   Copy

   +---------+------------+-------------+
   | ITEM_ID | DAY        | SALES_COUNT |
   |---------+------------+-------------|
   | 1       | 2025-05-01 | 2           |
   | 1       | 2025-05-02 | 2           |
   +---------+------------+-------------+
   

3. Recrie a tabela dinâmica usando o clone editado como fonte do preenchimento.

   CREATE OR REPLACE DYNAMIC TABLE my_dynamic_table
     BACKFILL FROM my_dt_clone_table
     IMMUTABLE WHERE (day <= '2025-05-01')
     TARGET_LAG = 'DOWNSTREAM'
     WAREHOUSE = mywh
     INITIALIZE = on_create
     AS
       SELECT item_id, date_trunc('DAY', ts) day, count(sales_price) AS sales_count FROM sales
       GROUP BY item_id, day;
   

   Copy

   Esse método permite recuperar ou corrigir dados em uma tabela dinâmica sem modificar a tabela base:

   SELECT item_id, to_char(day, 'YYYY-MM-DD') AS day, sales_count FROM my_dynamic_table;
   

   Copy

   +---------+------------+-------------+
   | ITEM_ID | DAY        | SALES_COUNT |
   |---------+------------+-------------|
   | 1       | 2025-05-01 | 2           |
   | 1       | 2025-05-02 | 2           |
   +---------+------------+-------------+
   

Exemplo: Uso do preenchimento para modificar o esquema de uma tabela dinâmica¶

Você não pode alterar diretamente o esquema de uma tabela dinâmica. Para atualizar o esquema (por exemplo, adicionar uma coluna), siga estas etapas:

1. Clone a tabela dinâmica em uma tabela regular. O exemplo a seguir usa my_dynamic_table criada a partir de sales (antes de).

   CREATE OR REPLACE TABLE my_dt_clone_table CLONE my_dynamic_table;
   

   Copy

2. Modifique o esquema da tabela clonada:

   ALTER TABLE my_dt_clone_table ADD COLUMN sales_avg FLOAT;
   
   SELECT item_id, to_char(day, 'YYYY-MM-DD') as DAY, SALES_COUNT, SALES_AVG FROM my_dt_clone_table;
   

   Copy

3. Opcionalmente, adicione dados à nova coluna.

4. Recrie a tabela dinâmica usando o clone editado como fonte do preenchimento.

   CREATE OR REPLACE DYNAMIC TABLE my_dynamic_table
     BACKFILL FROM my_dt_clone_table
     IMMUTABLE WHERE (day <= '2025-05-01')
     TARGET_LAG = 'DOWNSTREAM'
     WAREHOUSE = mywh
     INITIALIZE = on_create
     AS
       SELECT item_id, date_trunc('DAY', ts) day, count(sales_price) AS sales_count, avg(sales_price) as sales_avg FROM sales
       GROUP BY item_id, day;
   

   Copy

5. Verifique se a nova coluna aparece na tabela dinâmica:

   SELECT item_id, to_char(day, 'YYYY-MM-DD') as DAY, SALES_COUNT, SALES_AVG, metadata$is_immutable as IMMUTABLE from my_dynamic_table ORDER BY ITEM_ID, DAY;
   

   Copy

   +---------+------------+-------------+-----------+-----------+
   | ITEM_ID | DAY        | SALES_COUNT | SALES_AVG | IMMUTABLE |
   |---------+------------+-------------|-----------|-----------|
   | 1       | 2025-05-01 | 3           | NULL      | TRUE      |
   | 1       | 2025-05-02 | 2           | 12        | FALSE     |
   +---------+-------------+------------+-----------+-----------+