Free Snowflake SnowPro Advanced: Data Engineer (DEA-C02) Sample Questions

Correct answers: A, B, C — Schemas; Tables; External Functions

• A. Correct. The `USAGE` privilege on a schema can be granted to a share. This allows the consumer to access all the shareable objects (like tables and views) within that schema, providing a convenient way to share a group of related objects.
• B. Correct. The `SELECT` privilege on a table can be granted to a share. This is a fundamental capability of Snowflake Data Sharing, allowing consumer accounts to query data directly from the provider's tables without copying the data.
• C. Correct. The `USAGE` privilege on an external function can be granted to a share. This enables consumer accounts to call the provider's external functions in their own queries, extending the functionality available to them.
• D. Incorrect. Warehouses are compute resources and are account-specific; they cannot be shared. Consumers of a share must use their own virtual warehouses to query the shared data, which is a key architectural principle of Snowflake's separation of storage and compute.
• E. Incorrect. Users are account-level objects and cannot be granted to a share. Data sharing operates at the account-to-account level. The provider shares objects with a consumer account, and the consumer's administrator grants access to their own users and roles.

Correct answer: C — `ABORT_STATEMENT` - The entire load operation is aborted and rolled back.

• A. Incorrect. `CONTINUE` is a non-default behavior that must be explicitly specified. It instructs the COPY command to skip only the erroneous rows and continue loading valid data from the rest of the file.
• B. Incorrect. `SKIP_FILE` is a non-default behavior. When specified, it instructs the COPY command to stop processing the current file upon encountering an error and move on to the next file in the list to be loaded.
• C. Correct. According to Snowflake documentation, `ABORT_STATEMENT` is the default behavior for the `ON_ERROR` copy option. When the first error is encountered in any file, the entire COPY operation is halted, and any data loaded during that specific command's execution is rolled back to ensure transactional integrity.
• D. Incorrect. `SKIP_FILE_<number>%` is a non-default option. It must be explicitly set to skip a file if the percentage of error rows within that file exceeds a specified threshold. It is not the default behavior.

Correct answer: C — MATCH_BY_COLUMN_NAME = 'CASE_INSENSITIVE'

• A. Incorrect. The `ON_ERROR = 'CONTINUE'` option instructs the COPY command to continue loading data when it encounters errors in data files, skipping the rows that contain errors. It does not address schema mismatches like extra columns in the source file.
• B. Incorrect. The `TRUNCATECOLUMNS = TRUE` option specifies whether to automatically truncate string values that are longer than the defined length of the target column. It does not handle the scenario of ignoring extra columns present in the source file.
• C. Correct. The `MATCH_BY_COLUMN_NAME` option is specifically designed for loading semi-structured data (like ORC, Parquet, or AVRO) by matching column names between the source file and the target table. When this option is enabled, any column present in the source file that does not have a matching column name in the target table is simply ignored, preventing the load from failing. This is the exact behavior required by the scenario.
• D. Incorrect. `ENFORCE_LENGTH = FALSE` is a deprecated option that is functionally equivalent to `TRUNCATECOLUMNS = TRUE`. It deals with truncating data values that are too long for the target column, not with ignoring extra source columns.

Correct answer: B — A `SELECT` transformation with LATERAL FLATTEN

• A. Incorrect. The `MATCH_BY_COLUMN_NAME` copy option is used to load data from staged files into target table columns that have the same name. It does not provide the capability to perform complex transformations such as extracting specific fields, casting data types, or flattening nested arrays during the load process.
• B. Correct. Using a `SELECT` statement within the `COPY INTO` command enables powerful in-flight transformations. This approach allows you to query the staged semi-structured data, extract specific elements (e.g., `$1:event_id`), cast them to the desired data type, and use functions like `LATERAL FLATTEN` to unnest arrays into separate rows, fulfilling all the requirements of the question.
• C. Incorrect. `STRIP_NULL_VALUES` is a file format option used during parsing to remove object key-value pairs where the value is null. It does not perform the required transformations like extracting specific elements, casting data types, or flattening arrays.
• D. Incorrect. `ON_ERROR` is a copy option that defines how the `COPY` command should behave when it encounters errors in the data files. It controls the load operation's error handling (e.g., 'CONTINUE', 'SKIP_FILE', 'ABORT_STATEMENT') and is unrelated to data transformation.

Correct answers: B, D — `authenticator` set to `'externalbrowser'`; `user`

• A. Incorrect. The `password` parameter is explicitly forbidden by the security requirement to not store passwords in code. Furthermore, the browser-based authentication flow delegates the password entry (if any) and MFA challenge to the browser, so it is not provided in the connection string.
• B. Correct. The `authenticator` parameter set to `'externalbrowser'` is the specific configuration required to instruct the Snowflake connector to initiate a browser-based authentication flow. This allows for interactive login and handles the MFA challenge as mandated by the security team.
• C. Incorrect. The `private_key` parameter is used for Key Pair Authentication, which is a separate, non-interactive authentication method. It is not used for the interactive, browser-based MFA flow described in the scenario.
• D. Correct. The `user` parameter is a fundamental and required parameter for establishing a connection. Snowflake needs to know which user account is attempting to authenticate before it can initiate the browser-based flow for that specific user. Without it, the connector does not know who to authenticate.
• E. Incorrect. The `token` parameter is used for other authentication methods like OAuth, where a pre-generated access token is passed to Snowflake. In the `externalbrowser` flow, tokens may be used internally during the authentication process, but they are not provided by the developer in the initial connection parameters.

