Question bank
SnowPro Core Certification
42 SnowPro Core Certification questions with the correct answer and a written explanation, organised across 5 exam domains.
- 42 questions
- 5 domains
- Foundation level
certilumQuestion index
Jump straight to any question. Answers and explanations are inline.
Account Management & Data Governance
8 questions in this domain
- Question 1Single Choice
Which Snowflake role is the highest-level super-user role that encompasses both SYSADMIN and USERADMIN privileges?
- AACCOUNTADMINCorrect answer
- BSYSADMIN
- CSECURITYADMIN
- DUSERADMIN
Why this is the answer
ACCOUNTADMIN is the top-level role that includes both SYSADMIN and USERADMIN privileges, providing full account control.
- Question 2Multiple Select
Which of the following statements about the SYSADMIN role are correct? (Select two.)
- AIt can create virtual warehouses.Correct answer
- BIt can create new roles.
- CIt can manage users.
- DIt can create databases and schemas.Correct answer
Why this is the answer
SYSADMIN can create warehouses, databases, and schemas, but cannot create roles or manage users; those require USERADMIN or ACCOUNTADMIN.
- Question 3Interactive
Review the following SQL commands. Which privilege is missing that would allow the data_engineer role to query the table?
- AGRANT USAGE ON DATABASE analytics_db TO ROLE data_engineer;
- BGRANT USAGE ON SCHEMA analytics_db.public TO ROLE data_engineer;Correct answer
- CGRANT SELECT ON ALL TABLES IN SCHEMA analytics_db.public TO ROLE data_engineer;
- DGRANT CREATE TABLE ON SCHEMA analytics_db.public TO ROLE data_engineer;
Why this is the answer
Even with USAGE on the database and SELECT on the table, the role still needs USAGE on the schema to access objects within it.
- Question 4Single Choice
Which system-defined role has the privilege to create new users and assign roles to them?
- ASYSADMIN
- BUSERADMINCorrect answer
- CPUBLIC
- DACCOUNTADMIN
Why this is the answer
USERADMIN is specifically designed to create and manage users and roles; ACCOUNTADMIN inherits this but is not the dedicated role.
- Question 5Multiple Select
Which of the following are true about the PUBLIC role? (Select two.)
- AEvery user automatically has the PUBLIC role.Correct answer
- BPrivileges granted to PUBLIC are only available to new users after the grant.
- CGranting SELECT on a table to PUBLIC makes it accessible by all current users.Correct answer
- DPUBLIC is a system role that cannot be dropped or modified.
Why this is the answer
All users automatically get PUBLIC, and granting privileges to PUBLIC immediately affects all current and future users.
- Question 6Interactive
After executing the statements below, why can user John still query the table even though his DATA_ANALYST role was revoked?
- AJohn still has the ACCOUNTADMIN role that was never revoked.
- BThe SELECT privilege was also granted to the PUBLIC role.Correct answer
- CThe revocation command failed silently because the role was not granted.
- DJohn's default role provides the same access via another role.
Why this is the answer
If the table's SELECT privilege was granted to PUBLIC, all users retain access regardless of individual role revocations.
- Question 7Single Choice
Which privilege must be granted on a database to allow a role to use objects inside it?
- AUSAGECorrect answer
- BSELECT
- COWNERSHIP
- DCREATE
Why this is the answer
USAGE on a database is the entry-level privilege; it allows the role to see the database and reference its schemas, but not query tables directly.
- Question 8Multiple Select
Which of the following are true regarding role hierarchy in Snowflake? (Select two.)
- AIf role B is granted to role A, role A inherits all privileges of role B.Correct answer
- BIf role B is granted to role A, role B inherits the privileges of role A.
- CPrivileges flow upward from child roles to parent roles.Correct answer
- DRole hierarchy allows users to activate any role in the chain automatically.
Why this is the answer
Granting a role to another role creates inheritance upward; the parent role gets the child's privileges, but not vice versa.
Data Collaboration
4 questions in this domain
- Question 9Single Choice
What is the key advantage of Snowflake Data Sharing compared to traditional data transfer?
- AData is copied to the consumer's account
- BReal-time access without data movementCorrect answer
- CRequires complex ETL processes
- DOnly works with Reader Accounts
Why this is the answer
Snowflake Data Sharing provides real-time access to live data without copying or moving it, reducing storage cost and latency.
- Question 10Multiple Select
Which statements are true about Reader Accounts in Snowflake?
- AThey require the consumer to have a Snowflake account
- BThey are lightweight accounts for data consumption onlyCorrect answer
- CThe provider pays for compute costsCorrect answer
- DThey can write data back to the provider
Why this is the answer
Reader Accounts are fully-managed accounts for consumers without Snowflake; provider pays compute and consumers cannot write data.
- Question 11Interactive
What SQL command is used to create a share for data sharing?
- ACREATE SHARECorrect answer
- BCREATE LISTING
- CCREATE READER ACCOUNT
- DCREATE DATABASE
Why this is the answer
The CREATE SHARE command creates a share object to grant access to a set of database objects.
- Question 12Single Choice
What is a listing in the Snowflake Marketplace?
- AA published share with metadataCorrect answer
- BA direct share without discoverability
- CA reader account for a consumer
- DA database that is copied to consumers
Why this is the answer
A listing is a published share with metadata, description, and optional pricing, discoverable via the Marketplace.
Data Loading, Unloading & Connectivity
8 questions in this domain
- Question 13Single Choice
What is the primary command for bulk loading data from staged files into a Snowflake table?
- ACOPY INTOCorrect answer
- BINSERT
- CMERGE
- DLOAD DATA
Why this is the answer
COPY INTO is the foundational command for bulk loading data from staged files into tables in Snowflake.
- Question 14Multiple Select
Which of the following are true about Snowpipe? (Choose two.)
- AIt uses serverless computeCorrect answer
- BIt requires a virtual warehouse
- CIt is event-drivenCorrect answer
- DIt only supports internal stages
Why this is the answer
Snowpipe uses serverless compute and is event-driven; it does not require a virtual warehouse and supports external stages.
- Question 15Interactive
Refer to the code block. Which file format is being used for unloading?
- ACSV
- BJSON
- CParquetCorrect answer
- DAvro
Why this is the answer
The FILE_FORMAT option TYPE=PARQUET indicates Parquet is used for unloading.
- Question 16Single Choice
Which stage type is automatically created for every Snowflake user?
- AUser stageCorrect answer
- BTable stage
- CNamed internal stage
- DExternal stage
Why this is the answer
Every Snowflake user automatically gets a user stage, referenced as @~.
- Question 17Multiple Select
Which two connectors are commonly used to connect BI tools to Snowflake?
- AJDBC
- BODBCCorrect answer
- CPython Connector
- DSpark Connector
Why this is the answer
ODBC is the standard driver for BI tools; JDBC is for Java apps, not commonly directly used by BI tools.
- Question 18Interactive
Examine the SQL. What does the ON_ERROR option do?
- ASkips the entire file
- BContinues with the next row
- CAborts the statementCorrect answer
- DSkips the file only if >10% errors
Why this is the answer
With ABORT_STATEMENT (default), the entire COPY operation halts on the first error.
- Question 19Single Choice
What triggers Snowpipe to load files when AUTO_INGEST=TRUE?
- ACloud storage event notificationsCorrect answer
- BScheduled tasks
- CManual REST API calls
- DUser login
Why this is the answer
With AUTO_INGEST=TRUE, Snowpipe responds to cloud storage event notifications (e.g., S3 events).
- Question 20Multiple Select
Which two file formats are columnar and support predicate pushdown in Snowflake?
- AParquetCorrect answer
- BORCCorrect answer
- CAvro
- DJSON
Why this is the answer
Parquet and ORC are columnar formats that support predicate pushdown; Avro and JSON are row-oriented.
Performance Optimization, Querying & Transformation
9 questions in this domain
- Question 21Single Choice
What is the primary purpose of a clustering key in Snowflake?
- ATo improve partition pruningCorrect answer
- BTo create indexes on columns
- CTo enable row-level security
- DTo speed up data loading
Why this is the answer
A clustering key reorganizes micro-partitions to improve partition pruning, reducing data scanned during queries.
- Question 22Multiple Select
Which two statements about materialized views are true?
- AThey store precomputed resultsCorrect answer
- BThey require a base table over 10 GBCorrect answer
- CThey support window functions
- DThey refresh synchronously with every DML
Why this is the answer
Materialized views store precomputed results and require a large base table. They do not support window functions and refresh asynchronously.
- Question 23Interactive
Examine the SQL: which operation does this profile help diagnose?
- AData spilling during joinCorrect answer
- BPoor partition pruning
- CExcessive result caching
- DToo many micro-partitions
Why this is the answer
The profile shows 'Bytes Spilled' on a HashJoin operator, indicating spilling due to insufficient warehouse memory.
- Question 24Single Choice
Which DML operation does Snowflake internally execute for an UPDATE?
- ADELETE + INSERT in same transactionCorrect answer
- BIn‑place modification of micro-partitions
- CRow‑level locking then update
- DDrop and recreate the table
Why this is the answer
Snowflake never modifies micro-partitions in place; an UPDATE is a DELETE followed by an INSERT within the same transaction.
- Question 25Multiple Select
Which two features can improve performance for point-lookup queries?
- ASearch Optimization ServiceCorrect answer
- BClustering key on the lookup columnCorrect answer
- CMaterialized view with aggregation
- DStandard view
Why this is the answer
Search Optimization Service and clustering on the lookup column both improve point-lookup performance. Materialized views are for aggregations, not point lookups.
- Question 26Interactive
Interpret the Query Profile metric: what does 'Partitions Scanned' indicate?
- ANumber of micro-partitions accessedCorrect answer
- BTotal rows returned by the query
- CCredits consumed by the warehouse
- DAmount of memory used
Why this is the answer
'Partitions Scanned' shows how many micro-partitions were read, directly affecting performance and cost.
- Question 27Single Choice
What is the default Time Travel retention for a transient table?
- A1 dayCorrect answer
- B7 days
- C0 days
- D90 days
Why this is the answer
Transient tables have a default Time Travel retention of 1 day, while permanent tables have 1 day by default but can be set to 90 days.
- Question 28Multiple Select
Which two are valid Snowflake DML statements?
- AMERGECorrect answer
- BINSERTCorrect answer
- CTRUNCATE
- DCREATE
Why this is the answer
MERGE and INSERT are DML statements. TRUNCATE is DDL, and CREATE is DDL (Data Definition Language).
- Question 29Interactive
Examine this SQL: which Snowflake feature does it demonstrate?
- AZero-copy cloningCorrect answer
- BMaterialized view
- CSearch optimization
- DSecure view
Why this is the answer
The 'CLONE' keyword creates a zero-copy clone, which duplicates metadata without copying data.
Snowflake AI Data Cloud Features & Architecture
13 questions in this domain
- Question 30Interactive
Analyze the SQL command and determine which layer is being used to store the query result for reuse.
- AStorage Layer
- BCompute Layer
- CCloud Services LayerCorrect answer
- DMetadata Layer
Why this is the answer
The result cache is maintained by the Cloud Services Layer and is shared across warehouses for identical queries within 24 hours.
- Question 31Single Choice
What is the default auto-suspend timeout for a virtual warehouse?
- A5 minutes
- B10 minutesCorrect answer
- C15 minutes
- D60 minutes
Why this is the answer
The default AUTO_SUSPEND setting is 10 minutes; it can be changed to as low as 1 minute.
- Question 32Multiple Select
Which two statements about multi-cluster warehouses are true? (Choose two.)
- AThey allow scaling out to handle many concurrent queries.Correct answer
- BThey improve the execution speed of individual complex queries.
- CThey use a scaling policy that can be set to 'economy' or 'standard'.Correct answer
- DThey automatically increase warehouse size to handle more data.
Why this is the answer
Multi-cluster warehouses add identical clusters to increase concurrency, not single-query speed. Scaling policy options include economy and standard.
- Question 33Interactive
Examine the scenario: A team has 50 concurrent BI users running short queries. Which warehouse configuration is most cost-effective?
- ASingle X-Large warehouse, auto-suspend 10 min
- BMulti-cluster Small warehouse, standard scaling, auto-suspend 1 minCorrect answer
- CSingle 4X-Large warehouse, no auto-suspend
- DMulti-cluster Large warehouse, economy scaling
Why this is the answer
For many concurrent short queries, multiple small clusters provide concurrency at lower cost; standard scaling minimizes queue time.
- Question 34Single Choice
What happens to the local SSD cache when a virtual warehouse is suspended?
- AIt is preserved until the warehouse is resumed.
- BIt is cleared and repopulated upon resume.Correct answer
- CIt is transferred to another warehouse.
- DIt is stored in the Cloud Services Layer.
Why this is the answer
The local SSD cache is volatile and cleared when a warehouse is suspended. It must be rebuilt from storage on resume.
- Question 35Multiple Select
Which two are true about zero-copy cloning? (Choose two.)
- AIt creates a new physical copy of the data immediately.
- BIt shares the underlying micro-partitions until modifications occur.Correct answer
- CIt can clone databases, schemas, and tables.Correct answer
- DIt incurs full storage cost at the time of creation.
Why this is the answer
Zero-copy cloning creates metadata pointers to the same micro-partitions; storage is shared until either source or clone is modified.
- Question 36Interactive
Review the statement: 'CREATE DATABASE prod_clone CLONE prod_db;' Which architectural feature makes this operation nearly instantaneous?
- AZero-copy cloning using metadata pointersCorrect answer
- BParallel data copy across clusters
- CCompression of data before copying
- DUse of external staging area
Why this is the answer
Zero-copy cloning leverages Snowflake's metadata layer to create a new object without duplicating data, making it instantaneous.
- Question 37Single Choice
In Snowflake data sharing, what is a Reader Account?
- AA full Snowflake account with compute resources
- BA lightweight, managed account that can only consume shared dataCorrect answer
- CAn account that can create its own shares
- DAn account that stores data locally
Why this is the answer
A Reader Account is a managed account that allows external users to query shared data without owning a Snowflake account.
- Question 38Multiple Select
Which two statements about Snowflake Time Travel are correct? (Choose two.)
- AIt allows users to query historical data up to 90 days.Correct answer
- BIt is automatically enabled for all table types, including temporary tables.
- CIt supports UNDROP for recovering dropped objects.Correct answer
- DIt is only available in Business Critical edition.
Why this is the answer
Time Travel retention can be set up to 90 days for permanent tables; temporary tables have no Time Travel. UNDROP is a Time Travel operation. Standard edition supports 1 day, Enterprise and above support up to 90 days.
- Question 39Interactive
A user accidentally deleted all rows from a table. Which SQL clause should be used to view the data as it existed just before the DELETE?
- AAT(TIMESTAMP => ...)
- BBEFORE(STATEMENT => ...)Correct answer
- CAFTER(STATEMENT => ...)
- DWITHIN(INTERVAL => ...)
Why this is the answer
BEFORE clause excludes the effects of the specified statement, allowing you to see data as it was before the DELETE.
- Question 40Single Choice
What is the maximum Time Travel retention for a transient table in Snowflake?
- A0 days
- B1 dayCorrect answer
- C7 days
- D90 days
Why this is the answer
Transient tables support a maximum of 1 day Time Travel; temporary tables have 0 days.
- Question 41Multiple Select
Which two are true about Fail-safe in Snowflake? (Choose two.)
- AIt is a user-configurable feature.
- BIt provides an additional 7 days of data protection after Time Travel expires.Correct answer
- CUsers can query data during the Fail-safe period.
- DIt is a disaster recovery mechanism managed by Snowflake.Correct answer
Why this is the answer
Fail-safe is a mandatory 7-day period after Time Travel, used only for Snowflake-managed disaster recovery; users cannot access data during this time.
- Question 42Interactive
A table was dropped and the user wants to recover it after 30 days. The table had DATA_RETENTION_TIME_IN_DAYS = 14. What is the correct recovery approach?
- AUse UNDROP TABLE command imme diately.
- BSubmit a support ticket to Snowflake for Fail-safe recovery.Correct answer
- CUse CREATE TABLE ... CLONE ... AT(TIMESTAMP => ...) with the drop timestamp.
- DThe table cannot be recovered because data is purged.
Why this is the answer
After 14 days Time Travel, the table enters Fail-safe for 7 days (total 21 days). After 30 days, Fail-safe has expired and data is purged. No recovery possible.
Frequently asked questions
Everything about the SnowPro Core Certification question bank and how to use it.