Snowflake Access History: 8 formas de auditar sua conta

O Access History traz 1 linha por query executada na sua conta. Para cada query, ele guarda várias colunas com informações sobre os objetos acessados e/ou modificados pela consulta.

Para começar, três colunas são úteis para localizar as queries que te interessam:

• query_id: o identificador único da query
• user_name: o usuário que executou a query
• query_start_time: quando a query começou

Se precisar de mais informações sobre a query, como a role usada na execução ou o warehouse em que ela rodou, dá para fazer um join entre o dataset do Access History e a view Snowflake Query History.

Quanto a quais objetos a query acessou, a view oferece duas colunas:

• direct_objects_accessed: um array JSON com os objetos de dados acessados diretamente pela query
• base_objects_accessed: um array JSON com os objetos de dados que a query acessa direta ou indiretamente (ou seja, as tabelas subjacentes que alimentam uma view)

Para objetos que a query modificou, há duas colunas:

• objects_modified: um array JSON que indica os objetos modificados pela query. Será preenchido para queries do tipo INSERT, UPDATE, MERGE, CREATE ou similares, que atualizam/inserem/excluem registros em uma tabela
• objects_modified_by_ddl: traz informações sobre operações DDL em um banco de dados, schema, tabela, view e/ou coluna.

Direct vs. Base Objects Accessed

Para entender a diferença entre direct e base objects accessed, veja a query abaixo, que acessa duas colunas de uma view chamada user_sales_summary:

select
	user_name,
	total_sales
from user_sales_summary

A coluna direct_objects_accessed teria 1 entrada referente ao acesso direto à view user_sales_summary, enquanto a coluna base_objects_accessed traria duas entradas, uma para cada tabela subjacente (users e sales) que alimenta a view.

Retenção de dados do Access History

Assim como em outras views de account usage, como a Snowflake Query History, o Snowflake guarda os dados dos últimos 365 dias.

O Access History está disponível para todos os clientes do Snowflake?

A view Access History só está disponível para clientes Snowflake nas edições Snowflake Enterprise ou superiores.

Agora que já vimos o básico, vamos aos exemplos práticos que você pode rodar na sua conta para responder a várias perguntas comuns.

A query abaixo mostra como encontrar todas as tabelas acessadas por um determinado usuário nos últimos 30 dias. Como a coluna base_objects_accessed é um array, precisamos usar um lateral join combinado com a table function flatten para transformar cada entrada do array em uma linha separada. Você vai ver esse padrão se repetir ao longo do post.

with
-- This will output 1 row per table accessed in a query
access_history_flattened as (
    select
        access_history.query_id,
        access_history.query_start_time,
        access_history.user_name,
        objects_accessed.value:objectName::text as object_name,
        objects_accessed.value:objectDomain::text as object_domain,
        objects_accessed.value:columns as columns_array

    from admin.audit.access_history_last_30d as access_history, lateral flatten(access_history.base_objects_accessed) as objects_accessed
    where
		    access_history.query_start_time > current_date - 30
)

Expand Code

Repare que filtro por object_domain='Table'. Você pode ajustar isso conforme a necessidade para responder a perguntas parecidas, como:

• Quais views o usuário acessou?
• Quais funções ele usou?

Para encontrar todas as tabelas acessadas em um schema específico, dá para reaproveitar o CTE access_history_flattened apresentado acima. O object_name no Access History sempre vem como nome totalmente qualificado, ou seja, no formato database_name.schema_name.table_name. Por isso, fazemos o parse desse nome para extrair o database e o schema, e depois filtramos como precisarmos:

