Get Ready with DAA-C01 Exam Dumps (2025) [Q138-Q162]

NO.138 You have a CSV file loaded into a Snowflake table named ‘raw data’. The file contains customer order data, but some rows have missing values in the ‘order date’ column. You need to create a new table, ‘cleaned data’ , that contains only valid records and handles missing ‘order date’ values by substituting them with the date ‘1900-01-01’. Which of the following approaches is the MOST efficient and correct way to achieve this using Snowflake features?

NO.139 You have a Snowflake table ‘RAW DATA containing a ‘VARIANT column called ‘json_data’. This column stores JSON objects representing customer orders. The structure includes a nested array of items within each order. You need to create a flattened table called ‘ORDER ITEMS with the following columns: ‘order_id’, and However, the field is not directly present in the JSON data’. Instead, it needs to be derived by concatenating the ‘order_id’ with the index (ordinal position) of the item within the ‘items’ array. The structure looks like this: { “order_id”: “ORD-123”, “customer_id”: “CUST-456”, “items”: [ { “item_name”: “Laptop”, “item_price”: 1200 }, { “item_name”: “Mouse”, “item_price”: 25 } ] } Which of the following SQL statements correctly creates the ‘ORDER ITEMS table?

Option A

Option B

Option C

Option D

Option E

NO.140 You are tasked with loading JSON files into Snowflake using Snowsight. The JSON files are semi-structured and contain nested arrays and objects. You want to flatten the JSON structure during the load process to facilitate easier querying. Which of the following Snowsight-integrated features or approaches are suitable for flattening the JSON data during the load process? Select all that apply.

Load the JSON data into a VARIANT column in Snowflake. Then, use SQL with ‘LATERAL FLATTEN’ to query and extract data from the nested structures, creating a view to represent the flattened data.

Use Snowsight’s ‘Load Data’ wizard to load the JSON files directly into relational tables by defining mappings between JSON elements and table columns. Snowsight automatically flattens the JSON structure based on the defined mappings.

Create a Snowpipe that automatically loads new JSON files from a stage into a Snowflake table. Within the Snowpipe definition, define a transformation that uses ‘LATERAL FLATTEN’ to flatten the JSON data before loading it into the target table.

Load the JSON data into a staging table as raw JSON strings. Then, write a custom Python User-Defined Function (UDF) that parses the JSON strings and returns a flattened representation of the data. Use this UDF in a ‘CREATE TABLE AS SELECT statement to load the flattened data into a new table.

Use Snowsight’s ‘Load Data’ wizard along with Snowflake’s built-in functions like ‘GET_PATH’ and operator within the computed column expressions during the load process to extract and load specific elements from the JSON data into individual columns.

NO.141 You are tasked with building a near real-time data pipeline that ingests streaming data from Kafka into Snowflake. The data is semi- structured JSON. The goal is to transform and load only records with a ‘status’ field equal to ‘active’ into a table named ‘ACTIVE RECORDS’. Which approach provides the most efficient and scalable solution, minimizing latency and Snowflake resource consumption?

Use Snowflake’s Kafka connector to load all data into a staging table, then create a scheduled task that runs every minute to transform and load the ‘active’ records into ‘ACTIVE RECORDS

Implement a Snowpipe with a continuous ingest endpoint. Use a transformation defined within the COPY INTO statement to filter for ‘status’ = ‘active’ during the load process.

Create an external table pointing to the Kafka topic. Use a Snowflake stream on the external table to track changes, and a task to transform and load ‘active’ records into ‘ACTIVE RECORDS’

Use a third-party ETL tool to consume the Kafka stream, filter for ‘status’ = ‘active’, and then load the data directly into ‘ACTIVE RECORDS’ using the Snowflake JDBC driver.

Ingest all data into a raw table using Snowpipe. Create a materialized view on top of the raw table, filtering for ‘status’ = ‘active’, and store the results in ‘ACTIVE RECORDS’.

NO.142 You are tasked with ingesting data from a REST API that returns data in JSON format. The API requires authentication via an API key passed in the header and returns a large dataset, potentially exceeding Snowflake’s recommended file size for optimal loading. Furthermore, the API has rate limits, and exceeding them results in temporary blocking. What is the MOST efficient and reliable way to retrieve this data into Snowflake?

Use a Snowflake external function to directly call the API within a COPY INTO statement. Handle authentication and rate limiting within the external function’s code.

Create a Python script using Snowpark that connects to the API, handles authentication, rate limiting, and partitions the data into smaller files. Stage these files in cloud storage and then use COPY INTO to load them into Snowflake.

