Snowflake Architecture · Topic 6 of 18
Data Lake Layout, Warehouse Zones, DataOps and Environments
Design data lake storage paths, warehouse zones, DevOps and DataOps practices and separate production, development and sandbox environments in Snowflake.
- 5 min
- Lay out lake paths by zone, source and date; stages, directory tables and external tables all benefit.
- Zones: raw, staging, curated, consumption (bronze, silver, gold).
- Transient tables: no Fail-safe, Time Travel of 0 or 1 day.
What it is
An architect designs two things at once: how data flows from raw files to trusted tables, and how changes to that design move from a developer's idea to production. Snowflake supports both with storage layout, layered databases, cloning and account structure.
The exam asks scenario questions such as "where do raw files land", "how should developers test against production like data", or "should dev and prod be separate accounts". The answers use a small set of patterns.
How it works
Data lake storage directory structure
Files in cloud storage (Amazon S3, Azure Blob or Data Lake Storage, Google Cloud Storage) are read through an external stage, a directory table, or an external or Iceberg table. A predictable path layout makes all three cheaper and faster.
A common layout puts the source and the date in the path:
s3://lake/raw/<source_system>/<table>/<yyyy>/<mm>/<dd>/part-0001.parquet
s3://lake/curated/<domain>/<table>/...
Why the layout matters:
- COPY can read a narrow path or a
PATTERNinstead of scanning a whole stage. - External tables can derive partition columns from the path so queries prune files.
- Separate prefixes per zone and source allow different access rules, lifecycle rules and storage integrations.
- Sizing files at roughly 100 to 250 MB compressed gives efficient loads.
Where possible keep the data lake and the warehouse in the same cloud and region as the Snowflake account to avoid transfer cost and latency.
Zones (data warehouse layers)
Zones are layers that data passes through, each with a clearer meaning and tighter quality. Many teams call them bronze, silver and gold.
| Zone | Content | Typical Snowflake objects |
|---|---|---|
| Landing or raw | Data exactly as received, kept for replay | Stage, raw tables with VARIANT, transient tables |
| Staging or cleansed | Typed, deduplicated, validated | Staging schema, dynamic tables or tasks |
| Integration or curated | Conformed business entities, history | Data Vault or normalized tables |
| Consumption or presentation | Marts and aggregates for users | Star schema, secure views, shares |
Use one schema or database per zone so grants stay simple: engineers write to raw and curated, analysts read only consumption. Transient tables fit staging data that can be rebuilt, because they have no Fail-safe and at most one day of Time Travel, which lowers storage cost. Keep permanent tables for the zones you cannot recreate.
Support of DevOps and DataOps principles
DevOps and DataOps bring software practice to data: everything is code, changes go through review, deployments are automated and repeatable, and quality is tested before release.
For Snowflake this means:
- Store DDL, grants and pipeline code in Git; never hand edit production.
- Deploy through CI/CD with the Snowflake CLI or a change tool, so every environment gets the same scripts.
- Use declarative or versioned scripts (for example
CREATE OR ALTER, or numbered migration files) so reruns are safe. - Test on zero copy clones of production data, then promote.
- Monitor pipelines and data quality, using tasks, alerts and data metric functions.
- Give each environment its own roles so a pipeline user cannot touch other environments.
Production, development and sandbox environments
| Environment | Purpose | Typical setup |
|---|---|---|
| Production | Business critical, controlled changes | Own warehouses, strict roles, replication or failover for resilience |
| Development / test | Build and verify changes | Clone of production, smaller warehouses |
| Sandbox | Exploration with no promises | Isolated database or account, resource monitor and short retention |
Two ways to separate environments:
| Approach | Pros | Cons |
|---|---|---|
| Separate databases in one account | Cheap to clone, simple sharing of roles and integrations | Weaker isolation; a mistake with grants reaches production |
| Separate accounts (in one organization) | Strong isolation of roles, parameters and network policies | Data must be shared or replicated across; more admin |
Zero copy cloning (CREATE DATABASE dev_db CLONE prod_db) gives developers a full copy in seconds with no extra storage until data changes. A clone of a point in the past is possible with Time Travel (AT or BEFORE). Cloning works inside one account. To reach another account you share or replicate instead.
"Developers need production like data quickly and cheaply" means zero copy clone. "Dev must be isolated from prod security settings" means a separate account.
Put resource monitors and smaller warehouses on non-production work, and consider masking or synthetic data when production data is sensitive.
Common mistakes
Mixing zones in one schema. Grants and quality rules become impossible to separate.
Copying production data into dev with INSERT SELECT. It is slow and doubles storage. Clone instead.
Promoting data instead of code. Environments should differ in data and size, but get the same scripts.
Using permanent tables for rebuildable staging data. Transient tables avoid Fail-safe storage cost.
What to remember for the exam
- Lay out lake paths by zone, source and date; stages, directory tables and external tables all benefit.
- Zones: raw, staging, curated, consumption (bronze, silver, gold).
- Transient tables: no Fail-safe, Time Travel of 0 or 1 day.
- DataOps: everything in Git, automated deployment, tests on clones, monitoring.
- Zero copy clone for dev and test; separate accounts for the strongest isolation.
- Resource monitors cap non-production spend.
Check yourself
Which feature gives developers a full copy of production without duplicating storage up front?
Zero copy cloning (CREATE DATABASE ... CLONE). Storage is only added as the clone or source diverges.
Why partition lake paths by source and date?
So COPY, patterns and external table partition columns read only the needed files, and access rules can differ per prefix.
When should dev and prod be in separate accounts rather than databases?
When you need strong isolation of roles, parameters and network policies, accepting that data must be shared or replicated between them.
Topic 6 of 18 in Snowflake Architecture
Test yourself on Snowflake Architecture
The full Snowflake ARA-C01 question bank has 687 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.