Study guide
1.3 Database and Schema Design Architecture
SnowPro Advanced: Architect ARA-C01
Track your progress as you read
Finished sections get a check mark.
1 Introduction: The ARA-C01 Exam
About the SnowPro Advanced: Architect (ARA-C01) Exam
2 Chapter 1: Account Strategy & Organization Architecture
1.1 Multi-Account Strategy & Snowflake Organizations 1.2 Snowflake Parameter Hierarchy & Configuration 1.3 Database and Schema Design Architecture
3 Chapter 2: Identity, Access Control & Storage Integrations
2.1 Role-Based Access Control Architecture & Hierarchy 2.2 Secondary Roles, Object Ownership & Delegated Administration 2.3 Cloud Storage Integrations & IAM Trust Policies 2.4 Identity Federation, SSO, SCIM & Authentication Policies
4 Chapter 3: Governance, Data Privacy, Network Security & Compliance
3.1 Column-Level Masking & Row Access Policies 3.2 Object Tagging, Tag-Based Masking & Audit Governance 3.3 Encryption, Key Management & Tri-Secret Secure 3.4 Aggregation Policies, Projection Policies & External Tokenization 3.5 Network Policies, Network Rules, External Access & Private Connectivity 3.6 Snowflake Editions & Compliance Architecture (PCI DSS, PII & PHI)
5 Chapter 4: Data Modeling in Snowflake
4.1 Dimensional Modeling: Star vs. Snowflake Schemas 4.2 Data Vault 2.0 Architecture in Snowflake 4.3 Relational Constraints & Query Optimizer Integration
6 Chapter 5: Data Sharing & Collaboration Solutions
5.1 Direct Data Sharing Architecture & Security 5.2 Managed Reader Accounts for External Consumers 5.3 Snowflake Marketplace, Data Exchange & Clean Rooms
7 Chapter 6: Multi-Cloud, Multi-Region & Business Continuity
6.1 Database & Account Replication Mechanics 6.2 Disaster Recovery Architecture & Client Redirect 6.3 Cross-Cloud Auto-Fulfillment & Listing Replication 6.4 Data Protection: Time Travel, Fail-safe & Storage Costs
8 Chapter 7: Environments, Lakehouse, DevOps & AI/ML Applications
7.1 Multi-Tier Environment Strategy & Zero-Copy Cloning 7.2 Data Lakehouse Architecture & Apache Iceberg 7.3 DataOps, CI/CD & Snowflake Git Integration 7.4 AI/ML Pipelines & Applications: Cortex AI, ML Functions, Snowpark Container Services, Streamlit & Native Apps
9 Chapter 8: Data Ingestion Architecture
8.1 Bulk Ingestion Architecture: COPY INTO Optimization 8.2 Snowpipe Continuous Automated Ingestion 8.3 Snowpipe Streaming & Real-Time Ingestion 8.4 Data Unloading Architecture & Cloud Export Patterns 8.5 Schema Detection, Schema Evolution & Source-Change Architecture
10 Chapter 9: Continuous Pipelines & Stream Transformation
9.1 Change Data Capture (CDC) with Snowflake Streams 9.2 Task Orchestration, DAGs & Serverless Scheduling 9.3 Declarative Data Pipelines with Dynamic Tables 9.4 Views, Materialized Views & Transformation Table Design
11 Chapter 10: Semi-Structured Data & Ecosystem Tooling
10.1 Semi-Structured Data: VARIANT, OBJECT & ARRAY Processing 10.2 Snowflake Ecosystem Connectors, Drivers & SQL API 10.3 Snowpark, User-Defined Functions & Stored Procedures
12 Chapter 11: Virtual Warehouse Architecture & Concurrency
11.1 Virtual Warehouse Architecture, Sizing & Credit Economics 11.2 Multi-Cluster Warehouses & Concurrency Scaling Policies 11.3 Warehouse Types & Query Acceleration Service
13 Chapter 12: Query Profiling & Performance Diagnostics
12.1 Query Profile Deep-Dive & Bottleneck Analysis 12.2 Memory Spilling, Warehouse Queuing & Timeout Governance 12.3 Three-Tier Caching Architecture & Result Reuse
14 Chapter 13: Storage Optimization, Cost Governance & Observability
13.1 Micro-Partitioning & Automatic Clustering 13.2 Search Optimization Service (SOS) & Point Lookup Acceleration 13.3 Resource Monitors, Financial Governance & Usage Observability 13.4 Warehouse Monitoring, Alerts, Event Tables & Performance Explorer
1.3 Database and Schema Design Architecture
Key Takeaways Explain This Topic AI
A multi-layered database taxonomy (Raw/Bronze, Harmonized/Silver, Presentation/Gold) isolates transformation stages, storage lifecycles, and security access boundaries.
Managed access schemas (WITH MANAGED ACCESS) let only the schema owner or a role with MANAGE GRANTS grant privileges on contained objects, which suits governed marts and shared data.
Permanent tables include up to 90 days of Time Travel and 7 days of non-configurable Fail-safe; Transient tables offer 0–1 day of Time Travel and 0 days of Fail-safe, reducing storage footprint in staging layers.
Temporary tables exist only for the session lifecycle, shadow permanent tables of the same name, and incur no Fail-safe storage overhead.
Database Roles provide database-scoped access control that can be granted across Snowflake Secure Shares without exposing account-level RBAC structures.
Last updated: September 2026
1.3 Database and Schema Design Architecture
A robust data architecture in Snowflake demands a coherent database and schema taxonomy. For the SnowPro Advanced: Architect exam, you must understand how to structure architectural data layers, enforce centralized privilege control using Managed Access Schemas , optimize storage credits across Permanent, Transient, and Temporary table lifecycles , manage object lifecycles via UNDROP and zero-copy cloning, and encapsulate access boundaries using Database Roles .
Database & Schema Taxonomy: Architectural Layering
Enterprise Snowflake deployments typically adopt a medallion or multi-tiered database architecture to isolate processing stages, lifecycle retention, and access permissions.
The Three-Layer Architecture Pattern +-------------------------------------------------------------------------+
- | RAW / INGESTION LAYER |
- | • Databases/Schemas: RAW_DB / BRONZE |
- | • Table Types: TRANSIENT or PERMANENT (1-day Time Travel) |
- | • Ingestion: Snowpipe, COPY INTO, Snowpipe Streaming |
- | • Ownership: LOADER_ROLE | Access: Strictly restricted |
- +-------------------------------------------------------------------------+
- │
- ▼
- +-------------------------------------------------------------------------+
- | HARMONIZED / TRANSFORM LAYER |
- | • Databases/Schemas: CORE_DB / SILVER |
- | • Table Types: PERMANENT (Full Time Travel + Fail-safe) |
- | • Transformation: Dynamic Tables, Streams & Tasks, dbt |
- | • Modeling: Data Vault 2.0 (Hubs/Links/Sats), 3NF Normalization |
- | • Ownership: TRANSFORMER_ROLE | Access: Internal Data Engineers |
- +-------------------------------------------------------------------------+
- │
- ▼
- +-------------------------------------------------------------------------+
- | PRESENTATION / MARTS LAYER |
- | • Databases/Schemas: MARTS_DB / GOLD (WITH MANAGED ACCESS) |
- | • Table Types: PERMANENT / SECURE VIEWS |
- | • Structure: Dimensional Star Schemas (Facts & SCD Type 2 Dimensions) |
- | • Governance: Tag-based masking, Row Access Policies, Database Roles |
- | • Ownership: GOVERNANCE_ROLE | Access: BI Tools, Consumers, Shares |
- +-------------------------------------------------------------------------+
- Standard vs. Managed Access Schemas
The presentation layer above uses a managed access schema ( CREATE SCHEMA ... WITH MANAGED ACCESS or ALTER SCHEMA ... ENABLE MANAGED ACCESS ). In a standard schema, each object's owner can grant access to it (discretionary access control); in a managed access schema, only the schema owner or a role with MANAGE GRANTS can grant privileges on the objects inside it. That makes managed access schemas the usual choice for governed marts and shared data. Section 2.2 covers the grant rules, future grants, and ownership transfers in detail.
The Snowflake Object Hierarchy and Why It Shapes Architecture
Every Snowflake object lives at a specific level of the hierarchy, and that level determines how it is secured, replicated, shared, and cloned:
Level Examples Architectural consequence
Organization Accounts, organization users and groups, organization listings Managed by organization administrators; spans accounts
Account Users, account roles, virtual warehouses, resource monitors, integrations (storage, API, security, notification, external access), network policies, shares, connections, replication/failover groups Not contained in a database, so they are replicated with account-level object types, never shared or cloned with a database
Database Schemas, database roles Unit of cloning, replication, and direct sharing
Schema Tables (permanent, transient, temporary, external, dynamic, hybrid, Iceberg, event), views and materialized views, stages, file formats, sequences, pipes, streams, tasks, functions (UDFs/UDTFs), stored procedures, alerts, masking/row access/aggregation/projection policies, tags, secrets, network rules Addressed as database.schema.object ; privileges require USAGE on the parent database and schema
Architectural implications to remember:
Privilege chain: a role needs USAGE on the database and schema plus the object privilege (for example SELECT ), and warehouses need their own USAGE grant because they are account-level.
Stages and file formats are schema objects that pipes, COPY statements, and external tables reference by name; dropping or recreating them breaks the objects that depend on them.
Streams and tasks are schema objects that reference other objects (a source table, a warehouse or serverless compute); cloning and replication preserve the references only within the same cloned or replicated container.
Functions and procedures are schema objects whose ownership and EXECUTE AS mode decide whose privileges are used at run time (Section 10.3).
Object Types & Storage Lifecycle: Permanent vs. Transient vs. Temporary
Snowflake tables are categorized into three distinct types, each exhibiting specific Time Travel retention capabilities, Fail-safe protections, and storage cost profiles.
1. Permanent Tables
Creation Syntax: CREATE TABLE <name> ...
Time Travel: 0 to 90 days (Enterprise Edition and above); 0 to 1 day (Standard Edition).
Fail-safe: 7 non-configurable days immediately following the conclusion of the Time Travel period.
Storage Cost Components: Active Storage + Time Travel Storage + Fail-safe Storage.
Use Case: Production data assets, core financial ledgers, master data management, and historical audit stores where point-in-time recovery and disaster recovery protection are legally required.
2. Transient Tables
Creation Syntax: CREATE TRANSIENT TABLE <name> ...
Time Travel: 0 or 1 day maximum (regardless of Snowflake Edition).
Fail-safe: 0 days (No Fail-safe) .
Storage Cost Components: Active Storage + Time Travel Storage (maximum 1 day). Zero Fail-safe cost.
Transient Containers: A transient table can reside in any database or schema. However, if a database or schema is created with the TRANSIENT modifier ( CREATE TRANSIENT DATABASE raw_db; ), all tables created within it default to transient.
Use Case: Daily ETL staging tables, intermediate transformation pipelines, and high-churn landing zones. Eliminating Fail-safe saves enormous storage costs for data that can be easily reproduced from external source systems.
3. Temporary Tables
Creation Syntax: CREATE TEMPORARY TABLE <name> ...
Session Scope: Exists strictly within the user session that created it. Invisible to all concurrent sessions.
Lifecycle: Automatically dropped and purged when the user session disconnects or times out.
Time Travel: 0 or 1 day maximum (accessible only while the session remains active).
Fail-safe: 0 days (No Fail-safe) .
Table Name Shadowing: If a temporary table is created with the identical name as an existing permanent or transient table in the same schema, the temporary table shadows (masks) the permanent table for that session. All subsequent queries in that session resolve to the temporary table until it is dropped.
Storage Lifecycle & Table Type Matrix
- Architectural Attribute Permanent Table Transient Table Temporary Table
- Syntax CREATE TABLE CREATE TRANSIENT TABLE CREATE TEMPORARY TABLE
- Max Time Travel Up to 90 Days (Enterprise+) Max 1 Day Max 1 Day (Within session)
- Fail-safe Period 7 Days (Mandatory) 0 Days (None) 0 Days (None)
- Session Scoped? No (Persistent) No (Persistent) Yes (Dropped on disconnect)
- Name Shadowing? No No Yes (Shadows permanent tables)
Storage Credit Impact Active + Time Travel + Fail-safe Active + 1d Time Travel (No Fail-safe) Active + 1d Time Travel (No Fail-safe)
Primary Architectural Use Core Enterprise Data Models Staging & Daily ETL Pipelines Scratchpad & Session Calculations
The Staging Layer Storage Bloat Scenario
Consider an ETL pipeline that truncates and reloads a 10 TB staging table every night:
If configured as a Permanent Table: Each daily reload moves 10 TB into Time Travel (1 day) and subsequently into Fail-safe (7 days). Over 7 days, Fail-safe accumulates 10 TB × 7 days = 70 TB 10\text{ TB} \times 7\text{ days} = 70\text{ TB} 10 TB × 7 days = 70 TB of storage! At standard cloud storage rates ($23/TB/month), the organization pays over $1,600/month just for Fail-safe storage on disposable staging data.
If configured as a Transient Table: Fail-safe is zero. The organization pays only for the active 10 TB plus 1 day of Time Travel, eliminating 70 TB of useless storage burn.
Object Lifecycle at the Container Level
Container design also decides how recovery and copying behave:
Drop cascades: dropping a database or schema drops everything inside it, and UNDROP DATABASE or UNDROP SCHEMA restores the children that were active at the time of the drop (within the Time Travel window). A table dropped before its schema was dropped is not brought back by undropping the schema — it must be undropped separately.
Retention inheritance: DATA_RETENTION_TIME_IN_DAYS set on a database or schema becomes the default for the objects inside it, so a transient RAW_DB and a permanent MARTS_DB get very different recovery windows without per-table settings.
Clones follow the container: cloning a database or schema copies its child objects (with some exceptions such as external tables and, unless requested, internal named stages) and the grants on those child objects, but not the grants on the container itself.
Undrop name collisions and point-in-time recovery are covered in Section 6.4, and cloning boundaries (pipes, stages, streams, tasks, grants) in Section 7.1.
Database Roles: Encapsulation & Secure Data Sharing
Traditionally, Snowflake access control relied exclusively on Account Roles ( CREATE ROLE <role_name> ). While powerful, account roles create severe administrative coupling in large enterprises and cannot be shared across Snowflake accounts.
The Architecture of Database Roles
Database Roles are first-class securable access control objects scoped strictly inside a specific database namespace ( <database_name>.<database_role_name> ). USE DATABASE core_db;
- - Create a database-scoped role
- CREATE DATABASE ROLE finance_reader;
- - Grant privileges on schemas and tables within that database
- GRANT USAGE ON SCHEMA core_db.finance TO DATABASE ROLE finance_reader;
- GRANT SELECT ON ALL TABLES IN SCHEMA core_db.finance TO DATABASE ROLE finance_reader;
- Hierarchy Rules for Database Roles
Cannot be granted directly to users: A database role cannot be assigned to an end-user identity ( GRANT DATABASE ROLE ... TO USER ... is invalid SQL).
Granting to Account Roles: Database roles are granted to Account Roles ( GRANT DATABASE ROLE core_db.finance_reader TO ROLE bi_reporting; ), bridging local database privileges to enterprise users.
Nesting: Database roles can be granted to other database roles within the same database, forming modular access tiers.
The Killer Architectural Use Case: Secure Data Sharing
In Snowflake Secure Data Sharing, account roles cannot be added to a Share . Prior to database roles, consumers received all shared objects without granular internal permission distinctions.
With Database Roles, a data provider can define distinct permission tiers inside the shared database, grant object privileges to those database roles, and attach the database roles directly to the Share: -- In the Provider Account:
USE DATABASE customer_analytics;
- CREATE DATABASE ROLE tier1_basic;
- CREATE DATABASE ROLE tier2_advanced;
- GRANT SELECT ON VIEW customer_analytics.marts.summary_v TO DATABASE ROLE tier1_basic;
- GRANT SELECT ON TABLE customer_analytics.marts.detailed_facts TO DATABASE ROLE tier2_advanced;
- - Attach database roles directly to the secure share
- GRANT DATABASE ROLE customer_analytics.tier1_basic TO SHARE b2b_partner_share;
- GRANT DATABASE ROLE customer_analytics.tier2_advanced TO SHARE b2b_partner_share;
When consumer accounts mount the shared database, their local ACCOUNTADMIN can grant tier1_basic or tier2_advanced to different local account roles, cleanly separating provider RBAC from consumer governance.