Utilize Snowpipe with a REST API integration configured directly with the API endpoint. Snowflake will automatically handle the data retrieval and loading.

Employ a third-party ETL tool that supports REST API connections, data partitioning, rate limiting, and Snowflake integration. Configure the tool to extract, transform, and load the data into Snowflake.

Write a Snowpark stored procedure that invokes a Java UDF to connect to the REST API. Handle rate limiting and authentication within the Java UDF and use it to insert data directly into a Snowflake table.

NO.143 You are working on a data ingestion pipeline that loads data from a CSV file into a Snowflake table called The CSV file occasionally contains invalid characters in the ‘Email’ column (e.g., spaces, non-ASCII characters). You want to ensure data integrity and prevent the entire load from failing due to these errors. Which of the following strategies, used in conjunction, would BEST handle this situation during the COPY INTO command and maintain data quality?

Use the ERROR = ‘SKIP FILE” option in the ‘COPY INTO’ command along with a file format that specifies ‘TRIM SPACE = TRUE and ‘ENCODING =’UTF8″.

Use the ‘ON ERROR = ‘CONTINUE” option in the ‘COPY INTO’ command. Create a separate error queue table and configure the ‘COPY INTO’ command to automatically insert error records into the queue.

Use the ‘ON ERROR = ‘SKIP FILE” option in the ‘COPY INTO’ command and then run a subsequent SQL query to identify and correct any invalid email addresses in the ‘EmployeeData’ table.

Use a file format with ‘VALIDATE UTF8 = TRUE, and ‘ON ERROR=’SKIP FILE”. Create a separate stage containing invalid data to be handled at a later stage with another transformation job

Employ the ‘VALIDATE function during the ‘COPY INTO command to identify erroneous Email columns and use the ‘ON ERROR = ‘CONTINUE” along with using file format that specifies ‘TRIM_SPACE = TRUE and ENCODING = ‘UTF8″.

NO.144 A financial institution needs to build a dashboard to monitor fraudulent transactions. They have transaction data (amount, timestamp, location, merchant category), customer data (age, income, credit score), and fraud flags. They want to identify patterns and correlations that indicate potentially fraudulent activity. Which of the following visualizations and Snowflake features, when used in combination, would be MOST effective for this purpose? (Select TWO)

Use Snowflake’s secure data sharing to share the raw transaction data with a third-party analytics vendor.

Develop an interactive dashboard using Power BI connected to Snowflake, incorporating a geographical map showing transaction locations, a time series chart of transaction volume, and a scatter plot of transaction amount vs. customer credit score. Use anomaly detection algorithms in Snowflake (via Snowpark or stored procedures using Python) to highlight unusual transactions. Implement User defined table Function in Power BI to generate more data.

Create a static report showing the total number of fraudulent transactions per month, using Snowflake’s aggregate functions.

Utilize a heatmap visualization in a Tableau dashboard to show the correlation between merchant category, transaction amount, and fraud flag. Implement interactive filters for customer age, income, and location. Implement Snowflake Row Access Policy to restrict data to specific users.

Export the transaction data to a data lake and use Spark to perform fraud detection.

NO.145 You are tasked with ingesting clickstream data from a website into Snowflake for real-time analytics. The website generates approximately 100,000 events per minute. The business requires insights into user behavior with a maximum latency of 5 minutes. Which data collection strategy would be MOST appropriate to meet these requirements, considering both cost and near real-time needs?

Batch ingestion using Snowpipe with file sizes of IGB uploaded every 15 minutes.

Near real-time ingestion using Snowpipe with auto-ingest enabled and micro-batches of data.

Scheduled task using a Python script to extract data from an API endpoint every 10 minutes.

Utilizing a third-party ETL tool to transfer data in hourly batches.

Directly inserting data using JDBC from the web application.

NO.146 You have a Snowflake table called ‘CUSTOMER ORDERS that stores customer order data’. The business requires you to generate a weekly report on the top 10 customers by order value, delivered as an Excel file to a shared network drive. The network drive is accessible by a service account that your Snowflake account can authenticate against. The report must include customer name, total order value, and number of orders. Which approach is the MOST secure and efficient for automating this process?

Create a Snowflake Task that executes a stored procedure. The stored procedure uses a Snowflake Scripting block to query the data, format the data using Javascript UDF to XML, write the Excel file to an internal stage using Java UDF, and then use a Python UDF to copy the file to the network drive. Grant necessary permissions to the task’s service account.

