Snowflake account and database layout: what to split, what to keep together

The decision that constrains everything else is whether development, test, and production live in one account or in separate ones. The strongest argument for a single account is cloning. create database analytics_test clone analytics_prod; is a metadata operation on objects inside the account, so a full-size test environment costs no storage until something is written to it. Moving data to a different account is a different feature, replication, with its own configuration and its own running cost. Teams that split accounts early usually end up rebuilding test data by hand, which is the exact problem cloning solves.

Separate accounts earn their cost in three situations: a regulatory requirement for hard isolation of production data, a need to run in different regions or cloud providers, and organizations large enough that account-level settings themselves need to differ. Replication between accounts is available but it is a running cost and an operational surface, not a free boundary.

The common middle ground is one account with environments as databases and role-based access enforcing the boundary, plus a separate account only where a regulator asked for one in writing.

The layout that survives is environment on the database and layer on the schema. ANALYTICS_DEV and ANALYTICS_PROD each contain RAW, STAGING, INT, and MARTS. Promotion is then running the same code against a different database, which is a one-variable change in dbt or in your deployment pipeline, and object names below the database are identical across environments so no query needs rewriting.

The inverse layout, a database per layer with environment in the schema name, looks tidier in a list of databases and creates constant friction. Grants have to be repeated per layer database, clones become multi-database operations, and every piece of code that references a schema now carries the environment string.

Keep the environment out of schema names for the same reason. A schema called MARTS_PROD inside a database called ANALYTICS_PROD states the environment twice and guarantees that a copy between environments will leave a wrong name behind.

Virtual warehouses are a separate axis and should not follow the database layout. Size and separate them by workload, one for loading, one for transformations, one for BI, one for ad hoc, so that a heavy query cannot slow a dashboard and so credit consumption is attributable per workload without tagging every query.

use role sysadmin;

create database if not exists analytics_prod;
create schema   if not exists analytics_prod.raw;
create schema   if not exists analytics_prod.staging;
create schema   if not exists analytics_prod.int;
create schema   if not exists analytics_prod.marts;

-- access roles describe a permission, functional roles describe a job
use role securityadmin;
create role if not exists ar_analytics_prod_marts_r;
grant usage  on database analytics_prod          to role ar_analytics_prod_marts_r;
grant usage  on schema   analytics_prod.marts    to role ar_analytics_prod_marts_r;
grant select on all tables in schema analytics_prod.marts to role ar_analytics_prod_marts_r;
grant select on future tables in schema analytics_prod.marts to role ar_analytics_prod_marts_r;

create role if not exists fr_analyst;
grant role ar_analytics_prod_marts_r to role fr_analyst;
grant role fr_analyst to role sysadmin;

Separate access roles from functional roles. An access role represents one permission on one object set, for example read on the marts schema of the production database. A functional role represents a job, for example analyst or data engineer, and is granted a set of access roles. Users only ever get functional roles.

The benefit shows up during an access review. With the split, answering what an analyst can read is reading one role membership list. Without it, the same question requires walking every grant on every object, which is why review gets skipped.

Two operational rules matter more than any naming scheme. Objects should be owned by a role in the SYSADMIN hierarchy, not by ACCOUNTADMIN and not by a personal role, so that ownership survives staff changes. And grants should be created by code in the repository rather than by hand in a worksheet, because a grant nobody can diff is a grant nobody will remove.

A Snowflake account is a hierarchy plus a permission graph, and the two are hard to hold in your head at once. On the Datadef canvas, databases become zones, schemas become nodes inside them, virtual warehouses sit apart with labelled edges to the workloads they serve, and the functional roles form their own zone with edges to the access roles they carry. The result is one picture that answers both what exists and who can reach it.

If the account is provisioned with the Snowflake Terraform provider, connect that repository read-only and the layout is parsed from the .tf files themselves, with no init, no state, and no Snowflake credentials handed to a diagramming tool. The parser knows the provider: snowflake_database and snowflake_warehouse are drawn as individual boxes with warehouse_size resolved and shown on the node, snowflake_schema is supporting detail, so ten schemas declared in one module roll up into a single node carrying the count and the member names instead of ten boxes, and any type whose name matches role, grant, or permission is classified as wiring and counted rather than drawn. The picture answers what exists and how big it is, while the permission graph stays a number rather than a hairball.

The daily sync regenerates the diagram and an architecture.md from the chosen branch, and hand-placed nodes keep their positions across syncs so the map stays familiar. See how repository sync works.

• Snowflake architecture guide
• Living diagram from Snowflake
• Warehouse naming conventions
• How to design a data warehouse
• Repository sync documentation