Criação de tabelas dinâmicas

Este tópico descreve os principais conceitos para a criação de tabelas dinâmicas.

Antes de começar, verifique se você tem os privilégios para criar tabelas dinâmicas e se todos os objetos usados pela consulta de tabela dinâmica têm o rastreamento de alterações ativado.

Algumas limitações podem ser aplicadas à criação de tabelas dinâmicas. Para obter uma lista completa, consulte Limitações da tabela dinâmica.

Habilitar o rastreamento de alterações

Ao criar uma tabela dinâmica com o modo de atualização incremental, se o rastreamento de alterações ainda não estiver habilitado nas tabelas que ele consulta, o Snowflake tentará automaticamente habilitar o rastreamento de alterações nelas. Para oferecer suporte a atualizações incrementais, o rastreamento de alterações deve ser ativado com retenção no Time Travel diferente de zero em todos os objetos subjacentes usados por uma tabela dinâmica.

À medida que os objetos de base mudam, a tabela dinâmica também muda. Se você recriar um objeto base, deverá reativar o controle de alterações.

Nota

O Snowflake não tenta habilitar automaticamente o rastreamento de alterações em tabelas dinâmicas criadas com o modo de atualização completa.

Para ativar o rastreamento de alterações em um objeto de banco de dados específico, use ALTER TABLE, ALTER VIEW e comandos semelhantes nesse objeto. O usuário que cria a tabela dinâmica deve ter o privilégio OWNERSHIP para habilitar o rastreamento de alterações em todos os objetos subjacentes.

Para verificar se o rastreamento de alterações está habilitado, use SHOW VIEWS, SHOW TABLES e comandos semelhantes nos objetos subjacentes e inspecione a coluna change_tracking.

Objetos base compatíveis

As tabelas dinâmicas são compatíveis com os seguintes objetos base:

  • Tabelas

  • Tabelas Apache Iceberg™ gerenciadas pelo Snowflake

  • Tabelas Apache Iceberg™ gerenciadas externamente

Exemplo: criar uma tabela dinâmica simples

Suponha que você queira criar uma tabela dinâmica com as colunas product_id e product_name de uma tabela chamada staging_table e você decide:

  • Você quer que os dados na tabela dinâmica estejam no máximo 20 minutos atrás dos dados na staging_table.

  • Você deseja usar o warehouse mywh para os recursos de computação necessários para a atualização.

  • Você quer que o modo de atualização seja escolhido automaticamente.

  • Você quer que a tabela dinâmica seja atualizada de forma síncrona na criação.

  • Você deseja que o modo de atualização seja escolhido automaticamente e que a tabela dinâmica seja atualizada de forma síncrona na criação.

Para criar esta tabela dinâmica, você teria que executar a seguinte instrução CREATE DYNAMIC TABLE SQL:

CREATE OR REPLACE DYNAMIC TABLE my_dynamic_table
  TARGET_LAG = '20 minutes'
  WAREHOUSE = mywh
  REFRESH_MODE = auto
  INITIALIZE = on_create
  AS
    SELECT product_id, product_name FROM staging_table;
Copy

Para obter uma lista completa de parâmetros e sintaxe variante, consulte a referência CREATE DYNAMIC TABLE.

Criação de tabelas dinâmicas que leem de tabelas Apache Iceberg™ gerenciadas pelo Snowflake ou externamente

Criar uma tabela dinâmica a partir de uma tabela Iceberg é semelhante a criar uma com base em uma tabela regular. Execute a instrução SQL CREATE DYNAMIC TABLE como você faria para uma tabela regular, usando como objeto base uma tabela gerenciada pelo Snowflake ou por um catálogo externo.

Tabelas dinâmicas que leem de uma tabela Iceberg gerenciada pelo Snowflake como tabela base são úteis se você quiser que seus pipelines operem com dados em uma tabela Iceberg gerenciada pelo Snowflake ou gravada por outros mecanismos. Observe que mecanismos externos não podem gravar em tabelas Iceberg gerenciadas pelo Snowflake; elas são de leitura e gravação para o Snowflake e somente leitura para mecanismos externos.

