Tudo o que você precisa saber sobre Dynamic Tables no Snowflake

No Snowflake, uma Dynamic Table é uma tabela que materializa o resultado de uma consulta SQL e fica atualizada automaticamente conforme uma programação. Assim como uma view, o SQL usado para criar a dynamic table pode ter complexidade considerável: joins entre várias tabelas, diferentes tipos de join (left join, full join, cartesiano), unions, cálculos etc. Mas, diferente de uma view, que nunca persiste os dados, o resultado de uma dynamic table é persistido como uma tabela física.

As Dynamic Tables são atualizadas na frequência que você define, chamada de "target lag" ou target_lag. Por isso, fazer um select (leitura) em uma dynamic table costuma ser muito mais performático do que ler uma view com vários joins complexos.

Dynamic tables são uma forma simples e econômica de criar pipelines de dados performáticos e sempre atualizados.

No Snowflake, uma Materialized View é uma view de uma única tabela. Ela é persistida como tabela física, então a consulta é rápida, mas se mantém atualizada em tempo real, como uma view comum.

Abaixo estão as principais diferenças entre Materialized Views e Dynamic Tables.

Joins

No Snowflake, uma Materialized View não pode ter joins. Essa é uma limitação importante, que reduz bastante a utilidade das Materialized Views.

Uma Dynamic Table no Snowflake é muito mais parecida com uma Materialized View em outros bancos, como o PostgreSQL, em que há pouquíssimas restrições sobre o SQL que você pode escrever. No Postgres, porém, uma Materialized View precisa ser atualizada manualmente pelo comando refresh materialized view, enquanto uma Dynamic Table no Snowflake pode ser atualizada automaticamente com base no target lag.

Frequência de atualização

As Materialized Views no Snowflake têm a vantagem de estarem sempre atualizadas. Funcionam em tempo real, sem precisar de nenhuma ação sua.

Já as Dynamic Tables atualizam conforme uma programação. O usuário final pode precisar saber quão atuais estão os dados.

Reescrita de consultas (Query Rewrite)

Ao consultar as tabelas base de uma materialized view, o otimizador de queries do Snowflake pode reescrever sua consulta para usar a materialized view no lugar.

Ao consultar os dados base de uma dynamic table, o Snowflake não reescreve a consulta para usar a dynamic table.

Minha opinião sobre as diferenças

Embora as materialized views tenham duas vantagens (sempre atualizadas e query rewrite), o fato de precisarem se basear em uma única tabela limita tanto sua utilidade que recorrer a uma dynamic table acaba sendo muito mais comum.

Use uma materialized view quando você só precisa agregar, adicionar cálculos ou transformar uma única tabela.

Use uma Dynamic Table em casos de uso mais complexos.

Uma dynamic table é criada de forma parecida com o conhecido CTAS, mas com alguns parâmetros a mais, como target_lag, warehouse e vários outros opcionais mostrados abaixo.

Sintaxe completa, copiada da documentação do Snowflake:

