Table Types and Views

Overview

Snowflake provides various table types and view architectures to handle different data engineering, temporary processing, and application workloads. For the COF-C03 exam, knowing the differences in persistence, Time Travel, Fail-Safe, and use cases for each table type is heavily tested.

Key Concepts: Table Types

1. Permanent Tables

• Description: The default table type. Used for long-term, critical business data.
• Time Travel: 0 to 90 days (Enterprise edition required for > 1 day).
• Fail-Safe: 7 days (Non-configurable).
• Persistence: Indefinite.

2. Transient Tables

• Description: Used for intermediate data, ETL staging, or data that can be easily recreated. They do not have a Fail-Safe period, saving on storage costs.
• Time Travel: 0 to 1 day (Cannot be extended).
• Fail-Safe: 0 days.
• Persistence: Indefinite (exists until explicitly dropped).

3. Temporary Tables

• Description: Used for temporary scratchpad data during a specific session.
• Time Travel: 0 to 1 day.
• Fail-Safe: 0 days.
• Persistence: Session-scoped. Automatically dropped when the session ends. Note: A temporary table can have the same name as a permanent table in the same schema; the temporary table will take precedence for that session.

4. External Tables

• Description: Point to data files stored in external cloud storage (e.g., S3, Azure Blob) without bringing the data into Snowflake's internal storage. They are read-only.
• Persistence: Data remains external. Snowflake only stores metadata.
• Use Case: Querying data lakes directly; cost-effective for infrequently accessed data.

5. Dynamic Tables

• Description: Declarative data pipelines. You specify a SQL query, and Snowflake automatically manages the scheduling and incremental refreshes to materialize the results.
• Use Case: Replacing complex streams and tasks for simple continuous data pipelines.

6. Hybrid Tables

• Description: Designed for operational and transactional (OLTP) workloads (Snowflake Unistore). They use row-based storage and support constraints like primary keys (enforced), foreign keys, and unique constraints.
• Use Case: High-concurrency, low-latency transactional applications natively built in Snowflake.

Key Concepts: Views

Standard Views

A saved SQL query. Evaluated at query time. Does not store data physically.

Secure Views

• Prevents users from seeing the underlying SQL definition of the view (via GET_DDL or Snowsight).
• Query optimizer bypasses certain optimizations to prevent data leakage (e.g., preventing users from deducing hidden data via error messages).
• Crucial for Secure Data Sharing.

Materialized Views

• Pre-computes and physically stores the result set of the query.
• Requires Enterprise Edition.
• Snowflake automatically maintains the materialized view in the background as underlying base tables change.
• Best for queries that are complex but run frequently over data that doesn't change constantly.

Table Comparison

| Table Type | Scoped to | Time Travel | Fail-Safe | Enforced PKs |

| :--- | :--- | :--- | :--- | :--- |

| Permanent | Database/Schema | 0-90 days | 7 days | No |

| Transient | Database/Schema | 0-1 day | 0 days | No |

| Temporary | Session | 0-1 day | 0 days | No |

| Hybrid | Database/Schema | 0-90 days | 7 days | Yes |

SQL Examples

-- Create a Transient Table
CREATE TRANSIENT TABLE staging_data (id INT, val VARCHAR);

-- Create a Temporary Table
CREATE TEMPORARY TABLE session_scratch (id INT);

-- Create a Secure View
CREATE SECURE VIEW secure_sales_vw AS
SELECT * FROM sales WHERE region = 'US';

Exam Tips

⚠️ Crucial for COF-C03:

• Cost Savings: Transient and Temporary tables save money because they do not incur Fail-Safe storage costs.
• Naming Collisions: If you create a temporary table with the same name as a permanent table, queries in that session resolve to the temporary table.
• Hybrid Tables: Remember they are for OLTP workloads and *enforce* constraints (unlike standard Snowflake tables where PKs are just metadata).
• Secure Views: Used heavily in Data Sharing to hide underlying logic.

Key Takeaways

• Permanent: Default, maximum protection.
• Transient: Intermediate data, no Fail-Safe.
• Temporary: Session-specific, auto-dropped.
• Dynamic: Declarative pipelines.
• Hybrid: OLTP workloads.
• Materialized views cost compute to maintain but speed up read queries.