I85PREP
⌘ K
SnowPro Advanced Data Engineer study materials
SnowPro Advanced Data Engineer study resources

SnowPro Advanced Data Engineer practice questions

One question at a time. Build your SnowPro Advanced Data Engineer understanding.

First 50 of 200 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1Data Movement (Data Loading and Unloading)

A COPY INTO <table> statement encounters a malformed value in one row, and no ON_ERROR option was specified. What is the default behavior?

  1. The erroneous rows are skipped and the remaining rows load.
  2. The entire file is skipped but later files in the statement still load.
  3. The erroneous rows are loaded with NULL values in the affected columns.
  4. The COPY statement aborts and returns an error. Correct answer.

Explanation

The default value of ON_ERROR for COPY INTO <table> is ABORT_STATEMENT, so the statement stops and returns an error when a data error is encountered. Tolerating bad rows requires ON_ERROR = CONTINUE, and skipping an entire file requires ON_ERROR = SKIP_FILE.

Question 2Data Movement (Data Loading and Unloading)

Which COPY INTO <table> option deletes the staged files once they have been loaded successfully?

  1. PURGE = TRUE Correct answer.
  2. OVERWRITE = TRUE
  3. FORCE = TRUE
  4. REMOVE = TRUE

Explanation

PURGE = TRUE removes the files referenced by a COPY INTO <table> statement from the stage after the load succeeds. Leaving it at the default of FALSE keeps the files, which is useful for auditing or reloading.

Question 3Data Movement (Data Loading and Unloading)

A file was already loaded into a table and now must be loaded again from the same stage. Which option forces COPY INTO to ignore the recorded load history for that file?

  1. REFRESH = TRUE
  2. OVERWRITE = TRUE
  3. VALIDATION_MODE = RETURN_ERRORS
  4. FORCE = TRUE Correct answer.

Explanation

Snowflake records which files have been loaded into a table, so a second COPY of the same file is normally skipped. FORCE = TRUE overrides that check and reloads the file.

Question 4Data Movement (Data Loading and Unloading)

Which metadata column returns the name of the source file for each row during a COPY INTO <table> operation?

  1. METADATA$FILE_PATH
  2. METADATA$STAGE_NAME
  3. SOURCE$FILENAME
  4. METADATA$FILENAME Correct answer.

Explanation

METADATA$FILENAME is one of the file-level metadata columns that can be selected in the source query of a COPY INTO <table> statement. These columns are available only in COPY statements and describe the staged file being read.

Question 5Data Movement (Data Loading and Unloading)

Which command uploads files from a local file system into an internal stage?

  1. GET
  2. PUT Correct answer.
  3. COPY INTO
  4. LIST

Explanation

PUT copies local files to an internal stage and can compress and split them during the upload. It is the counterpart of GET, which downloads staged files to the local file system.

Question 6Data Movement (Data Loading and Unloading)

A CSV file's first line contains column names and the target table has no column for that line. Which file format option skips it during the load?

  1. FIELD_DELIMITER = ','
  2. SKIP_BLANK_LINES = TRUE
  3. PARSE_HEADER = TRUE
  4. SKIP_HEADER = 1 Correct answer.

Explanation

SKIP_HEADER = n instructs the CSV reader to ignore the first n lines of each file, which is the standard way to discard a header line. PARSE_HEADER is different: it reads the header for name-based column matching instead of discarding it.

Question 7Data Movement (Data Loading and Unloading)

Which command deletes files from a Snowflake stage?

  1. DELETE FROM @stage
  2. PURGE STAGE
  3. DROP STAGE
  4. REMOVE Correct answer.

Explanation

REMOVE deletes staged files and can target specific file names or a pattern. Files deleted from a stage must be uploaded again before they can be loaded.

Question 8Data Movement (Data Loading and Unloading)

Which command copies files from an internal stage to a local directory?

  1. DOWNLOAD
  2. COPY INTO @stage
  3. PUT
  4. GET Correct answer.

Explanation

GET retrieves files from an internal stage and writes them to a local directory specified in the command. It cannot be used against external stages because those files are already accessible in cloud storage.

Question 9Data Movement (Data Loading and Unloading)