Create a Snowflake external function using AWS API Gateway and AWS Lambda. The external function queries the data from Snowflake, formats it as an Excel file using a Python library (e.g., openpyxl) within the Lambda function, and saves the file directly to the network drive using the service account’s credentials. Configure API Gateway to authenticate requests from Snowflake.

Create a view on top of the CUSTOMER_ORDERS table that calculates the required metrics. Use a third-party ETL tool to extract the data from the view, format it as an Excel file, and save it to the network drive. Configure the ETL tool with appropriate Snowflake credentials.

Use a Snowflake Task to trigger a Snowpipe. A Snowflake stored procedure that executes SQL code to query for relevant data, convert it to JSON, then the Snowpipe load into the network directory using REST API. Grant necessary permissions to the task’s service account.

Leverage a Snowflake Task to run a stored procedure. The procedure queries the data, transforms it into CSV format using Snowflake scripting. Then uses a Java UDF to copy the CSV to an internal stage, from where a separate process (outside Snowflake) monitors for new files and transfers them to the network drive using the service account. Securely manage credentials for both the Java UDF and the external process.

NO.147 Your company uses Snowflake to store sales data’. A dashboard reporting weekly sales trends is performing poorly. The underlying table, ‘SALES DATA, contains billions of rows with columns like ‘SALE DATE, ‘PRODUCT ID’, ‘CUSTOMER D’, and ‘SALE AMOUNT. The dashboard queries use ‘SALE DATE for filtering and grouping. The query execution plan shows full table scans. You need to optimize the dashboard’s performance with minimal impact on data loading processes. Which of the following strategies should you implement FIRST to improve query performance?

Create a clustered table on ‘SALE DATE’ in the ‘SALES DATA’ table.

Create a materialized view that aggregates sales data weekly by ‘PRODUCT_ID and ‘CUSTOMER ID

Increase the warehouse size to X-LARGE.

Create a search optimization on ‘SALES DATA’ table for ‘SALE DATE.

Partition the ‘SALES_DATX table by ‘SALE_DAT

NO.148 You are responsible for a Snowflake data pipeline that loads data from various external sources into a data warehouse. You have implemented Snowpipe for continuous data ingestion from AWS S3. One of your data sources is known to occasionally produce corrupted data files. You need to design a resilient pipeline that can handle these corrupted files without halting the entire data loading process and allows for the identification and quarantine of these files for further investigation. Assume you have a table called ‘RAW DATA’ and you are using a file format ‘MY CSV FORMAT. Select TWO actions which best address this situation:

Set ‘ON_ERROR = ‘SKIP_FILE” in the ‘COPY INTO’ statement used by Snowpipe. This setting will skip entire files that contain errors, preventing them from being loaded into the table.

Configure Snowpipe’s ‘ERROR INTEGRATION’ to log details of files that fail during ingestion, including the error messages. Then, set ERROR-CONTINUE” on the Copy Statement. This requires an external stage for the error logs. In addition, create a separate task that queries the ERROR INTEGRATION external stage and moves the corrupt files into a separate quarantine bucket.

Implement a pre-processing step using a Snowflake task to validate the data in each file before it’s loaded into the ‘RAW_DATR table using a ‘COPY INTO’ statement. The Task would then move files failing validation to a quarantine bucket.

Create a separate stream on the ‘RAW DATA’ table that filters for records with errors and then move these erroneous records into a quarantine table using a task. Implement ERROR=CONTINIJE on the Copy Statement.

NO.149 You are designing a data ingestion pipeline for a financial institution. The pipeline loads transaction data from various sources into a Snowflake table named ‘TRANSACTIONS. The ‘TRANSACTIONS table includes columns such as TRANSACTION , ‘ACCOUNT ID’, ‘TRANSACTION DATE, ‘TRANSACTION AMOUNT, and ‘TRANSACTION TYPE. The data is loaded in micro- batches using Snowpipe. Due to potential source system errors and network issues, duplicate records with the same ‘TRANSACTION ID’ are occasionally ingested. You need to ensure data integrity by preventing duplicate ‘TRANSACTION_ID’ values in the ‘TRANSACTIONS’ table while minimizing the impact on ingestion performance. Which of the following approaches is the MOST efficient and reliable way to handle this deduplication requirement in Snowflake, considering data integrity and performance?

Define as the primary key on the ‘TRANSACTIONS’ table. Snowflake will automatically reject any duplicate inserts during Snowpipe ingestion.