Tabelas dinâmicas que leem tabelas Iceberg gerenciadas por catálogos externos (não Snowflake), como AWS Glue, e gravadas por mecanismos, como o Apache Spark, são úteis para processar dados de data lakes externos. Você pode criar tabelas dinâmicas com base em dados gerenciados externamente, processando-os de forma contínua no Snowflake sem duplicar ou ingerir os dados.

Limitações e considerações sobre o uso de tabelas Iceberg

Todas as limitações de tabelas dinâmicas regulares e tabelas Iceberg dinâmicas ainda se aplicam.

Adicionalmente:

  • Todas as limitações de tabelas Iceberg básicas se aplicam. Para obter mais informações, consulte Considerações e limitações.

  • Você pode criar uma tabela dinâmica que lê tabelas nativas do Snowflake, tabelas Iceberg gerenciadas pelo Snowflake e tabelas Iceberg gerenciadas externamente.

  • As tabelas dinâmicas monitoram alterações no nível do arquivo para tabelas Iceberg de base gerenciadas externamente, ao contrário de outras tabelas que monitoram alterações no nível da linha. Operações frequentes de cópia ao gravar (por exemplo, atualizações ou exclusões) em tabelas Iceberg gerenciadas externamente podem afetar o desempenho das atualizações.

Criação de tabelas dinâmicas usando preenchimento

O preenchimento é uma operação de cópia zero e baixo custo que torna os dados de origem imediatamente disponíveis em uma tabela dinâmica. Você pode criar uma tabela dinâmica com dados iniciais preenchidos a partir de uma tabela regular ou dinâmica enquanto ainda define uma consulta personalizada para atualizações futuras.

Com restrições de imutabilidade, somente a região imutável é preenchida novamente e permanece inalterada mesmo que a região imutável não corresponda mais à fonte. A região imutável é computada a partir da definição da tabela dinâmica e das tabelas base como de costume.

Only data defined by the IMMUTABLE WHERE immutability constraint can be backfilled because the backfill data must remain unchanged, even if it differs from the upstream source.

Exemplos de uso de preenchimento

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

Cada nome de coluna deve constar na tabela de preenchimento com os tipos de dados compatíveis e aparecer na mesma ordem que na tabela de preenchimento. As propriedades e os privilégios da tabela de preenchimento não são copiados.

Se os parâmetros do Time Travel AT | BEFORE forem especificados, os dados da tabela de preenchimento serão copiados no horário especificado.

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.

Nesse cenário, se a tabela dinâmica for criada com o modo de atualização incremental, a reinicialização exclui todas as linhas mutáveis e preenche novamente apenas a região mutável. Se a tabela dinâmica for criada com o modo de atualização completa, ela acontecerá da mesma forma.

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

Exemplo: Uso do 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:

  1. Clone a tabela dinâmica em uma tabela regular.

  2. Modifique a tabela clonada conforme necessário.

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

Mais tarde, você encontra um erro de vendas em 2025-05-01, em que sales_count deve ser 2. Para corrigir isso:

  1. Clone my_dynamic_table para uma tabela regular:

    CREATE OR REPLACE TABLE my_dt_clone_table CLONE my_dynamic_table;
    Copy

  2. 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;
    Copy
    +---------+------------+-------------+
| ITEM_ID | DAY        | SALES_COUNT |
|---------+------------+-------------|
| 1       | 2025-05-01 | 2           |
| 1       | 2025-05-02 | 2           |
+---------+------------+-------------+

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

    Essa abordagem 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;
    Copy
    +---------+------------+-------------+
| 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:

  1. Clone a tabela dinâmica em uma tabela regular. O exemplo a seguir usa my_dynamic_table criada a partir de sales (acima de).

    CREATE OR REPLACE TABLE my_dt_clone_table CLONE my_dynamic_table;
    Copy

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

  3. Opcionalmente, preencha os dados na nova coluna.

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

    Essa abordagem 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, 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     |
+---------+-------------+------------+-----------+-----------+

Limitações e considerações ao criar uma tabela dinâmica com dados preenchidos

Todas as limitações das tabelas dinâmicas regulares e limitações das restrições de imutabilidade ainda se aplicam.

As seguintes limitações e considerações adicionais se aplicam:

  • 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.

Práticas recomendadas para criar tabelas dinâmicas

Como encadear pipelines de tabelas dinâmicas

