Free SnowPro Advanced Data Engineer Sample Questions — SnowPro Advanced Data Engineer

What are characteristics of Snowpark Python packages? (Choose three.)

• A. Third-party packages can be registered as a dependency to the Snowpark session using the session.import() method
• B. Python packages can access any external endpoints
• C. Python packages can only be loaded in a local environment
• D. Third-party supported Python packages are locked down to prevent hitting
• E. The SQL command DESCRIBE FUNCTION will list the imported Python packages of the Python User-Defined Function (UDF)
• F. Querying information_schema.packages will provide a list of supported Python packages and versions

When using a stored procedure in Snowflake, how many threads can the Snowflake API run concurrently?

• A. 1
• B. 8
• C. An unlimited number
• D. The number is dependent on the size of the virtual warehouse

A Data Engineer is using Time Travel to clone a table using this query: CREATE TABLE MY_TABLE_CLONE CLONE MY_TABLE AT (OFFSET => -60*30); An error is returned. What could be causing the error? (Choose two.)

• A. The source object is an external table
• B. The object has exceeded the maximum cloning limit
• C. Time Travel and cloning features cannot be run together
• D. The object cannot be cloned because it has a row access policy attached to it
• E. The source object did not exist at the time specified in the AT | BEFORE parameter

Which callback function is required within a JavaScript User-Defined Function (UDF) for it to execute successfully?

• A. initialize()
• B. processRow()
• C. handler()
• D. finalize()

A Data Engineer ran a stored procedure containing various transactions. During the execution, the session abruptly disconnected, preventing one transaction from committing or rolling back. The transaction was left in a detached state and created a lock on resources. What step must the Engineer take to immediately run a new transaction?

• A. Call the system function SYSTEM$ABORT_TRANSACTION
• B. Call the system function SYSTEM$CANCEL_TRANSACTION
• C. Set the LOCK_TIMEOUT to FALSE in the stored procedure
• D. Set the TRANSACTION_ABORT_ON_ERROR to TRUE in the stored procedure

A Data Engineer is debugging a SQL stored procedure that contains a transaction with a rollback using this code: DROP TABLE A1; CREATE OR REPLACE TABLE A1(i int); BEGIN TRANSACTION; INSERT INTO A1 VALUES (1), (2); INSERT INTO A1 VALUES (3), (4); CREATE OR REPLACE TABLE table2 (i VARCHAR); INSERT INTO A1 VALUES (5), (6); ROLLBACK; How many rows will the code write into table A1?

• A. 0 rows - the ROLLBACK command at the end of the query will remove all inserted data
• B. 2 rows - the CREATE statement acts as an implicit ROLLBACK, so all prior inserted rows are COMMITTED. The ROLLBACK command is outside of the transaction
• C. 4 rows - the ROLLBACK command at the end of the query will result in values 5 and 6 not being committed
• D. 6 rows - the CREATE statement acts as an implicit END to the BEGIN transaction. All values are therefore COMMITTED

A Data Engineer needs to load JSON output from some software into Snowflake using Snowpipe. Which recommendations apply to this scenario? (Choose three.)

• A. Load large files (1 GB or larger)
• B. Ensure that data files are 100-250 MB (or larger) in size, compressed
• C. Load a single huge array containing multiple records into a single table row
• D. Verify each value of each unique element stores a single native data type (string or number)
• E. Extract semi-structured data elements containing null values into relational columns before loading
• F. Create data files that are less than 100 MB and stage them in cloud storage at a sequence greater than once each minute

How should a Data Engineer build a pipeline that executes a stored procedure to parse and load unstructured data files as the files arrive in a stage?

• A. Create a Snowpipe with the parameter AUTO_INGEST = TRUE
• B. Create a Snowpipe that includes the INTEGRATION = ‘’ parameter
• C. Create a stage with parameter ENCODING = ‘’ to call the procedure when flies land in the stage
• D. Create a stage with a directory table, a stream on the directory table, and a task referencing the stream

Data is loaded into a staging table every day. From there, several departments perform transformations on the data and load it into different production tables. How should the staging table be created and used to MINIMIZE storage costs and MAXIMIZE performance?

• A. Create it as an external table, which is not covered by Time Travel
• B. Create it as a transient table with a retention time of 0 days
• C. Create it as a temporary table with a retention time of 0 days
• D. Create it as a permanent table with a retention time of 0 days

Which of the following grants are required for the role kafka_load_role_1 running the Snowflake Connector for Kafka, with the intent of loading data to Snowflake? (Choose three.) (Assume this role already exists and has usage access to the schema kafka_schema in database kafka_db, the target for data loading.)

• A. grant create pipe on schema kafka_schema to role kafka_load_role_1;
• B. grant create stream on schema kafka_schema to role kafka_load_role_1;
• C. grant create stage on schema kafka_schema to role kafka_load_role_1;
• D. grant create table on schema kafka_schema to role kafka_load_role_1;
• E. grant create task on schema kafka_schema to role kafka_load_role_1;
• F. grant create external table on schema kafka_schema to role kafka_load_role_1;

A stream called TRANSACTIONS_STM is created on top of a TRANSACTIONS table in a continuous pipeline running in Snowflake. After a couple of months, the TRANSACTIONS table is renamed TRANSACTIONS_RAW to comply with new naming standards. What will happen to the TRANSACTIONS_STM object?

