Utiliser les contraintes d’immuabilité¶

Pour indiquer à Snowflake que certaines lignes ne changeront pas dans une table dynamique, utilisez la clause IMMUTABLE WHERE dans une instruction CREATE DYNAMIC TABLE ou ALTER DYNAMIC TABLE.

L’immuabilité rend les actualisations plus rapides en ignorant les lignes qui ne changent pas. Le remplissage rétroactif associé à l’immuabilité offre des avantages en termes de performances immédiats et continus :

Création initiale : Le remplissage rétroactif copie les données historiques instantanément sans frais de calcul. Cela rend les tables contenant des années de données historiques immédiatement disponibles au lieu de nécessiter des actualisations initiales coûteuses.
Actualisations en cours : Les contraintes d’immuabilité protègent les données réintégrées contre tout nouveau traitement lors des actualisations futures. Seule la région muable est actualisée, ce qui permet de conserver des temps d’actualisation rapides même lorsque le tableau s’agrandit.

Pour un arrière-plan conceptuel, voir Compréhension des contraintes d’immuabilité.

Exemples de base¶

Exemple : Empêcher le recalcul lorsqu’une table de dimension est modifiée¶

Lorsque vous mettez à jour une ligne dans une table de dimension, ne retraitez que les faits de la période muable :

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

Exemple : Conserver les données plus longtemps que dans la table source¶

Créez une table dynamique qui conserve les données analysées plus longtemps que la table de mise en zone de préparation, et supprimez les anciennes données de mise en zone de préparation à l’aide d’une tâche :

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

Exemple : Autoriser les tables en aval à utiliser l’actualisation incrémentielle à partir d’une table d’actualisation complète¶

Certaines constructions de requêtes (comme les fonctions de table définies par l’utilisateur en Python) nécessitent un mode d’actualisation complet. Les contraintes d’immuabilité permettent aux tables en aval de toujours utiliser l’actualisation incrémentielle :

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

Exemples de remplissage rétroactif¶

Les exemples suivants montrent comment créer de nouvelles tables dynamiques à partir de tables avec des données remplies.

La table de remplissage rétroactif doit contenir des colonnes correspondantes dont les types de données sont compatibles dans le même ordre que votre table dynamique. Snowflake ne copie pas les propriétés ou les privilèges de la table depuis la table de remplissage rétroactif.

Si vous spécifiez les paramètres Time Travel AT | BEFORE, Snowflake copie les données de la table de remplissage rétroactif à l’heure spécifiée.

Les limitations suivantes s’appliquent lorsque vous utilisez des contraintes d’immuabilité et des données réintégrées :

Actuellement, seules les tables standards et dynamiques peuvent être utilisées pour le remplissage.
Vous ne pouvez pas spécifier de politiques ni de balises dans la nouvelle table dynamique, car elles sont copiées à partir de la table de remplissage.
Les clés de clustering de la nouvelle table dynamique et de la table de remplissage doivent être identiques.

Exemple : Remplissage à partir d’une partie de la table¶

L’exemple suivant remplit la région immuable de my_dynamic_table à partir de my_backfill_table et de la région mutable de la définition de la table dynamique.

Lorsque vous réinitialisez cette table dynamique :

Mode d’actualisation incrémentielle : Snowflake supprime toutes les lignes muables et ne remplit que la région muable.
Mode d’actualisation complet : Snowflake effectue une actualisation complète avec le même effet.

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;

Exemple : Utiliser le remplissage rétroactif pour récupérer ou modifier des données dans une table dynamique¶

Vous ne pouvez pas modifier directement les données ou la définition d’une table dynamique. Pour récupérer ou corriger des données, procédez comme suit :

Cloner la table dynamique ver une table standard.
Modifiez la table clonée selon vos besoins.
Effectuez le remplissage depuis la table modifiée vers une nouvelle table dynamique.

Dans l’exemple suivant, my_dynamic_table agrège les données de ventes quotidiennes de la tables de 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           |
+---------+------------+-------------+

Vous pouvez également archiver les anciennes données pour réduire les coûts de stockage :

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;

Plus tard, vous trouvez une erreur de vente sur 2025-05-01, où sales_count devrait être 2. Pour corriger cela :

Clonez my_dynamic_table vers une table ordinaire :
```
CREATE OR REPLACE TABLE my_dt_clone_table CLONE my_dynamic_table;
```

Mettez à jour la table clonée :

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

Recréez la table dynamique en utilisant le clone modifié comme source de remplissage.

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;

Cette méthode vous permet de récupérer ou de corriger les données d’une table dynamique sans modifier la table de 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           |
+---------+------------+-------------+

Exemple : Modifier le schéma d’une table dynamique à l’aide du remplissage¶

Vous ne pouvez pas modifier directement le schéma d’une table dynamique. Pour mettre à jour le schéma, par exemple, ajouter une colonne, procédez comme suit :

Cloner la table dynamique ver une table standard. L’exemple suivant utilise my_dynamic_table créée à partir de sales (précédemment).
```
CREATE OR REPLACE TABLE my_dt_clone_table CLONE my_dynamic_table;
```

Modifiez le schéma de la table clonée :

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;

Si vous le souhaitez, ajoutez des données à la nouvelle colonne.

Recréez la table dynamique en utilisant le clone modifié comme source de remplissage.

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;

Vérifiez que la nouvelle colonne apparaît dans la table dynamique :

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

Vérifier le statut d’immuabilité¶

Pour vérifier si une ligne est muable dans une table dynamique, interrogez la colonne METADATA$IS_IMMUTABLE :

SELECT *, METADATA$IS_IMMUTABLE FROM my_dynamic_table;

Pour afficher la contrainte d’immuabilité sur une table dynamique, exécutez SHOW DYNAMIC TABLES et vérifiez la colonne immutable_where.