Correct answer: D — The source object must have change tracking enabled, which is the default for standard tables.

• A. Incorrect. A primary key is not a prerequisite for creating a stream. Snowflake streams track changes to individual rows using internal metadata, independent of any user-defined primary or unique keys.
• B. Incorrect. In Snowflake, streams are the objects used to implement Change Data Capture (CDC). You do not explicitly enable a separate 'CDC' feature on a table before creating a stream. The underlying mechanism is called 'change tracking'.
• C. Incorrect. The ability to create and use streams is a standard feature available in all Snowflake editions, including the Standard edition. It is not restricted to Enterprise edition or higher.
• D. Correct. The fundamental prerequisite for a stream to function is that the source object must have change tracking enabled. The `CHANGE_TRACKING` table parameter, which defaults to `TRUE` for standard tables, enables this functionality. If this parameter is explicitly set to `FALSE`, a stream cannot be created on that object.

Correct answers: B, E — Creating a Storage Integration object in Snowflake that references a secure authentication mechanism like an IAM Role (for AWS) or a Service Principal (for Azure).; Creating a named Stage object that references a pre-configured Storage Integration.

• A. Incorrect. Embedding secret keys directly in a `CREATE STAGE` command is strongly discouraged as it exposes sensitive credentials in plain text within DDL statements and query history, posing a significant security risk.
• B. Correct. This is the primary recommended practice for securely accessing private cloud storage. A Storage Integration is a first-class Snowflake object that encapsulates authentication details (like an AWS IAM Role ARN or Azure Service Principal) and establishes a secure trust relationship, avoiding the need to store and manage explicit access keys within Snowflake.
• C. Incorrect. Secure views are a data governance feature used to control access to data within tables and views inside Snowflake. They are not designed for or capable of managing external credentials for stages.
• D. Incorrect. Key Pair authentication is a mechanism for clients (e.g., users, applications) to authenticate *to* the Snowflake service. It is not used for Snowflake to authenticate *to* external cloud storage providers.
• E. Correct. This is the second part of the recommended workflow. After a Storage Integration is created (as described in option B), a named stage is created that references this integration. This decouples the stage definition from the credential management, allowing for secure, centralized, and reusable access to cloud storage.

Correct answers: B, C — `ACCOUNT_USAGE.QUERY_HISTORY`; `ACCOUNT_USAGE.LOAD_HISTORY`

• A. Incorrect. The `INFORMATION_SCHEMA` views, including `COPY_HISTORY`, have a data retention period of only 14 days. This view is therefore unsuitable for troubleshooting issues that occurred more than 14 days ago.
• B. Correct. All `COPY INTO` statements are executed as queries within Snowflake. The `ACCOUNT_USAGE.QUERY_HISTORY` view retains query data for 365 days, including the full SQL text, execution status, and any error messages. This makes it a valuable resource for investigating the details of failed load jobs from months prior.
• C. Correct. The `ACCOUNT_USAGE.LOAD_HISTORY` view is specifically designed to track the history of data loaded into tables using the `COPY INTO` command. It retains data for 365 days and includes details on file names, load status, and errors, making it the primary tool for analyzing historical data loading activity and failures.
• D. Incorrect. The `SYSTEM$PIPE_STATUS` function is used to check the *current* operational status of a Snowpipe, such as the number of pending files. It does not provide a historical log of past data loading successes or failures.
• E. Incorrect. The `INFORMATION_SCHEMA.TABLE_STORAGE_METRICS` view provides information about a table's storage consumption (e.g., active bytes, time-travel bytes). It contains no information about data loading operations or their outcomes.

Correct answer: B — The command will fail because a `NOT NULL` constraint is violated.

• A. Incorrect. The load will not succeed. The `ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE` setting directs Snowflake to populate any unmatched target columns with `NULL`. However, this will fail because the 11th and 12th columns are explicitly defined with a `NOT NULL` constraint.
• B. Correct. The option `ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE` allows the `COPY` command to attempt loading data despite the differing column counts. Snowflake will try to load the 10 columns from the file and then insert `NULL` into the remaining 11th and 12th columns of the table. Because these target columns have a `NOT NULL` constraint and no `DEFAULT` value, this insertion attempt violates the constraint, causing the entire command to fail.
• C. Incorrect. The command will not succeed. A `NOT NULL` constraint violation is a fatal error for a load operation and will prevent any rows from being committed.
• D. Incorrect. The command will fail, but not with a 'column count mismatch error.' That specific error is suppressed by the `ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE` setting. The actual error will be a `NOT NULL` constraint violation.

Correct answers: A, B, D — A primary key must be defined upon table creation.; Foreign key constraints are strictly enforced.; Unique constraints are strictly enforced.

