Read-only mode for primary failover groups simplifies Snowflake DR drills

Read-only mode for primary failover groups is now generally available (GA). This feature allows you to freeze writes on the primary failover group with a single command, streamlining planned disaster recovery (DR) drills and account migrations without risking data loss.

Use cases

Imagine you need to run a planned DR drill or migrate accounts with zero data loss. Previously, operators had to disable users, revoke permissions, or manually suspend tasks and pipes to prevent writes before running the final replication refresh.

With read-only mode on primary failover groups, you can:

• Atomically block new DDL and DML statements targeting objects inside the failover group.
• Automatically pause the scheduling of tasks, alerts, and pipe ingest queues (Snowpipe) belonging to databases in the group.
• Keep read queries (SELECT, SHOW, DESCRIBE) running normally on the primary account.
• Revert the read-only state at any time without having to complete a failover.

How to use

The syntax to toggle read-only mode is executed against the primary failover group on the source account:

-- Enable read-only mode on the primary failover group
ALTER FAILOVER GROUP meu_fg SET READ_ONLY = TRUE;

-- Restore standard read-write mode (if not performing a failover)
ALTER FAILOVER GROUP meu_fg UNSET READ_ONLY;

In an end-to-end planned DR drill or migration flow, the operator freezes writes, terminates in-flight writers, runs a final refresh, and promotes the target:

-- 1. On source account (primary): freeze writes
ALTER FAILOVER GROUP meu_fg SET READ_ONLY = TRUE;

-- 2. On source account: terminate running queries targeting databases in the group
SHOW DATABASES IN FAILOVER GROUP meu_fg;

SELECT SYSTEM$CANCEL_QUERY(q.query_id)
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) q
WHERE q.execution_status = 'RUNNING'
  AND q.database_name IN (SELECT "name" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())));

-- 3. On target account: perform the final refresh from the quiescent primary
ALTER FAILOVER GROUP meu_fg REFRESH;

-- 4. On target account: promote connection and failover group to become the new primary
ALTER CONNECTION minha_conexao PRIMARY;
ALTER FAILOVER GROUP meu_fg PRIMARY;

What happens in this code:

1. Running SET READ_ONLY = TRUE rejects new DDL/DML at compilation and skips scheduling for tasks and pipes.
2. The query finds active queries on the affected databases and aborts them via SYSTEM$CANCEL_QUERY to free compute immediately.
3. The REFRESH statement on the target account pulls the final state from the frozen primary with no concurrent write interference.
4. Promoting the connection redirects client traffic, and promoting the failover group makes the target the new read-write primary. On the source, the group transitions to a standard secondary and the read-only flag clears automatically.

What it returns

Observability for the read-only state is surfaced through group inspection commands:

Field	Description
read_only	Returns TRUE if the primary failover group is in read-only mode; FALSE when in normal read-write operation.

This column is visible from both source and target accounts using SHOW FAILOVER GROUPS or SHOW REPLICATION GROUPS.

Limitations and requirements

• Maturity: Generally Available (GA).
• Prerequisites: The executing role requires OWNERSHIP or the FAILOVER privilege on the failover group.
• In-flight transactions: Queries started prior to setting read-only mode are not actively aborted by Snowflake; uncommitted writes will simply fail at commit time. Use SYSTEM$CANCEL_QUERY or SYSTEM$ABORT_SESSION if you need immediate termination.
• Dynamic tables: Dynamic table refreshes are not automatically suppressed. Snowflake will retry up to five times, fail due to the read-only state, and subsequently suspend their schedule. Running UNSET READ_ONLY does not automatically resume dynamic table schedules; they must be resumed manually.
• Membership modifications: You cannot use ADD or REMOVE to change failover group members while in read-only mode.