Create a scheduled task that runs every hour to identify and delete duplicate records based on ‘TRANSACTION ID. The task will use a SQL query to find duplicate ‘ TRANSACTION ID values and remove the older entries.

Create a stream on the ‘TRANSACTIONS’ table and use it to identify newly inserted rows. Then, use a merge statement to insert new, distinct transactions into a separate staging table. Finally, periodically truncate the original ‘TRANSACTIONS table and load the deduped data from the staging table.

Use a materialized view built on top of the TRANSACTIONS table that selects distinct transaction ids. This ensures that querying through the materialized view returns no duplicates.

Create a staging table with the same schema as ‘TRANSACTIONS’. Use a ‘MERGE’ statement within the Snowpipe load process to insert new records from the incoming data into the ‘TRANSACTIONS’ table, only if the ‘TRANSACTION ID does not already exist. Define ‘TRANSACTION ID’ as the primary key in the staging table. Use clustering on ‘TRANSACTION_ID on the target ‘TRANSACTIONS’ table.

NO.150 Consider a scenario where you’re analyzing website user behavior data in Snowflake. You have a table named ‘user sessionS with a column containing semi-structured data (VARIANT type) describing user interactions during a session. You need to create a UDF that accepts and extracts all the distinct event types that occurred within that session. The UDF should return an array of unique event type strings. This array will be used later to identify users who have participated in a specific combination of events. Which of the following approaches can effectively achieve this using Snowflake’s SQL extensibility features?

Using a JavaScript UDF that iterates through the session data, extracts the event types, adds them to a JavaScript Set to ensure uniqueness, and then converts the Set to an array. This array is returned as a result. (Single Correct Answer)

Creating a SQL UDF that utilizes Snowflake’s ARRAY AGG and DISTINCT functions to aggregate all event types from the session data into an array, ensuring uniqueness. (Single Correct Answer)

Implementing a Java UDF that parses the session data, extracts the event types, uses Java’s HashSet to guarantee uniqueness, and returns the HashSet as a comma-separated string. (Single Correct Answer)

Using an external function with a remote service to process ‘session_data’ and return JSON array with distinct event types. The external function setup must include API integration with the third party service and Role based access control. (Single Correct Answer)

All of the above can satisfy the question. (Single Correct Answer)

NO.151 A marketing team wants to understand the impact of their campaigns on website traffic and conversions. You have the following tables in Snowflake: sCAMPAlGN PERFORMANCE: ‘CAMPAIGN ONT), (DATE), ‘CLICKS’ (INT), ‘IMPRESSIONS’ (INT), ‘COST (NUMBER) ‘DATE’ (DATE), ‘PAGE_VIEWS’ ONT), (INT) ‘CONVERSIONS’ ‘DATE (DATE), ONT), (NUMBER) Which SQL query and visualization technique would be most suitable for identifying the correlation between campaign spend and website conversions over time, allowing the team to quickly identify campaigns with a high return on investment (ROI)?

A simple bar chart showing total clicks per campaign ID, generated from a query that joins ‘CAMPAIGN PERFORMANCE with ‘WEBSITE _ TRAFFIC’ on the DATE column.

A scatter plot visualizing the relationship between ‘COST’ from ‘CAMPAIGN PERFORMANCE and ‘CONVERSION COUNT from ‘CONVERSIONS’, aggregated by ‘DATE and calculated ROI, generated from a query using window functions to compute cumulative sums and moving averages.

Three separate pie charts, one showing the percentage of clicks per campaign, one showing the percentage of impressions per campaign, and one showing the percentage of conversions per campaign.

Aline chart displaying ‘COST from ‘CAMPAIGN PERFORMANCE, from ‘WEBSITE TRAFFIC, and ‘CONVERSION COUNT from ‘CONVERSIONS’ over time ( ‘ DATE), joined on the ‘DATE column, with a calculated ROI metric displayed as a separate line on the same chart. The query uses a common table expression (CTE) to first calculate daily ROI.

An excel Pivot table from exported data from all these tables which uses the data points required.

NO.152 You are designing a data ingestion pipeline for IoT sensor data’. Sensors transmit data in JSON format every 5 seconds. The volume of data is expected to grow exponentially. The business requires both real-time dashboards and historical trend analysis. Which of the following strategies should you employ to address these requirements, particularly focusing on optimizing for both ingestion frequency and cost?

Ingest data directly into a single Snowflake table using Snowpipe with JSON data type. Create separate materialized views for real-time dashboards and historical trend analysis.

Use Snowpipe to ingest data into a raw landing table, and then use Snowflake tasks to transform and load the data into separate tables optimized for real-time dashboards and historical analysis.

