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
In a nutshell
  • 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.

Cloud storageraw filesexternal stageRaw zoneas receivedcleanCurated zoneconformedmodelConsumption zonemarts and shares

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 PATTERN instead 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.

ZoneContentTypical Snowflake objects
Landing or rawData exactly as received, kept for replayStage, raw tables with VARIANT, transient tables
Staging or cleansedTyped, deduplicated, validatedStaging schema, dynamic tables or tasks
Integration or curatedConformed business entities, historyData Vault or normalized tables
Consumption or presentationMarts and aggregates for usersStar 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.

Rawtransient tablesstream and tasksStagingtyped, deduplicatedMERGECuratedpermanent tablesviewsConsumptionsecure views and shares

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

EnvironmentPurposeTypical setup
ProductionBusiness critical, controlled changesOwn warehouses, strict roles, replication or failover for resilience
Development / testBuild and verify changesClone of production, smaller warehouses
SandboxExploration with no promisesIsolated database or account, resource monitor and short retention

Two ways to separate environments:

ApproachProsCons
Separate databases in one accountCheap to clone, simple sharing of roles and integrationsWeaker isolation; a mistake with grants reaches production
Separate accounts (in one organization)Strong isolation of roles, parameters and network policiesData 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.

Exam tip

"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.

Production databasesource of truthzero copy cloneDev databasedevelopers testCI/CDProductionpromote scripts, not data

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.