• A. Hybrid tables require a primary key to be defined at the time of table creation. This key is used to uniquely identify rows and is essential for the underlying row-store engine to handle transactional operations like point lookups and updates.
• B. In a departure from standard Snowflake tables (where constraints are informational only), Hybrid tables strictly enforce foreign key constraints to ensure referential integrity between tables.
• C. Hybrid tables do not support the CLUSTER BY clause. Instead of clustering keys, they utilize the primary key for organization and support secondary indexes for further performance optimization.
• D. Unique constraints are strictly enforced in Hybrid tables at runtime. This allows data engineers to ensure that column data remains unique, providing the integrity guarantees required for transactional workloads.
• E. Hybrid tables do not currently support temporary or transient table types; they are created as permanent tables to ensure the persistence and consistency required for Unistore workloads.
• F. While it is a current characteristic that Hybrid tables do not support Snowflake Secure Data Sharing, the question specifically highlights the requirements for enforcing data integrity and optimizing performance, making the constraint enforcement (A, B, D) the most relevant set of characteristics.

Correct answer: B — Increase the size of the virtual warehouse (e.g., from Medium to Large).

• A. Incorrect. While using Common Table Expressions (CTEs) can improve query readability and logical organization, it does not directly address the underlying issue of data spilling to local storage. Spilling is a memory management problem, not a logical complexity problem, so CTEs are not the appropriate solution.
• B. Correct. 'Bytes spilled to local storage' is a clear indicator that the query execution process lacks sufficient memory to hold intermediate results for operations like joins and aggregations. Increasing the virtual warehouse size (scaling up) provides each worker node with more memory, directly addressing the root cause of the spilling. This is the most appropriate and straightforward first step to resolve this specific performance bottleneck.
• C. Incorrect. Setting the `STATEMENT_TIMEOUT_IN_SECONDS` parameter to a lower value is a control mechanism, not a performance optimization. It would simply cause the long-running query to fail faster, without fixing the underlying memory spilling issue that is causing the poor performance.
• D. Incorrect. Adding clustering keys is an excellent optimization for improving data pruning and reducing I/O during table scans. However, it does not directly solve the problem of memory spilling within Join and Aggregate operators once the data has already been read. While better pruning could potentially reduce the amount of data processed and thus indirectly reduce memory pressure, the most direct solution for an active memory spill is to increase the available memory by scaling up the warehouse.

Correct answer: B — SELECT query_id, total_elapsed_time FROM snowflake.account_usage.query_history WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP()) ORDER BY total_elapsed_time DESC LIMIT 10;

• A. This query is incorrect because it lacks the required 30-day time filter. Furthermore, it only considers `credits_used_cloud_services`, which typically represents a small fraction of the total query cost and ignores the primary cost driver: warehouse compute credits.
• B. This is the correct answer. It properly uses the `snowflake.account_usage.query_history` view for an account-wide, historical perspective and correctly filters for the last 30 days. While `TOTAL_ELAPSED_TIME` is not a direct measure of cost (as it includes queueing, compilation, and execution time), it is the best available proxy among the choices for identifying long-running and resource-intensive queries that are likely to be expensive. The other options are fundamentally flawed.
• C. This query is incorrect for multiple reasons. It uses `information_schema.query_history`, which is not suitable for an account-wide, 30-day analysis due to its limited data retention (maximum 7 days) and scope. It also lacks a date filter and uses a non-existent placeholder `<credits_per_second>` for its cost calculation.
• D. This query is syntactically incorrect and would fail. The column `warehouse_total_elapsed_time` does not exist in the `snowflake.account_usage.query_history` view. While measuring warehouse execution time is the right concept for determining cost, this query attempts to do so by referencing a non-existent column in the specified view.

Correct answer: C — To trigger a cloud messaging notification (e.g., AWS SNS, Azure Event Grid) when the task run enters a `FAILED` state.

• A. Incorrect. The `ERROR_INTEGRATION` parameter is not used for specifying a custom error logging table. Task execution history and failures can be monitored using Snowflake's built-in functions like `TASK_HISTORY`.
• B. Incorrect. This parameter does not control the automatic retry behavior of a task. While tasks may have some built-in retry logic, it is not configured or enabled through the `ERROR_INTEGRATION` setting.
• C. Correct. The `ERROR_INTEGRATION` parameter specifies the name of a pre-configured notification integration. Snowflake uses this integration to send a notification to a cloud messaging service (like AWS SNS, Azure Event Grid, or Google Pub/Sub) when a task run enters a `FAILED` or `CANCELLED` state. This is a crucial feature for monitoring data pipelines and triggering external alerting or remediation workflows.
• D. Incorrect. While a notification sent via the error integration could potentially trigger a process in a third-party data quality tool, the parameter's direct purpose is not to integrate with such tools. It specifically integrates with cloud provider messaging services for notifications.

Correct answer: C — CREATE TASK check_control_task ... SCHEDULE = '5 minute' AS SELECT COUNT(*) FROM PIPELINE_CONTROL WHERE status = 'READY'; CREATE TASK root_task AFTER check_control_task WHEN SYSTEM$GET_PREDECESSOR_RETURN_VALUE('check_control_task') > 0 AS ...;

