Documentation
Toggle Dark/Light/Auto modeToggle Dark/Light/Auto modeToggle Dark/Light/Auto modeBack to homepage

Example Labels

Basic Labels

These are example labels that make use of conditionals on columns in the enriched query history table (REPORTING.ENRICHED_QUERY_HISTORY). This is SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY augmented with computed cost.

ConditionEffect
BYTES_SPILLED_TO_LOCAL_STORAGE > 0 OR BYTES_SPILLED_TO_REMOTE_STORAGE > 0All queries that spilled to disk
COMPILATION_TIME > 1000Queries that took a long time to compile
QUERY_ACCELERATION_BYTES_SCANNED > 0Queries that were accelerated
COST = 0 OR COST IS NULLFree queries
COST > 1Queries that cost more than $1
QUERY_TYPE = ‘CALL’Stored Procedures
query_type in (‘CREATE_TABLE_AS_SELECT’. ‘INSERT’)CTAS or Insert queries
coalesce(partitions_scanned, 0)/coalesce(partitions_total, 1) > 0.95Queries that read 95% of the input tables (ie full table scans)

QTag Labels

We have populated two columns in ENRICHED_QUERY_HISTORY with qtag information. The first is called qtag and it is the direct output of the qtag udf. The second is called qtag_filter and this is a transformed version of the qtag that makes it more amenable to writing conditions against. Here are some examples:

ConditionEffect
tools.qtag(qtag_filter, ‘dbt’, ‘dbt_version’, ‘1.2’)Exactly match a qtag source, key and value. This example will find all dbt queries run with version 1.2
tools.qtag_exists(qtag_filter, ‘dbt’, ‘dbt_version’)Find all qtag-ed queries with the given source and key, regardless of value
tools.qtag_matches(qtag_filter, ‘dbt’, dbt_version’, ‘1..*’)Regex match against the value of the given qtag. This uses rlike underneath and carries all the same conditions/parameters

You can see all the functions live in the TOOLS schema. There are lots more there, run show functions like 'QTAG%'; to see them all.

SqlGlot Conditions

We have embedded the sqlglot library into the the Sundeck Native App app. This means you can use it to extract semantic information from the SQL text in ENRICHED_QUERY_HISTORY with a python UDF. Currently we only expose [one function](https://github.com/sundeck-io/the Sundeck Native App/blob/main/bootstrap/031_sqlglot.sql) to return a list of fully qualified tables but it is very easy to add your own either directly or by contributing on github.

An example condition to check if LINEITEMS from TPCH is being used in a query would be: tools.table_contains(query_text, 'SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.LINEITEMS').