Data and Object Management · Topic 10 of 11

Queries, Sequences, Persisted Results, Cancelling and Query History

Run queries, use sequences and know their limits, reuse persisted query results, cancel statements for one or many users and filter Query History.

  • 5 min
In a nutshell
  • Sequences: unique, not gap free, 64-bit range, no CURRVAL, NOORDER for concurrent speed.
  • Results persist 24 hours, extended on reuse, with a 31 day maximum; text must match exactly.
  • SYSTEM$CANCEL_QUERY, SYSTEM$CANCEL_ALL_QUERIES, ALTER USER ... ABORT ALL QUERIES, ALTER WAREHOUSE ... ABORT ALL QUERIES.

What it is

This topic covers everyday query work from the administrator's side: how queries run, how to generate unique numbers, when results are reused for free, how to stop a statement that should not be running, and how to find a query afterwards.

Run queryneeds active warehouseResult cached24 hoursReused by identical queryReview in Query HistoryCancel if runaway

How it works

Performing queries

Queries can be run in Snowsight, SnowSQL, the Snowflake CLI, drivers and connectors. A SELECT needs a current warehouse, a role with USAGE on the database and schema, and SELECT on the objects. SHOW and DESCRIBE use only cloud services and need no warehouse.

The pipe operator ->> chains statements so that one statement's result is the input of the next, and RESULT_SCAN with LAST_QUERY_ID() lets you query the output of an earlier statement such as SHOW.

SHOW WAREHOUSES;
SELECT "name", "size" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) WHERE "size" = 'Large';

Sequences

A sequence generates unique numbers, often for surrogate keys.

CREATE SEQUENCE order_seq START = 1 INCREMENT = 1 ORDER;
CREATE TABLE orders (id NUMBER DEFAULT order_seq.NEXTVAL, item STRING);
INSERT INTO orders (item) VALUES ('pen');

Limits and behavior to know:

PointFact
GapsValues are not guaranteed gap free or contiguous
UniquenessUnique across sessions and concurrent statements, as long as the step does not switch between positive and negative
OrderingORDER gives increasing values across statements; NOORDER drops that guarantee and can improve concurrent insert speed. NOORDER_SEQUENCE_AS_DEFAULT (default TRUE) makes NOORDER the default
RangeA 64-bit signed integer range; exceeding it fails the query
CURRVALNot supported
Several usesEach seq.NEXTVAL in a statement gives a distinct value
Column defaultA sequence can be a column DEFAULT; dropping the sequence later breaks inserts that rely on it
Step changeALTER SEQUENCE ... SET INCREMENT may not apply to the very next value

Cloning a table that uses a sequence default points to the cloned sequence only if the sequence is cloned in the same container; otherwise it still uses the source sequence.

Watch out

Do not use a sequence when the business needs numbers with no gaps, such as invoice numbers. Failed or rolled back statements leave gaps.

Persisted query results

When a query finishes, Snowflake keeps its result for 24 hours. Each reuse resets the 24 hours, but a result is purged 31 days after the original run. Reuse costs no warehouse compute.

A result is reused when:

  • The new query text matches exactly (case and aliases count).
  • The underlying data has not changed and micro-partitions were not reclustered because of other changes.
  • The query uses no non-reusable functions such as UUID_STRING or RANDOM, no external functions and no hybrid tables.
  • The role has the privileges on all tables used. For SHOW the role must be the one that ran it.

Control it with the USE_CACHED_RESULT parameter, set at account, user or session level. Turning it off is useful in performance tests so each run does real work. Meeting the conditions does not guarantee reuse.

CacheLives inCleared when
Persisted resultCloud servicesAfter 24 hours without reuse, or 31 days
Warehouse local cacheWarehouseWarehouse suspends or scales down

Cancelling statements

GoalCommandWho can run it
One querySELECT SYSTEM$CANCEL_QUERY('<query_id>');You for your own; others need ownership of the user, OPERATE or ownership on the warehouse, or ACCOUNTADMIN
Everything in a sessionSELECT SYSTEM$CANCEL_ALL_QUERIES(<session_id>);Own session, or ownership of the user, or OPERATE or ownership on the warehouse
All queries of one userALTER USER <name> ABORT ALL QUERIES;A role with ownership on the user or higher
All queries on a warehouseALTER WAREHOUSE <name> ABORT ALL QUERIES;Role with OPERATE or ownership on the warehouse
A query run by a taskSYSTEM$CANCEL_QUERYTask ownership or OPERATE on the task, or ACCOUNTADMIN

ALTER USER ... ABORT ALL QUERIES stops everything the user is running or has scheduled on any warehouse, but the user can sign in and run new queries. To also block login, set DISABLED = TRUE. Find query IDs, and the session that ran them, in Query History.

Exam tip

Several users blowing up one warehouse points to ALTER WAREHOUSE ... ABORT ALL QUERIES. One user with many runaway queries points to ALTER USER ... ABORT ALL QUERIES.

Query History filters

Query History in Snowsight shows the last 14 days. Filters include status (for example failed, queued or long running), user, time range, SQL text, query ID, warehouse, statement type, duration, session ID and query tag.

Two filters are easy to forget:

  • Client generated statements: internal statements issued by the web interface or tools; a filter controls whether they appear.
  • Queries executed by user tasks: runs started by tasks; select the filter to see them.

Visibility rules:

RoleSees
Any userTheir own queries
ACCOUNTADMINAll queries in the account
Role with MONITOR or OPERATE on a warehouseQueries other users ran on it
IMPORTED PRIVILEGES on the SNOWFLAKE databaseAccount level history views

For longer history use SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY (365 days, with some delay) or the INFORMATION_SCHEMA.QUERY_HISTORY table function (recent activity, no delay).

Common mistakes

Expecting gap-free sequences. They are unique, not contiguous.

Expecting a cache hit after a case change in the SQL text. The text must match exactly.

Benchmarking with result reuse on. Disable USE_CACHED_RESULT for fair tests.

Assuming any admin can cancel any session. For SYSTEM$CANCEL_ALL_QUERIES the docs say ACCOUNTADMIN is not necessarily granted the needed privileges, so check ownership of the user or OPERATE on the warehouse.

Looking for task runs in Query History without the task filter. Turn on queries executed by user tasks.

What to remember for the exam

  • Sequences: unique, not gap free, 64-bit range, no CURRVAL, NOORDER for concurrent speed.
  • Results persist 24 hours, extended on reuse, with a 31 day maximum; text must match exactly.
  • SYSTEM$CANCEL_QUERY, SYSTEM$CANCEL_ALL_QUERIES, ALTER USER ... ABORT ALL QUERIES, ALTER WAREHOUSE ... ABORT ALL QUERIES.
  • Query History covers 14 days and has filters for client generated and task queries.

Check yourself

  • Why can two sequence based IDs have a hole between them?

    Sequences do not guarantee gap free values; failed statements and caching leave gaps.

  • How long is a query result available for reuse?

    24 hours, reset on each reuse, with a maximum of 31 days from the original run.

  • How do you cancel every running query of one user but let them sign in again?

    ALTER USER <name> ABORT ALL QUERIES.

Topic 10 of 11 in Data and Object Management

Test yourself on Data and Object Management

The full Snowflake ADA-C02 question bank has 814 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.