Which setup enables a Snowpipe to load files automatically as soon as they are written to cloud storage?

  1. A storage integration with server-side encryption keys rotated daily
  2. An external stage with event notifications wired to the pipe Correct answer.
  3. A task that runs COPY INTO on a one-minute schedule
  4. An internal stage with a directory table refreshed on a schedule

Explanation

Auto-ingest depends on the cloud provider publishing event notifications when new objects arrive, and on a pipe whose definition references the external stage. Snowflake receives those notifications and queues the referenced files for loading.

Question 10Data Movement (Data Loading and Unloading)

Columns in a staged Parquet file share names with the target table columns but appear in a different order. Which COPY INTO option loads them correctly by name?

  1. INFER_SCHEMA = TRUE
  2. FILE_FORMAT = (TYPE = PARQUET, COMPRESSION = SNAPPY)
  3. MATCH_BY_COLUMN_NAME = CASE_SENSITIVE Correct answer.
  4. PARSE_HEADER = TRUE

Explanation

MATCH_BY_COLUMN_NAME maps source columns to target columns by name rather than by position, and accepts CASE_SENSITIVE or CASE_INSENSITIVE. Its default value, NONE, forces positional loading.

Question 11Data Movement (Data Loading and Unloading)

Which COPY INTO clause returns the errors a staged file would produce without inserting any rows into the table?

  1. VALIDATION_MODE = RETURN_ERRORS Correct answer.
  2. ON_ERROR = CONTINUE on the COPY statement
  3. RETURN_FAILED_ONLY = TRUE together with FORCE = TRUE
  4. FILE_FORMAT = (TYPE = CSV, SKIP_HEADER = 1)

Explanation

VALIDATION_MODE instructs COPY INTO to report the errors that would occur while reading the staged files, and no data is loaded. It accepts values such as RETURN_ERRORS, RETURN_FIRST_N_ERRORS, and RETURN_N_ERRORS.

Question 12Data Movement (Data Loading and Unloading)

An unload should write rows into separate subdirectories keyed by the value of a date column. Which COPY INTO <location> parameter accomplishes this?

  1. SINGLE = TRUE
  2. INCLUDE_QUERY_ID = TRUE
  3. PARTITION BY (column_name) Correct answer.
  4. MAX_FILE_SIZE = 16000000

Explanation

PARTITION BY writes unloaded rows into subdirectories named after the partition column values, producing at least one file per partition. This is commonly used to create hive-style layouts for downstream engines.

Question 13Data Movement (Data Loading and Unloading)

During an unload to an external stage, files with the generated names already exist in the target path and the statement must replace them. Which option should be set?

  1. REPLACE_EXISTING = TRUE
  2. FORCE = TRUE
  3. PURGE = TRUE
  4. OVERWRITE = TRUE Correct answer.

Explanation

By default OVERWRITE = FALSE, and an unload fails if a file with the same name already exists at the target location. Setting OVERWRITE = TRUE allows the unload to replace existing files.

Question 14Data Movement (Data Loading and Unloading)

A CSV file has fewer fields per row than the target table has columns. Which file format option allows the rows to load with NULLs in the missing columns?

  1. ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE Correct answer.
  2. EMPTY_FIELD_AS_NULL = TRUE with NULL_IF = ('')
  3. TRIM_SPACE = TRUE
  4. SKIP_BLANK_LINES = TRUE with TRIM_SPACE = TRUE

Explanation

Setting ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE lets rows with a different number of fields than the table columns load, with missing trailing columns filled with NULL. The default of TRUE rejects such rows.

Question 15Data Movement (Data Loading and Unloading)

Which function returns the current execution state and pending file count for a named pipe?

  1. VALIDATE_PIPE_LOAD(<pipe_name>)
  2. SYSTEM$PIPE_STATUS() Correct answer.
  3. COPY_HISTORY()
  4. PIPE_STATUS()

Explanation

SYSTEM$PIPE_STATUS returns a JSON document containing the pipe's executionState (for example RUNNING or PAUSED) and the number of files still pending. It is the standard way to confirm that a pipe is actively ingesting.

Question 16Data Movement (Data Loading and Unloading)

Files were copied into an external stage before the Snowpipe was created, so no notifications exist for them. Which action loads those existing files into the target table?

  1. Recreate the pipe so that it re-reads the stage history
  2. Run ALTER PIPE <name> REFRESH Correct answer.
  3. Resume the pipe with ALTER PIPE ... SET PIPE_EXECUTION_PAUSED = FALSE
  4. Run GET on the stage to pull the files into a table