• A. This option is incorrect. The `IF` statement is placed within the task's SQL body (`AS ...`), not in the task's execution condition. This means the task itself will still start on its schedule and consume compute resources, even if the condition evaluates to false. The goal is to prevent the task from even starting if the condition is not met.
• B. This option is incorrect because the `WHEN` clause in a Snowflake task definition does not support subqueries. Attempting to create a task with this syntax will result in a compilation error.
• C. This is the correct pattern for implementing conditional logic based on a query result. A predecessor task (`check_control_task`) runs the query, and its single-row, single-column result is stored. The successor task (`root_task`) then uses the `SYSTEM$GET_PREDECESSOR_RETURN_VALUE` function within its `WHEN` clause to evaluate the result and determine if it should run.
• D. This option is incorrect because `SYSTEM$STREAM_HAS_DATA` only indicates that DML changes (INSERT, UPDATE, DELETE) have occurred on the table since the stream's last offset was consumed. It does not guarantee that the current state of the table meets the specific condition (i.e., that a record with status='READY' exists). The stream having data does not equate to the control table being in the desired state.

Correct answers: B, C — Create a clustering key on (`ORDER_DATE`, `CUSTOMER_ID`).; Enable the Search Optimization Service on the `CUSTOMER_ID` column.

• A. Query Acceleration Service is designed to handle unpredictable spikes in query workloads by offloading parts of query processing, but it does not specifically optimize the given consistent query patterns (aggregation by date and point lookups). It is not a targeted solution for these patterns, so it is not part of the comprehensive solution.
• B. Creating a clustering key on (`ORDER_DATE`, `CUSTOMER_ID`) is a recommended practice for large tables where queries frequently filter on these columns. It co-locates related data, enabling efficient partition pruning: for dashboard queries, the leading `ORDER_DATE` allows skipping partitions outside the date range; for ad-hoc queries, clustering reduces the data scanned within the date range. Research confirms this is an effective strategy for such workloads.
• C. Enabling Search Optimization on `CUSTOMER_ID` accelerates point lookups on high-cardinality columns. For ad-hoc queries that retrieve details for a specific `CUSTOMER_ID` within a date range, this service provides fast access paths, complementing the clustering key's date-based pruning. Research notes it as a complementary solution when the leading clustering key is not the point-lookup column.
• D. Materialized views are not recommended for frequently updated base tables because the automatic background maintenance incurs significant compute costs proportional to data changes. While they can accelerate aggregation queries, the high update frequency of this table makes them cost-inefficient and contrary to official guidance.
• E. Snowpark-optimized warehouses are designed specifically for executing Snowpark UDFs and are not relevant to standard SQL query optimization. The described workload does not involve Snowpark operations, so this option does not address the performance requirements.

Correct answers: B, D, E — Choose columns that are most frequently used in range or equality filtering predicates in `WHERE` clauses.; Order the columns in the clustering key from lowest cardinality to highest cardinality.; Ensure the total number of distinct values in the clustering key expression is large enough to enable effective pruning.

• A. Incorrect. This is an anti-pattern. Columns with extremely high cardinality, like a UUID or a nanosecond-level timestamp, have too many distinct values. This prevents Snowflake from grouping meaningful ranges of data into the same micro-partitions, rendering pruning ineffective and increasing the maintenance cost of clustering.
• B. Correct. This is the most fundamental best practice. The primary goal of clustering is to improve query performance by enabling effective partition pruning. By clustering on columns frequently used in `WHERE` clause filters (e.g., `WHERE date = '2023-10-26'` or `WHERE customer_id BETWEEN 100 AND 200`), Snowflake can quickly identify and scan only the necessary micro-partitions, avoiding a full table scan.
• C. Incorrect. If a column has very low cardinality in a large table, clustering on it provides minimal pruning benefit. For example, clustering a billion-row table on a boolean column would mean that a query filtering on `TRUE` would still have to scan roughly half of the table's micro-partitions. The column's cardinality should be large enough to allow for effective data segmentation.
• D. Correct. This is a critical best practice for multi-column clustering keys. Ordering the columns from the lowest cardinality (most general) to the highest cardinality (most specific) creates a more logical and efficient grouping of data. This allows Snowflake to prune more effectively, especially on queries that only filter on the leading columns of the clustering key. For example, clustering on `(date, customer_id)` is generally better than `(customer_id, date)`.
• E. Correct. This is the converse of the incorrect advice in option C. The clustering key, as a whole, should have a sufficient number of distinct values to divide the table data into a meaningful number of distinct groups. This allows the micro-partition metadata to be granular enough for the query optimizer to perform effective pruning and avoid scanning unnecessary data.

Correct answer: B — The `clustering_depth` will likely increase, and the `constant_partition_count` (partitions with only deleted rows) will also increase.

• A. Incorrect. While it is true that the `total_partition_count` remains the same because `DELETE` operations don't remove micro-partitions, option B provides a more complete description of the changes. The degradation of clustering health is primarily measured by changes in depth and partition overlap, which B describes more accurately.
• B. Correct. A `DELETE` operation marks rows as deleted but leaves the micro-partitions in place, making the data within them sparse. This degradation of clustering health is reflected by an increase in the reported `clustering_depth`. Additionally, if deleted rows happen to be at the boundaries of a micro-partition's key range, the effective range of active data can shrink. This could eliminate a previous overlap with a neighboring partition, causing an `overlap_partition` to become a `constant_partition` (non-overlapping partition) and thereby increasing the `constant_partition_count`.
• C. Incorrect. A `DELETE` operation degrades, not improves, clustering, so the `clustering_depth` would increase rather than decrease. Furthermore, since `DELETE` does not remove micro-partitions, the `total_partition_count` would remain unchanged, not be halved.
• D. Incorrect. System functions like `SYSTEM$CLUSTERING_INFORMATION` read the table's current metadata and reflect changes immediately. The negative impact of a large `DELETE` on clustering metrics would be visible without needing to recluster the table first.