with
-- This will output 1 row per table accessed in a query
access_history_flattened as (
    select
        access_history.query_id,
        access_history.query_start_time,
        access_history.user_name,
        objects_accessed.value:objectName::text as object_name,
        objects_accessed.value:objectDomain::text as object_domain,
        objects_accessed.value:columns as columns_array

    from admin.audit.access_history_last_30d as access_history, lateral flatten(access_history.base_objects_accessed) as objects_accessed
),
access_history_flattened_w_names as (
	select

Expand Code

Imagine que você quer identificar usuários que podem ter acessado dados sensíveis em uma tabela. Dá para usar a view access_history para listar rapidamente todos esses usuários:

with
-- This will output 1 row per table accessed in a query
access_history_flattened as (
    select
        access_history.query_id,
        access_history.query_start_time,
        access_history.user_name,
        objects_accessed.value:objectName::text as object_name,
        objects_accessed.value:objectDomain::text as object_domain,
        objects_accessed.value:columns as columns_array

    from admin.audit.access_history_last_30d as access_history, lateral flatten(access_history.base_objects_accessed) as objects_accessed
    where
	   access_history.query_start_time > current_date - 30 -- adjust as needed
),

Expand Code

Eu já escrevi antes sobre como identificar tabelas não utilizadas usando a view Access History. Confira aquele post para uma explicação detalhada. Aqui está o código que você pode usar:

with
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

    from snowflake.account_usage.access_history, lateral flatten(access_history.base_objects_accessed) as objects_accessed
),
table_access_history as (
	select

Expand Code

É fácil adaptar a query anterior para identificar views que não foram usadas nos últimos 30 dias. Basta trocar object_domain = 'Table' por object_domain = 'View':

with
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

    from snowflake.account_usage.access_history, lateral flatten(access_history.base_objects_accessed) as objects_accessed
),
table_access_history as (
	select

Expand Code

Até aqui, os exemplos olharam apenas para o acesso a tabelas e schemas. Dá para descer mais um nível e analisar o uso de colunas em uma tabela específica usando o array columns presente nos campos base/direct_objects_accessed. Com mais um lateral flatten, obtemos um dataset com 1 linha por coluna acessada em cada query (veja o CTE access_history_flattened_columns).

with
-- This will output 1 row per table accessed in a query
access_history_flattened as (
    select
        access_history.query_id,
        access_history.query_start_time,
        access_history.user_name,
        objects_accessed.value:objectName::text as object_name,
        objects_accessed.value:objectDomain::text as object_domain,
        objects_accessed.value:columns as columns_array

    from admin.audit.access_history_last_30d as access_history, lateral flatten(access_history.base_objects_accessed) as objects_accessed
    where
	   access_history.query_start_time > current_date - 30 -- adjust as needed
),

Expand Code

Partindo do exemplo anterior, fica fácil identificar quais usuários acessaram uma coluna específica:

with
-- This will output 1 row per table accessed in a query
access_history_flattened as (
    select
        access_history.query_id,
        access_history.query_start_time,
        access_history.user_name,
        objects_accessed.value:objectName::text as object_name,
        objects_accessed.value:objectDomain::text as object_domain,
        objects_accessed.value:columns as columns_array

    from admin.audit.access_history_last_30d as access_history, lateral flatten(access_history.base_objects_accessed) as objects_accessed
    where
	   access_history.query_start_time > current_date - 30 -- adjust as needed
),

Expand Code

Ao investigar por que ou como uma tabela mudou, pode ser útil identificar rapidamente as queries ou os usuários que mexeram no objeto. Ou talvez você queira saber com que frequência uma tabela é atualizada. Usando uma abordagem parecida com as anteriores, conseguimos identificar todas as queries que modificaram uma tabela aplicando o flatten na coluna objects_modified:

with
-- This will output 1 row per table accessed in a query
access_history_flattened as (
    select
        access_history.query_id,
        access_history.query_start_time,
        access_history.user_name,
        objects_modified.value:objectName::text as object_name,
        objects_modified.value:objectDomain::text as object_domain

    from admin.audit.access_history_last_30d as access_history, lateral flatten(access_history.objects_modified) as objects_modified
    where
	   access_history.query_start_time > current_date - 30 -- adjust as needed
)
select

Expand Code

Ian Whitestone·Co-founder & CEO da SELECT

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