Explanation

ALTER PIPE ... REFRESH queues the files already present in the stage that match the pipe's COPY statement. Files that arrive after the pipe exists are handled by event notifications instead.

Question 17Data Movement (Data Loading and Unloading)

Which Snowflake object is designed to hold the cloud provider identity used by an external stage, so that no credentials appear in the stage definition?

  1. A network policy
  2. An API integration with a scoped OAuth token
  3. A secret object holding the cloud access key
  4. A storage integration Correct answer.

Explanation

A storage integration stores the cloud identity once, for example an IAM role or a consent URL, and stages reference it through STORAGE_INTEGRATION. This centralizes access control and keeps keys out of DDL.

Question 18Data Movement (Data Loading and Unloading)

Which object must be enabled on a stage so that file-level metadata can be queried with SELECT ... FROM DIRECTORY(...)?

  1. A directory table Correct answer.
  2. A named file format
  3. A pipe object
  4. A storage integration

Explanation

A directory table stores the metadata for the files in a stage and is queried through the DIRECTORY table function. For internal stages it must be enabled explicitly with DIRECTORY = (ENABLE = TRUE).

Question 19Data Movement (Data Loading and Unloading)

Which function inspects staged Parquet or JSON files and returns column names and data types that can be used to create a matching table?

  1. INFER_SCHEMA Correct answer.
  2. VALIDATE
  3. DESCRIBE STAGE
  4. GET_METADATA

Explanation

INFER_SCHEMA examines staged files and returns column definitions that can feed CREATE TABLE ... USING TEMPLATE. It is commonly used with self-describing formats such as Parquet, Avro, ORC, and JSON.

Question 20Data Movement (Data Loading and Unloading)

A stage contains many files and only those whose path ends in .csv should be loaded. Which COPY INTO parameter filters the files with a regular expression?

  1. PATTERN = '.*[.]csv' Correct answer.
  2. FILES = ('orders_2024.csv', 'orders_2025.csv')
  3. REGEXP = 'orders.*'
  4. INCLUDE = ('orders')

Explanation

PATTERN applies a regular expression to the full stage path of each file and loads only matching files. It avoids having to enumerate every file name individually.

Question 21Data Movement (Data Loading and Unloading)

A file named orders.csv with unchanged content was loaded, deleted from the stage with REMOVE, and then staged again under the same name. A second COPY INTO runs without FORCE. What happens?

  1. The file is loaded because removing it from the stage resets the metadata.
  2. The file is skipped because prior load metadata still records it as loaded. Correct answer.
  3. The COPY statement fails with a duplicate file error.
  4. The file is loaded, but only rows that changed since the first load are inserted.

Explanation

Snowflake retains load metadata for each table for 64 days and uses it to avoid loading the same file twice. A file that is re-staged with the same name and unchanged content is therefore skipped unless FORCE = TRUE is specified.

Question 22Data Movement (Data Loading and Unloading)

A COPY INTO <table> statement specifies ON_ERROR = SKIP_FILE_25% and several files in the stage contain errors. How does the statement behave?

  1. The statement aborts as soon as one file exceeds 25 percent error rows.
  2. Rows are skipped until 25 percent of the rows in the statement have failed, then it aborts.
  3. The statement retries each failed file up to 25 percent of the total file count.
  4. Files with errors are skipped; if more than 25% of files fail, the statement aborts. Correct answer.

Explanation

ON_ERROR = SKIP_FILE_<n>% operates at file granularity: files containing errors are skipped and the statement aborts once the percentage of failed files exceeds n. This differs from ON_ERROR = CONTINUE, which skips individual rows instead of whole files.

Question 23Data Movement (Data Loading and Unloading)

A JSON file contains one top-level array holding 5,000 objects, and each object should become a separate row. Which file format option produces that result?

  1. PARSE_HEADER = TRUE
  2. STRIP_OUTER_ARRAY = TRUE Correct answer.
  3. STRIP_NULL_VALUES = TRUE
  4. REPLACE_INVALID_CHARACTERS = TRUE

Explanation

