Dev environment drift and per-PR cloning on Snowflake
A B2B SaaS company has 55 analytics engineers on dbt Cloud and Snowflake, with dev, CI, and prod environments. Every developer writes to one shared DEV database, with schemas separated by generate_schema_name. Dev reads a RAW database copied from production with a 5% sample for large tables and full rows for small ones; the sampler job refreshes weekly. Last month a source column account_tier was renamed in production on a Tuesday. The dev sample kept the old column for nine days, four pull requests merged green, and the production deployment failed at 02:00 and blocked the finance mart. Separately, twelve dashboards were found querying DEV.REPORTING, and the dev warehouse is a size-L warehouse that never suspends. CI takes 40 minutes on 20 open PRs. Two proposals are on the table: (A) zero-copy clone production into a per-PR database; (B) maintain one shared CLONE database refreshed nightly for everyone.
1. Evaluate proposals A and B on fidelity, cost, and concurrency. Which do you recommend, and what does neither proposal actually fix?
Read the worked answer
The two proposals differ mostly on the axis that caused the incident: how far the test environment can drift from production.
Proposal A (per-PR clone). A Snowflake zero-copy clone is a metadata operation: it completes in seconds and bills no storage until either side changes a micro-partition; after that you pay only for changed partitions on the clone side. Twenty concurrent clones is not a storage problem. The real cost is compute - twenty warehouses running dbt build - and hygiene: clones that are never dropped accumulate storage plus time-travel and fail-safe retention. Fix that by making clones transient (DATA_RETENTION_TIME_IN_DAYS = 0) and dropping them on a TTL when the PR closes or after 72 hours.
Proposal B (shared nightly clone). Cheaper, but it reproduces the exact failure mode: the clone is as-of the refresh, and a Tuesday production change is invisible until Wednesday. It also allows two PRs to write the same tables concurrently, producing flaky CI and results that cannot be reproduced. Reject.
Recommendation. Clone per PR, from a golden clone of production refreshed daily, with CI building only state:modified+ into it and deferring unbuilt models to production state.
What neither fixes. The nine-day drift was a schema-change detection gap, not a data-location gap. A clone still lags by up to 24 hours. Add a nightly job that diffs INFORMATION_SCHEMA.COLUMNS between RAW and the golden clone, failing loudly on added, removed, or retyped columns; and put dbt contracts on staging models so a renamed column breaks the build instead of the consumers.
2. How do you get the twelve dashboards off `DEV.REPORTING` without breaking the teams that depend on them, and what stops the next one from appearing?
Read the worked answer
Treat it as a migration with a hard deadline, not a scolding.
Inventory first. Query SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY and OBJECT_DEPENDENCIES to find every consumer of DEV.REPORTING, including scheduled queries and extracts that no dashboard metadata lists. Identify the owning team per object; a dashboard nobody owns is the hardest case and usually the one that breaks.
Create a destination. Stand up a SANDBOX database with its own read-only role, a size-XS warehouse with AUTO_SUSPEND = 60, and a documented 14-day object lifetime. Repoint the twelve dashboards, giving owners a two-week parallel-run window and a named contact.
Freeze the source. After the window, revoke all grants on DEV.REPORTING from every non-engineering role and rotate the role's warehouse. Do not rely on a warning email; revoke and let the failure be immediate and attributable.
Prevent recurrence. Three controls: (1) no BI or reporting role is ever granted on a _DEV database - codify it as a schema-level grant default rather than a one-off revoke; (2) the dev warehouse gets AUTO_SUSPEND = 60 and a resource monitor with a monthly credit quota that alerts at 75%; (3) dbt grants config on marts declares the consuming role, so a new mart that grants to PUBLIC fails review. Add an account-level query that flags any role with SELECT on a dev or CI schema and review it weekly - drift in grants is how this recurs.
3. Design the warehouse and cost-allocation layout across dev, CI, and prod. What would you change about the always-on size-L dev warehouse, and how do you keep the three environments attributable?
Read the worked answer
The always-on size-L dev warehouse is the single easiest fix: a size-L warehouse burns 8 credits per hour, roughly 5,800 credits a month if it truly never suspends, for workloads that are mostly interactive and low-volume. Replace it with a size-S warehouse at AUTO_SUSPEND = 60 and AUTO_RESUME = TRUE, and add a second size-XS warehouse for ad-hoc analyst queries. Twelve dashboards moved to SANDBOX should land on the XS warehouse, not the engineering one.
CI gets its own warehouse. CI is spiky and parallel across 20 PRs; give it a dedicated size-M warehouse with a short statement timeout (say 10 minutes) and a resource monitor that suspends at a monthly quota. Sharing a warehouse with dev means one runaway full-refresh starves everyone.
Prod stays separate and sized for the SLA, not for peak parallelism you never use. Multi-cluster only if queuing is measured, not assumed.
Attribution. Set a dbt query tag per invocation: dbt_<env>_<user>_<invocation_id>. That makes QUERY_HISTORY sliceable by environment and invocation, and lets you charge CI cost back to the PR that triggered it. Combine with per-warehouse resource monitors and WAREHOUSE_METERING_HISTORY to reconcile dbt-attributed spend against total spend monthly.
Review cadence. A monthly report of credits by environment and by model, with any model above a threshold requiring an owner sign-off. Cost visibility without an owner attached gets ignored.