Snowflake DEA-C02 real exam prep : SnowPro Advanced: Data Engineer (DEA-C02)

You are developing a JavaScript UDF in Snowflake to perform complex data validation on incoming data'. The UDF needs to validate multiple fields against different criteria, including checking for null values, data type validation, and range checks. Furthermore, you need to return a JSON object containing the validation results for each field, indicating whether each field is valid or not and providing an error message if invalid. Which approach is the MOST efficient and maintainable way to structure your JavaScript UDF to achieve this?

• A. Define a JavaScript object containing validation rules and corresponding validation functions. Iterate through the object and apply the rules to the input data, collecting the validation results in a JSON object. This object is returned as a string.
• B. Use a single, monolithic JavaScript function with nested if-else statements to handle all validation logic. Return a JSON string containing the validation results.
• C. Create separate JavaScript functions for each validation check (e.g., 'isNull', 'isValidType', 'isWithinRange'). Call these functions from the main UDF and aggregate the results into a JSON object.
• D. Directly embed SQL queries within the JavaScript UDF to perform data validation checks using Snowflake's built-in functions. Return a JSON string containing the validation results.
• E. Utilize a JavaScript library like Lodash or Underscore.js within the UDF to perform data manipulation and validation. Return a JSON string containing the validation results.

You are tasked with loading Parquet files into Snowflake from an AWS S3 bucket. The Parquet files are compressed using Snappy compression and contain a complex nested schem a. Some of the columns contain timestamps with nanosecond precision. You want to create a Snowflake table that preserves the timestamp precision. Which COPY INTO statement options and table definition are MOST appropriate?

• A. Table Definition: CREATE TABLE my_table (ts VARCHAR, other_col VARCHAR); COPY INTO my_table FROM FILE FORMAT = (TYPE = PARQUET COMPRESSION = SNAPPY) ON_ERROR = 'SKIP_FILE' = PARSE TIMESTAMP(ts));
• B. Table Definition: CREATE TABLE my_table (ts TIMESTAMP NTZ(9), other_col VARCHAR); COPY INTO my_table FROM FILE FORMAT = (TYPE = PARQUET COMPRESSION = SNAPPY) ON_ERROR = 'SKIP_FILE';
• C. Table Definition: CREATE TABLE my_table (ts TIMESTAMP NTZ, other_col VARCHAR); COPY INTO my_table FROM FILE FORMAT = (TYPE = PARQUET COMPRESSION = SNAPPY) ON_ERROR = 'SKIP_FILE';
• D. Table Definition: CREATE TABLE my_table (ts TIMESTAMP NTZ(9), other_col VARCHAR); COPY INTO my_table FROM FILE FORMAT = (TYPE = PARQUET COMPRESSION = SNAPPY) ON_ERROR = 'SKIP_FILE' VALIDATION_MODE = RETURN_ERRORS;
• E. Table Definition: CREATE TABLE my_table (ts TIMESTAMP NTZ(9), other_col VARCHAR); COPY INTO my_table FROM FILE FORMAT = (TYPE - - pARQUET COMPRESSION = AUTO) ON_ERROR = 'SKIP_FILE';

You are designing a data recovery strategy for a critical table 'CUSTOMER DATA' in your Snowflake environment. The data in this table is highly sensitive, and regulatory requirements mandate a retention period of at least 90 days for potential audits. You need to configure the Time Travel retention period to meet these requirements. What is the maximum supported Time Travel retention period, and how would you set it at the table level?

• A. The maximum retention period is 90 days. You can set it using: 'ALTER TABLE CUSTOMER_DATA SET = 90;'
• B. The maximum retention period is 90 days for Enterprise Edition or higher. You can set it using: 'ALTER TABLE CUSTOMER DATA SET DATA RETENTION TIME IN DAYS = 90;'
• C. The maximum retention period is 365 days. You can set it using: ALTER TABLE CUSTOMER DATA SET DATA RETENTION TIME IN DAYS = 365;'
• D. The maximum retention period depends on your Snowflake edition and can be set at the account level only.
• E. The maximum retention period is 7 days. You can set it using: 'ALTER TABLE CUSTOMER_DATA SET = 7;'

You are a data engineer responsible for data governance in a Snowflake environment. Your company has implemented data classification using tags to identify sensitive data'. The compliance team has requested a report detailing all tables and columns that contain PII data, specifically including the tag name, tag value, the fully qualified name of the table, and the column name. You have the necessary privileges to access the Snowflake metadata views. Which of the following queries would provide the MOST comprehensive and accurate report, considering performance and ease of understanding?

• A.
  
• B.
  
• C.
  
• D.
  
• E.
  

You are tasked with creating a Snowpark Java stored procedure to calculate a complex, custom rolling average for a time series dataset. This rolling average requires access to external libraries for statistical calculations. Which of the following steps are necessary to successfully deploy and execute this stored procedure?

• A. All of the above.
• B. Grant the necessary privileges on the stage and the database to the role executing the stored procedure.
• C. Create a stored procedure in Snowflake, specifying the fully qualified path to the JAR file in the stage, the handler class, and the return type.
• D. Package the Java code and all necessary external libraries into a single JAR file.
• E. Upload the JAR file to a Snowflake stage.