SnowPro Core common mistakes
SnowPro Core · Understand the traps and how to avoid them.
Treats a clustering key as traditional static partitioning - expects defining one to physically split the table into partitions that must be maintained, and answers pruning questions as if partition creation is a DDL decision.
Most candidates arrive from Oracle/Teradata/Synapse where partitioning is an explicit physical layout chosen at CREATE TABLE time, so they map 'clustering' onto that model.
What to do instead
State the rule explicitly: every table is automatically stored in immutable, columnar micro-partitions of roughly 50–500 MB uncompressed. A clustering key only co-locates similar rows to reduce partition overlap and improve pruning; it creates no partitions and needs no maintenance. Then judge pruning from the Query Profile (partitions scanned vs. total), not from the DDL.
Restarts or resizes a warehouse to make an identical repeated query faster, believing query results are cached per warehouse.
Warehouse-centric mental model from other cloud data warehouses, plus the fact that Snowflake actually has three different caches that are rarely separated in study material.
What to do instead
Separate the three: the result cache lives in the Cloud Services layer at account level (up to 24 hours, reusable by any warehouse if the SQL text is identical and underlying data is unchanged), the local disk cache holds raw micro-partitions on warehouse compute nodes and is lost when the warehouse suspends, and the metadata cache serves statistics and clustering information. A result-cache hit needs no running warehouse at all.
Reads the role hierarchy backwards - expects a parent role such as SYSADMIN to gain privileges that were granted to a custom child role.
The word 'hierarchy' suggests the top role accumulates all power, but Snowflake privilege inheritance flows downward from the role that holds the grant.
What to do instead
Memorize the direction: privileges granted to a role are inherited by every role that has been granted that role. Granting SELECT to a custom role gives SYSADMIN nothing. To let SYSADMIN manage those objects, grant the custom role TO SYSADMIN - i.e., put the custom role underneath SYSADMIN in the tree.
Assumes SECURITYADMIN is the role that creates users and roles, and performs routine object grants as ACCOUNTADMIN because that always works.
The role names sound authoritative, and most tutorials run every statement as ACCOUNTADMIN, which hides the intended separation of duties.
What to do instead
USERADMIN creates and manages users and roles; SECURITYADMIN owns MANAGE GRANTS and administers grants (and inherits USERADMIN); SYSADMIN creates warehouses, databases and other objects; ACCOUNTADMIN is reserved for account-level settings and billing and should not own routine objects. Build a custom role tree and attach it under SYSADMIN.
Declares a query inefficient because it 'scanned millions of rows', without checking partitions scanned versus total or bytes spilled, and then recommends a bigger warehouse.
Row counts dominate EXPLAIN output in other engines; Snowflake's Query Profile reports partitions, bytes and cache percentages, and pruning - not row count - is the dominant cost driver.
What to do instead
Read the profile in a fixed order: partitions scanned vs. total (pruning quality), bytes scanned, percentage scanned from cache, then the operator-level time breakdown and any spilling to local or remote storage. Fix pruning first via clustering or better filter predicates; only then consider warehouse size for concurrency or spill-heavy operators.
Uses LATERAL FLATTEN and expects one output row per input row, or writes JSON paths in the wrong case and gets NULLs back.
Relational intuition says a lateral join preserves row counts, and standard SQL identifiers are case-insensitive - neither holds for a table function exploding VARIANT.
What to do instead
Remember FLATTEN is a table function producing one row per array element or object key; it belongs in the FROM clause with LATERAL and needs an input row to explode, so row counts multiply. Element and key names inside VARIANT are case-sensitive: reference them with the exact stored case (e.g. '$.userId') or the value resolves to NULL.
Writes PUT or GET inside a worksheet, stored procedure, or task and expects files to be staged.
PUT and GET look like ordinary SQL and the editor accepts the text, but they are client-side commands executed by the SnowSQL/connector process, not by the warehouse.
What to do instead
Run PUT/GET from SnowSQL, a driver, or the Snowsight upload control; they fail in worksheets, procedures and tasks. Also recall that a stage holds only a URL and file metadata - the bytes live in the external bucket or on the local filesystem, never inside the stage itself.
Assumes a COPY INTO that encounters a malformed row keeps loading the remaining rows, and skips a dry run before loading production files.
Many bulk loaders default to continue-on-error, so candidates assume Snowflake does too - its default is the opposite and the load stops.
What to do instead
ON_ERROR defaults to ABORT_STATEMENT; the other options are CONTINUE, SKIP_FILE, SKIP_FILE_, and SKIP_FILE_%. Set it deliberately, and validate files first with VALIDATION_MODE = RETURN_ERRORS or RETURN__ROWS so problems surface before any data lands.
Treats FAIL_SAFE as a queryable recovery window and assumes the default Time Travel retention is 90 days on every account.
Marketing-level familiarity with 'Time Travel and Fail-safe' blurs the line between the two, and most hands-on practice happens on trials or Enterprise accounts where longer retention is available.
What to do instead
Time Travel retention defaults to 0 or 1 day and is configurable up to 90 days only on Enterprise Edition and above, at table, schema or database level. FAIL_SAFE is a fixed 7-day period that begins after Time Travel ends, is not queryable, and can only be restored by Snowflake Support - never with SQL.
Believes a share copies data into the consumer account and that the consumer therefore pays for the storage, or that a secure view is always mandatory to share data.
Export/import and file-transfer mental models carry over from other platforms, and older documentation stressed secure views before direct table sharing was available.
What to do instead
A share is metadata plus grants: no data is copied, the provider pays storage and the consumer pays only for the warehouse used to query. Tables, secure views, secure UDFs and Iceberg tables can all be shared; a secure view is needed only when you must filter rows or columns. The consumer creates a database FROM SHARE and needs IMPORTED PRIVILEGES plus a warehouse.