Utiliser les contraintes d’immuabilité¶

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

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 :

1. Cloner la table dynamique ver une table standard.

2. Modifiez la table clonée selon vos besoins.

3. 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 :

1. Clonez my_dynamic_table vers une table ordinaire :

   CREATE OR REPLACE TABLE my_dt_clone_table CLONE my_dynamic_table;
   

   Copy

2. 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;
   

   Copy

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

3. 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;
   

   Copy

   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;
   

   Copy

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

1. 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;
   

   Copy

2. 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;
   

   Copy

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

4. 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;
   

   Copy

5. 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;
   

   Copy

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