1.3 Database and Schema Design Architecture

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:

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

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\text{ TB} \times 7\text{ days} = 70\text{ 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

1. 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).
2. 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.
3. 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.