Week 2 Recap, Drill + Exam Gotchas
Week 2 covers the densest stretch of Domain 1. Days 8 to 13 covered warehouses (sizes, Gen 2, Snowpark-Optimized), multi-cluster behaviour, and scaling policies. They also covered micro-partitions, table types, Iceberg, Dynamic Tables, and the three view types. Today is a closed-book drill, a gotcha map for recurring exam patterns, and a 10-question Week 2 test. Score 8 or above and you move to Week 3.
| Step | Time | What you do |
|---|---|---|
| 1. Closed-book drill | 30 min | Run the five drill tasks below. No tabs open. Write SQL on paper or a scratch buffer. |
| 2. Self-grade | 5 min | Open Snowsight, verify your answers, score yourself. |
| 3. Read the gotchas | 10 min | Speed-read the patterns below. These are the exam’s recurring Domain 1 traps for storage and compute. |
| 4. Take the Week 2 Practice Test | 15 min | 10 mixed questions covering Days 8–13. Target: 8/10. Anything below: re-read the day flagged in your wrong answers. |
The 30-Minute Drill
Write the size → credits/hour table from memory. 10 rows from XS to 6XL.
Expected:
XS = 1 L = 8 3XL = 64 5XL = 256
S = 2 XL = 16 4XL = 128 6XL = 512
M = 4 2XL = 32
Rule of thumb: credits/hour = 2^N
where N = number of size steps above XS.Resolve four warehouse decisions in under 30 seconds each.
Q1: One complex query takes 40 minutes. Same warehouse, 1 user.
A: Scale UP — resize bigger (XS→M→L). Concurrency isn't the issue.
Q2: 60 users hit dashboards; each query is 2 seconds; queries queue.
A: Scale OUT — multi-cluster, MAX=4, SCALING_POLICY = STANDARD.
Q3: Python UDF for ML training hits OOM on a Large standard warehouse.
A: Switch to Snowpark-Optimized (warehouse TYPE, not size). Min size = Medium.
Q4: Batch ETL runs nightly, tolerates queuing, cost is top priority.
A: Multi-cluster ECONOMY policy (~6 min sustained load before scale-up).Draw the table-type comparison from memory. Time Travel, Fail-safe, session survival, default flag.
Expected:
Time Travel Fail-safe Survives session
Permanent 0–90d (Ent+) 7 days Yes ← default
Transient 0–1d NONE Yes
Temporary 0–1d NONE NO (auto-drops at session end)
External NONE NONE Yes (read-only by default)
UNDROP after dropping:
Permanent — within TT (up to 90d) + 7d Fail-safe via Support → up to 97d
Transient — within TT (max 1d). After that GONE FOREVER.
Temporary — within session only. After session ends GONE FOREVER.Decide the right view / table feature for 4 scenarios.
Q1: Repeated heavy aggregate on single 5 TB table, slow-changing data,
Enterprise edition.
A: Materialized View (Enterprise+, serverless refresh, single base table OK).
Q2: Declarative pipeline that joins 3 tables, refresh within 2 minutes
of source changes, no Streams/Tasks orchestration code.
A: Dynamic Table with TARGET_LAG = '2 minutes'.
Q3: Share an aggregate that joins data across two databases.
A: Secure View in the shared database referencing both DBs.
Q4: Snowflake writes an open-format table; Spark reads same files.
A: Snowflake-managed Iceberg table (CATALOG = 'SNOWFLAKE').Speed round (60 seconds each):
Q: A warehouse resumes, runs a 5-second query, suspends. Billed?
A: 60 seconds. Per-resume minimum.
Q: Is Gen 2 available at 5XL or 6XL?
A: No. Gen 2 maxes out at 4XL. 5XL/6XL default to Gen 1.
Q: Per-credit pricing of Gen 2 vs Gen 1?
A: Gen 2 costs MORE credits per hour (≈1.35× AWS, 1.25× Azure); the value comes from faster runtime, not cheaper per-credit pricing.
Q: Minimum edition for multi-cluster warehouses?
A: Enterprise.
Q: Recommended cluster-key column priority?
A: WHERE filter > JOIN predicate. NOT GROUP BY or ORDER BY.
Q: What's the minimum TARGET_LAG on a Dynamic Table?
A: 1 minute.
Q: Maximum micro-partition size, uncompressed?
A: 500 MB.
Q: Can a materialized view join two tables?
A: No. Single base table only.Week 2 Exam Gotchas
These are the patterns the exam reuses in different scenarios. Each one comes with its “Tell”: the trigger phrase that flags the trap when you read the stem.
Gotcha 1 : Resize fixes per-query complexity; multi-cluster fixes concurrency
If queries run fast individually but queue under load, the fix is multi-cluster (scale OUT), not a bigger warehouse. Bigger only helps when a single query is the bottleneck. Tell: “50 BI users queue at 9 AM” or “queries each run in seconds but pile up”.
Gotcha 2 : Every resume costs a 60-second minimum
Per-second billing kicks in after the first 60 seconds. Aggressive auto-suspend can increase cost via constant cycling, not decrease it. Tell: “AUTO_SUSPEND = 30 to save credits.”
Gotcha 3 : Gen 2 costs more credits/hr but is faster
Gen 2 consumes MORE credits per hour than Gen 1 at the same size (≈1.35× AWS, 1.25× Azure), but finishes work faster. Net cost can go either way depending on workload shape. Not available at 5XL or 6XL. Not available for Snowpark-Optimized. Existing Gen 1 warehouses aren’t auto-migrated. Gen 2 is still opt-in today via the GENERATION or RESOURCE_CONSTRAINT clause. Snowflake docs confirm Gen 2 “currently are not the default when you create a standard warehouse”. Tells: “the 5XL warehouse on Gen 2” is always wrong. “Gen 2 has the same per-credit cost as Gen 1” is wrong. “Gen 2 is the default for new warehouses” is wrong today.
Gotcha 4 : Snowpark-Optimized solves memory, not speed
It’s a different warehouse type with more memory per node. Minimum size is Medium. Costs more per credit than Standard. Gen 2 does not apply. Tell: “Python UDF out of memory” or “ML training in Snowpark”.
Gotcha 5 : Multi-cluster is Enterprise+ only
Standard edition forces MAX_CLUSTER_COUNT = 1. If a stem describes concurrency problems on Standard, the right answer is “upgrade edition” or “use additional separate warehouses”. Never “enable multi-cluster”. Tell: Standard edition + “queries are queuing”.
Gotcha 6 : Standard is the aggressive scaling policy, Economy is conservative
STANDARD and ECONOMY are named backwards from what cost-sensitive readers guess. STANDARD scales up after about 20 seconds of queuing. ECONOMY waits for about 6 minutes of sustained load. Tell: “ECONOMY to add clusters faster” is the wrong direction.
Gotcha 7 : Snowflake has no indexes; pruning uses partition metadata
Min/max metadata per column per micro-partition is what drives pruning. There is no CREATE INDEX statement in Snowflake. Tell: Any answer that mentions creating an index.
Gotcha 8 : Cluster on WHERE columns first, then JOIN; never GROUP BY or ORDER BY
That priority is straight out of the Snowflake docs. Clustering on aggregate columns or ORDER BY columns is documented as “usually less helpful”. Tell: any answer naming a GROUP BY column as the best clustering choice.
Gotcha 9 : Don’t cluster small tables or low-cardinality columns
Clustering pays off on multi-TB tables with selective filters and degraded pruning. Small tables don’t have enough partitions to benefit. Boolean and status columns have too few distinct values. Tell: “add a clustering key on STATUS” (3 distinct values).
Gotcha 10 : Transient tables have NO Fail-safe; once Time Travel expires, gone
Max 1 day Time Travel. After that window, Snowflake Support cannot recover the data. Tell: “Snowflake Support recovered the dropped transient table” is always FALSE.
Gotcha 11 : Temporary tables are absolutely session-scoped
No GRANT, no role escalation can make a temp table visible to another session. Tell: “another user runs SELECT” against a temp table.
Gotcha 12 : Table type is immutable after creation
You cannot ALTER a permanent table into transient. The workaround is CLONE or CTAS into the new type, then SWAP names. Tell: “convert this permanent table to transient” usually hints that CLONE + SWAP is the answer, not ALTER.
Gotcha 13 : Iceberg ≠ external table
Standard external tables are read-only. Iceberg supports full DML and snapshot Time Travel in both catalog modes. Tell: “open format, multi-engine read/write” points to Iceberg, not external.
Gotcha 14 : Dynamic Tables refresh on demand, not on a fixed schedule
TARGET_LAG is a freshness target, not a cron expression. If the source hasn’t changed, no refresh happens and no compute is consumed. Tell: “TARGET_LAG = ‘5 minutes’ means the table is refreshed every 5 minutes” is FALSE.
Gotcha 15 : Materialized views are Enterprise+ AND single-table only
Two restrictions, both common traps. Plus no HAVING, no ORDER BY, and limited aggregates. Tell: “create an MV that joins X and Y” returns an error.
Gotcha 16 : Secure views are slower, not faster
Don’t confuse “secure” with “materialized”. Secure disables optimizer pushdown to protect base data. It stores nothing extra by default. Materialized stores precomputed results. Tell: “secure views improve performance via caching” is FALSE.
Gotcha 17 : Sharing across databases requires a secure view (exam-canonical)
By default, non-secure views can’t be added to a share. That’s the C03 standard answer. Snowflake has since added an opt-in SECURE_OBJECTS_ONLY = FALSE setting on shares, but the exam still tests the “secure view required” pattern. To share across databases, build a secure view in the shared DB that references the cross-DB objects. Tell: “share a view that joins two databases” means a secure view is required.
Week 2 Readiness Checklist
| If you can confidently… | You’re solid on | Otherwise re-skim |
|---|---|---|
| Write the size → credits/hour table from memory | Day 8 | Day 8: Sizes |
| Explain the 60-second minimum billing on resume | Day 8 | Day 8: Billing |
| Pick Standard vs Snowpark-Optimized for a workload | Day 8 | Day 8: Snowpark-Optimized |
| State the Gen 2 limitations (no 5XL/6XL, no Snowpark-Optimized) | Day 8 | Day 8: Gen 2 |
| Resolve scale UP vs scale OUT decisions in seconds | Day 9 | Day 9: Multi-cluster |
| Distinguish STANDARD vs ECONOMY scaling policies | Day 9 | Day 9: Scaling policies |
| Identify maximized vs auto-scale mode (MIN==MAX rule) | Day 9 | Day 9: Modes |
| Recall micro-partition size + columnar + immutable | Day 10 | Day 10: Micro-partitions |
| Identify “Partitions scanned ≈ total” as poor pruning | Day 10 | Day 10: Pruning |
| State cluster-key column priority (WHERE > JOIN) | Day 10 | Day 10: Clustering keys |
| Map each table type to Time Travel / Fail-safe | Day 11 | Day 11: Table types |
| Explain what happens when a transient table is dropped | Day 11 | Day 11: Drop lifecycle |
| Distinguish Snowflake-managed vs externally-managed Iceberg | Day 12 | Day 12: Iceberg modes |
| Explain TARGET_LAG semantics and DOWNSTREAM special value | Day 12 | Day 12: Dynamic Tables |
| Pick between MV, Dynamic Table, Streams+Tasks | Day 12, Day 13 | Day 12 / Day 13: Decision trees |
| State why secure views are slower | Day 13 | Day 13: Secure views |
| Explain why cross-database sharing needs a secure view | Day 13 | Day 13: Sharing |
Week 2 is the most heavily-weighted week of the exam. Domain 1 carries 31% of the score. Warehouses plus storage are the largest sub-objectives inside it. If a question mentions compute size, concurrency, clustering, Time Travel, or view/table type, you have 15 seconds to land on the answer.
Read the qualifier words first. MINIMUM edition means start at Standard and only climb when a feature forces it. MOST appropriate means eliminate over-specified answers. BEST means eliminate the technically-valid-but-wasteful options. On “Select TWO” multi-select, both answers must be correct. Partial credit is rare on the C03.
You’ve covered: warehouse sizing and the doubling credit rule. The 60-second minimum billing. Gen 1 vs Gen 2 (Gen 2 opt-in today, costs more credits/hr but faster, no 5XL/6XL, not for Snowpark-Optimized). Snowpark-Optimized for memory-heavy code. Multi-cluster scale-OUT for concurrency. STANDARD vs ECONOMY scaling policies. Micro-partitions (50–500 MB immutable columnar). Pruning via partition metadata. Clustering keys (WHERE > JOIN priority). The four table types and their CDP lifecycles. Iceberg’s two catalog modes with full DML. Dynamic Tables with TARGET_LAG. The three view types (standard / materialized / secure).
Take the Week 2 Practice Test (10 questions, mixed). Target: 8/10. If you score 6 or below, re-read the days flagged in your wrong answers before starting Week 3.
Tomorrow (Day 15): Week 3 begins. We pivot to AI/ML inside Snowflake: Snowpark + Notebooks + Streamlit. Sub-objective 1.6 is heavily weighted on C03 and brand new for many learners. We’ll cover how Python and Java code runs inside Snowflake with no data movement. We’ll also cover what Notebooks add on top and how Streamlit apps publish straight from the platform.
