CODEWITHSUNDEEP
snowflake-cloud-data-platform
bnsh
bnsh

Reputation: 840

Costless queries on Snowflake

I'm trying to track down Snowflake usage via the snowflake query_history table. I notice that for a few apps, there's near "100%" usage, if I track it by start_time and elapsed time. But, the queries are full of things like "select /* nodejs:heartbeat */ 1;" which I'm told don't actually cost anything. That's cool. But, how can I make my report know that they don't cost anything?

I noticed that this query:

select query_text from query_history where warehouse_size is null;

seems to get me all the queries that are "trivial"... "SELECT current_date", "select /* nodejs:heartbeat */ 1;", etc...

But, sometimes, it also gives me seemingly non-trivial selects: "select * from cst_usr_info where deleted_at is null and lower(usr_email) = ?" for instance...

I guess my questions are:

  1. Does "warehouse_size is null" mean that there is no cost to the query?
  2. If so, then is the reason that the "non-trivial" selects are costless because perhaps they've been cached or something?

Upvotes: 0

Views: 237

Answers (1)

Mike Walton
Mike Walton

Reputation: 7369

  1. Yes, that is a great way to get all queries that relate to no warehouse being used (therefore, no cost).
  2. Your non-trivial queries are likely a query that was available in Snowflake's query result cache (meaning it was executed previously and the results are available without the need of a warehouse). There are other types of queries that might use metadata information that can be returned from the services layer without the need for a warehouse, like a MIN() or MAX() function, for example.

I hope that helps.

Upvotes: 3

Related Questions