Exam Focus · Part 6
Know where each object lives, which table type fits, and what streams, tasks, procedures and UDFs can do.
Know these cold
| Topic | Remember |
|---|---|
| Every database has | PUBLIC and INFORMATION_ |
| Object levels | Account: warehouses, users, roles, resource monitors, integrations. Schema: tables, stages, pipes, file formats |
| Info Schema vs Account Usage | Info Schema: one database, live. Account Usage: dropped objects, 365 days, lagged |
| Table types (Time Travel / Fail-safe) | Permanent: 0–90 / 7 days. Transient: 0–1 / none. Temporary: same, session only |
| Dynamic table | A query plus TARGET_. Snowflake refreshes it. No DML into it |
| Stream offset | Moves only when committed DML reads the stream, never on SELECT |
| Tasks | Created suspended. AFTER chains them. No WAREHOUSE means serverless |
| Procedure vs UDF | Procedure: CALL, DML, owner's rights by default. UDF: inline, no DML |
| Semi-structured | VARIANT, OBJECT, ARRAY. col:a.. Arrays: LATERAL FLATTEN |
| Iceberg vs external table | Iceberg: open format; Snowflake writes (own or REST catalog). External table: read-only |
Traps
- Myth A materialized view hides its definition.Fact Only SECURE ones do.
- Myth A materialized view can join two tables.Fact It reads one table. Use a dynamic table for joins.
- Myth LAST_QUERY_ID(2) is the second most recent query.Fact It's the session's second. -2 is the second most recent.
Before you turn the page
- Sort pipe, file format, warehouse, resource monitor and integration by level.
- Pick the table type for session scratch, rebuildable staging and source-of-truth data.
- Write a cast path to a nested JSON field, and a LATERAL FLATTEN.

