Monitorando custo e performance de modelos dbt com metadados

O dbt disparou em popularidade nos últimos 5 anos e se tornou o framework mais usado para construir e gerenciar modelos de dados dentro do data warehouse. Só que executar muitas transformações em grandes volumes de dados dentro do warehouse não sai barato. Seja com dbt ou com qualquer outra ferramenta de transformação baseada em SQL, os custos dessas transformações costumam representar uma fatia relevante do gasto com compute dos clientes Snowflake.

Conforme as empresas buscam entender, monitorar e reduzir o gasto na data cloud, ficou cada vez mais importante ter visibilidade sobre o custo de cada modelo dbt. Além disso, como o dbt vem sendo usado para sustentar aplicações e decisões críticas para o negócio, monitorar a performance dos modelos virou pré-requisito para garantir o cumprimento dos SLAs.

Snowflake e dbt não entregam esses recursos essenciais de monitoramento prontos. Mas, ao adicionar metadados aos modelos dbt via query tags ou comentários, dá para conquistar essas capacidades centrais de monitoramento.

No nosso post sobre query tags, descrevemos as três opções para definir query tags no dbt:

1. Definir globalmente no seu profiles.yml
2. Adicionar uma query_tag para cada modelo no dbt_project.yml ou na config do modelo
3. Criar uma macro set_query_tag para definir dinamicamente a query tag de cada modelo do projeto

A opção 3 é, disparado, a melhor, porque elimina a necessidade de definir as tags manualmente. Se quiser começar a definir query tags de forma dinâmica para cada modelo, você pode implementar uma macro customizada como esta aqui para adicionar metadados detalhados a cada query emitida pelo dbt.

Para metadados de dbt, recomendamos usar query comments em vez de query tags, já que os metadados gerados automaticamente podem, em alguns casos, ultrapassar o limite de 2.000 caracteres das query tags.

O dbt traz essa configuração de forma nativa. No seu dbt_project.yml, basta adicionar o seguinte:

1query-comment: append: true # Snowflake removes prefixed comments

Isso adiciona um query comment no final da query:

1create view analytics.analytics.orders as ( select ... ); /* {"app": "dbt", "dbt_version": "0.15.0rc2", "profile_name": "debug", "target_name": "dev", "node_id": "model.dbt2.my_model"} */

Para incluir metadados mais completos no query comment, você pode instalar o nosso pacote dbt-snowflake-monitoring. Ele disponibiliza os seguintes metadados para todas as queries do dbt:

1{ "dbt_snowflake_query_tags_version": "2.0.0", "app": "dbt", "dbt_version": "1.4.0", "project_name": "my_project", "target_name": "dev", "target_database": "dev", "target_schema": "larry_goldings", "invocation_id": "c784c7d0-5c3f-4765-805c-0a377fefcaa0", "node_name": "orders", "node_alias": "orders", "node_package_name": "my_project", "node_original_file_path": "models/staging/orders.sql", "node_database": "dev", "node_schema": "mart", "node_id": "model.my_project.orders", "node_resource_type": "model", "materialized": "incremental", "is_incremental": true, "node_refs": [ "raw_orders", "product_mapping" ], "dbt_cloud_project_id": "146126", "dbt_cloud_job_id": "184124", "dbt_cloud_run_id": "107122910", "dbt_cloud_run_reason_category": "other", "dbt_cloud_run_reason": "Kicked off from UI by [email protected]", }

Com essas informações, dá para monitorar custo e performance por várias dimensões interessantes, como projeto dbt, nome do modelo, ambiente (dev ou prod), tipo de materialização e muito mais.

Com query tags, você consegue monitorar a performance dos seus modelos dbt usando uma variação da query abaixo:

