Como identificar tabelas não utilizadas no Snowflake

A Access History é uma view do schema Account Usage do Snowflake Database, disponível para todas as contas Snowflake na edição Enterprise ou superior. Ela permite consultar quais objetos do Snowflake (tabelas, views e colunas) foram acessados por cada query, direta ou indiretamente.

Direct versus Base Objects Accessed

Para identificar quais colunas foram acessadas por uma query, há duas colunas que interessam: direct_objects_accessed e base_objects_accessed. A principal diferença entre elas está na forma como tratam views. Veja a definição de view a seguir:

create or replace view orders_view as (
	select *
	from orders
	where
		not test
		and success
);

A query select * from orders_view acessa diretamente o objeto orders_view e, indiretamente, a tabela base orders. Por isso, orders_view vai aparecer na coluna direct_objects_accessed do access_history, enquanto orders aparece em base_objects_accessed.

Na hora de decidir se uma tabela está em desuso, vale usar base_objects_accessed, já que ela leva em conta as queries que acessam a tabela indiretamente por meio de uma view.

Fazendo o parsing de base_objects_accessed

base_objects_accessed é um array JSON com todos os objetos de dados base acessados durante a execução da query. Veja um exemplo do conteúdo da coluna, retirado da documentação:

1[\
\
2  {\
\
3    "columns": [\
\
4      {\
\
5        "columnId": 68610,\
\
6        "columnName": "CONTENT"\
\
7      }\
\
8    ],\
\
9    "objectDomain": "Table",\
\
10    "objectId": 66564,\
\
11    "objectName": "GOVERNANCE.TABLES.T1"\
\
12  }\
\
13]

O array de objetos acessados por cada query pode ser transformado em uma linha por objeto com lateral flatten e, em seguida, filtrado para considerar apenas objetos do tipo tabela, como no exemplo abaixo:

with
access_history as (
    select *
    from snowflake.account_usage.access_history
),
access_history_flattened as (
    select
        access_history.query_id,
        access_history.query_start_time,
        access_history.user_name,
        objects_accessed.value:objectId::integer as table_id,
        objects_accessed.value:objectName::text as object_name,
        objects_accessed.value:objectDomain::text as object_domain,
        objects_accessed.value:columns as columns_array

Expandir código

Com o access_history "achatado" da query acima, dá para descobrir o momento exato em que uma tabela foi acessada pela última vez, além do usuário que executou a query:

with
access_history as (
    select *
    from snowflake.account_usage.access_history
),
access_history_flattened as (
    select
        access_history.query_id,
        access_history.query_start_time,
        access_history.user_name,
        objects_accessed.value:objectId::integer as table_id,
        objects_accessed.value:objectName::text as object_name,
        objects_accessed.value:objectDomain::text as object_domain,
        objects_accessed.value:columns as columns_array

Expandir código

Ao identificar tabelas não utilizadas para excluir, ajuda muito ver os custos de armazenamento envolvidos. Com a view de Account Usage table_storage_metrics e considerando uma taxa de armazenamento de US$ 23 por terabyte por mês, dá para calcular o custo anual de armazenamento de cada tabela:

select
    id as table_id,
    table_catalog || '.' ||table_schema ||'.' || table_name as fully_qualified_table_name,
    (active_bytes + time_travel_bytes + failsafe_bytes + retained_for_clone_bytes)/power(1024,4) as total_storage_tb,
    -- Considera uma taxa de armazenamento de US$ 23/TB/mês
    -- Ajuste para o valor adequado conforme o seu contrato Snowflake
    total_storage_tb*12*23 as annualized_storage_cost
from snowflake.account_usage.table_storage_metrics
where
    not deleted

Até aqui, vimos como descobrir quando uma tabela foi acessada pela última vez e os custos de armazenamento de cada uma. Agora dá para juntar essas peças e identificar todas as tabelas que não foram consultadas nos últimos 90 dias, mostrando a economia anual prevista caso elas sejam excluídas.

O SQL abaixo depende da view account_usage.access_history, disponível apenas para clientes Snowflake na edição Enterprise ou superior.

Se você usa dbt, vale conferir a versão alternativa deste SQL, que roda bem mais rápido.

with
access_history as (
    select *
    from snowflake.account_usage.access_history
),
access_history_flattened as (
    select
        access_history.query_id,
        access_history.query_start_time,
        access_history.user_name,
        objects_accessed.value:objectId::integer as table_id,
        objects_accessed.value:objectName::text as object_name,
        objects_accessed.value:objectDomain::text as object_domain,
        objects_accessed.value:columns as columns_array

Expandir código

Consultar e achatar a view access_history pode ser bem lento pelo volume de dados a processar. Para queries mais rápidas sobre o histórico de acesso às tabelas, recomendamos materializar esses dados de forma incremental com o nosso pacote dbt open-source: dbt_snowflake_monitoring. Depois de instalado o pacote, as queries para identificar tabelas não utilizadas ficam muito mais simples. O código acima pode ser reescrito assim:

with
table_access_summary as (
    select
        table_id,
        max(query_start_time) as last_accessed_at,
        max_by(user_name, query_start_time) as last_accessed_by,
        max_by(query_id, query_start_time) as last_query_id
    from query_base_table_access
    group by 1
),
table_storage_metrics as (
	select
      id as table_id,
      table_catalog || '.' ||table_schema ||'.' || table_name as fully_qualified_table_name,
      (active_bytes + time_travel_bytes + failsafe_bytes + retained_for_clone_bytes)/power(1024,4) as total_storage_tb,

Expandir código

Na hora de decidir se uma tabela deve ser excluída, pode ser útil saber quando ela foi atualizada pela última vez por uma operação DDL ou DML. A query abaixo mostra como encontrar todas as tabelas atualizadas na última semana usando a view tables do Account Usage:

select
    table_id,
    table_catalog||'.'||table_schema||'.'||table_name as fully_qualified_table_name,
    last_altered as last_altered_at
from snowflake.account_usage.tables
where
    last_altered > current_date - 7