Course contents

SnowPro Core COF-C03 Study GuidePart 6: Database objects and platformPractice Questions: Part 6

Practice Questions: Part 6

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.

Practice questions

  1. An administrator runs CREATE DATABASE sales_db; and then SHOW SCHEMAS IN DATABASE sales_db;. Which schemas does the output list?

    1. PUBLIC, INFORMATION_SCHEMA and ACCOUNT_USAGE
    2. INFORMATION_SCHEMA only
    3. PUBLIC only
    4. PUBLIC and INFORMATION_SCHEMA
  2. Which objects are created inside a schema? (Choose two.)

    1. Resource monitor
    2. Network policy
    3. Sequence
    4. Virtual warehouse
    5. Stream
  3. 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?

    1. SHOW TABLES IN ACCOUNT
    2. SNOWFLAKE.ACCOUNT_USAGE.TABLES
    3. SNOWFLAKE.INFORMATION_SCHEMA.TABLES
    4. INFORMATION_SCHEMA.TABLES in each database
  4. Which statements correctly compare ACCOUNT_USAGE views with INFORMATION_SCHEMA views? (Choose two.)

    1. ACCOUNT_USAGE data can lag by up to a few hours; INFORMATION_SCHEMA has no lag
    2. INFORMATION_SCHEMA views can be updated; ACCOUNT_USAGE views are read-only
    3. ACCOUNT_USAGE exists in each database; INFORMATION_SCHEMA is in SNOWFLAKE
    4. ACCOUNT_USAGE includes dropped objects; INFORMATION_SCHEMA doesn't
    5. INFORMATION_SCHEMA keeps 1 year of history; ACCOUNT_USAGE keeps 7 days
  5. 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?

    1. External
    2. Transient
    3. Temporary
    4. Permanent, with DATA_RETENTION_TIME_IN_DAYS = 0
  6. Which statements about table types are correct? (Choose two.)

    1. A permanent table can be cloned into a transient table
    2. A table's type is set at creation and can't be changed afterward
    3. A transient table can be cloned into a permanent table
    4. A temporary table can't have the same name as a permanent table in its schema
    5. A temporary table is visible to other sessions of the same user
  7. 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?

    1. UPSERT INTO customers SELECT * FROM stg_customers;
    2. INSERT INTO customers SELECT * FROM stg_customers;
    3. MERGE INTO customers USING stg_customers ON ...;
    4. INSERT OVERWRITE INTO customers SELECT * FROM stg_customers;
  8. 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?

    1. CREATE OR REPLACE TABLE report AS SELECT ...;
    2. INSERT OVERWRITE INTO report SELECT ...;
    3. INSERT INTO report SELECT ...;
    4. TRUNCATE TABLE report; then INSERT INTO report SELECT ...;, each auto-committed
  9. A team proposes making every view in the account SECURE "to be safe". What is the main drawback?

    1. Secure views store results and add storage cost
    2. Secure views can't join more than one table
    3. Secure views can run slower than standard views
    4. Secure views are available only on Enterprise edition
  10. Which statements about materialized views are correct? (Choose two.)

    1. A Snowflake-managed background process keeps them up to date
    2. They can join two or more tables
    3. Their definition can use CURRENT_TIMESTAMP()
    4. They need a scheduled task to refresh their stored results
    5. They require Enterprise edition or higher
  11. 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?

    1. A materialized view
    2. A dynamic table
    3. An external table
    4. A secure view
  12. A dynamic table is created with TARGET_LAG = '30 minutes'. What does this setting tell Snowflake?

    1. Keep 30 minutes of Time Travel on the table
    2. Refresh the table exactly every 30 minutes
    3. Suspend the warehouse 30 minutes after each refresh
    4. Keep the data at most 30 minutes behind its sources
  13. An engineer runs INSERT INTO daily_revenue VALUES ('2026-10-01', 1200);, where DAILY_REVENUE is a dynamic table. What happens?

    1. The row is added until the next refresh replaces it
    2. It fails, because dynamic tables don't accept DML
    3. The row is added and future refreshes keep it
    4. The row is queued and applied at the next refresh
  14. Which statement describes what a standard stream on a table stores?

    1. An offset into the table's version history, with no table data
    2. A full copy of every changed row, kept apart from the table
    3. A snapshot of the whole table, taken each time a task runs
    4. The inserted rows only, since updates and deletes aren't tracked
  15. 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?

    1. 0, because the committed MERGE advanced the offset
    2. 0, because the first SELECT consumed the changes
    3. 500, because only ALTER STREAM ... RESET moves the offset
    4. 500, because a task reads a stream without consuming it
  16. A 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?

    1. A task needs a WHEN clause before it runs on a schedule
    2. A new task's schedule starts only at the next full hour
    3. Tasks are created suspended and this one was never resumed
    4. Scheduled tasks can't run on a user-managed warehouse
  17. Which statements about tasks are correct? (Choose two.)

    1. A child task declared with AFTER runs after its predecessor succeeds
    2. A task can run single SQL statements but can't call a stored procedure
    3. Every task in a task graph needs its own SCHEDULE
    4. A task can't be scheduled to run more often than once an hour
    5. A task created without a WAREHOUSE parameter runs on serverless compute
  18. Which capability does a stored procedure have that a UDF doesn't?

    1. Being written in Python, Java or JavaScript
    2. Returning a value that a SELECT list can use directly
    3. Running DML statements such as INSERT and DELETE
    4. Being called many times within a single SQL statement
  19. 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?

    1. A scalar SQL UDF
    2. A user-defined table function (UDTF)
    3. A stored procedure that analysts CALL before each query
    4. A task that writes the result into a new column
  20. Which statements about user-defined functions are correct? (Choose two.)

    1. A UDTF can return several rows for a single input row
    2. A scalar UDF returns one value for each input row
    3. A single SQL statement can call only one UDF
    4. A UDF written in Python can run INSERT statements
    5. A UDF is invoked with CALL, as a statement of its own
  21. 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?

    1. With owner's rights, owned by a role with DELETE on ORDERS
    2. As a UDF, since UDFs run with their owner's privileges
    3. With caller's rights, after granting SYSADMIN to SUPPORT
    4. With caller's rights, so the DELETE runs as SUPPORT
  22. 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?

    1. With EXECUTE AS CALLER
    2. As CREATE SECURE PROCEDURE with no EXECUTE AS clause
    3. With no EXECUTE AS clause, since caller's rights is the default
    4. With EXECUTE AS OWNER
  23. Which are Snowflake data types for semi-structured data? (Choose three.)

    1. OBJECT
    2. VARIANT
    3. JSON
    4. ARRAY
    5. XML
  24. VARIANT column payload holds {"user": {"id": 42, "tags": ["gold", "beta"]}}. Which expression returns gold as a SQL string?

    1. payload:user.tags[0]
    2. payload.user.tags[0]::STRING
    3. payload:user.tags[0]::STRING
    4. payload['user']['tags'][1]::STRING
  25. Column 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?

    1. SELECT order_id, ARRAY_AGG(order_lines) FROM orders GROUP BY order_id;
    2. SELECT order_id, OBJECT_CONSTRUCT('lines', order_lines) FROM orders;
    3. SELECT order_id, PARSE_JSON(order_lines) FROM orders;
    4. SELECT order_id, f.value FROM orders, LATERAL FLATTEN(input => order_lines) f;
  26. Which statements about Apache Iceberg tables in Snowflake are correct? (Choose two.)

    1. Their data is stored as Parquet files with Iceberg metadata files
    2. They're always read-only in Snowflake, like external tables
    3. Their data is stored in Snowflake's own micro-partition file format
    4. With Snowflake as the catalog, Snowflake has full read and write support
    5. Those in a customer-managed external volume have 7 days of Fail-safe
  27. 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?

    1. COUNT(DISTINCT visitor_id)
    2. APPROX_TOP_K(visitor_id)
    3. APPROX_COUNT_DISTINCT(visitor_id)
    4. APPROX_PERCENTILE(visitor_id, 0.5)
  28. An engineer needs exactly 500 randomly chosen rows from ORDERS, which holds millions of rows. Which clause works?

    1. SAMPLE BLOCK (500 ROWS) SEED (7)
    2. SAMPLE (500)
    3. SAMPLE SYSTEM (500 ROWS)
    4. SAMPLE BERNOULLI (500 ROWS)
  29. An administrator runs SHOW TABLES IN SCHEMA sales.core; and wants only the tables with more than 1 million rows. What should run next?

    1. SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) WHERE "rows" > 1000000;
    2. SHOW TABLES IN SCHEMA sales.core WHERE "rows" > 1000000;
    3. WITH t AS (SHOW TABLES IN SCHEMA sales.core) SELECT * FROM t;
    4. SELECT * FROM TABLE(RESULT_SCAN('SHOW TABLES')) WHERE "rows" > 1000000;
  30. Which expression returns the query ID of the second most recent query in the current session?

    1. LAST_QUERY_ID(2)
    2. LAST_QUERY_ID(-2)
    3. LAST_QUERY_ID(-1)
    4. LAST_QUERY_ID()