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
- Sequences: unique, not gap free, 64-bit range, no
CURRVAL,NOORDERfor 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.
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:
| Point | Fact |
|---|---|
| Gaps | Values are not guaranteed gap free or contiguous |
| Uniqueness | Unique across sessions and concurrent statements, as long as the step does not switch between positive and negative |
| Ordering | ORDER 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 |
| Range | A 64-bit signed integer range; exceeding it fails the query |
CURRVAL | Not supported |
| Several uses | Each seq.NEXTVAL in a statement gives a distinct value |
| Column default | A sequence can be a column DEFAULT; dropping the sequence later breaks inserts that rely on it |
| Step change | ALTER 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.
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_STRINGorRANDOM, no external functions and no hybrid tables. - The role has the privileges on all tables used. For
SHOWthe 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.
| Cache | Lives in | Cleared when |
|---|---|---|
| Persisted result | Cloud services | After 24 hours without reuse, or 31 days |
| Warehouse local cache | Warehouse | Warehouse suspends or scales down |
Cancelling statements
| Goal | Command | Who can run it |
|---|---|---|
| One query | SELECT 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 session | SELECT SYSTEM$CANCEL_ALL_QUERIES(<session_id>); | Own session, or ownership of the user, or OPERATE or ownership on the warehouse |
| All queries of one user | ALTER USER <name> ABORT ALL QUERIES; | A role with ownership on the user or higher |
| All queries on a warehouse | ALTER WAREHOUSE <name> ABORT ALL QUERIES; | Role with OPERATE or ownership on the warehouse |
| A query run by a task | SYSTEM$CANCEL_QUERY | Task 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.
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:
| Role | Sees |
|---|---|
| Any user | Their own queries |
ACCOUNTADMIN | All queries in the account |
Role with MONITOR or OPERATE on a warehouse | Queries other users ran on it |
IMPORTED PRIVILEGES on the SNOWFLAKE database | Account 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,NOORDERfor 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.