Ao definir uma nova tabela dinâmica, em vez de definir uma tabela dinâmica grande com muitas instruções aninhadas, use tabelas dinâmicas pequenas com pipelines.

Você pode configurar uma tabela dinâmica para consultar outras tabelas dinâmicas. Por exemplo, imagine um cenário em que seu pipeline de dados extrai dados de uma tabela de preparação para atualizar várias tabelas de dimensão (por exemplo, customer, product, date e time). Além disso, seu pipeline atualiza uma tabela sales agregada com base nas informações dessas tabelas de dimensão. Ao configurar as tabelas de dimensão para consultar a tabela de preparação e a tabela sales agregada para consultar as tabelas de dimensão, você cria um efeito cascata semelhante a um gráfico de tarefa.

Nessa configuração, a atualização da tabela sales agregada é executada somente após as atualizações das tabelas de dimensão serem concluídas com sucesso. Isso garante a consistência dos dados e atende às metas de atraso. Por meio de um processo de atualização automatizado, quaisquer alterações nas tabelas de origem acionam atualizações em todas as tabelas dependentes nos momentos apropriados.

Comparação entre DAGs de tabelas dinâmicas e gráficos de tarefas

Uso de uma tabela dinâmica de “controlador” para os gráficos de tarefa complexos

Quando você tem um gráfico complexo de tabelas dinâmicas com muitas raízes e folhas e deseja executar operações (por exemplo, alteração de atraso, atualização manual, suspensão) no gráfico de tarefa completo com um único comando, faça o seguinte:

  1. Defina o valor para o TARGET_LAG de todas as suas tabelas dinâmicas para DOWNSTREAM.

  2. Crie uma tabela dinâmica de “controlador” que leia todas as folhas em seu gráfico de tarefa. Para garantir que este controlador não consuma recursos, faça o seguinte:

    CREATE DYNAMIC TABLE controller
  TARGET_LAG = <target_lag>
  WAREHOUSE = <warehouse>
  AS
    SELECT 1 A FROM <leaf1>, …, <leafN> LIMIT 0;
    Copy

  3. Use o controlador para controlar o gráfico inteiro. Por exemplo:

  • Defina um novo atraso de destino para o gráfico de tarefa.

    ALTER DYNAMIC TABLE controller SET
  TARGET_LAG = <new_target_lag>;
    Copy

  • Atualize manualmente o gráfico de tarefa.

    ALTER DYNAMIC TABLE controller REFRESH;
    Copy

Uso de tabelas dinâmicas transitórias para reduzir o custo de armazenamento

Tabelas dinâmicas transitórias mantêm os dados confiáveis ao longo do tempo e oferecem suporte à Time Travel dentro do período de retenção de dados, mas não retêm os dados além do período de fail-safe. Por padrão, os dados da tabela dinâmica são mantidos por sete dias no armazenamento fail-safe.

Para tabelas dinâmicas com alta taxa de atualização, isso pode aumentar significativamente o consumo de armazenamento. Portanto, você deve tornar uma tabela dinâmica transitória somente se seus dados não precisarem do mesmo nível de proteção e recuperação de dados fornecido pelas tabelas permanentes.

Você pode criar uma tabela dinâmica transitória ou clonar tabelas dinâmicas existentes para tabelas dinâmicas transitórias usando a instrução CREATE DYNAMIC TABLE.

Solução de problemas de criação de tabela dinâmica

Quando você cria uma tabela dinâmica, a atualização inicial ocorre em um cronograma (ON_SCHEDULE) ou imediatamente no momento da criação (ON_CREATE). O preenchimento inicial de dados, ou inicialização, depende de quando a atualização inicial ocorre. Por exemplo, para ON_CREATE, a inicialização poderá demorar mais se acionar atualizações de tabelas dinâmicas upstream.

A inicialização pode levar algum tempo, dependendo da quantidade de dados digitalizados. Para visualizar o progresso, faça o seguinte:

  1. Faça login no Snowsight.

  2. No menu de navegação, selecione Monitoring » Query History.

  3. No menu suspenso Filters, insira CREATE DYNAMIC TABLE no filtro SQL Text e insira o nome do seu warehouse no filtro Warehouse.

  4. Selecione a consulta com sua tabela dinâmica em SQL text e use as abas Query Details e Query Profile para acompanhar o progresso.

Linguagem: Português