Snowflake Data Engineer Practice Exam

These questions are part of the practice quiz. Start practicing

• Which use case is valid for SnowCD?
• To ensure multiple statements access the same change records in the stream, surround them with an explicit transaction statement (BEGIN .. COMMIT).
• Which technique leverages performance efficiencies by enabling large and complex Spark logical plans to be processed in Snowflake, thus using Snowflake to do most of the actual work?
• A stream can be queried multiple times to update multiple objects in the same transaction and it will return the same data.
• When defining a multi-column clustering key, which ordering is generally recommended?
• Which statement about Snowpipe staging steps is correct?
• What is the purpose of creating a named stage in Snowflake for Snowpipe?
• If a table has many micro-partitions and a query scans all of them, what does that indicate about partition pruning?
• What is the standard SQL command to execute a stored procedure?
• Which statement is true about the handling of dropped objects in ACCOUNT_USAGE versus INFORMATION_SCHEMA?
• Which option should be used to allow a user to have only OWNERSHIP privilege on a table without being able to manage privilege grants?
• Which of the following is NOT automatically created by the Kafka connector for each topic?
• Which error indicates that the storage integration cannot be found for a stage after recreation?
• Which query can be used to find all the file loading errors through Snowpipe in the last 1 hour?
• Which Snowflake component creates and maintains the search access path in the background?
• Why might COPY_HISTORY not include some files loaded via Snowpipe?
• Which stage types does Snowpipe support for loading data?
• To identify long-running queries in the last hour, which query is correct?
• Which technique speeds up queries on a table with city demography data for both range and equality searches?
• Creating a materialized view that uses the non-deterministic function CURRENT_ROLE will result in what?
• Which syntax correctly extracts the city field as a string in a query using a lateral flatten on a VARIANT column?
• Which statement about clustering in Snowflake is true?
• Task_history returns both completed and running tasks.
• What is an API integration in Snowflake?
• Which statement about initial stream creation and change tracking is true?
• What is the effect of specifying WITH MANAGED ACCESS on a schema?
• Can search optimization indirectly improve the performance of views that rely on a base table with a selective predicate?
• If you attempt to create a materialized view that includes an aggregate in a subquery, what happens?
• When loading Brotli-compressed CSV files, which option must be specified?
• Which statement is true about calling stored procedures in Snowflake?
• Which metadata column indicates the DML operation recorded in a Snowflake stream?
• To filter by city inside a VARIANT column when querying weather data, which syntax is used?
• Which statement is true about mapping Kafka topics to Snowflake tables?
• What is the maximum batch size for responses from external functions?
• In a simple tree of tasks, must all tasks have the same owner?
• In Snowflake, is a transaction inside a stored procedure treated as autonomous and not nested within the outer transaction?
• When using AUTO compression detection for CSV, Brotli cannot be auto-detected; what should you do?
• Which schema hosts the Warehouse Metering History table used for credential queries?
• Which statement about system$clustering_information is true for a table with a clustering key when the second argument is omitted?
• Which of the following is NOT an additional column in the stream output?
• When the Kafka connector loads data via Snowpipe, which cost is charged to your account?
• In Databricks, Snowflake's query pushdown is enabled by default.
• Currently, external functions must be scalar functions.
• External functions can appear in a view definition.
• If a query profile shows percentage read from cache is over 99%, what does this imply?
• What happens to the source table when the first stream for a table is created?
• Which command correctly adds search optimization to a table?
• Within a stored procedure, is a nested transaction considered autonomous?
• What determines the number of data files processed in parallel during a load operation?
• What is a key benefit of micro-partitioning with columnar storage?
• What is the recommended data file size to optimize parallel load operations?
• In Snowflake, which function is commonly used to flatten a JSON array stored in a VARIANT column?
• Which of the following is true about selecting a clustering depth for a table?
• For processing 2MB files per minute, which option minimizes Snowflake cost?
• Which statement best describes internal data transfer in the Spark connector?
• What is the likely outcome when you recreate an external stage that uses a storage integration and then try to load data from that stage?
• If a topic table already exists when the Kafka Connector runs, what action does it perform?
• What does the RECORD_METADATA column contain in a Snowflake table loaded by the Kafka connector?
• When multiple file format options are specified in different locations (COPY INTO statement, stage, and table definition), which takes precedence?
• Which query correctly retrieves the clustering depth for the table TPCH_ORDERS when only the table name is provided?
• External functions can appear in which of the following SQL constructs?
• If a table has no clustering key and you call system$clustering_information with only the table name omitted, what happens?
• Snowflake applies data egress charges in all use cases EXCEPT?
• In Snowflake's Simple Tree of Tasks, can a single task have more than one predecessor (parent)?
• To retrieve total compute credits used by warehouses over a given period, which ACCOUNT_USAGE table should you query?
• Which design yields better partition pruning when loading weather data with a city field?
• When the first stream for a table is created, what happens to the table?
• In a materialized view definition, are non-deterministic functions allowed?
• As part of the Kafka connector workflow, which objects are created for each topic?
• A stream contains table data.
• METADATA$ROW_ID: Specifies the unique and immutable ID for the row, which can be used to track changes to specific rows over time.
• Why can the association between a stage and its storage integration break when you recreate the storage integration?
• Which combination of privileges is required, at a minimum, to create additional streams on a Snowflake table?
• Which location has the highest precedence for applying file format options when they are defined in multiple places?
• Which of the following is a valid 3-argument call to system$clustering_depth to specify a subset of columns and a filter?
• The Snowflake Kafka Connector guarantees that rows are inserted in the order they were originally published.
• If you start multiple Kafka connector instances on the same topics or partitions, what can occur?
• Which predicate is NOT supported by search optimization?
• Aside from Snowpipe processing time, which cost is charged when using the Kafka connector?
• Which statement about CREATE TABLE ... CLONE is correct?
• If a table has a data retention of 10 days and is loaded with 256 MB of data daily, what will be the total storage after 5 days of ingestion?
• Which option allows skipping a problematic file during internal stage load?
• Time Travel has a default retention period of how many days?
• Which of the following is an example of an application developed using the Snowflake Python connector?
• Are very large files (e.g., 100 GB) recommended to load in Snowflake?
• COPY_HISTORY retains data loading history for how many days?
• Which stage type is not valid for Snowpipe loading?
• Is it best practice to avoid binding data using Python's string formatting functions due to SQL injection risk?
• Extending Time Travel retention up to 90 days requires which Snowflake edition?
• What is the primary use-case of Snowflake's Search Optimization Service?
• Which metadata column specifies the action (INSERT or DELETE) in the stream?
• Which metric is a data point reported by system$clustering_information?
• If a user is granted ROLE-A, which roles can they switch to in the Snowflake UI?
• In a query profile, which condition indicates a join explosion?
• Which query correctly retrieves clustering information for test2 when querying specific columns and there is no clustering key?
• Streams have no Fail-safe period or Time Travel retention period. The metadata in these objects cannot be recovered if a stream is dropped.
• If you override file format options in a COPY INTO statement to CSV while a stage is configured for JSON, which format is used for the load?
• Which statement accurately describes the scope of Snowflake's Search Optimization Service?
• Clustering depth can be used to monitor the clustering health of a large table and to decide whether a clustering key would help. True or False?
• To enable streams on shared tables, which action must you perform?
• In the CITY_TEMPERATURE example, which inner field stores the temperature value for each date?
• In JavaScript stored procedures, which operator checks strict equality without type coercion?
• Which of the following is NOT true about external functions?
• An existing clustering key is copied in which scenario?
• If an external function is defined as SECURE, are the URL and headers hidden from non-owners?
• Which object is not created for each Kafka topic by the Snowflake Kafka Connector?
• Which function can a task use to see whether a stream contains change data for a table?
• In the Kafka Connector workflow, which object is created to ingest data files for each topic partition?
• If you run multiple Kafka connector instances on the same topics or partitions, what is likely to happen?
• Which command creates an API integration in Snowflake?
• Which transformation is NOT supported when loading with COPY?
• To improve query performance, which class is used to bypass data conversions from Snowflake internal data types to Python data types?
• Which privilege on the source table is required to create an initial stream?
• When loading a table from an internal stage, which option helps fix errors due to string fields not being enclosed?
• Snowflake charges per-byte when transferring data to cloud storage in another region or to another cloud platform.
• When querying system$clustering_information for a table with a clustering key, what is returned if the second argument is omitted?
• Are tables and views protected by row access policies compatible with the Search Optimization Service?
• Query pushdown is supported in Snowflake Connector for Spark version 2.1.0 or higher.
• The fact that micro-partitions can overlap in their range of values helps to?
• Version 2.2.0+ of the Snowflake Spark Connector uses a default staging for data exchange.
• Does search optimization support external tables?
• Which SQL DDL statement creates an empty copy of an existing table?
• Snowflake's pruning behavior with subqueries: Which of the following is true?
• What happens to COPY_HISTORY data when the associated table is dropped?
• Which Snowflake tool can be used to evaluate the network connection to Snowflake at any time?
• In a managed schema, who centralizes privilege management?
• Which data transfer mode uses a storage location that is typically managed by the user?
• What does setting VALIDATION_MODE in COPY INTO TABLE do?
• Snowpipe supports loading from which stage types?
• Time Travel can be disabled for individual databases, schemas, and tables by setting DATA_RETENTION_TIME_IN_DAYS to 0.
• FORMAT_NAME and TYPE are mutually exclusive in the COPY command.
• If both FORMAT_NAME and TYPE are specified in a COPY command, what is the likely outcome?
• Extending Time Travel retention to 90 days incurs additional storage charges.
• What happens when you create the initial stream on a table where change tracking is not yet enabled?
• Is there a direct charge for using the Kafka connector?
• Each micro partition contains between 50 MB and 500 MB of uncompressed data.
• What is the general result of applying flatten to a JSON array in Snowflake?
• In a multi-cluster warehouse used to achieve extremely low latency for large numbers of concurrent users, which mode keeps all warehouses running to maximize resources?
• If the Python connector's temporary directory is not explicitly set, it uses which location?
• Which query will display the stage metadata including file name and row number?
• What is the typical micro-partition size before compression?
• In account usage views, which column displays the timestamp when an object was dropped?
• Which of the following is a supported data file type for COPY?
• A newly created, empty table has what clustering depth?
• If you change the data retention at the account level, do databases, schemas, and tables without explicit retention inherit the new value?
• Which privilege on the schema is required to add search optimization for the table?
• If a schema has a retention time of 10 days, a table in that schema with no explicit retention will inherit 10 days.
• When using DataFrames, which type of queries are supported by the Snowflake Spark connector?
• Which statement describes the purpose of the system$clustering_depth function?
• If a schema contains two tables with different data retention times, what happens when you run ALTER SCHEMA SET DATA_RETENTION_TIME_IN_DAYS = 20?
• Which minimum permissions allow a role to view COPY_HISTORY results for Snowpipe loads?
• Snowpipe Auto Ingest is supported only for external stages.
• What is a key billing characteristic of Snowpipe's serverless compute model?
• Which SQL will retrieve city, date, and temperature from CITY_TEMPERATURE's TEMP JSON using the given pattern?
• What is the recommended practice to avoid duplicate rows when using the Kafka connector?
• FORMAT_NAME and TYPE are mutually exclusive in the COPY command.
• Which statement best describes clustering depth?
• External functions can be used as part of a complex expression.
• Which technique uses a WITH clause that references itself to query hierarchical data?
• Which statement best describes the effect of the search optimization service on joins?
• Which privilege on the table is required to add search optimization?
• For semi-structured JSON logs with frequent last 10 days access, which modeling approach is recommended?
• Which Snowflake parameter limits the number of iterations for a recursive CTE?
• Which statement describes the speed benefits of materialized views?
• In Snowflake time travel, if a schema has DATA_RETENTION_TIME_IN_DAYS = 10 and a table inside it has DATA_RETENTION_TIME_IN_DAYS = 20, and then you drop the schema, what data retention value will be honored for the table when retrieving the table's data?
• When a new task is created and not activated yet, SHOW TASKS will display it in which state?
• If a table has a defined clustering key and you omit the second argument in system$clustering_information, what is returned?
• Which statement correctly describes Snowflake micro-partitions?
• To minimize processing overhead per file, what should you do with many small data files?
• Pushdown is not possible for which Spark component?
• COPY_HISTORY returns load activity for which loading methods?
• The amount of metadata stored in the RECORD_METADATA column is configurable using optional Kafka configuration properties.
• To request changing the MAX_RECURSIONS limit for your Snowflake account, who should you contact?
• The stream position is advanced when the stream is used in a DML statement, and the position is updated at the end of the transaction to the beginning timestamp of the transaction.
• Which statement about compression within micro-partitions is true?
• What is the default state of query pushdown in the Snowflake Spark Connector?
• Which query converts an array of project names into individual rows without using lateral?
• If Kafka topics are not mapped to existing Snowflake tables, what does the Kafka connector do?
• In internal data transfer with the Spark connector, what happens to the stage at the end of the Snowflake session?
• Which statement about CREATE TABLE ... LIKE is correct?
• If a data transfer is expected to take 48 hours, which Spark connector mode is recommended?
• Which design yields better partition pruning when you have a CITY attribute used for filtering in JSON data?
• Account usage views include internal IDs to differentiate records that have the same object name.
• In retention settings, does an explicit object-level retention take precedence over inherited values?
• Which of the following is a valid stage type for loading data into Snowflake?
• Which privilege on the source table is required to create an additional stream?
• Which statement describes the purpose of the system$clustering_ratio function?
• Inside a stored procedure, can you call another stored procedure or call itself recursively?
• COPY_HISTORY for Snowpipe loads includes which of the following?
• Does the Spark Connector support query pushdown to Snowflake?
• The MAX_RECURSIONS parameter limits the number of iterations in a recursive CTE to prevent infinite loops.
• Which function is used to understand the clustering depth of a table?
• Given a privilege setup where USER1 can SELECT on T1 via ROLE1 and has ROLE3 granted to USER1, which privileges does USER1 have on T1?