SnowPro Advanced Architect common mistakes
SnowPro Advanced Architect · Understand the traps and how to avoid them.
Assuming the local disk cache and the query result cache behave the same way - designing a workload that relies on a warehouse's local cache surviving a suspend or being shared with a second warehouse used for the same queries.
Candidates memorize 'Snowflake caches results for 24 hours' and then reason about hardware sizing and workload isolation as if all caching were account-wide. They miss that the local disk cache is tied to a single running warehouse and is lost on suspend/resume, while the result cache is account-level and is invalidated by DML on the underlying tables.
What to do instead
Separate the two explicitly when designing: result cache = reused across warehouses and users holding the same role privileges, up to 24h, killed by any DML on the referenced tables; local disk cache = per-warehouse, warmed only by that warehouse, gone after suspend. Size and isolate warehouses with that asymmetry in mind, and never count on a cold warehouse inheriting another's cache.
Choosing clustering keys from intuition about the business entity (e.g. clustering a 40M-row table on CUSTOMER_ID) without ever checking pruning effectiveness before and after.
Clustering is the most-marketed performance feature in Snowflake, so candidates reach for it as a default answer for slow queries. The exam rewards the diagnostic sequence: confirm the query is actually scanning poorly-pruned micro-partitions, and confirm the table is large enough for clustering to pay for itself.
What to do instead
Always state the validation step: run the query, inspect partitions scanned vs. total via the query profile / SYSTEM$CLUSTERING_INFORMATION and SYSTEM$CLUSTERING_DEPTH, apply the key, then re-measure. Recommend clustering only for large tables with selective, high-concurrency filters, and price in the automatic clustering credits.
Responding to query queueing and concurrency waits by resizing the warehouse to a larger size instead of adding clusters.
Size and cluster count both sound like 'more compute', and candidates conflate scaling up with scaling out. Scaling up helps a single complex query that is short on memory or parallelism; it does nothing for too many concurrent queries competing for the same warehouse slots.
What to do instead
Diagnose first: long individual query runtimes with memory spill → larger warehouse; many fast queries queuing behind each other → multi-cluster warehouse (Enterprise edition and above) with an appropriate min/max cluster count and scaling policy. State the edition requirement and the credit implications of each choice.
Building a cost model that only counts virtual warehouse credits, then being surprised by the bill because auto-clustering, materialized view maintenance, Snowpipe, search optimization, replication and Snowpark-optimized compute are billed as serverless services.
Warehouse credit consumption is the visible, teachable part of Snowflake cost, so candidates design governance around warehouses alone. Serverless features are background services with no warehouse to monitor, so they escape resource monitors entirely.
What to do instead
Enumerate serverless consumers explicitly in any cost design and reconcile them from ACCOUNT_USAGE.METERING_HISTORY / METERING_DAILY_HISTORY by service type, alongside WAREHOUSE_METERING_HISTORY. Pair that with resource monitors for warehouse credit quotas and be clear that monitors do not cap storage or serverless spend.
Designing access control around the assumption that the account-level network policy plus password MFA is a complete perimeter, without accounting for user-level network policies overriding account-level ones and for key-pair or SSO authentication bypassing password-based MFA prompts.
Security questions are answered from a checklist memory ('enable MFA, add a network policy') rather than from how the controls compose. Candidates also forget that a network policy applied to a user replaces the account policy rather than adding to it, which silently widens access.
What to do instead
Treat the controls as a composition problem: know the precedence (user-level network policy wins over account-level), know which authentication paths still require a second factor, and validate the design with LOGIN_HISTORY, POLICY_REFERENCES and a test from an out-of-range IP. Prefer federation with MFA at the IdP for human users and key-pair auth for service users.
Describing Time Travel as the backup strategy and assuming Fail-safe data can be recovered self-service with a clone or an UNDROP.
Time Travel is easy to demo and feels like a safety net. Candidates forget that Fail-safe is a separate 7-day period reachable only through Snowflake Support, that transient tables have no Fail-safe, and that Time Travel retention differs by table type and edition.
What to do instead
In any recovery design, name the retention windows per table type (permanent vs transient vs temporary), state that Fail-safe recovery is a support-assisted, last-resort path, and pair Time Travel with actual exports or replication to a second account for true business continuity.
Assuming a data consumer can do normal development work inside a shared database - create tables or views there, clone it, or join across two separately shared databases as if they were one.
The share is presented as 'a read-only database', so candidates transfer their normal database habits onto it. They miss the structural constraints: shared objects are read-only, a single share can draw objects from only one database, and the provider controls what is exposed.
What to do instead
Design from the consumer side explicitly: create a local database and views over the shared objects for any reshaping, request a single consolidated share if cross-domain joins are needed, and use reader accounts only when the consumer has no Snowflake account (noting the provider pays for reader account compute). Verify grants with SHOW GRANTS TO SHARE / SHOW GRANTS OF SHARE.
Treating change data capture and pipelines as uniform: assuming a stream captures TRUNCATE TABLE and DDL, and assuming every dynamic table query refreshes incrementally at exactly its target lag.
Streams and dynamic tables are taught with simple INSERT/UPDATE examples. Edge behaviour - which operations a stream records, and which query shapes force a full refresh - is where the pipeline silently breaks or becomes expensive.
What to do instead
State the limits when designing: streams record DML change records but not TRUNCATE or DDL, so handle those out of band; dynamic tables fall back to full refresh for query constructs that cannot be incrementalized, and target lag is a freshness target, not a cron schedule. Validate with refresh history and pipe/stream staleness monitoring rather than assuming.
Treating Snowpipe as an exactly-once loader at the row level and skipping validation, so overlapping files or re-sent files quietly duplicate rows.
The word 'continuous' implies reliability, and candidates assume the pipe deduplicates content. Snowpipe tracks loaded files by path, not row content, and it will not stop you from loading two files containing the same rows.
What to do instead
Design idempotency explicitly: unique file paths, a staging/merge pattern or MERGE on a natural key for overlapping feeds, and validation via VALIDATION_MODE against a COPY statement before promoting a pipe. Monitor INFORMATION_SCHEMA/ACCOUNT_USAGE COPY_HISTORY and SYSTEM$PIPE_STATUS, and use METADATA$FILENAME / METADATA$FILE_ROW_NUMBER for lineage.
Assuming privileges flow both ways through the role hierarchy, and assuming a future grant applied today retroactively covers objects that already exist.
Role hierarchies and future grants are learned as diagrams, so candidates reason that an owning role above another role automatically confers its rights downward/upward, and that a future grant 'fixes' existing objects. Neither is true, which produces privilege drift after migrations and clones.
What to do instead
Apply the two rules mechanically: privileges granted to a role are inherited only by roles granted that role (ownership does not propagate in either direction), and future grants apply only to objects created after the grant - issue explicit grants for existing objects. Audit with SHOW GRANTS and POLICY_REFERENCES after any account or object restructuring.