Update dbt-snowflake anti-query patterns to reduce "cloud services cost" of Warehouse Consumption

[harshvardhan-j]

We started using dbt with snowflake and noticed an increase in our Warehouse Costs, not for the compute component but for the cloud costs components.

On digging deeper it seems that dbt repeatedly queries Snowflake with queries that are marked as anti-patterns or have an alternative described here to optimize the cloud cost components.

Some queries that dbt issues repeatedly along with alternatives have been listed below

• Pattern: High-frequency INFORMATION_SCHEMA queries
• Pattern: High-frequency, simple queries
• Pattern: High-frequency SHOW commands (by data applications and third-party tools)

It would be nice if this can be incorporated into the adapter or if there are workarounds for this that can be shared.

[jeremyyeo]

dbt generally needs to run metadata queries because (just at the top of my head, but could be others):

1. It needs to know whether an table already exist or not, if not create, if exist, merge into (incrementals).
2. Expanding the data types of columns so that insert do not fail (e.g. existing column is char(10) but incoming data is char(20)).
3. Validating data types for contracted models.
4. Possibly others...

I'm not sure if it's possible to NOT run those metadata queries but also accomplish what we ask of dbt to do... (e.g. if we dont query information schema, then dbt will completely replace your incremental/snapshot every time we run dbt - which is not generally something we want).

[harshvardhan-j]

Thanks for responding @jeremyyeo .

I think the problem is not to avoid the dbt metadata queries, but use the alternatives suggested where applicable.

For example, instead of querying the information_schema maybe leverage the account_usage schema.

Alternatively, cache where applicable. Not quite sure if these approaches are viable given dbt's internals but they can be explored.

[jeremyyeo]

All good. This is a worth discussion for sure. 😊

Fwiw - some alternatives may not be tenable - for example the account usage view may have a latency of up to 3 hours... https://docs.snowflake.com/en/sql-reference/account-usage#differences-between-account-usage-and-information-schema

Let's say you have a dbt job running every 30 minutes, this means if the account usage view returns a "cached/stale/delayed result" (say we take the suggestion and switch to the account usage from info schema) - we may cause dbt to issue incorrect ddl/dml which causes incorrect data to materialize (which I'd argue is worse off than have some more cost associated with info schema querying).

As always, with these things - devils in the details... 😬