• A. TRANSACTIONS_STM will keep working as expected
• B. TRANSACTIONS_STM will be stale and will need to be re-created
• C. TRANSACTIONS_STM will be automatically renamed TRANSACTIONS_RAW_STM
• D. Reading from the TRANSACTIONS_STM stream will succeed for some time after the expected STALE_TIME

A CSV file, around 1 TB in size, is generated daily on an on-premise server. A corresponding table, internal stage, and file format have already been created in Snowflake to facilitate the data loading process. How can the process of bringing the CSV file into Snowflake be automated using the LEAST amount of operational overhead?

• A. Create a task in Snowflake that executes once a day and runs a COPY INTO statement that references the internal stage. The internal stage will read the files directly from the on-premise server and copy the newest file into the table from the on-premise server to the Snowflake table
• B. On the on-premise server, schedule a SQL file to run using SnowSQL that executes a PUT to push a specific file to the internal stage. Create a task that executes once a day in Snowflake and runs a COPY INTO statement that references the internal stage. Schedule the task to start after the file lands in the internal stage
• C. On the on-premise server, schedule a SQL file to run using SnowSQL that executes a PUT to push a specific file to the internal stage. Create a pipe that runs a COPY INTO statement that references the internal stage. Snowpipe auto-ingest will automatically load the file from the internal stage when the new file lands in the internal stage
• D. On the on-premise server, schedule a Python file that uses the Snowpark Python library. The Python script will read the CSV data into a DataFrame and generate an INSERT INTO statement that will directly load into the table. The script will bypass the need to move a file into an internal stage

What is the purpose of the BUILD_STAGE_FILE_URL function in Snowflake?

• A. It generates an encrypted URL for accessing a file in a stage
• B. It generates a staged URL for accessing a file in a stage
• C. It generates a permanent URL for accessing files in a stage
• D. It generates a temporary URL for accessing a file in a stage

A Data Engineer would like to define a file structure for loading and unloading data. Where can the file structure be defined? (Choose three.)

• A. COPY command
• B. MERGE command
• C. FILE FORMAT object
• D. PIPE object
• E. STAGE object
• F. INSERT command

Which command will retrieve results for all objects of a row access policy db1.policies.objectslist at the database level?

• A. SELECT * FROM TABLE( db1.ACCOUNT_USAGE.POLICY_REFERENCES( POLICY_NAME=>'db1.policies.objectslist' ) );
• B. SELECT * FROM TABLE( db1.INFORMATION_SCHEMA.POLICY_REFERENCES( POLICY_NAME=>'db1.policies.objectslist' ) );
• C. SELECT * FROM TABLE( db1.ACCOUNT_USAGE.ROW_ACCESS_POLICIES( POLICY_NAME=>'db1.policies.objectslist' ) );
• D. SELECT * FROM TABLE( db1.INFORMATION_SCHEMA.POLICY_REFERENCES( REF_ENTITY_NAME => 'db1.policies.objectslist', REF_ENTITY_DOMAIN => 'policy' ) );

A new CUSTOMER table is created by a data pipeline in a Snowflake schema where MANAGED ACCESS is enabled. Which roles can grant access to the CUSTOMER table? (Choose three.)

• A. The role that owns the schema
• B. The role that owns the database
• C. The role that owns the CUSTOMER table
• D. The SYSADMIN role
• E. The SECURITYADMIN role
• F. The USERADMIN role with the MANAGE GRANTS privilege

A Data Engineer executes a complex query and wants to make use of Snowflake’s query results caching capabilities to reuse the results. Which conditions must be met? (Choose three.)

• A. The results must be reused within 72 hours
• B. The query must be executed using the same virtual warehouse
• C. The USED_CACHED_RESULT parameter must be included in the query
• D. The table structure contributing to the query result cannot have changed
• E. The new query must have the same syntax as the previously executed query
• F. The micro-partitions cannot have changed due to changes to other data in the table

A company has deployed a data pipeline that streams customer transaction data from an on-premises PostgreSQL database to Snowflake for real-time analytics. A Data Engineer needs to configure a connector that will continuously and reliably transfer data to Snowflake. Which connector configuration will meet these requirements, while providing MINIMAL latency, and OPTIMAL performance?

• A. Configure the Snowflake Connector for Kafka to continuously stream data from the PostgreSQL database into Snowflake by configuring PostgreSQL as a Kafka producer
• B. Deploy the Snowflake JDBC driver to connect the PostgreSQL database directly to Snowflake, setting up a continuous query on the PostgreSQL database to push data to Snowflake
• C. Install the Snowflake Connector for Python on the PostgreSQL server to stream data directly to Snowflake, using Python scripts to manage the data transfer
• D. Configure the Snowflake Connector for Kafka on a Kafka Connect cluster to stream data from the PostgreSQL database to Kafka topics, which will be ingested using the Kafka connector

A Data Engineer is querying a large table (+100 million rows) with the following SQL: select name , address , datediff(year, birthdate, current_date()) from customers; The Engineer notices that the results cache is not being used on subsequent executions of the query. The underlying table does not change and the data is not updated in between query executions. What is causing this to occur?

• A. Calculations are not stored in the results cache
• B. Large results are not stored in the results cache
• C. Date functions are not stored in the results cache
• D. Non-deterministic calculations are not stored in the results cache

Which Snowflake feature facilitates access to external API services such as geocoders, data transformation, machine learning models, and other custom code?

• A. Security integration
• B. External tables
• C. External functions
• D. Java User-Defined Functions (UDFs)