With STRIP_OUTER_ARRAY = TRUE, the outer brackets are removed so each element of the top-level array is loaded as its own row. Without it, the entire array is stored as a single VARIANT value.

Question 24Data Movement (Data Loading and Unloading)

Which statement describes Snowpipe streaming?

  1. It loads micro-batches of files written to an internal stage every few seconds.
  2. It requires cloud event notifications to detect newly arrived files.
  3. It inserts rows through an SDK without staging files. Correct answer.
  4. It loads only Parquet files into VARIANT columns.

Explanation

Snowpipe streaming writes rows directly into tables using the Snowpipe Streaming SDK or a connector such as the Kafka connector, with no staged files or notification pipeline. This gives lower latency than file-based Snowpipe, which ingests files in micro-batches.

Question 25Data Movement (Data Loading and Unloading)

How does the VALIDATE table function differ from the VALIDATION_MODE copy option?

  1. VALIDATE is available only for Snowpipe; VALIDATION_MODE is available only for COPY INTO table statements.
  2. VALIDATE reports only row counts; VALIDATION_MODE reports only error messages.
  3. VALIDATE performs a dry-run load; VALIDATION_MODE only verifies the file format can be parsed.
  4. VALIDATE reports errors for files already loaded; VALIDATION_MODE checks staged files without loading. Correct answer.

Explanation

VALIDATE is a table function that returns the errors associated with data that COPY INTO already loaded into a table. VALIDATION_MODE is specified inside a COPY statement, inspects the staged files, returns the errors that would occur, and loads no rows.

Question 26Data Movement (Data Loading and Unloading)

A COPY INTO <table> statement has just finished loading files from a stage, and the loaded files must be deleted from the stage automatically at the end of the load. Which copy option produces this behavior?

  1. TRUNCATECOLUMNS = TRUE
  2. PURGE = TRUE Correct answer.
  3. VALIDATION_MODE = 'RETURN_ERRORS'
  4. FORCE = TRUE

Explanation

PURGE = TRUE tells COPY INTO to remove the staged data files once they have been loaded successfully; the default is FALSE, which leaves the files in place. Files are only removed when the load succeeds, so a failed statement leaves the stage untouched.

Question 27Data Movement (Data Loading and Unloading)

A COPY INTO <table> statement loads a file that contains one malformed row, and no ON_ERROR option is specified. What happens?

  1. The statement aborts and no rows are loaded. Correct answer.
  2. The file is skipped and the remaining files are loaded.
  3. The bad row is skipped and all other rows are loaded.
  4. The bad row is loaded as NULL and the rest are loaded.

Explanation

The default for ON_ERROR is ABORT_STATEMENT, so the COPY statement stops as soon as an error is encountered and the rows from that file are not loaded. Keeping the load going despite bad rows requires setting ON_ERROR explicitly, for example to CONTINUE or SKIP_FILE.

Question 28Data Movement (Data Loading and Unloading)

A local file is uploaded with the PUT command and no compression options are specified. What does Snowflake do with the file by default?

  1. It uploads the file uncompressed and keeps the original name.
  2. It compresses the file with gzip and adds a .gz extension. Correct answer.
  3. It rejects the file because compression is mandatory for internal stages.
  4. It compresses the file with snappy and adds a .sz extension.

Explanation

PUT compresses uploaded files with gzip by default, because AUTO_COMPRESS defaults to TRUE, and appends .gz to the staged file name. Setting AUTO_COMPRESS = FALSE uploads the original bytes unchanged.

Question 29Data Movement (Data Loading and Unloading)

A COPY INTO <table> statement loads from a stage, and each row must record the name of the staged file it came from. Which metadata column provides that value?

  1. METADATA$START_SCAN_TIME
  2. METADATA$FILE_ROW_NUMBER
  3. METADATA$FILE_LAST_MODIFIED
  4. METADATA$FILENAME Correct answer.

Explanation

METADATA$FILENAME is a pseudo-column that returns the path and name of the staged file that supplied each row. It is referenced in the SELECT list of a COPY INTO ... FROM (SELECT ...) query and stored in a target column.

Question 30Data Movement (Data Loading and Unloading)

A delimited file contains blank lines between some records, and loading it with COPY INTO fails with an error about missing columns. Which file format option addresses the problem?

  1. SKIP_HEADER = 1
  2. EMPTY_FIELD_AS_NULL = TRUE
  3. ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE
  4. SKIP_BLANK_LINES = TRUE Correct answer.

