What you will be able to do
- Generate unique values with seq_name.NEXTVAL, GETNEXTVAL and sequence-backed column defaults
- Name the limitations of Snowflake sequences: gaps, ordering, sign changes, range overflow and no currval
- Predict whether Snowflake will reuse a persisted query result, and post-process results with RESULT_SCAN
- Cancel running statements for one session or for a user, by SQL or through the client interface
- Cancel statements for a single user or for multiple users at once, and name the privileges needed to cancel another user's or a task's statements
Key concept
Uniqueness without contiguity — A Snowflake sequence promises unique values across sessions and concurrent statements. It does not promise that those values are gap-free or assigned in any particular order, and that trade-off explains most of the sequence limitations on the exam.
1.What a sequence guarantees, and what it doesn't
A sequence is a schema object that generates unique numbers across sessions and statements, including statements that run at the same time. You typically use one to fill a primary key or any other column that needs a unique value. Within a single query every value is distinct, and two concurrent queries never get the same value.
That is the whole guarantee. Snowflake does not promise that the numbers are contiguous. Each generated value also reserves a range of numbers that depends on the step (the interval): with a step of 10, generating 100 reserves 100 to 109. Snowflake can also work out the next value as soon as the current one is used, so an ALTER SEQUENCE ... SET INCREMENT might not affect the very next operation.
These are the limitations to know:
- Sign changes break uniqueness. Values are globally unique only while the sign of the interval stays the same.
- Ordering is conditional. A later statement gets values higher than an earlier one only if the sequence doesn't have the NOORDER property and the earlier statement completed and was acknowledged first. NOORDER can speed up concurrent inserts, but then the values can come back as, for example, 1, 3, 101, 5, 103. The only way to assign values in a specific row order is to use single-row statements, and even then gaps are possible.
- The range is finite. Values are 64-bit two's complement integers. If the next internal value goes past that range, the query fails and those values can be lost. Because of gaps, this can happen even when every value you've seen is within range. The fix is a smaller increment or a new sequence with a smaller start value.
- There is no currval. Other databases use currval to reuse the key they just generated when writing related rows. Snowflake leaves it out and recommends bulk patterns such as a multi-table INSERT instead.
Checkpoint 1 of 7· Check yourself
A sequence was created with NOORDER and is used by many clients inserting at the same time. Which statement is accurate?
NOORDER gives up increasing order, not uniqueness. Snowflake uses it to improve performance for concurrent INSERTs, and gaps are never ruled out.
“NOORDER specifies that the values are not guaranteed to be in increasing order.”Source: docs.snowflake.com
Checkpoint 2 of 7· Exam question
An ETL team loads order rows with `INSERT INTO orders (id, item) SELECT seq1.NEXTVAL, item FROM staging;` from several concurrent sessions. Auditors notice that `id` values contain holes and do not always ascend in load order. The team asks for the MOST accurate explanation of this behavior.
Correct answer: A — Sequences guarantee unique values but not gap-free or ordered numbers, so skipped values under concurrent statements are expected.
- A. Correct: Snowflake guarantees uniqueness of generated values but explicitly does not guarantee gap-free or ordered numbers, especially under concurrency, so holes are normal.
- B. Incorrect: ORDER is the property that requests ordered values and it is not the cause of holes; the claim about per-cluster private blocks is not how sequences behave.
- C. Incorrect: sequence values are never rolled back by failed statements; consumed values are simply lost, which is a source of gaps rather than a partial rollback.
- D. Incorrect: Time Travel retention of old rows has no effect on sequence value consumption, so it cannot explain the missing ids.
Sources1
2.Referencing a sequence: NEXTVAL, GETNEXTVAL and column defaults
In a query, you reference a sequence as seq_name.NEXTVAL. Unlike in many other databases, each occurrence of NEXTVAL generates its own set of values. Referencing it twice in the same row gives you two different numbers:
CREATE OR REPLACE SEQUENCE seq1; SELECT seq1.NEXTVAL a, seq1.NEXTVAL b FROM DUAL;To put the same generated value in two columns, generate it once in a nested subquery and then reference that alias twice. Each extra shared reference needs another level of nesting, which gets verbose. To avoid that, Snowflake provides the GETNEXTVAL table function. It's a special one-row table function that joins a unique value to each row. You must give it an alias, because its output is read through an attribute named NEXTVAL on that alias. Its position in the FROM clause also matters: values are generated over the join of all the objects listed before it, so Snowflake recommends putting it at the end of the FROM clause.
CREATE OR REPLACE SEQUENCE seq1; CREATE OR REPLACE TABLE foo (n NUMBER); INSERT INTO foo VALUES (100), (101), (102); SELECT n, s.nextval FROM foo, TABLE(GETNEXTVAL(seq1)) s;The simplest way to generate primary keys is to make a sequence the column's default, as in k NUMBER DEFAULT seq1.NEXTVAL. If you leave the column out of an INSERT, or set it to DEFAULT, Snowflake generates a new value. If you supply an explicit value, Snowflake stores that value instead. One sequence can be the default for several columns and several tables. If you drop a sequence that a column default still references, inserts and updates that rely on the default fail with an error that the identifier can't be found.
Checkpoint 3 of 7· Fill the gap
This query should put the SAME generated value in columns a and b. Which token completes it?
CREATE OR REPLACE SEQUENCE seq1; SELECT seqRef.a a, seqRef.a b FROM (SELECT seq1. ? a FROM DUAL) seqRef;The subquery generates one value with NEXTVAL, and the outer query reads that alias twice. Snowflake has no CURRVAL, and GETNEXTVAL is a table function that you call in the FROM clause.
Source: docs.snowflake.comSources1
3.Persisted query results: when Snowflake reuses an answer
Snowflake persists (caches) the result of every query for a period of time. If you repeat a query and nothing relevant has changed, Snowflake skips execution and returns the stored result. The cache expires after 24 hours for results of any size. Each reuse restarts that 24-hour period, up to a maximum of 31 days from the time the query first ran. After that, the result is purged and the next run generates a new one. Large results (over 100 KB) are accessed with a security token that expires after 6 hours, and a new token can be retrieved while the result is still cached.
SELECT DISTINCT(severity) FROM weather_events; SELECT DISTINCT(severity) FROM weather_events; SELECT DISTINCT(severity) FROM weather_events we; select distinct(severity) from weather_events;| Condition | What prevents reuse |
|---|---|
| Exact query match | Different keyword case, or adding a table alias |
| Only reusable functions | UUID_STRING, RANDOM, RANDSTR |
| No external functions or hybrid tables | Calling an external function or selecting from a hybrid table |
| Data and micro-partitions unchanged | Changed table data, or micro-partitions that were reclustered or consolidated |
| Role privileges | SELECT: the role lacks access to the tables. SHOW: the role differs from the one that generated the result |
| Result still available | Result purged after expiry or after the 31-day maximum |
Even when every condition is met, Snowflake doesn't guarantee reuse. Reuse is on by default, and you can turn it off at the account, user or session level with the USE_CACHED_RESULT parameter.
Persisted results also let you post-process an earlier result. The RESULT_SCAN table function returns a previous query's result as a table, so you can filter output from statements that are otherwise hard to query, such as SHOW, DESCRIBE or CALL. The pipe operator (->>) does the same job without displaying the first result.
SELECT "schema_name", "name" as "table_name", "rows" FROM table(RESULT_SCAN(LAST_QUERY_ID())) WHERE "rows" = 0;Checkpoint 4 of 7· Check yourself
A query's persisted result has been reused every day since it first ran. When will Snowflake stop reusing it and generate a new result?
Each reuse restarts the 24-hour period, but only up to 31 days after the first execution. After that, the result is purged and regenerated.
“up to a maximum of 31 days from the date and time that the query was first executed.”Source: docs.snowflake.com
Checkpoint 5 of 7· Exam question
A developer migrating from PostgreSQL writes a Snowflake script that inserts a parent row using `seq_ord.NEXTVAL` and then fetches the value just used with `SELECT seq_ord.CURRVAL`. Select TWO statements about Snowflake sequences that apply to this design.(Select 2)
Correct answers: A, B — Snowflake has no `CURRVAL` equivalent, so the script fails and must capture the generated value in another way, such as through a table default.; Sequence values are 64-bit integers, so a query that exceeds the range fails rather than silently wrapping around to earlier numbers.
- A. Correct: Snowflake does not support CURRVAL, so a script that relies on it fails and must obtain the generated value some other way.
- B. Correct: sequence values are 64-bit two's-complement integers, and exceeding that range causes query failure instead of wrapping.
- C. Incorrect: Snowflake pre-calculates values, so an `ALTER SEQUENCE` may not affect the next call and typically takes effect after one further use.
- D. Incorrect: uniqueness holds only while the sign of the interval stays the same; flipping from positive to negative can reproduce previously issued values.
- E. Incorrect: a sequence is a single schema-level object shared by all sessions, which is exactly why its values are unique across them.
Sources2
4.Cancelling statements for a session or a user
Snowflake recommends cancelling a statement through the application that is running it: the Snowsight worksheet, or the cancellation API in the ODBC or JDBC driver. When you have to use SQL, Snowflake provides two functions, SYSTEM$CANCEL_QUERY and SYSTEM$CANCEL_ALL_QUERIES. In the documented Java example, the code reads CURRENT_SESSION() and then calls system$cancel_all_queries(?) with that session identifier. Every running statement in the session is cancelled, and the long-running query fails with the QUERY_CANCELED SQL state.
To stop everything a particular user is running, use ALTER USER <name> ABORT ALL QUERIES. It aborts all of that user's running and scheduled statements on every warehouse. The user isn't locked out and can still sign in and start new queries. The command works on one user at a time, so to stop several users you run it once for each of them.
| Method | Scope |
|---|---|
| Application interface (worksheet) or ODBC/JDBC cancellation API | The statement running in that client; the recommended approach |
| SYSTEM$CANCEL_ALL_QUERIES | All running statements in the session identifier you pass |
| SYSTEM$CANCEL_QUERY | SQL cancellation function documented alongside SYSTEM$CANCEL_ALL_QUERIES |
| ALTER USER ... ABORT ALL QUERIES | All running or scheduled statements of one user, on any warehouse |
Checkpoint 6 of 7· Check yourself
An administrator runs ALTER USER jsmith ABORT ALL QUERIES. What is jsmith's situation afterward?
ABORT ALL QUERIES stops the user's statements on every warehouse but doesn't block new sign-ins or new queries.
“Note that the user can still log into Snowflake and initiate new queries.”Source: docs.snowflake.com
5.Cancel statements for single users or multiple users
When you cancel statements, first decide whose statements you are stopping: a single user's, or multiple users' at once.
- A single user. ALTER USER <name> ABORT ALL QUERIES aborts every statement that user is running or has scheduled, on any warehouse. If you also want to stop the user from signing in or starting new queries, set DISABLED = TRUE on the user instead.
- Multiple users at once. ALTER WAREHOUSE <name> ABORT ALL QUERIES aborts all the queries currently running or queued on that warehouse, no matter which user submitted them. If the warehouse is currently in use for your session, you can omit its name.
- One statement or one session. SYSTEM$CANCEL_QUERY cancels a single query by its query ID, and SYSTEM$CANCEL_ALL_QUERIES cancels everything in one session. Both function pages say they are not meant for cancelling queries for a particular warehouse or user. For that, use the two ALTER commands above.
You can always cancel your own statements. To cancel a statement that another user is running with these functions, your role needs OWNERSHIP on that user, or OPERATE or OWNERSHIP on the warehouse running the statement. SYSTEM$CANCEL_QUERY also accepts the ACCOUNTADMIN role. The SYSTEM$CANCEL_ALL_QUERIES page warns that ACCOUNTADMIN is not necessarily granted any of these privileges. If a task ran the query, SYSTEM$CANCEL_QUERY requires OPERATE or OWNERSHIP on the task, or the ACCOUNTADMIN role.
SELECT SYSTEM$CANCEL_QUERY('d5493e36-5e38-48c9-a47c-c476f2111ce5');| Command | Whose statements are cancelled |
|---|---|
| ALTER USER ... ABORT ALL QUERIES | A single user's running or scheduled statements, on any warehouse |
| ALTER WAREHOUSE ... ABORT ALL QUERIES | Every user's statements running or queued on that warehouse |
| SYSTEM$CANCEL_QUERY | One query by query ID. For another user's query: OWNERSHIP on the user, OPERATE or OWNERSHIP on the warehouse, or ACCOUNTADMIN |
| SYSTEM$CANCEL_ALL_QUERIES | All queries in one session. For another user's queries: OWNERSHIP on the user, or OPERATE or OWNERSHIP on the warehouse |
Checkpoint 7 of 7· Check yourself
A runaway workload from several analysts is overloading warehouse REPORT_WH. You want to stop every statement running or queued on it, no matter which user submitted it. Which command fits?
ALTER WAREHOUSE ... ABORT ALL QUERIES cancels the running and queued queries of all users on that warehouse in one statement. SYSTEM$CANCEL_ALL_QUERIES takes a session ID, not a warehouse, and ALTER USER covers only one user at a time.
“Aborts all the queries currently running or queued on the warehouse.”Source: docs.snowflake.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Snowflake supports seq.CURRVAL for reusing the key just generated, as Oracle and other databases do.Why is that wrong?
Snowflake has no currval. To share a key, use a nested subquery, GETNEXTVAL, or a multi-table INSERT.
2.Two references to seq1.NEXTVAL in the same row return the same value.Why is that wrong?
Each occurrence of NEXTVAL generates its own distinct values. To get the same value twice, generate it once and reference the alias twice.
Covered in Referencing a sequence: NEXTVAL, GETNEXTVAL and column defaults
3.A rerun SHOW command can reuse the cached result as long as the role has the right privileges.Why is that wrong?
For SHOW queries, the role running the query must be the same role that generated the cached result. Having the privileges isn't enough.
Covered in Persisted query results: when Snowflake reuses an answer
4.The ACCOUNTADMIN role can always cancel another user's session queries with SYSTEM$CANCEL_ALL_QUERIES.Why is that wrong?
Cancelling another user's queries requires OWNERSHIP on that user, or OPERATE or OWNERSHIP on the warehouse. The SYSTEM$CANCEL_ALL_QUERIES page warns that ACCOUNTADMIN may not hold these privileges.
Covered in Cancel statements for single users or multiple users
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“If the internal representation of a sequence’s next value exceeds this range (in either direction) an error results and the query fails.”
↩︎ What a sequence guarantees, and what it doesn't“Concurrent queries never observe the same value, and values within a single query are always distinct.”
↩︎ What a sequence guarantees, and what it doesn't“A call to GETNEXTVAL must be aliased; otherwise, the generated values cannot be referenced.”
↩︎ Referencing a sequence: NEXTVAL, GETNEXTVAL and column defaults“Omitting the column in an insert statement or setting the value to DEFAULT in an insert or update statement”
↩︎ Referencing a sequence: NEXTVAL, GETNEXTVAL and column defaults“Snowflake does not guarantee generating sequence numbers without gaps. The generated numbers are not necessarily contiguous.”
↩︎ Key concept“Many databases provide a currval sequence reference; however, Snowflake does not.”
↩︎ Exam trap 1“Each occurrence of a sequence generates a set of distinct values.”
↩︎ Exam trap 2“Changing the sequence interval from positive to negative (e.g. from 1 to -1), or vice versa may result in duplicates.”
↩︎ Prediction“NOORDER specifies that the values are not guaranteed to be in increasing order.”
↩︎ Checkpoint - 2.
“For persisted query results of all sizes, the cache expires after 24 hours.”
↩︎ Persisted query results: when Snowflake reuses an answer“can be overridden at the account, user, and session level using the USE_CACHED_RESULT session parameter.”
↩︎ Persisted query results: when Snowflake reuses an answer“You can perform post-processing by using the RESULT_SCAN table function.”
↩︎ Persisted query results: when Snowflake reuses an answer“If the query was a SHOW query, the role executing the query must match the role that generated the cached results.”
↩︎ Exam trap 3“Any difference in syntax, including lowercase versus uppercase, or the use of table aliases, will inhibit 100% cache reuse.”
↩︎ Prediction“up to a maximum of 31 days from the date and time that the query was first executed.”
↩︎ Checkpoint - 3.
“The recommended way to cancel a statement is to use the interface of the application in which the query is running”
↩︎ Cancelling statements for a session or a user“This task uses the session identifier as a parameter to SYSTEM$CANCEL_ALL_QUERIES.”
↩︎ Cancelling statements for a session or a user - 4.
“Aborts all the queries and other SQL statements currently running or scheduled by the user, regardless of the warehouse”
↩︎ Cancelling statements for a session or a user“Note that the user can still log into Snowflake and initiate new queries.”
↩︎ Cancel statements for single users or multiple users“Note that the user can still log into Snowflake and initiate new queries.”
↩︎ Checkpoint - 5.
“Aborts all the queries currently running or queued on the warehouse.”
↩︎ Cancel statements for single users or multiple users“When resuming/suspending a warehouse or aborting queries for a warehouse, if a warehouse is currently in use for the session, the identifier can be omitted.”
↩︎ Cancel statements for single users or multiple users - 6.
“Canceling running operations executed by another user requires a role with one of the following privileges: OWNERSHIP on the user who executed the operation.”
↩︎ Cancel statements for single users or multiple users“For a query run by a task, canceling running operations requires a role with one of the following privileges:”
↩︎ Cancel statements for single users or multiple users - 7.
“This function is not intended for canceling queries for a particular warehouse or user.”
↩︎ Cancel statements for single users or multiple users“Note that the ACCOUNTADMIN role is not necessarily granted any of these privileges.”
↩︎ Exam trap 4