Utilize an external stream processing engine to pre-aggregate the data into time windows (e.g., I-minute, I-hour) before ingesting into Snowflake using Snowpipe.

Employ a combination of Snowpipe for near real-time data ingestion into a raw table, and then use Snowflake’s Search Optimization Service for faster queries.

Implement Snowpipe for initial data ingestion, complemented by Snowflake’s clustering feature based on timestamp to optimize historical analysis queries. And employ a stream processing engine to perform time window pre-aggregation.

NO.153 You are working with a Snowflake table ‘raw_data’ containing a column of type TEXT that stores log messages in various formats, including JSON and CSV. You need to extract specific data points from these log entries, treating JSON entries differently from CSV entries. Specifically: For JSON log entries (identified by starting with ‘{l), extract the value of the ‘user_id’ key. For CSV log entries (identified by starting with a number), extract the second field (assuming comma-separated values). Which of the following queries is the most efficient and correct way to achieve this data extraction?

Option A

Option B

Option C

Option D

Option E

NO.154 You are tasked with validating the ‘SALES DATA’ table containing sales records. One of the columns, ‘SALE AMOUNT’, is defined as VARCHAR, but it should be a NUMERIC. Some rows contain non-numeric characters and NULL values represented as the string ‘NULL’. You need to identify rows that will cause errors when casting ‘SALE AMOUNT to NUMERIC, and replace these rows with valid values using Snowflake SQL. Which of the following SQL statements, when executed in sequence, effectively identifies and corrects problematic ‘SALE AMOUNT’ values? Note: Assume the “REPLACE INVALID CHARACTERS’ UDF correctly replaces non- numeric characters with empty strings.

NO.155 A telecommunications company wants to segment its customers based on their usage patterns for targeted marketing campaigns. You have access to a table named ‘CUSTOMER USAGE with the following columns: ‘CUSTOMER ONT), ‘DATA USAGE GB’ (FLOAT), ‘VOICE CALL MINUTES (INT), and (INT). Which of the following Snowflake features or techniques would be MOST appropriate for performing customer segmentation and determining distinct customer clusters?

Using the ‘APPROX COUNT DISTINCT function to estimate the number of distinct usage patterns without performing actual clustering.

Implementing a K-Means clustering algorithm using Snowflake’s Python User-Defined Functions (UDFs) and storing the cluster assignments in a new column within the ‘CUSTOMER USAGE table.

Creating a series of complex SQL queries with multiple ‘CASE statements to manually define customer segments based on predefined thresholds for data usage, voice calls, and SMS count.

Using Snowflake’s built-in ‘QUALIFY’ clause combined with window functions to rank customers based on individual usage metrics and categorize them based on rank percentiles.

Utilizing Snowflake’s external functions to call a machine learning model hosted on a platform like AWS SageMaker or Azure Machine Learning to perform the clustering and return the segment assignments.

NO.156 A Snowflake table ‘transactions’ stores data about financial transactions. The table includes the following columns: ‘transaction_id’ (INTEGER), ‘account_id’ (INTEGER), ‘transaction_date’ (DATE), and ‘transaction_amount’ (NUMBER). You need to analyze the moving average of transaction amounts for each account over a 7-day window. The moving average should be calculated for each transaction date, considering the 3 preceding days, the current day, and the 3 following days. You want to show the ‘account_id’ , ‘transaction_date’, ‘transaction amount’, and the calculated ‘moving_average”. What’s the most appropriate and efficient Snowflake query to perform this calculation?

Option A

Option B

Option C

Option D

Option E

NO.157 A company ingests sensor data into a Snowflake table named READINGS with columns (VARCHAR), ‘reading_time’ (TIMESTAMP NTZ), and ‘raw_value’ (VARCHAR). The ‘raw_value’ column contains numeric data represented as strings, but sometimes includes non-numeric characters (e.g., ‘123.45’, ‘N/A’, ‘500’). You need to calculate the average of the numeric raw_value’ readings for each within the last hour, excluding invalid readings. Which of the following Snowflake SQL statements will correctly accomplish this, handling potential conversion errors and filtering for valid data?

SELECT sensor_id, FROM SENSOR_READINGS WHERE reading_time DATEADD(hour, -1, CURRENT _ TIMESTAMP()) GROUP BY sensor id;