Explanation

SKIP_BLANK_LINES = TRUE tells the delimited-file parser to ignore empty lines instead of interpreting them as rows whose fields are all missing. It is a file format option, so it is set either in a named FILE FORMAT object or inline in the FILE_FORMAT clause.

Question 31Data Movement (Data Loading and Unloading)

A stage holds hundreds of files. Exactly three of them must be loaded by a single COPY INTO statement, and their names share no common pattern. Which approach selects only those files?

  1. Load the whole stage and use ON_ERROR to skip the unwanted files.
  2. List the three paths in the FILES parameter. Correct answer.
  3. Set PATTERN to a regular expression that matches the three file names.
  4. Create a directory table and filter on the file names.

Explanation

The FILES parameter takes an explicit list of staged file paths relative to the stage location, and only those files are read by the statement. It is the direct way to name an exact set of files when no shared naming pattern exists.

Question 32Data Movement (Data Loading and Unloading)

During a load, a staged column must be converted with a SQL expression and the result written to a differently named table column. Which COPY INTO form supports this?

  1. COPY INTO <table> FROM (SELECT ... FROM @stage) Correct answer.
  2. COPY INTO <table> FROM @stage MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE
  3. COPY INTO <table> FROM @stage ON_ERROR = 'CONTINUE'
  4. COPY INTO <table> FROM @stage FILE_FORMAT = (FORMAT_NAME = my_fmt)

Explanation

Wrapping the stage in a SELECT lets COPY apply SQL expressions, casts, and column aliases to the staged data before it is inserted into the table. The query may also project metadata pseudo-columns such as METADATA$FILENAME into target columns.

Question 33Data Movement (Data Loading and Unloading)

A CSV file is encoded in ISO-8859-1, and accented characters load as incorrect values. Which file format option corrects the load?

  1. FIELD_DELIMITER = ';'
  2. REPLACE_INVALID_CHARACTERS = TRUE
  3. ENCODING = 'ISO-8859-1' Correct answer.
  4. ESCAPE_UNENCLOSED_FIELD = NONE

Explanation

The ENCODING file format option declares the character set of the staged file so Snowflake converts its bytes to UTF-8 correctly during the load. It defaults to UTF-8, which is why the ISO-8859-1 bytes were misread as multi-byte sequences.

Question 34Data Movement (Data Loading and Unloading)

A CSV file uses commas as the field delimiter, and some text values contain commas inside double quotes. Which file format setting keeps those values in a single field?

  1. FIELD_OPTIONALLY_ENCLOSED_BY = '"' Correct answer.
  2. Set FIELD_DELIMITER to a pipe character instead of a comma
  3. Set TRIM_SPACE = TRUE to strip the surrounding whitespace
  4. Set SKIP_HEADER = 1 to discard the first line of the file

Explanation

FIELD_OPTIONALLY_ENCLOSED_BY names the quote character that may wrap a field value, so a value containing the delimiter inside quotes is parsed as one field. Without it, the parser treats every comma as a field boundary.

Question 35Data Movement (Data Loading and Unloading)

A Snowpipe loads files continuously into a table. Which compute resources does the pipe itself use?

  1. Snowflake-provided compute that the account does not size. Correct answer.
  2. The virtual warehouse referenced by the pipe definition.
  3. A warehouse that must be kept running for the pipe to work.
  4. Compute located in the customer's cloud account and managed by the pipe.

Explanation

Snowpipe runs on Snowflake-managed compute, so loading continues even when no virtual warehouse is running, and the usage appears as Snowpipe compute in billing. A warehouse is only needed for queries against the loaded data, not for the load itself.

Question 36Data Movement (Data Loading and Unloading)

An unload to a stage produces many small files, but the requirement is a single output file. Which COPY INTO <location> option produces one file?

  1. OVERWRITE = TRUE
  2. DETAILED_OUTPUT = TRUE
  3. MAX_FILE_SIZE = 0
  4. SINGLE = TRUE Correct answer.

Explanation

SINGLE = TRUE instructs COPY INTO <location> to write the entire result set to one file instead of splitting it across parallel output files. Because a single file cannot be written in parallel, unloading large result sets this way is slower.

