Usar chaves primárias para otimizar pipelines de tabelas dinâmicas¶

O Snowflake pode usar chaves primárias para rastrear alterações no nível da linha em tabelas dinâmicas sem depender de colunas de rastreamento de alterações. Isso permite a atualização incremental para pipelines que executam cargas de trabalho de inserção/substituição, incluindo tabelas dinâmicas de atualização completa, que normalmente bloqueiam o processamento incremental downstream.

As chaves primárias são especialmente eficazes quando INSERT OVERWRITE é realizado em uma tabela base em que apenas uma pequena fração dos dados é de fato alterada. Nesses casos, o rastreamento de alterações baseado em chave primária processa apenas as linhas alteradas em vez de recalcular toda a tabela. Uma chave primária fornece um identificador de linha estável que persiste após as substituições.

Para saber o contexto conceitual, consulte Explicando as chaves primárias em tabelas dinâmicas.

Melhorar o desempenho de cargas de trabalho de INSERT OVERWRITE¶

Quando uma tabela base é periodicamente regravada por meio de INSERT OVERWRITE, as colunas padrão de rastreamento de alterações são redefinidas, e uma tabela dinâmica que consome a tabela base vê um conjunto de inserções e exclusões para todas as linhas na tabela base.

No exemplo a seguir, um processo externo regrava dimension_table periodicamente, mas a maioria das linhas permanece a mesma:

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;

Quando a tabela de dimensões é regravada por meio de INSERT OVERWRITE, o Snowflake usa a chave primária para identificar as linhas de dimensão que realmente mudaram e atualiza apenas os fatos afetados, em vez de recalcular a junção inteira.

Habilitar a atualização incremental downstream de uma tabela dinâmica de atualização completa¶

Normalmente, uma tabela dinâmica com REFRESH_MODE = INCREMENTAL não pode ler uma tabela dinâmica com REFRESH_MODE = FULL. Quando a tabela dinâmica de atualização completa tem uma chave exclusiva derivada do sistema, você pode definir explicitamente o modo de atualização como INCREMENTAL.

Exemplo: usar uma chave primária de tabela base¶

Crie uma tabela base com uma chave primária e defina a propriedade RELY para que o Snowflake a use para rastreamento de alterações no nível da linha:

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

Crie uma tabela dinâmica de atualização completa que leia a tabela base. Como a tabela base tem uma chave primária confiável, o Snowflake pode derivar uma chave exclusiva da tabela base e registrá-la como uma restrição única para a tabela dinâmica:

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;

Crie uma tabela dinâmica incremental downstream. Isso funciona porque a tabela upstream tem uma chave exclusiva confiável derivada do sistema:

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;

Exemplo: usar uma chave primária derivada de consulta¶

Quando a consulta de uma tabela dinâmica inclui uma cláusula GROUP BY, o Snowflake deriva automaticamente uma chave exclusiva das colunas de agrupamento. Tabelas downstream podem usar essa chave derivada para rastreamento de alterações baseado em chave primária e habilitar atualizações incrementais.

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;

A tabela daily_sales tem uma chave exclusiva derivada em (sale_day, product_id) porque GROUP BY garante uma linha por combinação. Uma tabela downstream pode ser atualizada de forma incremental:

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;

Verificar as chaves exclusivas derivadas do sistema em uma tabela dinâmica¶

Para ver se uma tabela dinâmica tem uma chave exclusiva derivada, use o comando SHOW UNIQUE KEYS:

SHOW UNIQUE KEYS IN daily_sales;

Se a saída contiver uma chave exclusiva, a tabela dinâmica permitirá o rastreamento de alterações baseado em chave primária. As tabelas dinâmicas downstream podem usar REFRESH_MODE = INCREMENTAL para lê-la, mesmo que ela use o modo de atualização completa.

Você também pode verificar a compatibilidade criando uma tabela dinâmica downstream com REFRESH_MODE = INCREMENTAL. Se a tabela upstream não tiver uma chave exclusiva confiável, a criação falhará com um erro.