Correct answer: B — CREATE TEMPORARY TABLE LEDGER_COPY CLONE FINANCE.LEDGER;

• A. Incorrect. A `TRANSIENT` table persists beyond the user's session and must be explicitly dropped. While it does not have a Fail-safe period, it fails to meet the requirement of being automatically dropped when the session ends.
• B. Correct. A `TEMPORARY` table is visible only within the session that created it and is automatically dropped at the end of the session. It has no Fail-safe period, making it the perfect choice for short-lived, isolated testing without incurring long-term storage or data protection costs.
• C. Incorrect. `VOLATILE` is not a valid table type in Snowflake SQL. This command would result in a syntax error. While other database systems use this keyword, it is not part of Snowflake's syntax.
• D. Incorrect. This command creates a `PERMANENT` table by default. Permanent tables have both Time Travel and a 7-day Fail-safe period, and they persist indefinitely until explicitly dropped. This violates all the specific requirements of the analyst.

Correct answer: B — The failover will be automatically blocked by Snowflake until replication is resumed and the secondary is fully synchronized.

• A. This is incorrect. A primary goal of Snowflake's replication and failover feature is to prevent data loss. The system will not allow a failover to proceed to a secondary database that is known to be out of sync, as this would violate data consistency guarantees.
• B. This is the correct outcome. Snowflake's failover mechanism includes a crucial safety check to ensure data integrity. If replication is suspended for any database within the failover group, the secondary is considered stale. The `ALTER FAILOVER GROUP ... PRIMARY` command will be blocked and will not complete until replication is manually resumed and the secondary database is fully synchronized with the primary.
• C. This is incorrect. Snowflake does not automatically resume replication or perform a final synchronization if the schedule has been explicitly suspended by an administrator. The failover operation itself will be blocked and will not succeed until the administrator takes action to resume replication.
• D. This is incorrect. The command does not immediately fail with a specific error message. Instead, the operation is blocked or appears to hang. It waits for the condition preventing the failover—the unsynchronized state of the secondary—to be resolved before it can proceed.

Correct answer: A — The stream will appear empty, as the changes are not yet committed.

• A. According to Snowflake documentation, streams only reflect changes that have been committed to the source table. Since the MERGE is inside an explicit transaction and not yet committed, those changes are not visible. The stream’s offset remains unchanged, and it shows only committed changes from transactions completed before the current transaction began. If no other committed changes exist since the stream’s offset, the stream will indeed appear empty. This is part of Snowflake’s ACID-compliant transaction model and repeatable read isolation.
• B. Incorrect. Streams do not expose uncommitted data or any metadata indicating that a transaction is still open. They only report changes that have been committed to the source table, ensuring transactional consistency and isolation.
• C. Incorrect. Snowflake uses multi-version concurrency control; readers do not block writers, and vice versa. Querying a stream is a read operation that sees a consistent snapshot of committed data and will execute immediately without waiting for the ongoing transaction.
• D. Incorrect. Streams do not display old and new values side by side. When an update is committed, the stream generates a pair of change records: a DELETE of the old row and an INSERT of the new row, each with appropriate metadata. However, since the MERGE is not yet committed, no rows from it appear in the stream at all.

Correct answer: D — To allow a masking policy to be applied on a column that is also referenced in a row access policy's `USING` clause without being masked within the row policy evaluation.

• A. Incorrect. This parameter does not grant the policy's owner role special bypass privileges. Its function is specifically to manage the interaction between different types of policies (masking and row access), not to override them based on user roles.
• B. Incorrect. The parameter does not establish precedence for the masking policy. Instead, it facilitates the coexistence of masking and row access policies by controlling how a column's value is handled during the evaluation of a row access policy.
• C. Incorrect. The `EXEMPT_OTHER_POLICIES` parameter is a current and functional feature in Snowflake. It is not deprecated and serves a critical purpose when implementing multiple governance policies on the same data.
• D. Correct. When a column is both masked and used in a row access policy's `USING` clause, the row access policy needs the original, unmasked value to correctly determine row visibility. Setting `EXEMPT_OTHER_POLICIES = TRUE` on the masking policy ensures that the unmasked value is passed to the row access policy for its evaluation, preventing the masking from interfering with the row filtering logic. The masking is then applied to the final result set after the row filtering is complete.

Correct answer: D — Internal dependencies where one object's definition refers to another object by name.

• A. Incorrect. The OBJECT_DEPENDENCIES view tracks dependencies based on object definitions (DDL), not on historical query patterns. Query-based data lineage is found in the ACCESS_HISTORY view.
• B. Incorrect. This view tracks dependencies between objects *within* a Snowflake account. It does not track dependencies on external objects located in cloud storage providers like S3 or Azure Blob Storage.
• C. Incorrect. While a foreign key reference does create a dependency, this view is not limited to them. It tracks a much broader range of dependencies, such as a view referencing a table or a policy attached to an object. Its scope is any reference by name within an object's definition.
• D. Correct. The primary purpose of the SNOWFLAKE.ACCOUNT_USAGE.OBJECT_DEPENDENCIES view is to track internal object relationships where one object's definition (e.g., a view, UDF, or policy) explicitly refers to another object by its name. This is essential for performing impact analysis before altering or dropping objects.

