Q193. You’ve created a JavaScript stored procedure using Snowpark to transform data’. The stored procedure is failing, and you suspect an issue with how Snowpark is handling null values during a join operation. Given two Snowpark DataFrames, and ‘df2 , what is the expected behavior when performing an inner join on a column containing null values in both DataFrames, and how can you mitigate potential issues?

Q194. Given the following scenario: You have an external table ‘EXT SALES in Snowflake pointing to a data lake in Azure Blob Storage. The storage account network rules are configured to only allow specific IP addresses and virtual network subnets, enhancing security. You are getting intermittent errors when querying ‘EXT SALES. Which of the following could be the cause(s) and the corresponding solution(s)? Select all that apply.

Q195. You are using Snowpipe to load data from an AWS S3 bucket into Snowflake. The data files are compressed using GZIP and are being delivered frequently. You have observed that the pipe’s backlog is increasing and data latency is becoming unacceptable. Which of the following actions could you take to improve Snowpipe’s performance? (Select all that apply)

Q196. You are designing a data ingestion process that involves loading data from an external stage. The data is partitioned into multiple files based on date. The stage is configured to point to the root directory of the partitioned dat a. You want to efficiently load only the data for a specific date (e.g., ‘2023-01-15’) using the ‘COPY’ command. Assume your stage name is ‘my _ stage’ , your table is ‘my_table’, your date column is named ‘event_date’, and the files in the stage are named in the format ‘data YYYY-MM-DD.csv’. Which of the following options allows you to selectively load the data for the specific date? (Select ALL that apply)

Q197. You are tasked with loading a large dataset (50TB) of JSON files into Snowflake. The JSON files are complex, deeply nested, and irregularly structured. You want to maximize loading performance while minimizing storage costs and ensuring data integrity. You have a dedicated Snowflake virtual warehouse (X-Large).
Which combination of approaches would be MOST effective?

Q198. You are designing a data governance strategy for a Snowflake data warehouse. One of the key requirements is to track data lineage for sensitive data, specifically Personally Identifiable Information (PII). You need to understand how PII data flows through various transformations and tables. Which Snowflake feature, when combined with appropriate tagging and metadata management practices, can BEST help you achieve this?

Q199. You are tasked with implementing data masking on a ‘CUSTOMER’ table. The requirement is to mask the ‘EMAIL’ column for all users except those with the ‘DATA ADMIN’ role. You have the following code snippet. What is wrong with it?

Q200. You have a Snowflake Stream named ‘ORDERS STREAM’ on an ‘ORDERS’ table, which is used to incrementally load data into a historical orders table named ‘HISTORICAL ORDERS’. The data pipeline involves a series of tasks: 1) Consume changes from the ‘ORDERS STREAM’, 2) Apply transformations and data quality checks, and 3) Merge the changes into ‘HISTORICAL ORDERS’ using a MERGE statement. After a recent data load, you notice that the ‘HISTORICAL ORDERS’ table contains duplicate records for certain ‘ORDER values. The MERGE statement uses ‘ORDER ID’ as the matching key. You have confirmed that the transformation logic is correct and idempotent. Examine the MERGE statement below. What could be causing the duplicates, given the context of Streams and incremental loading?

Q201. A company stores raw clickstream data in AWS S3. They need to query this data occasionally (less than once per day) for ad-hoc analysis and auditing purposes without ingesting it into Snowflake. Which of the following approaches is MOST suitable and cost- effective, and which considerations regarding data freshness are crucial?

Q202. You are using Snowpark Python to perform a complex data transformation involving multiple tables and several intermediate dataframes. During the transformation, an error occurs within one of the Snowpark functions, causing the entire process to halt. To ensure data consistency, you need to implement transaction management. Which of the following Snowpark DataFrameWriter options or session configurations would be MOST appropriate for rolling back the entire transformation in case of an error during the write operation to the final target table?

Q203. You are creating a Snowflake Listing to share data with multiple consumers. One consumer requires access to the complete dataset while other consumers need access to a subset of the data based on geographical region (e.g., only data related to the ‘US’). You want to minimize data duplication and management overhead. Select all the valid ways to implement this using Snowflake Data Sharing features.

Q204. Consider the following scenario: You are ingesting JSON data from an external stage into Snowflake. The JSON data contains an array of objects, where each object represents a product with attributes like ‘product id’, ‘name’, and ‘price’. However, sometimes the ‘price’ field is missing entirely from some product objects. You want to load this data into a Snowflake table with columns ‘product_id’, ‘name’, and ‘price’ (defined as NUMBER). How can you handle the missing ‘price’ field gracefully during the COPY INTO operation, ensuring that missing prices are represented as NULL in the Snowflake table without causing errors?

Q205. You have implemented a row access policy on a ‘products’ table to restrict access based on the user’s group. The policy uses a mapping table ‘user_groups’ to determine which products a user is allowed to see. After implementing the policy, users are reporting significant performance degradation when querying the ‘products’ table. What are the MOST likely causes of this performance issue, and what steps can you take to mitigate them? Select all that apply.

Q206. A data engineer is tasked with implementing a data governance strategy in Snowflake. They need to automatically apply a tag ‘PII CLASSIFICATION’ to all columns containing Personally Identifiable Information (PII). Given the following requirements: 1. The tag must be applied as close to data ingestion as possible. 2. The tagging process should be automated and scalable. 3. The tag value should be dynamically set based on a regular expression match against column names and data types. Which of the following approaches would be MOST effective and efficient in achieving these goals?

Q207. A data engineer is tasked with creating a Snowpark Python UDF to perform sentiment analysis on customer reviews. The UDF, named ‘analyze_sentiment’ , takes a string as input and returns a string indicating the sentiment (‘Positive’, ‘Negative’, or ‘Neutral’). The engineer wants to leverage a pre-trained machine learning model stored in a Snowflake stage called ‘models’. Which of the following code snippets correctly registers and uses this UDF?

Q208. You have a Snowflake table called ‘RAW ORDERS that contains semi-structured JSON data in a column named ‘ORDER DETAILS. You need to extract specific fields from the JSON data, perform some data type conversions, and then load the transformed data into a relational table named ‘CLEAN ORDERS’. Your requirements are as follows: 1. Extract the (STRING) from the JSON and store it as ‘ORDER ID (NUMBER). 2. Extract the (STRING) from the JSON and store it as ‘CUSTOMER ID (NUMBER). 3. Extract the ‘order_date’ (STRING) from the JSON and store it as ‘ORDER DATE’ (DATE). 4. Extract (STRING) from the JSON and store it as ‘TOTAL AMOUNT’ (FLOAT). Which of the following Snowpark Python code snippets correctly transforms the data and loads it into the ‘CLEAN ORDERS table using a combination of Snowpark DataFrame operations and SQL? Assume that session ‘sp’ is already initialized.

Q209. Consider a scenario where you have a Snowflake table named ‘CUSTOMER DATA’ containing customer IDs (INTEGER) and encrypted credit card numbers (VARCHAR). You need to create a secure JavaScript UDF to decrypt these credit card numbers using a custom encryption key stored securely within Snowflake’s internal stage, and then mask all but the last four digits of the decrypted number for data protection. Which of the following actions are necessary to ensure both functionality and security while adhering to Snowflake’s best practices for UDF development and security?

Q210. Consider the following scenario: You have a Snowflake task that refreshes a materialized view. The materialized view is based on a large base table that is constantly being updated. The refresh operation is taking longer than expected, impacting downstream reporting. Which of the following actions will likely NOT improve the performance of the materialized view refresh?