Snowflake DEA-C02: Storage and Data Protection Practice Questions

UNDROP TABLE is the correct command to restore a dropped table within its Time Travel retention period. Since the table is permanent on Enterprise Edition with a 7-day retention, the 5-day-old drop is still within the window. — ALTER TABLE ... UNDELETE is not valid Snowflake syntax. — RESTORE TABLE is not a Snowflake DML command. — CREATE TABLE ... CLONE with BEFORE can recreate the table's data from Time Travel history, but this would create a new clone object rather than restoring the original table in place, and it requires referencing the dropped object with its ID (BEFORE STATEMENT syntax), which is a more complex workaround, not the canonical recovery command.

REPLICATION GROUP failover is atomic — all objects (databases, shares, etc.) within the group are failed over together to a consistent snapshot, which is a key advantage over per-database replication for maintaining cross-database consistency. Client redirect (using a connection URL) is a Snowflake feature that allows connection strings to automatically route to the current primary after failover, significantly reducing application reconfiguration effort. — Individual database failover is not possible within a REPLICATION GROUP; the group fails over as a unit. This is precisely why REPLICATION GROUPs exist. — After failover, the old primary is NOT automatically demoted and reconfigured; the administrator must manually reconfigure replication direction or set up a reverse replication path. — Network policies are associated with accounts, not replication groups; they do not need to be re-applied after failover because the new primary account already has its own network policies.

When using an external catalog (like AWS Glue or a REST catalog), Snowflake can query Iceberg tables written by other engines (e.g., Spark, Flink), enabling true multi-engine interoperability. Snowflake fully supports DML (INSERT, UPDATE, DELETE, MERGE) on Iceberg tables when Snowflake is the catalog, making them viable for operational workloads, not just read-only analytics. — Snowflake-managed Iceberg Tables do store data in a Snowflake-managed location, but the metadata IS stored as standard Iceberg metadata files in cloud storage (not hidden), so external engines CAN read them given appropriate permissions. — Native Snowflake tables (with proprietary micro-partitions and clustering) generally outperform Iceberg tables for most Snowflake query patterns; Iceberg tables trade some performance for open-format interoperability. — The statement that Snowflake-as-catalog Iceberg metadata is proprietary is false; it follows the Iceberg open specification.

Secondary databases in Snowflake replication are read-only replicas; write operations are not permitted until a failover is performed that promotes the secondary to primary. This makes them suitable for offloading BI reads. Replication is not synchronous — it uses a refresh cycle, so there is inherent latency between the primary and secondary, and the secondary may lag behind depending on when the last REFRESH was executed. — The secondary database does not support write operations until failover; this is by design. — After failover, the original primary does NOT automatically re-synchronize; you must manually reconfigure replication roles if you want to reverse the direction or set up a new secondary. — Cross-region data transfer, even within the same cloud provider, typically incurs egress charges in Snowflake replication.

Temporary tables are session-scoped: they are automatically dropped when the session ends, incur no Fail-safe storage costs (0-day Fail-safe), and have a maximum Time Travel of 1 day (default 0). They are ideal for truly transient, single-session intermediate data. — Permanent tables with retention set to 0 still incur 7-day Fail-safe charges, increasing storage costs unnecessarily. — Transient tables avoid Fail-safe but persist beyond the session until explicitly dropped; they are better suited for short-lived data that must survive across multiple sessions or users, not exclusively single-session work. — External tables store no data in Snowflake (data lives in cloud storage) and are not suitable for staging pipeline data being written and processed within Snowflake.