Practice Questions · Part 6
Thirty questions in the exam's format: single choice and multiple select, no topic hints. Answer each one, then read the explanation even when you got it right.
SnowPro Core COF-C03 Study GuidePart 6: Database objects and platformPractice Questions: Part 6
Thirty questions in the exam's format: single choice and multiple select, no topic hints. Answer each one, then read the explanation even when you got it right.
An administrator runs CREATE DATABASE sales_db; and then SHOW SCHEMAS IN DATABASE sales_db;. Which schemas does the output list?
Which objects are created inside a schema? (Choose two.)
A security team needs a list of every table dropped in the last 90 days, across all databases in the account. Where should they query?
SHOW TABLES IN ACCOUNTSNOWFLAKE.ACCOUNT_USAGE.TABLESSNOWFLAKE.INFORMATION_SCHEMA.TABLESINFORMATION_SCHEMA.TABLES in each databaseWhich statements correctly compare ACCOUNT_USAGE views with INFORMATION_SCHEMA views? (Choose two.)
An ETL script needs scratch tables that exist only while its session runs, can't be seen by any other session, and add no Fail-safe cost. Which table type fits?
DATA_RETENTION_TIME_IN_DAYS = 0Which statements about table types are correct? (Choose two.)
Each day a file of changed and new customers is loaded into STG_CUSTOMERS. CUSTOMERS must get the changed rows updated and the new rows added, and customers missing from the file must stay as they are. Which statement fits?
UPSERT INTO customers SELECT * FROM stg_customers;INSERT INTO customers SELECT * FROM stg_customers;MERGE INTO customers USING stg_customers ON ...;INSERT OVERWRITE INTO customers SELECT * FROM stg_customers;A reporting table must be completely replaced by the result of a query each night. The replacement must happen in a single transaction, and the grants on the table must stay in place. Which statement fits?
CREATE OR REPLACE TABLE report AS SELECT ...;INSERT OVERWRITE INTO report SELECT ...;INSERT INTO report SELECT ...;TRUNCATE TABLE report; then INSERT INTO report SELECT ...;, each auto-committedA team proposes making every view in the account SECURE "to be safe". What is the main drawback?
Which statements about materialized views are correct? (Choose two.)
CURRENT_TIMESTAMP()A team wants a summary table that joins ORDERS and CUSTOMERS, aggregates by region, and stays within an hour of its sources. They don't want to write or maintain streams, tasks or MERGE code. What should they create?
A dynamic table is created with TARGET_LAG = '30 minutes'. What does this setting tell Snowflake?
An engineer runs INSERT INTO daily_revenue VALUES ('2026-10-01', 1200);, where DAILY_REVENUE is a dynamic table. What happens?
Which statement describes what a standard stream on a table stores?
A standard stream on ORDERS holds 500 unconsumed change rows. An analyst runs SELECT * FROM orders_stream; twice. A task then runs a MERGE that reads the stream, and the transaction commits. ORDERS hasn't changed since. How many change rows does the stream return right after the commit?
ALTER STREAM ... RESET moves the offsetA data engineer creates a task with SCHEDULE = '5 MINUTE' and WAREHOUSE = etl_wh. An hour later, the task history shows no runs at all. What is the most likely cause?
Which statements about tasks are correct? (Choose two.)
Which capability does a stored procedure have that a UDF doesn't?
A data engineer needs a reusable calculation that takes a price and a percentage and returns one value per row. Analysts will use it in the SELECT list of many queries. What should the engineer create?
Which statements about user-defined functions are correct? (Choose two.)
Role SUPPORT has no privileges on table ORDERS. An engineer wants SUPPORT to be able to delete one customer's orders, and nothing else, by calling a stored procedure. How should the procedure be set up?
A utility procedure must read and set the session variables of the user running it, and must never do more than that user could do directly. How should it be created?
EXECUTE AS CALLERCREATE SECURE PROCEDURE with no EXECUTE AS clauseEXECUTE AS OWNERWhich are Snowflake data types for semi-structured data? (Choose three.)
VARIANT column payload holds {"user": {"id": 42, "tags": ["gold", "beta"]}}. Which expression returns gold as a SQL string?
payload:user.tags[0]payload.user.tags[0]::STRINGpayload:user.tags[0]::STRINGpayload['user']['tags'][1]::STRINGColumn order_lines in ORDERS holds a JSON array of line items. Analysts need one row per line item, each still carrying the order_id of its parent row. Which query does this?
SELECT order_id, ARRAY_AGG(order_lines) FROM orders GROUP BY order_id;SELECT order_id, OBJECT_CONSTRUCT('lines', order_lines) FROM orders;SELECT order_id, PARSE_JSON(order_lines) FROM orders;SELECT order_id, f.value FROM orders, LATERAL FLATTEN(input => order_lines) f;Which statements about Apache Iceberg tables in Snowflake are correct? (Choose two.)
An analyst needs a fast estimate of the number of distinct visitors across 40 billion events. An error of a few percent is fine. Which function fits?
COUNT(DISTINCT visitor_id)APPROX_TOP_K(visitor_id)APPROX_COUNT_DISTINCT(visitor_id)APPROX_PERCENTILE(visitor_id, 0.5)An engineer needs exactly 500 randomly chosen rows from ORDERS, which holds millions of rows. Which clause works?
SAMPLE BLOCK (500 ROWS) SEED (7)SAMPLE (500)SAMPLE SYSTEM (500 ROWS)SAMPLE BERNOULLI (500 ROWS)An administrator runs SHOW TABLES IN SCHEMA sales.core; and wants only the tables with more than 1 million rows. What should run next?
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) WHERE "rows" > 1000000;SHOW TABLES IN SCHEMA sales.core WHERE "rows" > 1000000;WITH t AS (SHOW TABLES IN SCHEMA sales.core) SELECT * FROM t;SELECT * FROM TABLE(RESULT_SCAN('SHOW TABLES')) WHERE "rows" > 1000000;Which expression returns the query ID of the second most recent query in the current session?
LAST_QUERY_ID(2)LAST_QUERY_ID(-2)LAST_QUERY_ID(-1)LAST_QUERY_ID()Finish this set to count it as done.