SnowPro Advanced: Administrator free question 8 / 10 · Domain 4: Performance Monitoring and Tuning
An ingestion app inserts rows into table T in session 1, and immediately afterward another service reads T in session 2, which was opened before the ingestion. Session 2 sometimes does not see the new rows. Which measure does the documentation recommend most for making both processes get the same results from the same queries?
- Set READ_CONSISTENCY_MODE to 'GLOBAL' with ALTER ACCOUNT so that reads span sessions
- Run the dependent queries together in one session instead of separate sessions
- Commit the ingestion changes, then reopen reader session 2 and read T
- Run ALTER SESSION SET READ_CONSISTENCY_MODE = 'GLOBAL' in reader session 2
Show the answer and explanation
Answer: B
Why this is correct
The documentation says Snowflake generally maintains read consistency for changes within a given session, but across separate sessions running at about the same time, a later reader may not see new rows. To guarantee the same results, it lists three measures in order of preference: first, run dependent queries in a single session; second, commit the changes before starting the reader session; third, set READ_CONSISTENCY_MODE to 'GLOBAL' with ALTER ACCOUNT. The most recommended is the first.
Why the other options are wrong
"Set READ_CONSISTENCY_MODE to 'GLOBAL' with ALTER ACCOUNT so that reads span sessions" … Setting READ_CONSISTENCY_MODE to 'GLOBAL' is one documented measure, but it is third in order of preference and comes with a small delay in query response times (usually milliseconds).
"Run ALTER SESSION SET READ_CONSISTENCY_MODE = 'GLOBAL' in reader session 2" … The documentation says READ_CONSISTENCY_MODE can only be set at the account level by a user with ACCOUNTADMIN privileges. It cannot be changed for the reader session alone.
"Commit the ingestion changes, then reopen reader session 2 and read T" … This is one of the documented measures, and starting the reader session after the commit guarantees it sees the new rows. But it is second in order of preference; the first is running dependent queries in one session.
Default value
READ_CONSISTENCY_MODE defaults to 'SESSION', under which changes within a session are immediately visible to later queries in that session. READ COMMITTED is the only isolation level supported for tables: a statement sees only data committed before it began.