Correct answer: D — Projection Policy

• A. Incorrect. Row Access Policies control which rows users can see by filtering the result set, but they do not govern whether a specific column can be projected (returned) in the query output. For column-level projection control, a different policy type is required.
• B. Incorrect. Snowflake has session parameters that influence the behavior of the current session, but there is no distinct 'Session Policy' object designed to manage column projection or data masking. The correct feature for controlling column projection is a Projection Policy.
• C. Incorrect. No 'Aggregation Policy' exists in Snowflake. Data security is handled through Row Access Policies, Dynamic Data Masking, and Projection Policies, each solving a different aspect of access control.
• D. Correct. A Projection Policy is a schema-level object that defines whether a column can be projected (appear in the final SELECT output). According to Snowflake documentation, it can prevent a column’s values from being returned in query results while still allowing the column to be used in operations like JOINs or aggregations. When used alongside a Dynamic Data Masking policy, it adds an additional layer of protection: if the column is allowed to be projected, the masking policy masks its values; otherwise, the column is not shown at all. This is especially useful when sensitive columns are referenced in JOIN clauses, ensuring they are not inadvertently exposed.

Correct answer: B — A Caller's Rights procedure runs with the privileges of the calling role, while an Owner's Rights procedure runs with the privileges of the procedure's owning role.

• A. Incorrect. The ability to execute a procedure is not determined by its rights type but by privileges granted to a role. Any role with the `USAGE` privilege on a procedure can execute it, regardless of whether it's an Owner's Rights or Caller's Rights procedure. The primary difference is the privilege context under which the procedure's code runs, not who is permitted to call it.
• B. Correct. This statement accurately describes the fundamental difference. An Owner's Rights procedure executes with the privileges of the role that owns the procedure. This is useful for creating routines that perform actions on objects the calling role doesn't have direct access to. Conversely, a Caller's Rights procedure executes with the privileges of the calling role, ensuring the procedure operates strictly within the security boundaries of the user who invokes it.
• C. Incorrect. Both Owner's Rights and Caller's Rights Snowpark stored procedures can execute dynamic SQL using the `session.sql()` method. The ability to perform dynamic SQL is not a distinguishing factor between the two types of procedures.
• D. Incorrect. Transaction management statements like `BEGIN`, `COMMIT`, and `ROLLBACK` are supported within both Owner's Rights and Caller's Rights procedures. The procedure's rights definition does not restrict its ability to control transactions.

Correct answers: A, B, E — Directory tables can be created on both internal and external stages.; A stream can be created on a directory table to track new, updated, and deleted files.; The `FILE_URL` column provides a Snowflake-managed URL to access the file, which requires an active session and privileges.

• A. This statement is correct. Directory tables can be enabled on both named internal stages and external stages, providing a flexible and unified way to catalog and query files regardless of their underlying storage location.
• B. This statement is correct. A stream can be created on a directory table to capture changes (new, updated, or deleted files) in the associated stage. This feature is crucial for building incremental and event-driven data pipelines that process files as they arrive.
• C. This statement is incorrect. Directory table metadata is not updated instantly and automatically by default. For external stages, the metadata must be refreshed either manually by executing `ALTER STAGE ... REFRESH` or automatically by configuring event notifications from the cloud storage provider. For internal stages, a manual refresh is also required.
• D. This statement is incorrect. Like any standard `SELECT` query in Snowflake, querying a directory table requires an active, running virtual warehouse to provide the compute resources needed to execute the query and retrieve the file metadata.
• E. This statement is correct. The `FILE_URL` column contains a scoped URL, which is a temporary, Snowflake-generated URL that provides secure, time-limited access to the file. To use this URL, the user must have an active Snowflake session and possess the necessary privileges on the stage containing the file.

Correct answer: B — Scoped URL

• A. Incorrect. While Snowflake can generate pre-signed URLs using the `GET_PRESIGNED_URL` function, these URLs are primarily time-bound (e.g., valid for 60 minutes). They do not automatically become invalid when the user's Snowflake session ends, which is a key requirement of the question. Therefore, a pre-signed URL does not fully meet the specified security constraints.
• B. Correct. A Scoped URL, generated using the `BUILD_SCOPED_FILE_URL` function, is specifically designed for this use case. It provides temporary, encoded access to a file in an internal stage. Critically, the URL's validity is tied to the user's session; it automatically expires when the Snowflake session that generated it ends, or after 24 hours, whichever comes first. This perfectly aligns with the requirement to invalidate access upon session termination.
• C. Incorrect. A stage file URL, using the format `@mystage/path/to/file.png`, is an identifier used within Snowflake SQL commands. It is not a web-accessible URL that can be embedded in an HTML `<img>` tag and does not have any built-in expiration or session-based security mechanisms for external access.
• D. Incorrect. A static URL from a cloud provider (like S3 or Azure Blob Storage) would only be possible if the file were in an external stage. Furthermore, such a URL would bypass Snowflake's session management and authentication, making the file accessible to anyone with the link and not meeting the requirement that the URL becomes invalid when the user's session ends.

