Utiliser des clés primaires pour optimiser les pipelines de tables dynamiques¶

Snowflake peut utiliser des clés primaires pour suivre les modifications au niveau des lignes dans les tables dynamiques sans s’appuyer sur des colonnes de suivi des modifications. Cela permet une actualisation incrémentielle pour les pipelines qui exécutent des charges de travail d’insertion et de réécriture, y compris les tables dynamiques à actualisation complète, qui bloquent normalement le traitement incrémentiel en aval.

Les clés primaires sont particulièrement efficaces lorsqu’une charge de travail INSERT OVERWRITE est exécutée sur une table de base où seule une petite fraction des données est réellement modifiée. Dans ce cas, le suivi des modifications basé sur une clé primaire ne traite que les lignes modifiées au lieu de recalculer la table entière. Une clé primaire fournit un identificateur de lignes stable qui persiste en cas de réécriture.

Pour un arrière-plan conceptuel, voir Comprendre les clés primaires des tables dynamiques.

Améliorer les performances pour les charges de travail INSERT OVERWRITE¶

Lorsqu’une table de base est réécrite périodiquement via INSERT OVERWRITE, les colonnes de suivi des modifications standard sont réinitialisées et une table dynamique consommant la table de base verra un ensemble d’insertions et de suppressions pour toutes les lignes de la table de base.

Dans l’exemple suivant, un processus externe réécrit la dimension_table périodiquement, mais la plupart des lignes restent les mêmes :

CREATE TABLE dimension_table (
  dim_id INT PRIMARY KEY RELY,
  dim_name VARCHAR,
  category VARCHAR
);

CREATE TABLE fact_table (
  fact_id INT,
  dim_id INT,
  measure FLOAT,
  ts TIMESTAMP
);

CREATE DYNAMIC TABLE enriched_facts
  TARGET_LAG = '30 minutes'
  WAREHOUSE = mywh
  REFRESH_MODE = INCREMENTAL
AS
  SELECT f.fact_id, f.measure, d.dim_name, d.category, f.ts
  FROM fact_table f
  INNER JOIN dimension_table d ON f.dim_id = d.dim_id;

Lorsque la table de dimension est réécrite via INSERTOVERWRITE, Snowflake utilise la clé primaire pour identifier les lignes de dimension effectivement modifiées et n’actualise que les faits concernés, plutôt que de recalculer l’intégralité de la jointure.

Activer l’actualisation incrémentielle en aval d’une table dynamique à actualisation complète¶

En principe, une table dynamique avec REFRESH_MODE = INCREMENTAL ne peut pas lire à partir d’une table dynamique avec REFRESH_MODE = FULL. Lorsque la table dynamique à actualisation complète possède une clé unique dérivée du système, vous pouvez définir explicitement le mode d’actualisation sur INCREMENTAL.

Exemple : Utiliser une clé primaire de table de base¶

Créez une table de base avec une clé primaire et définissez la propriété RELY pour que Snowflake l’utilise pour le suivi des modifications au niveau des lignes :

CREATE TABLE raw_events (
  event_id INT PRIMARY KEY RELY,
  event_type VARCHAR,
  payload VARIANT,
  created_at TIMESTAMP
);

Créez une table dynamique à actualisation complète qui lit à partir de la table de base. Comme la table de base possède une clé primaire fiable, Snowflake peut dériver une clé unique de la table de base et l’enregistrer comme une contrainte unique pour la table dynamique :

CREATE DYNAMIC TABLE transformed_events
  TARGET_LAG = '10 minutes'
  WAREHOUSE = mywh
  REFRESH_MODE = FULL
AS
  SELECT event_id, event_type, payload:user_id::STRING AS user_id, created_at
  FROM raw_events;

Créez une table dynamique incrémentielle en aval. Cela fonctionne car la table en amont possède une clé unique fiable dérivée du système :

CREATE DYNAMIC TABLE event_summary
  TARGET_LAG = '10 minutes'
  WAREHOUSE = mywh
  REFRESH_MODE = INCREMENTAL
AS
  SELECT user_id, COUNT(*) AS event_count, MAX(created_at) AS last_event
  FROM transformed_events
  GROUP BY user_id;

Exemple : Utiliser une clé primaire basée sur une requête¶

Lorsqu’une requête d’une table dynamique comprend une clause GROUP BY, Snowflake dérive automatiquement une clé unique à partir des colonnes de regroupement. Les tables en aval peuvent utiliser cette clé dérivée pour le suivi des modifications basé sur la clé primaire et activer les actualisations incrémentielles.

CREATE DYNAMIC TABLE daily_sales
  TARGET_LAG = '1 hour'
  WAREHOUSE = mywh
  REFRESH_MODE = FULL
AS
  SELECT DATE_TRUNC('day', sale_ts) AS sale_day, product_id, SUM(amount) AS total_sales
  FROM sales
  GROUP BY sale_day, product_id;

La table daily_sales possède une clé unique dérivée sur (sale_day, product_id), car GROUP BY garantit une ligne par combinaison. Une table en aval peut être actualisée de manière incrémentielle :

CREATE DYNAMIC TABLE product_trends
  TARGET_LAG = '1 hour'
  WAREHOUSE = mywh
  REFRESH_MODE = INCREMENTAL
AS
  SELECT product_id, AVG(total_sales) AS avg_daily_sales, COUNT(*) AS days_with_sales
  FROM daily_sales
  GROUP BY product_id;

Vérifier les clés uniques dérivées du système sur une table dynamique¶

Pour voir si une table dynamique possède une clé unique dérivée, utilisez la commande SHOW UNIQUE KEYS :

SHOW UNIQUE KEYS IN daily_sales;

Si la sortie contient une clé unique, la table dynamique prend en charge le suivi des modifications basé sur la clé primaire. Les tables dynamiques en aval peuvent utiliser REFRESH_MODE = INCREMENTAL pour la lire, même si elle utilise le mode d’actualisation complète.

Vous pouvez également vérifier la prise en charge en créant une table dynamique en aval avec REFRESH_MODE = INCREMENTAL. Si la table en amont ne possède pas de clé unique fiable, la création échoue avec une erreur.