Question 37Data Movement (Data Loading and Unloading)

During an unload, some string values are longer than the maximum length of the corresponding table column, and the unload fails. Which copy option lets the unload complete by shortening those values?

  1. VALIDATE_UTF8 = FALSE
  2. EMPTY_FIELD_AS_NULL = TRUE
  3. TRUNCATECOLUMNS = TRUE Correct answer.
  4. ON_ERROR = 'CONTINUE'

Explanation

TRUNCATECOLUMNS = TRUE shortens character values to the maximum length of the target column instead of raising an error during an unload. It applies to string data and leaves numeric and other data types unaffected.

Question 38Data Movement (Data Loading and Unloading)

A COPY INTO <table> statement runs with ON_ERROR = 'CONTINUE' against a stage holding several files. The goal is a report listing only the rejected rows and the errors they produced. Which option combination achieves this?

  1. RETURN_FAILED_ONLY = TRUE with ON_ERROR = 'ABORT_STATEMENT'
  2. DETAILED_OUTPUT = TRUE with ON_ERROR = 'SKIP_FILE'
  3. RETURN_FAILED_ONLY = TRUE with ON_ERROR = 'CONTINUE' Correct answer.
  4. VALIDATION_MODE = 'RETURN_ERRORS' with ON_ERROR = 'CONTINUE'

Explanation

RETURN_FAILED_ONLY = TRUE limits the statement output to the rows that failed, and ON_ERROR = 'CONTINUE' keeps the load going so that every file is processed and reported. Together they yield a load that completes while reporting exactly the rejected rows.

Question 39Data Movement (Data Loading and Unloading)

Data lands in cloud storage as thousands of 4 KB files that a Snowpipe loads continuously. Which practice does Snowflake recommend to keep the pipe efficient?

  1. Aggregate files to roughly 100-250 MB compressed. Correct answer.
  2. Increase the size of the virtual warehouse that the pipe uses.
  3. Keep the small files but configure the pipe to pause between loads.
  4. Create one pipe per file so that the loads run in parallel.

Explanation

Snowpipe overhead and cost scale with the number of files, so thousands of tiny files add scheduling and metadata work without improving throughput. Snowflake recommends staging files in the range of about 100 to 250 MB compressed before loading.

Question 40Data Movement (Data Loading and Unloading)

Files were encrypted client-side with a passphrase before being uploaded with PUT to an internal stage. What must the COPY INTO <table> statement include so the files can be loaded?

  1. ENCRYPTION = (TYPE = 'AWS_SSE_S3') inside the file format definition.
  2. MASTER_KEY = '<passphrase>' matching the one used at upload. Correct answer.
  3. A storage integration whose trust relationship stores the passphrase.
  4. Nothing; Snowflake decrypts them automatically using the stage's settings.

Explanation

When files are encrypted client-side with a passphrase during PUT, the same passphrase must be supplied as MASTER_KEY on the COPY INTO statement so Snowflake can decrypt them. Without it the load fails because the file contents cannot be decrypted.

Question 41Data Transformation

A table has a VARIANT column that holds a JSON array. Which construct produces one output row per array element so each element can be processed individually?

  1. LATERAL FLATTEN, which returns one row per array element Correct answer.
  2. ARRAY_TO_STRING, which concatenates the elements into one value
  3. PARSE_JSON, which converts text into a VARIANT
  4. OBJECT_CONSTRUCT, which builds a new object from key/value pairs

Explanation

FLATTEN is a table function that shreds a VARIANT array (or object) into multiple rows, one per element or key, and is normally applied with LATERAL so it can reference a column of the preceding table. It exposes columns such as SEQ, KEY, PATH, INDEX and VALUE.

Question 42Data Transformation

A standard (default) stream is created on a table. Which changes does that stream track?

  1. Only rows inserted after the stream was created
  2. Inserts, updates, and deletes committed after the stream's current offset Correct answer.
  3. Only metadata changes such as added or dropped columns
  4. Only changes made by the role that owns the stream

Explanation

A standard stream records all DML change records - inserts, updates, and deletes - that occur on the source object after the stream's offset. Restricting capture to inserts requires an append-only stream, and stream capture is independent of the role that performs the DML.

Question 43Data Transformation