Correct answers: C, D, E — `collect()`; `count()`; `save_as_table()`

• A. Incorrect. The `select()` method is a transformation, not an action. It is used to define the columns to be included in the DataFrame, adding a projection to the query plan without triggering its execution. Query execution is deferred until an action method is called.
• B. Incorrect. The `filter()` method is a transformation. It is used to specify a condition for filtering rows, which modifies the underlying query plan. However, it does not trigger the execution of the plan on the warehouse.
• C. Correct. The `collect()` method is an action. It triggers the execution of the entire compiled query plan on the Snowflake warehouse and retrieves all the resulting data back to the client application as an array of Row objects.
• D. Correct. The `count()` method is an action. To determine the number of rows in the DataFrame, Snowpark must execute the query plan on the Snowflake warehouse (typically as a `COUNT(*)` query) and return the resulting scalar value.
• E. Correct. The `save_as_table()` method is an action. It executes the DataFrame's query plan and materializes the results by writing them into a new or existing Snowflake table. This is analogous to a `CREATE TABLE AS SELECT` (CTAS) statement.

Correct answer: C — In the class constructor (`__init__` method), so it is executed only once per partition.

• A. Incorrect. The `process` method is invoked for every single input row. Placing the initialization logic here, even with a conditional flag, is highly inefficient as it would execute the check on every row and is not the idiomatic way to handle one-time setup.
• B. Incorrect. The `end_partition` method is called once after all rows in a partition have been processed. It is intended for finalization, aggregation, or cleanup logic for that partition, not for initialization.
• C. Correct. The class constructor (`__init__` method) is executed exactly once when an instance of the handler class is created for a partition. This is the designated and most efficient place to perform expensive, one-time setup operations like loading a model, as the initialized state will be available to the `process` method for all rows within that partition.
• D. Incorrect. Using a separate UDF adds unnecessary complexity, overhead, and potential state management issues. The UDTF handler class structure is specifically designed to handle this pattern internally via the constructor, making a separate function redundant and inefficient.

Correct answer: C — Define the Python logic as a lambda function and pass it directly to `snowflake.snowpark.functions.udf` to create an anonymous, temporary UDF.

• A. Incorrect. While it is possible to execute a `CREATE TEMPORARY FUNCTION` statement using `session.sql()`, this is not the idiomatic Snowpark approach. It requires writing the function logic in SQL, which moves away from the Python-centric workflow that Snowpark is designed for.
• B. Incorrect. This is the least efficient method. Creating a permanent UDF involves writing to Snowflake's persistent metadata. This process is slower and requires an explicit `DROP FUNCTION` command at the end of the session, adding unnecessary complexity and risk of orphaned objects.
• C. Correct. This is the most efficient and idiomatic way to handle this requirement in Snowpark. Using a lambda function with `snowflake.snowpark.functions.udf` creates an anonymous, temporary UDF that is automatically scoped to the current session and requires no explicit cleanup. It is perfect for simple, inline calculations during data exploration.
• D. Incorrect. While you can create a temporary UDF from a named Python function using `session.udf.register(my_func, is_permanent=False)`, using a lambda (as in option C) is more idiomatic and concise for a simple, one-off calculation that doesn't warrant a full function definition. For the described use case, the anonymous UDF is the superior choice.

Correct answer: B — `return { "data": [ [0, 0.98], [1, 0.45] ] };`

• A. Incorrect. This format is invalid for two reasons. First, Snowflake requires the response translator to return a JSON object with a single top-level key named `data`. This option returns a raw array. Second, it returns an object `{score: 0.98}` for the value instead of the required scalar value `0.98`.
• B. Correct. This statement adheres to the required format for a response translator UDF. It returns a JSON object with the mandatory `"data"` key. The value of this key is an array of arrays, where each inner array contains the 0-indexed row number followed by the scalar return value for that row, correctly extracted from the remote service's response.
• C. Incorrect. While the JavaScript logic `data.predictions.map(...)` correctly transforms the input into the desired inner structure `[[0, 0.98], [1, 0.45]]`, the `return` statement is wrong. It returns this array directly, omitting the mandatory enclosing `{ "data": ... }` object that Snowflake requires to parse the response.
• D. Incorrect. This statement returns the original `predictions` array from the remote service without applying the necessary transformation. The required Snowflake format must be an array of arrays (containing the row index and the value) nested within a JSON object under the `data` key.

Correct answer: A — `[ 0, "example", 123 ]`

• A. Correct. According to Snowflake documentation, the request payload sent to a remote service contains a `"data"` key, which holds an array of rows. Each row is itself an array where the first element is the 0-based row number, followed by the arguments in the order they were passed to the function. This format `[ 0, "example", 123 ]` perfectly matches the required structure.
• B. Incorrect. The data for each row must be represented as a JSON array, not a JSON object with key-value pairs. Snowflake does not use argument positions as keys in the payload.
• C. Incorrect. The format for a single row is a simple array and does not contain descriptive keys like `"row"` or `"data"` within it. This structure is unnecessarily complex and does not match the Snowflake specification.
• D. Incorrect. The arguments passed to the function should be elements in the main row array, directly following the row number. They should not be nested within a separate, inner array.

