Week 3 Recap — Drill + Exam Gotchas
No new material today. Week 3 closed out the AI/ML developer surface (Snowpark, Notebooks, Streamlit, Cortex AI, Cortex Search, Cortex Analyst, Snowflake ML) and sub-objective 2.1 of Domain 2 (RBAC, DAC, the six system roles, secondary roles, and authentication including the mid-rollout MFA mandate). Two of the five official C03 sample questions are now wired in — Q4 (DAC) on Day 18 and Q5 (Snowpark) on Day 15. When I run SnowPro sessions at EY, Week 3 material is where the question split shifts: people stop asking “what does this feature do” and start asking “how do I tell these two apart on a one-screen multiple choice.” That’s exactly what today targets. Thirty-minute closed-book drill, then the gotchas, then the Week 3 test (10 questions, separate post). Score 8 or better and you’re cleared for Week 4 — governance and cost, the densest stretch of Domain 2.
| 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, run your SQL where applicable, score yourself honestly. |
| 3. Read the gotchas | 10 min | Speed-read the patterns below. These are the recurring traps for Week 3 material. |
| 4. Take the Week 3 Practice Test | 15 min | 10 mixed questions, separate post. Target: 8/10. Below that, re-read the day flagged in your wrong answers before Week 4. |
The 30-Minute Drill
Write the two sample-Q anchor sentences from memory. One for DAC, one for Snowpark. These are the two highest-yield single facts of Week 3 — both are paraphrased directly from official COF-C03 sample questions.
Expected:
SAMPLE Q4 → "Discretionary Access Control (DAC) is the framework
where each object has an owner role that can grant
access on that object to other roles."
SAMPLE Q5 → "Snowpark hosts non-SQL code (Python, Java, Scala)
that runs INSIDE Snowflake on a warehouse, with no
data movement."
Trap reminders:
• DAC does NOT replace RBAC — both run simultaneously.
• External Functions are the OPPOSITE of Snowpark (data goes out
to an HTTPS endpoint, not code coming in).List the six Cortex SQL function names and what each does. The most-tested fact from Day 16 is the names. The second-most-tested is that they are pure SQL — no Snowpark, no Python.
Acceptable answers (all six):
1. COMPLETE → free-form generation; first arg = model name
2. SUMMARIZE → shrink a passage to its essentials
3. TRANSLATE → language → language
4. SENTIMENT → score -1 to 1 (NOT a label!)
5. EXTRACT_ANSWER → Q&A over an unstructured passage
6. CLASSIFY_TEXT → label from a list you supply
Schema prefix: SNOWFLAKE.CORTEX.
Required role: CORTEX_USER (granted to PUBLIC by default)
Trap to avoid: "These functions require Snowpark/Python." FALSE.
They are SQL functions, callable from any worksheet or driver. Name all six system roles and one defining responsibility for each.
Expected (Day 19):
• ORGADMIN → organization-level (creating accounts).
NOT in the standard role hierarchy.
Being phased out for GLOBALORGADMIN.
• ACCOUNTADMIN → top of the standard hierarchy. Sees billing.
NOT a superuser (cannot manage objects owned
by custom roles outside its chain).
• SECURITYADMIN → MANAGE GRANTS privilege; creates NETWORK
POLICIES; inherits USERADMIN.
• USERADMIN → CREATE USER and CREATE ROLE.
• SYSADMIN → warehouses and databases; recommended
parent for all custom roles.
• PUBLIC → pseudo-role every user gets automatically;
every role inherits PUBLIC.
Hierarchy (standard chain only):
ACCOUNTADMIN
├── SECURITYADMIN
│ └── USERADMIN
└── SYSADMIN
└── (your custom roles)
└── PUBLIC ← everyone inheritsWrite the three-level privilege chain to read a table, plus the secondary roles command.
-- Read a table named sales_db.public.orders from role analyst_r
GRANT USAGE ON DATABASE sales_db TO ROLE analyst_r;
GRANT USAGE ON SCHEMA sales_db.public TO ROLE analyst_r;
GRANT SELECT ON TABLE sales_db.public.orders TO ROLE analyst_r;
-- ↑ outermost innermost
-- Activate every granted role as secondary
USE SECONDARY ROLES ALL;
-- Inspect what's active
SELECT CURRENT_ROLE(), CURRENT_SECONDARY_ROLES();
Trap reminders:
• The order is Database → Schema → Table. Memorize it.
• USAGE is for containers (DB, schema, warehouse). NOT for tables.
• SELECT is for tables. NOT for schemas.
• Secondary roles CANNOT create objects — only the primary role can.
• DEFAULT_SECONDARY_ROLES = 'ALL' is the new default (BCR 1692, 2024).Bonus speed round (60 seconds each):
Q: Match each scenario to its authentication method.
1. Nightly ETL CI/CD job → Key-pair
2. Tableau querying for a user → OAuth
3. Enterprise user via Okta → SSO / SAML 2.0
4. Interactive password login → MFA (mandatory now)
Q: Which Snowpark-Optimized warehouse fact is the canonical exam answer?
A: Minimum size MEDIUM. ~16× memory. ~1.5× credits.
(The newer RESOURCE_CONSTRAINT clause lets XS work, but stick to
the canonical "Medium minimum" on test day.)
Q: Cortex Search vs SQL LIKE — when does each win?
A: Cortex Search → semantic / hybrid retrieval (vector + lexical +
reranker). When meaning matters more than exact words.
LIKE → exact pattern matching. When the words ARE the query.
Q: Cortex Analyst is broken — accuracy is bad. What's the fix?
A: Build a semantic model (YAML on stage, or a Semantic View).
Schema-only text-to-SQL is the failure mode.
Q: Account identifier — which format is recommended and why?
A: orgname-accountname. Stable across region migrations.
account_locator includes region+cloud, so it changes on migration.
Q: Three security models in Snowflake's current docs?
A: DAC + RBAC + UBAC (User-Based Access Control, narrow case,
only active with USE SECONDARY ROLES = ALL).
C03 canonical = DAC + RBAC (Practice Q18 verbatim).Week 3 Exam Gotchas
These are the recurring patterns the exam dresses up in scenarios. Match each gotcha to its “tell” (the trigger phrase in the stem) and you’ll catch them in the moment, not in the post-mortem.
Gotcha 1 — “Cortex AI requires Snowpark / Python” → FALSE
Cortex AI functions live in the SNOWFLAKE.CORTEX schema and are SQL functions. Any SQL interface can call them — worksheets, JDBC, dbt, anywhere a SELECT runs. Tell: an option claiming Cortex needs Python, Snowpark, or an external API.
Gotcha 2 — “SENTIMENT returns positive / negative / neutral” → FALSE
SENTIMENT returns a float between -1 and 1. A score, not a label. The function that returns a label from a list you supply is CLASSIFY_TEXT. Tell: any answer that frames SENTIMENT’s output as a string category.
Gotcha 3 — “Snowpark = external code calling Snowflake” → FALSE
Snowpark is the opposite. Your code is hosted inside Snowflake, executing on a warehouse, with no data movement. External Functions call out to a third-party HTTPS endpoint. These two get swapped on the exam more often than any other pair in Domain 1. Tell: “high-performance non-SQL code on Snowflake data” → Snowpark. “Call an external REST API” → External Function.
Gotcha 4 — Snowpark-Optimized minimum size is MEDIUM, not X-Small
In classic mode, Snowpark-Optimized warehouses don’t support X-Small or Small. The minimum is Medium. They provide ~16× memory and consume ~1.5× the credits of a standard warehouse at the same size. Query Acceleration Service is not supported. Tell: “ML training” or “memory-intensive Python” in the stem — the answer is almost always Snowpark-Optimized.
Gotcha 5 — Streamlit-in-Snowflake = native object, no external hosting
Source files live on an internal stage (external stages are not supported), access is governed by RBAC, and users open the app through Snowsight. Tell: any answer that mentions Streamlit Community Cloud, a separate hosted URL, or “deploy to an external service.”
Gotcha 6 — Two-warehouse Notebook model
A Snowflake Notebook on the Warehouse Runtime uses two warehouses. The Notebook warehouse runs the Python kernel (stays alive for the session via EXECUTE NOTEBOOK) and the Query warehouse runs SQL and Snowpark queries on demand. The default Notebook warehouse is the Snowflake-managed SYSTEM$STREAMLIT_NOTEBOOK_WH. Tell: “the team’s notebook costs are surprisingly high” — the answer is almost always that the Notebook warehouse is sized too large for what is essentially a kernel host.
Gotcha 7 — Cortex Analyst needs a semantic model
Without a semantic model (YAML on stage, or a Semantic View), Cortex Analyst falls back to schema-only text-to-SQL and accuracy collapses. The fix is always the same: build a semantic model. Larger warehouses, retraining the LLM, and changing editions are all distractors and all wrong. Tell: “Cortex Analyst SQL is wrong” or “accuracy is poor.”
Gotcha 8 — Cortex Search vs LIKE vs Search Optimization Service
Three different things, frequently mixed in the same multi-select:
- Cortex Search = hybrid retrieval (vector + lexical + reranker) on text. Answers “find documents that mean X.”
- SQL LIKE / ILIKE = exact pattern matching. Answers “find rows where column matches the wildcard.”
- Search Optimization Service (Day 38 territory) = a persistent search-access-path structure that accelerates point-lookup queries on high-cardinality columns — equality, IN, substring. A performance feature, not a semantic search.
Tell: the verbs in the stem. “Find documents about X” → Cortex Search. “Find rows where status = ‘X'” → LIKE or Search Optimization.
Gotcha 9 — Snowflake ML built-ins are SQL-callable
FORECAST, ANOMALY_DETECTION, and CLASSIFICATION are accessed via CREATE SNOWFLAKE.ML.<CLASS> then model!METHOD(…). No Python required. The Snowpark ML Python library is a separate, broader tool for data scientists who want to write training code in Python. Tell: “must use Snowpark Python for time-series forecasting” is the trap.
Gotcha 10 — ML Models are first-class database objects (1 of 12)
The “which are database objects?” multi-select is a recurring trap. ML Models belong in the database-object list, alongside Tables, Views, Schemas, Stages, File Formats, Sequences, UDFs, Stored Procedures, Pipes, Shares, and Applications. Roles, Users, Warehouses, Resource Monitors, Network Policies, and Storage Integrations do not belong — those are account-level securables. Tell: a mix-list multi-select with ML Models adjacent to Roles or Resource Monitors.
Gotcha 11 — “Snowflake uses RBAC only” → FALSE
Snowflake uses DAC + RBAC simultaneously (and optionally UBAC). DAC is ownership-based. RBAC is role-based. UBAC grants privileges directly to a user but only takes effect when USE SECONDARY ROLES = ALL. For C03, the canonical answer to “which models does Snowflake use?” is DAC and RBAC (this is the Practice doc Q18 verbatim answer). Tell: a single-select that names only one model.
Gotcha 12 — “Users own objects” → FALSE
In Snowflake, roles own objects. Whichever role was active when CREATE ran becomes the owner. If three users share the same role, they all effectively share ownership of anything that role creates. Ownership transfers with GRANT OWNERSHIP ON <obj> TO ROLE <other>. Tell: a stem that talks about a user being “the owner” of a table.
Gotcha 13 — SYSADMIN creates network policies → FALSE
SECURITYADMIN (or higher) creates network policies. SYSADMIN handles warehouses and databases — the operational layer, not the perimeter. ACCOUNTADMIN can create one too because it inherits SECURITYADMIN, but the principle-of-least-privilege answer the exam wants is SECURITYADMIN. Tell: “which role should create the network policy?” — pick SECURITYADMIN.
Gotcha 14 — ACCOUNTADMIN is not a superuser
ACCOUNTADMIN sits at the top of the standard hierarchy, but by default it cannot modify objects owned by custom roles that aren’t granted into its chain. This is the reason Snowflake recommends granting every custom role to SYSADMIN — otherwise you end up with orphan roles whose objects ACCOUNTADMIN literally can’t see. Tell: any option saying “ACCOUNTADMIN can do anything” or “ACCOUNTADMIN sees all objects.”
Gotcha 15 — Database roles cannot be activated with USE ROLE
Account roles can be activated directly (USE ROLE my_role). Database roles cannot. They must be granted to an account role, which is then activated, and the privileges flow through. Tell: any answer suggesting USE ROLE my_db.my_db_role works.
Gotcha 16 — “Key-pair authentication requires a password” → FALSE
Key-pair auth uses a private key to sign a JWT. No password is set, stored, or sent to Snowflake. A passphrase that encrypts the private key locally is a client-side secret — it never leaves the machine. Service users (TYPE = SERVICE) cannot use passwords at all under the password-deprecation rollout — they must use key-pair, OAuth, PAT, or WIF. Tell: any answer pairing “key-pair” with “password.”
Gotcha 17 — OAuth ≠ SSO/SAML
SSO/SAML is for humans logging in via an external IdP. OAuth is for tools acting on behalf of humans, using scoped tokens. Different problems, frequently both present in the same architecture. Tell: “Tableau queries Snowflake on behalf of the user” → OAuth. “Users authenticate through corporate Okta” → SSO/SAML.
Gotcha 18 — Account locator changes on region migration
The legacy account-locator form (xy12345.us-east-1.aws) bakes the region and cloud into the identifier. If your account is replicated or failed over to a different region, the locator changes — and every driver, BI tool, and connection string using it breaks. The orgname-accountname form is stable across migrations and is the format Snowflake recommends today. Tell: “the company plans to migrate to a different region; how should they configure connection strings?”
Week 3 Readiness Checklist
| If you can confidently… | You’re solid on | Otherwise re-skim |
|---|---|---|
| Recognize the Sample Q5 paraphrase (“host non-SQL code”) | Day 15 | Day 15 — Snowpark one-liner |
| Distinguish Snowpark / External Function / Stored Procedure / UDF | Day 15 | Day 15 — Code-into-Snowflake vs data-out |
| Recall Snowpark-Optimized min size + memory/credit ratios | Day 15 | Day 15 — Snowpark-Optimized table |
| Name all 6 Cortex SQL functions + purposes (and the no-Python claim) | Day 16 | Day 16 — Cortex SQL functions |
| Match SENTIMENT to “returns float -1..1, NOT a label” | Day 16 | Day 16 — SENTIMENT trap |
| Distinguish Cortex Search from LIKE and Search Optimization Service | Day 16 | Day 16 — Cortex Search vs LIKE |
| Recall Cortex Analyst needs a semantic model | Day 17 | Day 17 — Cortex Analyst |
| Name the Snowflake ML built-in classes (FORECAST, ANOMALY_DETECTION, CLASSIFICATION) | Day 17 | Day 17 — Snowflake ML |
| Confirm ML Models are first-class database objects (1 of 12) | Day 17 (cross to Day 5) | Day 17 + Day 5 callback |
| Recognize Sample Q4 paraphrase (“owner grants access”) = DAC | Day 18 | Day 18 — DAC one-liner |
| Resolve a privilege-chain question (DB → Schema → Table) | Day 18 | Day 18 — Privilege chain |
| Recall that database roles cannot be activated with USE ROLE | Day 18 | Day 18 — Database vs account roles |
| Name all 6 system roles + one defining responsibility each | Day 19 | Day 19 — System roles table |
| Recall SECURITYADMIN (not SYSADMIN) creates network policies | Day 19 | Day 19 — Network policy trap |
| Recall ACCOUNTADMIN is not a superuser | Day 19 | Day 19 — ACCOUNTADMIN |
| Recall secondary roles syntax + only-primary-role-creates rule | Day 19 | Day 19 — Secondary roles |
| Match each auth method to its canonical scenario | Day 20 | Day 20 — Auth methods table |
| Recall key-pair = no password; service users can’t use passwords | Day 20 | Day 20 — Key-pair + user TYPE |
| Recall orgname-accountname stable; locator changes on migration | Day 20 | Day 20 — Account identifiers |
Identify LOGIN_HISTORY as the auth-method audit source | Day 20 | Day 20 — LOGIN_HISTORY |
Watch for inverted-direction traps. Both Week 3 sub-objectives have natural directional opposites that the exam loves to swap. Snowpark (code goes into Snowflake) versus External Functions (data goes out). SSO/SAML (humans authenticate to Snowflake via an IdP) versus OAuth (tools act on behalf of humans, with a scoped token). When a stem describes one direction and an answer states the opposite, slow down and re-read the stem. The exam writer’s favorite move is to describe the right scenario in plain English and slot the wrong direction’s name into the answer key. The direction words — into, out, on behalf of, through — are the signal.
What’s covered: the full AI/ML developer surface (Snowpark for non-SQL code inside Snowflake; Notebooks on the two-warehouse model with the default SYSTEM$STREAMLIT_NOTEBOOK_WH; Streamlit-in-Snowflake as a native, RBAC-governed object; the six Cortex AI SQL functions; Cortex Search for hybrid retrieval; Cortex Analyst for natural-language to SQL via a semantic model; Snowflake ML built-ins FORECAST, ANOMALY_DETECTION, and CLASSIFICATION; ML Models as first-class database objects) plus the access-control foundations of Domain 2 (DAC + RBAC + UBAC; the six system roles with SECURITYADMIN owning network policies; account roles versus database roles; secondary roles and the primary-only object-creation rule; the four authentication methods and the active mid-2026 password-deprecation rollout).
Now take the Week 3 Practice Test (10 questions, mixed Week-3 topics). Target: 8/10. A 6 or below means re-read the days flagged in your wrong answers before Week 4 begins.
Tomorrow (Day 22) — Week 4 begins. Domain 2 enters its densest stretch: Network Policies + Logging & Tracing on Day 22, masking and row access policies on Day 23, governance (tagging, classification, Trust Center) on Day 24, encryption + replication + alerts on Day 25, resource monitors on Day 26, and the ACCOUNT_USAGE deep-dive on Day 27. Two more sample-exam questions wire in on Day 24: Q1 (Data Classification) and Q3 (Trust Center).
