snowflake-failover-readiness-drill

Imported from jeremylongshore/tons-of-skills-marketplace (plugins/saas-packs/snowflake-pack/skills/snowflake-failover-readiness-drill/SKILL.md). Install upstream with npx skills add jeremylongshore/tons-of-skills-marketplace --skill snowflake-failover-readiness-drill. Copyright stays with the author (MIT).

Snowflake Failover Readiness Drill

Purpose

Produces a deterministic readiness or attestation result from exact, independently trusted evidence. It observes and verifies but never performs or authorizes a Snowflake control-plane change.

Prerequisites

• Python 3.10+ and this complete skill directory, including its reviewed SQL.
• An owner-approved scope and separately retained digests for policy, collection, and operator/validation evidence.
• Existing least-privilege Snowflake CLI profiles in source and target accounts when live collection is required. Profiles used for Information Schema history, progress, or dangling functions must select an approved current database; the skill does not configure authentication or session state.
• Business Critical Edition or higher for failover groups.

Workflow

1. Have the recovery owner approve an exact policy containing every in-scope group, dependency, validation, RPO, and RTO. Record its digest at a trusted boundary before evidence is transported.

2. Collect replication-current in both source and target accounts. Collect replication and replication-progress in each target account over the same current, explicit half-open UTC window. A full failback drill also requires both surfaces in the original source account to prove the reverse refresh leg. Collect replication-dangling in both accounts for every in-scope local group.

3. Build the exact schema-2 wrapper in references/evidence-contract.md. Record independent digests for the collector bundle, policy, and operator/validation receipts. A digest recomputed from the delivered file is not independent.

4. Run the analyzer; it writes only to stdout:

   python3 "${CLAUDE_SKILL_DIR}/scripts/analyze_failover_readiness.py" \
     --input ./snowflake-failover-evidence.json \
     --evaluated-at "$EVALUATED_AT_UTC" \
     --trusted-input-sha256 sha256:... \
     --trusted-policy-sha256 sha256:... \
     --trusted-operator-sha256 sha256:... \
     > ./snowflake-failover-report.json

An INCONCLUSIVE or NOT_READY exit is 1; malformed input exits 2.

Collection pattern

Use an existing least-privilege Snowflake CLI profile. Never pass credentials to the collector and never switch to ACCOUNTADMIN because evidence is missing.

python3 "${CLAUDE_SKILL_DIR}/scripts/collect_snowflake_evidence.py" \
  --surface replication-current --connection source-observer \
  --output ./source-current.json

python3 "${CLAUDE_SKILL_DIR}/scripts/collect_snowflake_evidence.py" \
  --surface replication --connection target-observer \
  --window-start "$WINDOW_START_UTC" --window-end "$WINDOW_END_UTC" \
  --output ./target-history.json

python3 "${CLAUDE_SKILL_DIR}/scripts/collect_snowflake_evidence.py" \
  --surface replication-progress --connection target-observer \
  --window-start "$WINDOW_START_UTC" --window-end "$WINDOW_END_UTC" \
  --output ./target-progress.json

python3 "${CLAUDE_SKILL_DIR}/scripts/collect_snowflake_evidence.py" \
  --surface replication-dangling --connection source-observer \
  --replication-group DR_CORE --output ./source-dangling.json

The group selector is validated locally and hashed inside Snowflake. Raw account, group, role, owner, object, and dependency identifiers are not emitted. Every receipt is live-only schema 2, no older than 15 minutes at evaluation, capped at 5,000 rows, and invalid if the cap is reached. History/progress window ends must be at or before and within 60 seconds of collection start, no more than 15 minutes behind evaluation, and identical for the paired receipts in an account.

Modes and proof

• PREFLIGHT: one current snapshot per source and target account, no operator events, and passing PRE_FAILOVER validations. A clean result is only READY_FOR_OPERATOR_DRILL_AS_OF.
• FAILOVER_ATTESTATION: before/after current snapshots, one successful scoped failover receipt per group, and passing pre/post validations. A clean result is FAILOVER_ATTESTED_AS_OF.
• FULL_DRILL_ATTESTATION: before/middle/after current snapshots, ordered successful failover then failback receipts, and passing validations at all three stages. A clean result is FULL_DRILL_ATTESTED_AS_OF.

RPO age is evaluated_at - PRIMARY_SNAPSHOT_TIMESTAMP from the latest refresh job for preflight, or transition_started_at - PRIMARY_SNAPSHOT_TIMESTAMP for each directional drill leg, only when that job has exactly one COMPLETED terminal phase and exactly one snapshot timestamp. Refresh end time, schedule time, and login success are never substitutes. RTO comes only from trusted operator event start/completion times.

Non-negotiable safety boundary

• Never run refresh, promotion, failover, failback, redirect, suspend/resume, cancel/abort, role switching, or session mutation.
• Never treat a self-checksum as provenance. Require all three separately supplied trusted digests.
• Never shrink a denominator after collection. Exact group, dependency, and validation counts are part of policy.
• Never treat missing, stale, permission-filtered, malformed, duplicated, or capped evidence as healthy.
• Never echo attacker-controlled raw identifiers or provider messages. Findings expose finite codes and hashed scopes only.
• Keep every actual transition in a separately approved human change window.

References

• references/evidence-contract.md — exact wrapper, policy, receipt coverage, trust, and verdict rules.
• references/source-notes.md — official Snowflake semantics and operational caveats.

Use the Read tool to inspect both references before evaluating production evidence; they are part of the contract, not optional background.

Output

The analyzer emits one JSON report to stdout with the overall status, integrity and coverage states, finite findings, RPO results, the three computed trust digests, admitted receipt hashes, conservative non-claims, and a deterministic report digest. It never copies collector, operator, or validation rows into the report.

Error handling

Stop and recollect on schema, digest, freshness, source, query-template, row, count, cap, selector, or authorization-context errors. Stop on a failed/canceled latest refresh, incomplete progress, RPO/RTO breach, suspended/unconfigured secondary schedule, blocking dangling reference, missing dependency ordering proof, stale validation, failed validation, unrestored final-secondary schedule, or unproved transition. A warning-only nonblocking dangling reference yields AT_RISK; it is never silently accepted.

Examples

• A snapshot exactly 3,600 seconds old against a 3,600-second RPO passes; one second older yields RPO_BREACH and NOT_READY.
• A recent successful refresh does not override a newer failed job or incomplete progress; the latest refresh remains unproved.
• A caller-authored SUCCEEDED event with a recomputed self-hash cannot produce an attested result when the independent operator digest does not match.
• A complete preflight produces READY_FOR_OPERATOR_DRILL_AS_OF, never FULL_DRILL_ATTESTED_AS_OF; that requires ordered operator failover and failback receipts plus before/middle/after state observations and passing validations.