Correct answer: C — To produce a lateral view of a VARIANT, OBJECT, or ARRAY, exploding the contained elements into multiple rows.

• A. Incorrect. The FLATTEN function deconstructs semi-structured data. Its purpose is the opposite of converting structured data into a single VARIANT column; it takes a complex type and expands it into a relational format.
• B. Incorrect. The function used to parse a JSON string into a VARIANT data type is PARSE_JSON. FLATTEN operates on data that is already in a VARIANT, OBJECT, or ARRAY format, not on the raw string.
• C. Correct. The FLATTEN table function is specifically designed to produce a lateral view of semi-structured data. It takes a VARIANT, OBJECT, or ARRAY and 'explodes' the individual elements within it into separate rows, allowing for relational querying of nested data.
• D. Incorrect. This describes the functionality of functions like OBJECT_CONSTRUCT or ARRAY_CONSTRUCT, which are used to build nested JSON objects. FLATTEN does the reverse; it deconstructs and flattens these nested structures.

Correct answers: A, C, D — Create separate functional roles for each environment (e.g., `DEV_ENGINEER`, `PROD_ENGINEER`).; Create environment-specific access roles (e.g., `DEV_DB_READ`, `PROD_DB_WRITE`) and grant them to the respective functional roles.; Use a dedicated service account role (e.g., `PROD_DEPLOYER`) for executing automated deployments to the Prod environment, restricting human access.

• A. Creating separate functional roles for each environment enforces isolation and ensures users have access only to the environments they require. This aligns with the principle of least privilege and reduces the risk of accidental cross-environment modifications.
• B. Granting the `SYSADMIN` role to a CI/CD account violates the principle of least privilege. `SYSADMIN` has broad powers over all objects in the account; a service account should instead be granted a custom role with the minimum set of privileges required to perform specific deployment tasks.
• C. Snowflake best practice recommends using a tiered RBAC hierarchy where object-level permissions are granted to 'Access Roles', which are then granted to 'Functional Roles'. Environment-specific access roles ensure granular control over who can read or write in Dev vs Prod.
• D. Restricting manual human access to production and utilizing a dedicated service account role (like `PROD_DEPLOYER`) for CI/CD pipelines enforces strict separation of duties and ensures that production changes follow a controlled, automated, and auditable path.
• E. The `ACCOUNTADMIN` role should be highly restricted and reserved for top-level account tasks. Managing masking policies should be delegated to a specific security-focused role rather than granting full administrative power to a data engineer.
• F. Using a single role for all developers across all environments fails to provide true environment isolation. Row-level security is intended for filtering data rows for specific users, not as a replacement for structural RBAC boundaries between Dev and Prod.

Correct answers: A, B — SELECT PAYLOAD:details.actions[0] FROM RAW_LOGS;; SELECT PAYLOAD:details:actions[0]::STRING FROM RAW_LOGS;

• A. This is a correct query. Snowflake allows path traversal into semi-structured data using dot (`.`) or colon (`:`) notation for object keys and bracket notation (`[]`) for array elements. This query correctly navigates to the first element of the `actions` array and returns it as a `VARIANT` type.
• B. This is a correct and recommended query. It uses valid colon and bracket notation to access the array element. Additionally, it explicitly casts the result to a `STRING` using `::STRING`, which is a best practice for ensuring data type consistency in subsequent operations.
• C. This query is incorrect. The `GET_PATH` function requires bracket notation (e.g., `[0]`) to specify array elements within its path string argument. Using dot notation (`.0`) for an array index is not the correct syntax for this function.
• D. This query is incorrect. The `FLATTEN` function is used to convert semi-structured data into a relational format (rows). Flattening `PAYLOAD:details` would produce rows for the `user` and `actions` keys, but it does not create a column named `actions` that can be directly queried in this manner.

Correct answers: C, E — A vectorized UDF can provide better performance than a scalar UDF due to reduced data serialization and invocation overhead.; Vectorized UDFs allow for the use of libraries like pandas and NumPy that can operate efficiently on arrays of data.

• A. Incorrect. While a vectorized UDF can accept a pandas Series for single‑column input, the official design and recommended approach for multi‑column scenarios is to receive a pandas DataFrame. For a function with multiple input columns like `tenure` and `monthly_charges`, the vectorized UDF handler expects a DataFrame containing those columns.
• B. Incorrect. The `@udf` decorator alone registers a scalar UDF that processes one row at a time. To create a vectorized UDF, you must use the `@vectorized` decorator (or specify `vectorized=True`) so that the handler receives batches as Pandas DataFrames.
• C. Correct. Snowflake documentation states that vectorized UDFs improve performance by processing batches of rows as Pandas DataFrames, which reduces the per‑row serialisation and invocation costs compared to scalar UDFs.
• D. Incorrect. This statement refers to the scalar `@udf` decorator, not to vectorized UDFs. Moreover, `input_types` and `return_type` are optional when the function uses Python type hints; Snowpark can infer them. For vectorized UDFs the proper decorator is `@vectorized` with `input_types` typically set to `pandas.DataFrame`.
• E. Correct. Because vectorized UDFs operate on batches of data as Pandas DataFrames or Series, you can leverage pandas, NumPy, and other array‑optimised libraries inside the UDF, making it particularly effective for machine learning inference on large datasets.