2.4 Common Traps in Snowflake AI Data Cloud Features and Architecture

Time Travel

Time Travel lets you access historical data — query, clone, or restore objects as they existed at a point in the past — for a configured retention period. It is one of the most-tested storage features.

Within the retention window you can use SELECT ... AT (TIMESTAMP => ...), AT (OFFSET => ...), or BEFORE (STATEMENT => ...) to read prior states, and UNDROP TABLE/UNDROP SCHEMA/UNDROP DATABASE to restore a dropped object. Setting retention to 0 disables Time Travel for that object. A common trap: assuming Standard Edition can set 90 days — it cannot; 90-day retention requires Enterprise Edition or higher.

Fail-safe

Fail-safe is a separate, non-configurable 7-day period that begins immediately after the Time Travel retention period ends. During Fail-safe, data is recoverable only by Snowflake support (you cannot query it), and recovery is a best-effort operation that may take hours. Fail-safe exists for disaster recovery, not user self-service.

Key distinctions:

• Fail-safe is fixed at 7 days and cannot be turned off or changed.
• Fail-safe applies to permanent tables only — transient and temporary tables have no Fail-safe.
• Both Time Travel and Fail-safe storage count toward storage cost, which is why heavy-churn or staging tables are often made transient to avoid Fail-safe charges.

Table Types and Their Protection

The interaction between table type, Time Travel, and Fail-safe is a high-yield trap:

Transient and temporary tables save money because they carry no Fail-safe storage. Temporary tables also vanish at the end of the session. Permanent is the default and the most protected.

Snowflake Editions

Snowflake is sold in four editions, ascending in capability and price:

1. Standard — Full SQL data warehouse; Time Travel up to 1 day; all core features.
2. Enterprise — Adds multi-cluster warehouses, Time Travel up to 90 days, materialized views, column-level and row-level security, and search optimization.
3. Business Critical — Adds enhanced security/compliance (HIPAA, PCI DSS, ITAR), customer-managed keys via Tri-Secret Secure, database failover/failback, and private connectivity (AWS PrivateLink/Azure Private Link).
4. Virtual Private Snowflake (VPS) — The highest tier: a completely isolated Snowflake environment with its own dedicated resources, for organizations with the strictest security requirements.

Each higher edition includes everything in the editions below it. A frequent trap is attributing a feature like multi-cluster warehouses or 90-day Time Travel to Standard Edition — those require Enterprise or higher.

Data Types and Object Limits

Snowflake natively stores both structured and semi-structured data:

• Numeric: NUMBER, DECIMAL, INT, FLOAT, DOUBLE
• String/binary: VARCHAR, CHAR, STRING, TEXT, BINARY
• Date/time: DATE, TIME, TIMESTAMP_NTZ/LTZ/TZ
• Logical: BOOLEAN
• Semi-structured: VARIANT (holds JSON, Avro, ORC, Parquet, XML), OBJECT, ARRAY
• Geospatial: GEOGRAPHY, GEOMETRY
• Vector: VECTOR (for AI/ML similarity workloads)

The VARIANT type is central to Snowflake's semi-structured handling: you can load JSON directly into a VARIANT column and query nested elements with dot/bracket notation and FLATTEN, without a rigid schema. A single VARIANT value is limited to 16 MB compressed.

Other limits and behaviors the exam may probe:

• Object identifiers can be up to 255 characters and must start with a letter or underscore.
• A table can have up to 2,000 columns (this includes regular, transient, temporary, and external tables).
• Users never see or touch the physical storage files — all interaction is through SQL, and Snowflake manages compression, file size, and structure automatically.

Storage Cost Traps

A recurring exam trap is that Time Travel and Fail-safe both consume billable storage in addition to your active data. A high-churn staging table left as a permanent table can quietly carry up to its retention window of Time Travel plus a fixed 7 days of Fail-safe storage. The fix the exam expects: make ephemeral, reload-from-source tables transient (no Fail-safe, ≤1 day Time Travel) or temporary (session-scoped). Reserve permanent tables for data you must protect.

Another trap: changing a table or schema's DATA_RETENTION_TIME_IN_DAYS affects how much history is kept and therefore how much you pay; setting it to 0 disables Time Travel entirely and removes the ability to UNDROP after the operation.

Editions Recap and Feature Mapping

Misattributing features to editions is the single most common architecture trap. Anchor this table:

Because each edition is a superset of the ones below it, if a question asks for the minimum edition that supports a feature, pick the lowest tier in the table that lists it — not the highest. Confusing "supports" with "minimum required" is exactly the distractor the exam plants.

Permanent vs. Transient Decision

When a stem describes data that can be regenerated and emphasizes cost, the defensible answer is usually transient. When it emphasizes recoverability or compliance, the answer is permanent with appropriate Time Travel. Reading that cost-vs-protection cue correctly is what separates a confident answer from a guess.