1select date_trunc('day', start_time) as date, try_parse_json(query_tag)['model_name']::string as model_name, count(distinct try_parse_json(query_tag)['invocation_id']) as num_executions, avg(total_elapsed_time/1000) as avg_total_elapsed_time_s, approx_percentile(total_elapsed_time/1000, 0.95) as p95_total_elapsed_time_s, avg(execution_time/1000) as avg_execution_time_s, approx_percentile(execution_time/1000, 0.95) as p95_execution_time_s -- optionally repeat for other query time metrics, like: -- compilation_time, queued_provisioning_time, queued_overload_time, etc. from snowflake.account_usage.query_history where try_parse_json(query_tag)['app']::string = 'dbt' and start_time > current_date - 30 group by 1,2

Se estiver usando query comments, antes é preciso extrair os metadados do texto do comentário:

1with query_history as ( select *, regexp_substr(query_text, '/\\*\\s({"app":\\s"dbt".*})\\s\\*/', 1, 1, 'ie') as _dbt_json_meta, try_parse_json(_dbt_json_meta) as dbt_metadata from snowflake.account_usage.query_history ) select date_trunc('day', start_time) as date, dbt_metadata['model_name']::string as model_name, count(distinct dbt_metadata['invocation_id']) as num_executions, avg(total_elapsed_time/1000) as avg_total_elapsed_time_s, approx_percentile(total_elapsed_time/1000, 0.95) as p95_total_elapsed_time_s, avg(execution_time/1000) as avg_execution_time_s, approx_percentile(execution_time/1000, 0.95) as p95_execution_time_s -- optionally repeat for other query time metrics, like: -- compilation_time, queued_provisioning_time, queued_overload_time, etc. ... from query_history where dbt_metadata is not null and start_time > current_date - 30 group by 1,2

Se você usa o nosso pacote dbt-snowflake-monitoring, esse parsing do query comment é feito automaticamente.

As duas queries acima olham apenas para o tempo de execução. Algumas outras métricas que vale acompanhar em conjunto são:

• partitions_scanned e partitions_total: mostram se as queries estão fazendo um pruning eficiente das micro-partições
• bytes_scanned: dá uma noção de quanto dado está sendo processado ao longo do tempo, o que pode explicar aumentos no tempo de execução
• bytes_spilled_to_local_storage e bytes_spilled_to_remote_storage: indicam se o seu modelo pode se beneficiar de rodar em um warehouse maior
• queued_overload_time: indica se talvez seja preciso aumentar o max_cluster_count do warehouse

Para acompanhar os custos dos modelos dbt ao longo do tempo, primeiro calcule o custo de cada query do Snowflake. Depois, use esses custos para agregar o gasto por modelo:

1select date_trunc('day', start_time) as date, try_parse_json(query_tag)['model_name']::string as model_name, count(distinct try_parse_json(query_tag)['invocation_id']) as num_executions, sum(query_cost) as total_cost, total_cost / num_executions as avg_cost_per_execution from query_history_w_costs where try_parse_json(query_tag)['app']::string = 'dbt' and start_time > current_date - 30 group by 1,2

Se quiser ter automaticamente um query history com o custo de cada query já incluído, instale o nosso pacote dbt-snowflake-monitoring.

No SELECT, usamos query tags e comments para que os nossos clientes monitorem as queries emitidas pelo dbt por várias dimensões: ambiente, materialização, tipo de recurso e por aí vai. Essa visibilidade vale ouro, porque permite que os times de dados priorizem facilmente quais modelos dbt precisam de atenção extra. Veja abaixo um exemplo de como exibimos essas informações no produto:

Se você quer cortar seus custos com Snowflake, entender melhor o que está puxando esses custos pra cima ou simplesmente acompanhar tudo de perto, dá para começar hoje mesmo ou agendar uma demo nos links abaixo.

Ian Whitestone·Co-founder & CEO do SELECT

Ian é Co-founder & CEO do SELECT, uma plataforma SaaS de gestão e otimização de custos do Snowflake. Antes de fundar o SELECT, Ian passou 6 anos liderando times full stack de data science e engineering no Shopify e na Capital One. No Shopify, Ian liderou o trabalho de otimização do data warehouse e o aumento da observabilidade de custos.