CREATE [ OR REPLACE ] [ TRANSIENT ] DYNAMIC TABLE [ IF NOT EXISTS ] <name> (
    -- Column definition
    <col_name> <col_type>
      [ [ WITH ] MASKING POLICY <policy_name> [ USING ( <col_name> , <cond_col1> , ... ) ] ]
      [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
      [ COMMENT '<string_literal>' ]

    -- Additional column definitions
    [ , <col_name> <col_type> [ ... ] ]

  )
  TARGET_LAG = { '<num> { seconds | minutes | hours | days }' | DOWNSTREAM }
  WAREHOUSE = <warehouse_name>
  [ REFRESH_MODE = { AUTO | FULL | INCREMENTAL } ]
  [ INITIALIZE = { ON_CREATE | ON_SCHEDULE } ]

Expandir código

create or replace dynamic table my_table
target_lag = '1 Day'
warehouse = 'TRANSFORMING'
refresh_mode = 'incremental'
as
select
     customers.name,
     count(*) as total_orders
from orders
inner join customers
   using (customer_id)
group by 1

Na prática, seu comando create pode ficar assim:

O refresh é iniciado imediatamente, porque o valor padrão de initialize é on_create.

Vamos nos aprofundar nos dois argumentos exclusivos que você mais vai usar: target_lag e refresh_mode

O target lag é o tempo máximo que você admite que os dados fiquem defasados em relação às mudanças nos dados de origem. Ele é expresso como um inteiro mais a unidade de tempo (segundos, minutos, horas, dias). Por exemplo, 5 minutes é um target lag válido.

Se você empilha uma dynamic table em cima de outra dynamic table, pode definir target_lag = downstream em todas as dynamic tables da cadeia, exceto na última, que deve ter uma programação de tempo. Veja o exemplo abaixo:

Na imagem acima, temos duas dynamic tables encadeadas. A primeira (orders_joined) tem target lag downstream. A segunda (orders_aggregated) tem target lag de 1 dia. Nesse caso, todo o DAG é atualizado uma vez por dia. Você não precisa se preocupar em configurar uma programação em cada tabela.

• Se a última dynamic table do seu DAG tiver target lag downstream, seus dados nunca serão atualizados!
• O Snowflake faz a seguinte observação na documentação: target lag não é uma garantia. É uma meta que o Snowflake tenta atingir. Os dados nas dynamic tables são atualizados o mais próximo possível dentro do target lag. Ainda assim, o target lag pode ser excedido por fatores como tamanho do warehouse, volume de dados, complexidade da consulta e similares.

O Refresh Mode pode ser auto, full ou incremental. O padrão é auto, que tenta atualizar de forma incremental e recorre ao full quando não consegue.

As Dynamic Tables atualizam incrementalmente com base em changes nos dados de origem. Você não precisa informar nada sobre chave primária ou como verificar mudanças: o Snowflake faz tudo por você, como mágica! Para saber mais sobre Changes, confira a documentação do Snowflake e nosso post sobre Streams.

Vale destacar que as Dynamic Tables são parentes próximas dos Streams no Snowflake, pois usam a mesma tecnologia de rastreamento de mudanças nos bastidores, changes. Aliás, esses recursos são mantidos pelo mesmo time no Snowflake!

Dynamic Tables são uma forma declarativa e amigável de construir um pipeline.

Streams são imperativos e exigem muito mais customização para fazer o pipeline funcionar.

Limitações do modo de refresh incremental

Uma dynamic table não pode ser atualizada de forma incremental se:

• Uma função SQL não suportada, como current_timestamp ou random, for usada.
• Uma construção SQL não suportada for usada: pivot, unpivot, union, minus, intersect, except.
  • union all é suportado no modo incremental! Exceto em alguns casos extremos.
• Cláusulas partion_by diferentes forem usadas em várias window functions.
• Mais de 5% dos dados forem alterados. Essa merece atenção redobrada!
• Operadores de subconsulta como in, any, all, exists forem usados.

Há várias outras limitações às quais vale ficar atento! Acima, listei só as que você provavelmente vai enfrentar com mais frequência. Para a explicação completa das limitações de Refresh Incremental em Dynamic Tables, leia esta página.

O auto refresh pode ser uma boa escolha… mas…

Se você criar uma Dynamic Table com incremental e, em algum momento, o Snowflake não conseguir atualizá-la de forma incremental por causa de uma das limitações documentadas, o refresh vai falhar. E vai falhar em silêncio, a menos que você tenha montado algum mecanismo de alerta para monitorar os refreshes. Para evitar essa dor de cabeça, você pode usar auto e deixar o Snowflake fazer um full refresh quando necessário. Depois, monitore suas dynamic tables para acompanhar como o Snowflake está lidando com os refreshes.

O Snowflake, porém, faz uma ressalva sobre o modo auto:

Para um comportamento consistente, defina explicitamente o refresh mode em todas as tabelas de produção. O comportamento de AUTO pode mudar entre releases do Snowflake, o que pode causar variações inesperadas de performance em pipelines de produção.

A aba Data no Snowsight

A forma mais fácil de monitorar Dynamic Tables é pela interface do Snowsight.

Na aba Data, na barra lateral esquerda do Snowsight, navegue até a sua dynamic table. Clique nela e depois na aba Refresh History.

Algumas coisas super úteis para destacar:

• A seção Lag Metrics é bem prática. Aqui você consegue ver se o lag já ultrapassou o target lag, quão atuais estão os dados e o maior lag já registrado na tabela.
• Na seção de histórico de refresh, do lado direito, você encontra um link para o query profile do refresh. (O ícone do raio dentro de uma casa.) Isso é super útil para investigar refreshes demorados!

Para monitoramento automatizado de falhas de refresh, veja nossos artigos sobre como enviar alertas para o Slack e o Microsoft Teams.

A aba Monitoring no Snowsight

Além de monitorar DAGs individuais selecionando qualquer dynamic table na aba Data, você também pode ver o status de todas as dynamic tables em um só lugar pela aba "Monitoring".

Aqui, você pode ordenar por "Refresh Status" para encontrar refreshes que falharam.

O Snowflake oferece várias propriedades que podem ser atualizadas via alter dynamic table. Veja a lista completa, direto da documentação do Snowflake:

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> { SUSPEND | RESUME }

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> RENAME TO <new_name>

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> SWAP WITH <target_dynamic_table_name>

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> REFRESH [ COPY SESSION ]

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> { clusteringAction }

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> { tableColumnCommentAction }

ALTER DYNAMIC TABLE <name> { SET | UNSET } COMMENT = '<string_literal>'

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> dataGovnPolicyTagAction

Expandir código

Exemplos: como alterar uma Dynamic Table

-- Suspend the dynamic table / turn of automatic updates
alter dynamic table my_dynamic_table suspend

-- turn auto updates back on
alter dynamic table my_dynamic_table resume

-- update target lag
alter dynamic table my_dynamic_table set target_lag = '2 Days'

-- change target_lag from time interval to downstream
alter dynamic table my_dynamic_table set target_lag = 'downstream'

-- change refresh_mode from incremental to full
alter dynamic table my_dynamic_table set refresh_mode = 'full'

Expandir código

Editando o SQL de uma Dynamic Table

Vale destacar que você não pode adicionar uma coluna nem alterar o SQL pelo comando alter; é preciso substituir a tabela inteira. Isso pode ser feito com drop table my_dynamic_table e em seguida recriando a tabela. Ou você pode usar create or replace no DDL: create or replace dynamic table my_dynamic_table...

Vamos criar um exemplo simples para todo mundo conseguir acompanhar. Aqui, vamos copiar os dados de amostra do banco snowflake_sample_data. (Infelizmente, não dá para clonar nem criar dynamic tables em cima de dados de um share, então vamos só duplicar os dados e depois criar as dynamic tables.)

Veja uma imagem do DAG que vamos criar:

Setup

Vamos criar alguns objetos novos para que todos partam do mesmo ponto.

use role sysadmin;

create warehouse example_wh_xs
warehouse_size = xsmall
auto_suspend = 60
auto_resume = true;

use role securityadmin;

create role example_role;
grant role example_role to user jeff; -- swap in your user
grant all on warehouse example_wh_xs to role example_role;

use role sysadmin;
create database dynamic_demo;

Expandir código

Agora vamos copiar alguns dados de amostra para o nosso novo banco. (De novo: clone não funciona!)

use schema dynamic_demo.sample_data;

create or replace table orders as
select * from
snowflake_sample_data.tpch_sf10.orders;

create or replace table customer as
select * from
snowflake_sample_data.tpch_sf10.customer;

Agora vamos criar duas dynamic tables: uma para fazer o join das nossas duas novas tabelas e outra para agregar os dados. A primeira dynamic table do DAG terá target lag downstream. A segunda terá target lag de 1 dia. Isso vai controlar o lag de todo o DAG.

create or replace dynamic table obt_orders
target_lag = 'downstream'
warehouse = 'EXAMPLE_WH_XS'
refresh_mode = 'incremental'
as
select *
from orders join customer
on orders.o_custkey = customer.c_custkey;

create or replace dynamic table current_month_fulfillments
target_lag = '1 day'
warehouse = 'EXAMPLE_WH_XS'
refresh_mode = 'incremental'
as
with current_month as (

Expandir código

Alterando os dados de origem e rodando o pipeline

A tabela current_month_fulfillments deve estar vazia, porque nenhum pedido tem status F ou P naquele mês. (Confira a cláusula where da nossa consulta acima.)

Agora vamos atualizar os dados brutos e refazer manualmente o refresh da tabela current_month_fulfillments:

update orders set o_orderstatus = 'F'
where date_trunc('month', o_orderdate) = '1998-08-01';
-- yes, the max order month in the data is august 1998!
-- 12,466 rows updated

-- manually refresh the last table in the DAG so we don't have to wait:
alter dynamic table current_month_fulfillments refresh;

Podemos ver que a primeira tabela foi atualizada automaticamente, porque estava configurada como target_lag='downstream':

Vemos que 12,5 mil linhas foram excluídas e reinseridas.

Ao verificar a segunda tabela do DAG, vemos que 5 linhas foram inseridas:

Clique na aba "Graph", que mostra uma ótima visão de todas as tabelas do DAG. As dynamic tables exibem um status: "Succeeded" em verde ou "Failed" em vermelho.

Limpeza

drop database dynamic_demo;
use role sysadmin;
drop warehouse example_wh_xs;
use role securityadmin;
drop role example_role;

Criar uma dynamic table no dbt é tão simples quanto adicionar esta configuração ao seu arquivo .sql de sempre:

{{ config(
    materialized="dynamic_table",
    on_configuration_change="apply" | "continue" | "fail",
    target_lag="downstream" | "<integer> seconds | minutes | hours | days",
    snowflake_warehouse="<warehouse-name>",
    refresh_mode="AUTO" | "FULL" | "INCREMENTAL",
    initialize="ON_CREATE" | "ON_SCHEDULE",

) }}

Ou em um arquivo de propriedades:

version: 2

models:
  - name: [<model-name>]
    config:
      materialized: dynamic_table
      on_configuration_change: apply | continue | fail
      target_lag: downstream | <time-delta>
      snowflake_warehouse: <warehouse-name>
      refresh_mode: AUTO | FULL | INCREMENTAL
      initialize: ON_CREATE | ON_SCHEDULE

Como funciona no dbt

Na primeira execução do dbt, ele cria a dynamic table. Nas execuções seguintes, identifica que a tabela já existe e pula. A tabela só é atualizada pelo target_lag, não por rodar o dbt.

Como mencionado antes, o Snowflake não suporta alterar o SQL de uma dynamic table. Por isso, qualquer mudança na definição do seu model exige um --full-refresh.

Rodar o dbt com --full-refresh vai dropar e recriar a dynamic table.

Comparando Dynamic Tables no dbt vs. dbt Incremental

Dynamic Tables:

• O mecanismo de refresh é gerenciado pelo Snowflake, e não pelo dbt.
• Declarativo: você só define o select, não a lógica incremental.

Incremental Models:

• O refresh é gerenciado pelo dbt ou por quem orquestra o dbt.
• Imperativo: você precisa definir a lógica incremental customizada.
• Use quando precisar de controle mais fino sobre como uma tabela é atualizada incrementalmente.

As dynamic tables têm várias limitações. Para a lista completa, recomendo consultar a documentação. Mas aqui estão as que você provavelmente vai encontrar com mais frequência:

• Dynamic tables não podem estar a jusante de materialized views, external tables ou streams.
• Você não pode criar uma dynamic table temporária.
• A documentação do Snowflake menciona que você não pode truncar uma dynamic table. Mas deixa de mencionar que você não pode fazer nenhuma operação DML. Insert, Update e Delete vão falhar em uma dynamic table. Isso faz sentido, já que uma dynamic table precisa acompanhar as fontes subjacentes e a definição SQL.
• Você não pode definir o parâmetro DATA_RETENTION_TIME_IN_DAYS das suas tabelas de origem como zero. Isso ocorre porque changes no Snowflake depende do time travel. O time travel precisa estar habilitado.
• O target lag precisa ser menor que o data_retention_time_in_days das tabelas a montante.
• Você não pode usar SQL dinâmico (session variables) em dynamic tables.
• Operações em dynamic tables não são capturadas pela view access_history do Snowflake.
• Você não pode usar sequences. Por exemplo, a definição SQL da dynamic table não pode conter: select my_sequence.nextval
• Você não pode usar sample ou tablesample na definição da dynamic table.
• Dynamic tables incrementais clonadas podem fazer full-refresh ao serem inicializadas.

Aqui vão minhas principais recomendações para considerar ao usar Dynamic Tables:

• Use o maior target lag possível para o seu caso de uso. Isso ajuda a reduzir custos de computação, minimizando o número de vezes que suas tabelas serão recalculadas (atualizadas).
• Encadear dynamic tables é recomendado. Isso permite criar pipelines compostos inteiramente por dynamic tables e views, deixando o Snowflake gerenciar o refresh.
• Defina target lag downstream em todas as tabelas, exceto na última do DAG.
  • Se você tiver vários nós folha, dá para usar uma tabela controladora para manter o tempo do target lag (e outras propriedades) em uma única tabela na sua conta. Exemplo aqui.
• Use dynamic tables transientes para reduzir o custo de armazenamento.
  • Use um time travel maior nas fontes.
• Mais boas práticas do time do Snowflake estão aqui.

Com dynamic tables, você é cobrado por três coisas principais:

1. Os custos de computação associados à atualização das tabelas
2. Os custos de armazenamento das próprias dynamic tables
3. Custos de cloud services associados aos refreshes, somente se ultrapassarem 10% dos seus custos diários de computação

Como monitorar os custos das Dynamic Tables?

O Snowflake recomenda usar um warehouse dedicado para monitorar o custo das dynamic tables; mas eu recomendaria não multiplicar warehouses.

Em vez disso, você pode usar uma ferramenta como o SELECT, que mostra automaticamente o custo de cada dynamic table e como ele evolui ao longo do tempo.

Você também consegue ver o custo de todo o seu DAG de dynamic tables, o que muitas vezes ajuda a detectar problemas maiores, como execuções com frequência excessiva.

Dynamic tables são uma ótima ferramenta para ter no seu arsenal de pipelines de dados. Os refreshes automáticos e a simplicidade de não precisar definir lógica incremental tornam as dynamic tables uma opção muito atrativa. Só fique atento às muitas limitações das dynamic tables, principalmente as que discutimos sobre refresh incremental, que são significativas.

Esperamos que você agora se sinta pronto para usar dynamic tables nos seus pipelines de dados! Adoraria saber sobre as suas experiências com dynamic tables.

Jeff é Consultor de Dados e Analytics, com mais de 15 anos de experiência em automatizar insights e usar dados para controlar processos de negócio. Do ponto de vista tecnológico, é especialista em Snowflake + dbt + Tableau. Do ponto de vista de negócios, tem experiência em Serviços Públicos, Ensaios Clínicos, Editorial, CPG e Manufatura. Entre em contato a qualquer momento: [email protected].