Which task definition clause makes one task run only after another task in the same account has completed, forming a task DAG?

  1. PRECEDES <task_name>
  2. DEPENDS ON <task_name>
  3. SCHEDULE = 'AFTER <task_name>'
  4. AFTER <task_name> Correct answer.

Explanation

A child task is defined with the AFTER clause naming one or more predecessor tasks, which establishes the dependency edges of the task graph. Only the root task of a DAG carries a SCHEDULE; child tasks are triggered by their predecessors.

Question 44Data Transformation

Which dynamic table parameter specifies the maximum amount of time the table's content may lag behind the data of its sources?

  1. WAREHOUSE_SIZE
  2. TARGET_LAG Correct answer.
  3. DATA_RETENTION_TIME_IN_DAYS
  4. REFRESH_INTERVAL

Explanation

TARGET_LAG is the freshness bound: Snowflake schedules refreshes so the dynamic table is no more than that far behind its base objects. The value can be a time interval such as '5 minutes' or DOWNSTREAM, which ties freshness to the table's consumers.

Question 45Data Transformation

A CSV file in a named stage must be loaded into a table with only two of its columns retained and a simple expression applied to one of them. Which single statement accomplishes this?

  1. Run SELECT against the stage and insert each returned row with INSERT ... VALUES
  2. Merge the staged files into the table with MERGE INTO target USING @my_stage
  3. COPY INTO target FROM (SELECT $1, UPPER($2) FROM @my_stage) Correct answer.
  4. Download the files with GET, edit them locally, and load them with PUT

Explanation

COPY INTO accepts a SELECT query over the stage as its source, which allows column pruning, casting, reordering, and expression evaluation during the load itself. This avoids a staging table and a separate transformation step.

Question 46Data Transformation

A VARCHAR column contains JSON text. Which function converts that text into a VARIANT whose keys can then be addressed with bracket or dot notation?

  1. OBJECT_CONSTRUCT
  2. TO_VARIANT
  3. PARSE_JSON Correct answer.
  4. TO_VARCHAR

Explanation

PARSE_JSON interprets a string as JSON and returns a VARIANT, after which individual elements can be read with path notation. TO_VARIANT on a string only wraps the text in a VARIANT without interpreting its JSON structure.

Question 47Data Transformation

Which single statement can insert new rows, update matched rows, and delete rows in a target table based on a source query?

  1. REPLACE
  2. COPY INTO
  3. MERGE Correct answer.
  4. UPSERT

Explanation

MERGE matches target rows to source rows on a join condition and applies the WHEN MATCHED and WHEN NOT MATCHED branches, which may include UPDATE, DELETE, and INSERT actions in one statement. It is the standard pattern for applying change data to a dimension or fact table.

Question 48Data Transformation

A pipe is created for continuous loading and no warehouse is assigned to it. What compute performs the ingestion?

  1. Snowflake-provided serverless compute Correct answer.
  2. A dedicated warehouse named SNOWPIPE_SERVICE
  3. The default warehouse of the role that created the pipe
  4. The warehouse named in the pipe's SCHEDULE expression

Explanation

Snowpipe uses Snowflake-managed serverless compute, so no user warehouse is needed or billed for the pipe's load activity. Warehouse compute is only relevant when loading with client-driven COPY statements.

Question 49Data Transformation

Which kind of stream records only newly inserted rows and never emits change records for updates or deletes?

  1. A standard stream
  2. A stream created on a directory table
  3. An append-only stream, created with APPEND_ONLY = TRUE Correct answer.
  4. A stream created on a view

Explanation

An append-only stream captures only insert operations, which makes it cheaper for pure-append feeds where updates and deletes are irrelevant. For append-only sources Snowflake can also avoid storing extra change-tracking metadata.

Question 50Data Transformation

A dynamic table's defining query includes logic that cannot be computed incrementally from the changed rows. What does Snowflake do at the next refresh?

  1. The dynamic table is automatically converted into a materialized view
  2. The refresh fails and the table stays stale until it is recreated
  3. Snowflake falls back to a full refresh of the query result Correct answer.
  4. Only newly arriving rows are appended and removed rows are ignored

Explanation

A dynamic table refreshes incrementally when the query supports it, otherwise Snowflake performs a full refresh that recomputes the entire result from the sources. This guarantees correctness at the cost of more compute for that refresh.