Uso de restrições de imutabilidade¶

Para informar ao Snowflake que certas linhas não mudarão em uma tabela dinâmica, use a cláusula IMMUTABLE WHERE em uma instrução CREATE DYNAMIC TABLE ou ALTER DYNAMIC TABLE.

A imutabilidade torna as atualizações mais rápidas, ignorando as linhas que não mudam. O preenchimento com imutabilidade proporciona benefícios de desempenho imediatos e contínuos:

Criação inicial: o preenchimento copia os dados históricos instantaneamente, sem custos de computação. Isso torna as tabelas com anos de dados históricos disponíveis imediatamente, em vez de exigir atualizações iniciais caras.
Atualizações em andamento: restrições de imutabilidade protegem os dados já preenchidos de serem reprocessados durante atualizações futuras. Somente a região mutável é atualizada, mantendo os tempos de atualização rápidos mesmo conforme a tabela cresce.

Para saber o contexto conceitual, consulte Explicando as restrições de imutabilidade.

Exemplos básicos¶

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);

Exemplos de preenchimento¶

Os exemplos a seguir mostram como criar novas tabelas dinâmicas a partir de tabelas com dados preenchidos.

A tabela de preenchimento deve conter colunas correspondentes com tipos de dados compatíveis na mesma ordem que a tabela dinâmica. O Snowflake não copia propriedades ou privilégios da tabela de preenchimento.

Se você especificar os parâmetros de Time Travel AT | BEFORE, o Snowflake copia os dados da tabela de preenchimento no momento especificado.

As seguintes limitações se aplicam quando você trabalha com restrições de imutabilidade e dados preenchidos:

Atualmente, apenas tabelas regulares e dinâmicas podem ser usadas para preenchimento.
Não é possível especificar políticas ou tags na nova tabela dinâmica porque elas são copiadas da tabela de preenchimento.
As chaves de clustering na nova tabela dinâmica e na tabela de preenchimento devem ser as mesmas.

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:

Clone a tabela dinâmica em uma tabela regular.
Modifique a tabela clonada conforme necessário.
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:

Clone my_dynamic_table para uma tabela regular:
```
CREATE OR REPLACE TABLE my_dt_clone_table CLONE my_dynamic_table;
```

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;

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

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;

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;

+---------+------------+-------------+
| 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:

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;
```

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;

Opcionalmente, adicione dados à nova coluna.

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;

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;

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

Verificação do status de imutabilidade¶

Para verificar se uma linha de uma tabela dinâmica é mutável, consulte a coluna METADATA$IS_IMMUTABLE:

SELECT *, METADATA$IS_IMMUTABLE FROM my_dynamic_table;

Para visualizar a restrição de imutabilidade em uma tabela dinâmica, execute SHOW DYNAMIC TABLES e verifique a coluna immutable_where.