Snowflake COF-C02 - SnowPro Core Certification Exam
When should a stored procedure be created with caller ' s rights?
When the caller needs to be prevented from viewing the source code of the stored procedure
When the caller needs to run a statement that could not execute outside of the stored procedure
When the stored procedure needs to run with the privileges of the role that called the stored procedure
When the stored procedure needs to operate on objects that the caller does not have privileges on
The Answer Is:
CExplanation:
Stored procedures in Snowflake can be created with either ' owner ' s rights ' or ' caller ' s rights ' . A stored procedure created with caller ' s rights executes with the privileges of the role that calls the procedure, not the privileges of the role that owns the procedure. This is particularly useful in scenarios where the procedure needs to perform operations that depend on the caller ' s access permissions, ensuring that the procedure can only access objects that the caller is authorized to access.
What MINIMUM permissions are required to create a pipe in Snowflake? (Select TWO).
CREATE PIPE at the schema level
SELECT and INSERT on the table in the pipe definition
CREATE SCHEMA at the database level
USAGE on the virtual warehouse
OWNERSHIP on the table in the pipe definition
The Answer Is:
A, BWhich statements reflect valid commands when using secondary roles? (Select TWO).
Use SECONDARY ROLES RESUME
USE SECONDARY ROLES SUSPEND
USE SECONDARY RLES ALL
USE SECONDARY ROLES ADD < Role Name >
Use SECONDARY ROLES NONE
The Answer Is:
C, EExplanation:
USE SECONDARY ROLES ALL: Activates all granted secondary roles for the current session.
USE SECONDARY ROLES NONE: Deactivates all active secondary roles for the current session.
Incorrect Commands: The options referencing " RESUME " , " SUSPEND " , and " ADD " are not valid commands in the context of secondary roles.
What is the benefit of using the STRIP_OUTER_ARRAY parameter with the COPY INTO < table > command when loading data from a JSON file into a table?
It flattens multiple arrays into a single array.
It removes the outer array structure and loads separate rows of data
It transforms a pivoted table into an array.
It tokenizes each data string using the defined delimiters.
The Answer Is:
BExplanation:
The STRIP_OUTER_ARRAY parameter in the COPY INTO < table > command is used when loading data from a JSON file into a table. This parameter removes the outer array structure from the JSON data and loads separate rows of data into the table.
Understanding the STRIP_OUTER_ARRAY Parameter:
JSON files often contain data in an array format where multiple records are nested within a single outer array.
The STRIP_OUTER_ARRAY parameter helps in simplifying the loading process by removing this outer array, allowing each element within the array to be loaded as a separate row in the target table.
How It Works:
When the STRIP_OUTER_ARRAY parameter is set to TRUE, Snowflake treats each item within the outer array as an individual record.
This eliminates the need for additional parsing or transformation steps that would otherwise be required to handle nested arrays.
Example Usage:
FROM @my_stage/file.json
FILE_FORMAT = (TYPE = ' JSON ' STRIP_OUTER_ARRAY = TRUE);
In this example, the JSON file containing an array of objects is loaded into the tablemy_table.
Each object within the array is loaded as a separate row, without the outer array structure.
Benefits:
Simplifies data loading: By removing the outer array, the data is directly loaded into the table without additional manipulation.
Enhances performance: Streamlines the loading process, reducing the complexity and potential errors in handling nested JSON structures.
Snowflake Documentation: COPY INTO < table >
Snowflake Documentation: JSON File Format Options
Which feature of Snowflake ' s Continuous Data Protection (CDP) has associated costs?
Fail-safe
Network policies
End-to-end encryption
Multi-Factor Authentication (MFA)
The Answer Is:
AExplanation:
Snowflake ' s Continuous Data Protection (CDP) features encompass several mechanisms designed to protect data and ensure its availability and recoverability. Among these features, the one that has associated costs is Fail-safe.
Fail-safe is an additional layer of protection that kicks in after the Time Travel period expires. While Time Travel allows users to access historical data within a defined retention period (which can vary from 1 to 90 days depending on the Snowflake edition), Fail-safe provides a further 7 days (for a total of 7 additional days beyond the Time Travel period)during which Snowflake retains the data. This period is primarily intended for Snowflake ' s internal operations to recover data in the event of extreme scenarios, such as significant operational failures, and is not directly accessible by customers for data recovery purposes.
The associated costs with Fail-safe arise because Snowflake continues to store the data beyond the customer-specified Time Travel period, thereby incurring additional storage costs. It ' s important to note that while users do not incur direct costs for enabling Fail-safe (as it is an automatic feature of Snowflake), the extended storage of data during this period contributes to overall storage costs.
Which task is supported by the use of Access History in Snowflake?
Data backups
Cost monitoring
Compliance auditing
Performance optimization
The Answer Is:
CExplanation:
Access History in Snowflake is primarily utilized for compliance auditing. The Access History feature provides detailed logs that track data access and modifications, includingqueries that read from or write to database objects. This information is crucial for organizations to meet regulatory requirements and to perform audits related to data access and usage.
Role of Access History:Access History logs are designed to help organizations understand who accessed what data and when. This is particularly important for compliance with various regulations that require detailed auditing capabilities.
How Access History Supports Compliance Auditing:
By providing a detailed log of access events, organizations can trace data access patterns, identify unauthorized access, and ensure that data handling complies with relevant data protection laws and regulations.
Access History can be queried to extract specific events, users, time frames, and accessed objects, making it an invaluable tool for compliance officers and auditors.
What is the purpose of the use of the VALIDATE command?
To view any queries that encountered an error
To verify that a SELECT query will run without error
To prevent a put statement from running if an error occurs
To see all errors from a previously run COPY INTO < table > statement
The Answer Is:
DExplanation:
TheVALIDATEcommand in Snowflake is used to check for errors that occurred during the execution of aCOPY INTO < table > statement. This command helps users identify and resolve data loading issues.
Run the COPY INTO Statement:Execute theCOPY INTO < table > command to load data from a stage into a table.
COPY INTO my_table
FROM @my_stage
FILE_FORMAT = (FORMAT_NAME = ' my_format ' );
Validate the Load:Use theVALIDATEfunction to see if there were any errors during the data load.
SELECT *
FROM TABLE(VALIDATE(my_table, JOB_ID = > ' my_copy_job_id ' ));
Review Errors:TheVALIDATEfunction will return details about any errors that occurred, such as parsing errors or data type mismatches.