SELECT sensor_id, FROM SENSOR_READINGS WHERE reading_time DATEADD(hour, -1 , AND TRY_TO IS NOT NULL GROUP BY sensor_id;

SELECT sensor_id, AVG(CASE WHEN THEN ELSE NULL END) FROM SENSOR_READINGS WHERE reading_time DATEADD(hour, -1, CURRENT TIMESTAMP()) GROUP BY sensor_id;

SELECT sensor_id, raw_value, NULL))) FROM SENSOR_READINGS WHERE reading_time DATEADD(hour, -1, CURRENT TIMESTAMP()) GROUP BY sensor_id;

SELECT sensor_id, ‘N/A’))) FROM SENSOR_READINGS WHERE reading_time DATEADD(hour, -1 , CURRENT TIMESTAMP()) GROUP BY sensor_id;

NO.158 A data analyst accidentally dropped a crucial table, ‘SALES DATA’, containing historical sales information. The table was dropped 5 days ago. The data retention period for the Snowflake account is set to the default value. The analyst needs to recover the table with all its data’. What is the MOST efficient and reliable method to recover the ‘SALES DATA” table in Snowflake?

Restore the table using the ‘UNDROP TABLE SALES DATA’ command.

create a clone of the table using Time Travel at a point in time before it was dropped, using the ‘CREATE TABLE CLONE SALES DATA BEFORE(STATEMENT y command.

Request Snowflake Support to restore the table from their backups.

Use the ‘TIME TRAVEL’ function in a SELECT statement to retrieve the data and recreate the table.

create a clone of the table using Time Travel at a point in time before it was dropped, using the ‘CREATE TABLE CLONE SALES DATAAT (OFFSET -86400 5)’ command.

NO.159 You are tasked with loading data from an S3 bucket into a Snowflake table named ‘SALES DATA’. The data is in CSV format, compressed with gzip, and contains a header row The S3 bucket requires AWS IAM role authentication. The ‘SALES DATA’ table already exists, and you want to use a named stage for this ingestion process. Which of the following steps are necessary to successfully load the data, minimizing administrative overhead?

Create a new IAM role in AWS with access to the S3 bucket, then create a Snowflake storage integration object referencing that role’s ARN and the S3 bucket’s URL.

Create a Snowflake stage object that references the storage integration, the S3 bucket URL, and specifies the file format (CSV with gzip compression and header skip). Use the ‘COPY INTO’ command referencing the stage.

Create an external function to read the data from S3 and then insert it into the table, as Snowflake cannot directly read gzipped CSV files from S3.

Grant usage privilege on the storage integration to the role performing the data load. Ensure the user loading data has access to the Snowflake stage and the ‘ INSERT privilege on the table.

Ensure the S3 bucket has public read access; Snowflake’s COPY INTO command will handle decompression and data loading without further configuration.

NO.160 A data analyst is working with a large table partitioned by (DATE type). The table contains millions of rows spanning several years. They need to optimize a query that retrieves sales data for a specific quarter of 2023. The initial query is: ‘SELECT FROM sales_data WHERE EXTRACT(YEAR FROM sale_date) = 2023 AND EXTRACT(QUARTER FROM sale_date) = To improve performance using partition pruning, which of the following queries is the MOST efficient alternative?

NO.161 You need to create a UDF in Snowflake to mask personally identifiable information (PII) in a ‘customers’ table. The UDF should take a string as input (e.g., email address, phone number) and return a masked version. For email addresses, it should replace everything before the ‘@’ symbol with ‘ For phone numbers (assuming a simple format like ‘123-456-7890’), it should mask all but the last four digits with ‘X’. Which of the following UDF definitions is MOST efficient and correctly implements this logic using Snowflake’s JavaScript UDF capabilities and appropriate error handling?

Option A

Option B

Option C

Option D

Option E

NO.162 A financial institution is migrating its transactional data warehouse to Snowflake. They need to optimize query performance for daily reporting on customer spending habits. The current data model is a highly normalized relational model with numerous joins across multiple tables. The reporting requirements include frequent aggregations and filtering on customer demographics, transaction types, and date ranges. Which data modeling approach would be MOST effective in this scenario, considering Snowflake’s architecture and the need for performant reporting?

Maintain the existing highly normalized model and rely solely on Snowflake’s query optimizer to handle the joins.

Denormalize the data into a single, wide fact table containing all necessary attributes to avoid joins during reporting.

Implement a star schema with a central fact table for transactions and dimension tables for customer demographics, transaction types, and dates.

Use a third normal form (3NF) data model to minimize data redundancy and ensure data integrity.

Create a snowflake schema with several layers of normalized dimensions to reduce storage costs.