Course contents

SnowPro Core COF-C03 Study GuidePart 4: Data loading and storagePractice Questions: Part 4

Practice Questions: Part 4

Practice Questions · Part 4

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 external stage has no file format, the COPY INTO statement sets none, and the target table has no STAGE_FILE_FORMAT. The staged files are JSON documents with a .json extension. What happens when the COPY runs?

    1. Snowflake reads them as CSV, so the load fails or loads junk
    2. Snowflake detects the .json extension and parses the files as JSON
    3. Snowflake loads each whole file into a single VARIANT column
    4. Snowflake refuses to start the load until a format is given
  2. Stage LANDING has a CSV file format with SKIP_HEADER = 1. A data engineer runs a COPY from LANDING that adds FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = '|'). The first line of each file is a header. What happens to the header lines?

    1. They're skipped, because COPY always ignores a file's first line
    2. They're skipped, because SKIP_HEADER carries over from the stage
    3. They're read as data, because the COPY's format replaces the stage's
    4. They're skipped, because SKIP_HEADER defaults to 1 for CSV
  3. A COPY INTO <table> with ON_ERROR = CONTINUE has already run in the current session. Which statements about running the VALIDATE function on that load are true? (Choose two.)

    1. It checks the staged files before any row is loaded.
    2. It returns the rows that failed, with their error messages.
    3. It corrects the rejected rows and loads them again.
    4. It can target the session's last load with JOB_ID => '_last'.
    5. It still returns the errors after the load's metadata has expired.
  4. Where does Snowflake keep the load metadata for files loaded by a bulk COPY INTO <table>, and for how long?

    1. In the stage's metadata, for 64 days
    2. In the pipe's metadata, for 14 days
    3. In the target table's metadata, for 64 days
    4. In the target table's metadata, for 14 days
  5. A COPY INTO statement loaded 20 files an hour ago. The same statement runs again against the same stage and table, and none of the files has changed. What happens?

    1. All 20 files load again, creating duplicate rows
    2. The files are reparsed but no rows are inserted
    3. The files are skipped and zero rows are loaded
    4. The statement fails because the files are locked
  6. A data engineer must unload relational table ORDERS (columns ORDER_ID and AMOUNT) to a stage as JSON, one object per row. Which approach works?

    1. COPY INTO a stage FROM a query using ARRAY_AGG(OBJECT_CONSTRUCT(*))
    2. COPY INTO a stage FROM the ORDERS table with TYPE = JSON
    3. COPY INTO a stage FROM ORDERS with TYPE = JSON and SINGLE = TRUE
    4. COPY INTO a stage FROM a query using OBJECT_CONSTRUCT
  7. A loading pipeline uses several objects. Which of them live in a schema, so that using them needs USAGE on that schema? (Choose three.)

    1. File format
    2. Virtual warehouse
    3. Pipe
    4. Storage integration
    5. Named stage
  8. What is the maximum size of a single value stored in a VARIANT column?

    1. 16 MB
    2. 256 MB
    3. 64 MB
    4. 128 MB
  9. Small files land in an S3 bucket at unpredictable times all day. A team wants each file loaded within a few minutes of arrival, without scheduling anything or keeping a warehouse running. What should it use?

    1. A task that runs COPY INTO every minute on an XS warehouse
    2. Snowpipe with auto-ingest from S3 event notifications
    3. An external table with AUTO_REFRESH = TRUE
    4. Snowpipe Streaming reading from the S3 bucket
  10. A security team requires that Snowflake read an S3 bucket without any AWS access key or secret ever appearing in Snowflake DDL or query history. What should the data engineer configure?

    1. An external stage with CREDENTIALS set in CREATE STAGE
    2. A network policy covering Snowflake's IP ranges
    3. A storage integration used by the external stage
    4. An API integration used by the external stage
  11. Which statements about external tables are true? (Choose two.)

    1. They're read-only: INSERT, UPDATE and DELETE aren't allowed.
    2. Snowflake bills storage for the files they reference.
    3. Every row exposes a VARIANT column named VALUE.
    4. They support Time Travel like standard tables.
    5. Their data is copied into Snowflake-managed storage when created.
  12. A web application must give its end users short-lived HTTPS links to download PDFs stored on an internal stage. The end users have no Snowflake login. Which function generates these links?

    1. GET_PRESIGNED_URL
    2. GET_STAGE_LOCATION
    3. BUILD_STAGE_FILE_URL
    4. BUILD_SCOPED_FILE_URL
  13. A source system exports one 20 GB gzipped CSV file per day, and the nightly COPY on a Large warehouse is slow. Which change has the most impact?

    1. Move the nightly COPY to a 4X-Large warehouse instead
    2. Set ON_ERROR = CONTINUE on the COPY statement
    3. Convert the daily export to JSON before staging
    4. Split the export into files of 100-250 MB compressed
  14. An application emits sensor readings as rows, never as files. The business wants them queryable in Snowflake within seconds, with no staging step and no warehouse to manage. What should the team use?

    1. Snowpipe with auto-ingest
    2. Bulk COPY INTO run by a task
    3. An external table with auto-refresh
    4. Snowpipe Streaming
  15. Which statements about stages are true? (Choose two.)

    1. A table stage can be shared to load files into several tables.
    2. A user stage (@~) is created automatically and can't be dropped.
    3. READ and WRITE on a named internal stage can be granted to roles.
    4. Files in an external stage are kept in Snowflake-managed storage.
    5. A table stage can be dropped separately from its table.
  16. A data engineer runs COPY INTO @export/orders/ FROM orders; on a large table, with no other options. What does Snowflake write by default?

    1. One file for each micro-partition of the table
    2. Several files of about 16 MB, written in parallel
    3. Several files of about 250 MB, written in parallel
    4. One file, whatever the table's size
  17. An external table over Parquet files in S3 was created without auto-refresh. New files have since landed in the bucket, but queries don't return their rows. What should the team do?

    1. Run ALTER EXTERNAL TABLE ... REFRESH
    2. Recreate the external stage with new credentials
    3. Run COPY INTO the external table to load the files
    4. Wait for the 24-hour result cache to expire
  18. A team runs the classic Snowflake Connector for Kafka (version 3) with default settings and lets it create the target table for a topic. Which columns does that table have?

    1. One typed column for each field of the first message
    2. RECORD_CONTENT as VARCHAR and RECORD_METADATA as OBJECT
    3. RECORD_CONTENT and RECORD_METADATA, both VARIANT
    4. A VARIANT column named VALUE, plus METADATA$FILENAME
  19. Which statements about the ON_ERROR option of a bulk COPY INTO <table> are true? (Choose two.)

    1. ABORT_STATEMENT loads nothing if any row fails.
    2. CONTINUE skips bad rows and loads the rest of each file.
    3. SKIP_FILE stops the whole statement at the first error.
    4. The default value for bulk COPY is SKIP_FILE.
    5. SKIP_FILE_10 skips each file after its first 10 rows.
  20. A pipe's COPY statement doesn't set ON_ERROR. Of 50 files that arrive, one contains a malformed row. What does Snowpipe do?

    1. It stops loading all files until the pipe is resumed
    2. It loads every valid row from all 50 files
    3. It skips that file and loads the other 49
    4. It loads that file's rows up to the malformed one
  21. Before loading new files, a data engineer wants a list of every error the files would produce, without loading any rows. What should the engineer use?

    1. ON_ERROR = CONTINUE on the COPY statement
    2. VALIDATION_MODE = RETURN_ERRORS on the COPY
    3. The VALIDATE function with JOB_ID => '_last'
    4. The COPY_HISTORY table function
  22. Which statements describe Snowpipe compared with bulk COPY INTO? (Choose two.)

    1. It's billed a fixed amount of credits per GB loaded.
    2. Its load metadata is kept in the pipe for 14 days.
    3. It accepts FORCE = TRUE in the pipe's COPY statement.
    4. It guarantees files load in the order they were staged.
    5. It runs on a virtual warehouse that you size.
  23. A stage path holds files of mixed age. Files loaded in the last few weeks must not load again. Some files staged more than 64 days ago were never loaded, and COPY skips them because their load status is unknown. Which copy option loads those old files without reloading the recent ones?

    1. FORCE = TRUE
    2. LOAD_UNCERTAIN_FILES = TRUE
    3. PURGE = TRUE
    4. MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE
  24. An analyst in SnowSQL runs GET @s3_landing/orders/ file:///tmp/; where S3_LANDING is an external stage on Amazon S3. What happens?

    1. The command fails, because GET works only with internal stages
    2. The files download, because the role holds USAGE on the stage
    3. The files download, because the stage uses a storage integration
    4. The command copies the files into an internal stage
  25. Which statements about directory tables are true? (Choose two.)

    1. It's enabled on a stage with DIRECTORY = (ENABLE = TRUE).
    2. A directory table parses each file's contents into table rows.
    3. It can be used on both internal and external stages.
    4. It's a separate schema object with its own privileges.
    5. It loads new files into a target table automatically.
  26. Standard table ORDERS declares ORDER_ID as its PRIMARY KEY. A COPY loads a file in which two rows share the same ORDER_ID. What happens?

    1. The duplicate row replaces the earlier one
    2. The second duplicate row is rejected and the rest load
    3. Both rows load, because the key isn't enforced
    4. The COPY fails with a primary key violation
  27. Each staged JSON file holds one top-level array of thousands of objects. The team wants one table row per object, each stored in a VARIANT column. Which file format option does this?

    1. ENABLE_OCTAL = TRUE
    2. STRIP_NULL_VALUES = TRUE
    3. ALLOW_DUPLICATE = TRUE
    4. STRIP_OUTER_ARRAY = TRUE
  28. A COPY INTO <table> loads from a SELECT over staged files. Which operations can that SELECT perform? (Choose two.)

    1. Cast a column to another data type
    2. Join two staged files on a key
    3. Load a subset of the columns
    4. Filter rows with a WHERE clause
    5. Aggregate rows with GROUP BY
  29. A bucket can't send event notifications to Snowflake, but the application that writes the files knows exactly when each batch lands. How should the team trigger Snowpipe for each new batch?

    1. Schedule ALTER PIPE ... REFRESH every minute
    2. Set AUTO_INGEST = TRUE on the pipe
    3. Call the pipe's insertFiles REST endpoint
    4. Open a Snowpipe Streaming channel for each file
  30. A team has hundreds of Parquet files in a stage. It wants to create a standard table whose column names and types match the files, without typing the column list, and then load the files into it. What should it run first?

    1. COPY INTO a new table name using MATCH_BY_COLUMN_NAME
    2. CREATE EXTERNAL TABLE with AUTO_REFRESH = TRUE
    3. CREATE FILE FORMAT with PARSE_HEADER = TRUE
    4. CREATE TABLE ... USING TEMPLATE with INFER_SCHEMA

Finish this set to count it as done.