Free Snowflake SnowPro Advanced: Architect (ARA-C01) Sample Questions

Correct answers: A, D — RSA_PUBLIC_KEY; RSA_PUBLIC_KEY_2

• A. Correct. The `RSA_PUBLIC_KEY` property is used to set the primary public key for a user. In the context of non-disruptive key rotation, it is used in the final step to promote the new key (initially set in `RSA_PUBLIC_KEY_2`) to become the primary key, thus completing the rotation cycle. Both `RSA_PUBLIC_KEY` and `RSA_PUBLIC_KEY_2` are essential for the process.
• B. Incorrect. `HAS_RSA_PUBLIC_KEY` is a read-only property visible in the output of the `DESCRIBE USER` command. It indicates whether a user has a public key assigned but is not a parameter that can be set with `ALTER USER` to facilitate the rotation process.
• C. Incorrect. `KEY_ROTATION_DEADLINE` is not a valid property for the `ALTER USER` command in Snowflake. It does not exist in the context of user-level key rotation.
• D. Correct. The `RSA_PUBLIC_KEY_2` property is specifically designed to enable non-disruptive key rotation. It allows a second public key to be assigned to a user, which Snowflake will accept for authentication. This enables applications to start using the new key while the old key (in `RSA_PUBLIC_KEY`) remains active, ensuring zero downtime during the transition.
• E. Incorrect. The `PASSWORD` property is used for password-based authentication and is entirely unrelated to key-pair authentication or the process of rotating RSA keys.

Correct answer: B — Create a new VPCE_ID network rule, add this new rule to the existing network policy's allowed list, and then remove the old IPV4 rule after the migration is complete.

• A. Incorrect. Immediately replacing the existing IP-based rule with the new VPCE_ID rule would instantly revoke access for the current pipeline running on EC2 instances. This would cause an outage before the migration to PrivateLink is complete, violating the no-downtime requirement.
• B. Correct. This approach follows the best practice for a seamless migration. By first adding the new VPCE_ID rule to the existing network policy's allowed list, both the old (IP-based) and new (PrivateLink) connection methods are permitted simultaneously. This ensures the existing pipeline remains operational while the new connection is established and verified. Once the migration is successfully completed, the old IPV4 rule can be safely removed for security hygiene.
• C. Incorrect. A Snowflake account can only have one network policy active at a time. Attempting to apply a second policy would replace the existing one, not supplement it. This would lead to the same downtime issue as option A, as the original IP-based access would be removed.
• D. Incorrect. Detaching the network policy from the account would cause immediate downtime for the existing pipeline, which relies on the policy for access. Furthermore, this action would temporarily expose the Snowflake account to access from any IP address, creating a significant security vulnerability during the migration process.

Correct answers: A, B — A separate account can be provisioned in a Snowflake Government region (e.g., GovCloud) to meet compliance, which is not possible for the Public data.; A separate account allows for completely distinct network policies and user populations, providing stronger isolation than RBAC alone.

• A. Correct. To meet stringent government compliance requirements like FIPS 140-2, the CUI data can be placed in a separate Snowflake account provisioned in a dedicated government region (e.g., AWS GovCloud or Azure Government). This provides a physically and logically isolated environment built to meet such federal regulations, a capability not required for the public data.
• B. Correct. A fundamental benefit of a separate account is complete isolation at the account level. This allows for entirely distinct network policies, user authentication methods (e.g., separate IdPs), and user populations. This level of isolation is much stronger than what can be achieved with Role-Based Access Control (RBAC) and other object-level controls within a single account, directly addressing the requirement for complete isolation and separate network controls.
• C. Incorrect. A single Snowflake account is fully capable of storing different classifications of data. Segregation within a single account can be managed using security features like RBAC, data masking, and row-access policies. However, this does not provide the same level of network and administrative isolation as separate accounts.
• D. Incorrect. Separating data into different accounts is an architectural choice for security, compliance, and governance, not for performance. Query performance is determined by factors like virtual warehouse size, query complexity, and data clustering, which are independent of the account structure.
• E. Incorrect. While using separate accounts does simplify the process of tracking and charging back costs for different business units or data types, it is a secondary benefit. The primary drivers for this scenario are the mandatory security, isolation, and compliance requirements for CUI data.

Correct answers: A, C, E — Account; Warehouse; User

• A. Correct. The account is the highest-level object in the hierarchy where parameters can be set. Account-level parameters serve as the default for all users and sessions within the account unless overridden at a more granular level (e.g., user or session).
• B. Incorrect. Roles are used for role-based access control (RBAC) to manage privileges on securable objects. They are not entities on which session or object parameters can be set.
• C. Correct. A virtual warehouse is a Snowflake object with its own set of parameters that can be configured directly. These parameters, such as WAREHOUSE_SIZE, AUTO_SUSPEND, and STATEMENT_TIMEOUT_IN_SECONDS, control the behavior and cost of the compute resource.
• D. Incorrect. While schemas have properties that can be altered (e.g., DATA_RETENTION_TIME_IN_DAYS, MANAGED_ACCESS), they are not considered a primary level for setting the broad range of session and object parameters that apply at the Account, User, or Warehouse levels.
• E. Correct. Parameters can be set for individual users to define their default session environment. These user-level parameters override any defaults set at the account level and apply to all sessions initiated by that user.

Correct answer: B — Create two separate virtual warehouses: one dedicated to ELT and one for data science, sized appropriately for each workload.

• A. Incorrect. Increasing the `STATEMENT_QUEUED_TIMEOUT_IN_SECONDS` parameter only allows a query to wait longer in the queue before being cancelled. This does not address the root cause of the delay, which is resource contention, and would likely worsen the problem by extending the potential delay for the time-sensitive ELT jobs.
• B. Correct. This is the best practice for workload isolation in Snowflake. Creating separate virtual warehouses provides dedicated compute resources for each workload. The ELT jobs will no longer have to compete with the unpredictable, resource-intensive ML queries, ensuring they have the necessary resources to run on schedule. Each warehouse can be sized and configured optimally for its specific workload.
• C. Incorrect. A resource monitor is a tool for controlling credit consumption, not for managing query performance or priority. Suspending the warehouse would halt all jobs, including the critical ELT processes, which is the opposite of the desired outcome. It does not solve the resource contention issue.
• D. Incorrect. Increasing the `MAX_CONCURRENCY_LEVEL` allows more queries to run simultaneously on a single cluster, but it forces them to share the cluster's resources. For resource-intensive queries, this can lead to slower performance for all running queries and exacerbate the resource contention problem. It does not guarantee priority or dedicated resources for the ELT jobs.

Correct answer: C — Implement a hierarchy where functional roles (e.g., `FR_SALES_TEAM`) own objects, which inherently gives them the ability to grant privileges on those objects to access roles (e.g., `AR_BI_READ`).

• A. Incorrect. This approach centralizes all grant management capabilities within the `SYSADMIN` role, which is the exact opposite of the stated goal to decentralize management. It violates the principle of least privilege by concentrating a highly powerful permission.
• B. Incorrect. Consolidating all object ownership under a single role centralizes control, creating a management bottleneck and concentrating power. This directly contradicts the objective of decentralizing grant management to various object owners.
• C. Correct. This is the recommended best practice for decentralized management in Snowflake. A role that owns an object (i.e., has the `OWNERSHIP` privilege) can grant privileges on that specific object to other roles without needing the global `MANAGE GRANTS` privilege. By assigning ownership to distinct functional roles, you empower those teams to manage access to their own data, achieving decentralization and adhering to the principle of least privilege.
• D. Incorrect. While `FUTURE GRANTS` are useful for automating permissions on newly created objects, the role executing the `GRANT ... ON FUTURE ...` statement must possess the `MANAGE GRANTS` privilege. Therefore, this approach does not eliminate the need for this powerful privilege and does not in itself decentralize the authority to manage grants.

Correct answer: C — The user will see only the subset of rows where `DIAGNOSIS_CODE` starts with 'C', and for those visible rows, the `PATIENT_NAME` column will be masked.

• A. Incorrect. Snowflake is designed to allow multiple security features to work together. It fully supports applying both row access policies and dynamic data masking policies to the same table, even for the same role. These policies serve distinct but complementary purposes for row-level and column-level security.
• B. Incorrect. This statement correctly identifies that the masking policy will apply, but it fails to account for the row access policy. The `research_policy` will filter the results, meaning the user will not see all rows from the table, only those matching the policy's condition.
• C. Correct. This accurately describes the combined effect of the two policies. During query execution, Snowflake first applies the row access policy to determine the set of visible rows (where `DIAGNOSIS_CODE` starts with 'C'). Then, for that filtered result set, it applies the masking policy to the `PATIENT_NAME` column, returning the hashed value.
• D. Incorrect. Row access policies and masking policies operate independently and are not mutually exclusive. One does not take precedence over or negate the other. The row access policy filters the rows, and the masking policy is then applied to the specified columns within those filtered rows.

Correct answer: B — `CREATE TAG pii_level;` -> `ALTER TAG pii_level SET MASKING POLICY pii_hash_mask;` -> `ALTER TABLE t1 MODIFY COLUMN c1 SET TAG pii_level = 'true';`

• A. Incorrect. This option uses invalid syntax. The command `ALTER MASKING POLICY ... SET TAG ...` is not the correct way to associate a policy with a tag. The association must be set on the tag object itself.
• B. Correct. This demonstrates the proper sequence for implementing tag-based masking policies. First, the tag is created. Second, the masking policy is associated directly with the tag using `ALTER TAG ... SET MASKING POLICY ...`. Finally, applying this tag to any column (`ALTER TABLE ... SET TAG ...`) will automatically enforce the associated masking policy on that column.
• C. Incorrect. This suggests a manual, programmatic approach using a stored procedure. This is unnecessary as Snowflake provides a native, automatic mechanism for applying policies based on tags, which is the more efficient and scalable solution.
• D. Incorrect. This option's premise that tagging cannot trigger policy application is false. Associating masking policies with tags for automatic enforcement is a core feature of Snowflake's data governance framework.

Correct answers: B, C — A Data Exchange provides a centralized environment for multiple providers and consumers to discover and share data.; A Direct Share is a one-to-one relationship, whereas a Data Exchange is designed for one-to-many or many-to-many sharing.

• A. Incorrect. This statement is the opposite of a Data Exchange's primary purpose. A Data Exchange is specifically designed to facilitate data sharing *across* multiple, distinct organizations, not just within a single one.
• B. Correct. This is a core feature of a Data Exchange. It functions as a centralized hub or marketplace where multiple data providers can publish listings, and multiple data consumers can discover, access, and collaborate on that data in a single, managed environment.
• C. Correct. This highlights the fundamental architectural difference. A Direct Share creates a direct, point-to-point connection between a provider and a specific consumer. In contrast, a Data Exchange uses a hub-and-spoke model, enabling one provider to easily offer data to many consumers (one-to-many) or for multiple providers and consumers to interact (many-to-many).
• D. Incorrect. Both Direct Shares and Data Exchanges are built on Snowflake's Secure Data Sharing technology, which fully supports cross-cloud and cross-region data sharing. This capability is not a differentiating factor between the two.
• E. Incorrect. While the Snowflake Marketplace is a public Data Exchange, organizations can create private Data Exchanges. In a private exchange, an administrator controls membership, deciding which providers can publish data and which consumers can discover and request access, ensuring data is not publicly available.

Correct answer: C — Create a secure view on the source tables and grant `SELECT` on the view to the consumer unit's role.

• A. This is incorrect. Share objects are designed for sharing data between different Snowflake accounts, not for sharing within the same account. Using a share for this scenario would be unnecessarily complex and is not the intended use case.
• B. This is a plausible but suboptimal answer. While granting `SELECT` directly on the table is the simplest method to provide read-only access, it is not the most secure or architecturally sound. This approach tightly couples the consumer to the physical structure of the base table and exposes the entire table, which may not be desirable. Best practices favor using an abstraction layer, like a view, for sharing.
• C. This is the correct answer. Creating a secure view provides a robust, manageable, and secure abstraction layer. It decouples the consumer from the underlying table, allowing the provider to change the base table without affecting the consumer. It also enables fine-grained access control (row-level or column-level security) and, as a *secure* view, prevents the consumer from seeing the underlying query logic. Granting `SELECT` on this view enforces read-only access, and all access is auditable via `QUERY_HISTORY`. This represents the best practice for secure sharing, even within the same account.
• D. This is incorrect. Unloading data to a stage and having the consumer load it is not a direct data sharing method. This process creates a copy of the data, which introduces data latency, requires additional data management, and is significantly more complex than using Snowflake's built-in access control features.

Correct answer: B — Create a private listing with Auto-Fulfillment. The consumer pays for the replicated storage.

• A. Incorrect. A direct share is not a viable solution because it cannot be used to share data across different cloud providers or regions. While a direct share does not incur *additional* storage costs for the provider, its technical limitations make it unsuitable for this specific use case.
• B. Correct. A private listing with Auto-Fulfillment is the ideal solution. This method is designed for cross-cloud and cross-region data sharing and, crucially, it transfers the ongoing storage costs for the replicated data to the consumer's account. This directly fulfills the provider's requirement to minimize their own ongoing storage costs.
• C. Incorrect. While replicating the database makes the data available in the consumer's region, the provider's account is billed for both the data transfer and the ongoing storage of the replicated database. This approach directly contradicts the primary requirement to minimize the provider's costs.
• D. Incorrect. This method requires the provider to pay for egress costs to unload the data and, more significantly, the indefinite ongoing storage costs for the 5 TB file in the external cloud storage bucket. This is not a cost-effective solution for the provider.

Correct answer: B — Creating a Stream on the `RAW_EVENTS` table to capture new records and a Task that executes a `MERGE` statement with JSON parsing functions to populate `PROCESSED_EVENTS`.

• A. Incorrect. While feasible, using an external ETL tool introduces additional architectural complexity, latency, and cost. This approach underutilizes Snowflake's powerful native ELT (Extract, Load, Transform) capabilities, which are specifically designed to handle semi-structured data transformation efficiently within the platform.
• B. Correct. This is the canonical Snowflake architectural pattern for incremental ELT pipelines. A Stream on the `RAW_EVENTS` table efficiently captures new or changed data (CDC). A subsequent Task can be scheduled to periodically execute a `MERGE` or `INSERT` statement, which uses Snowflake's rich set of JSON parsing functions (e.g., `FLATTEN`, dot notation, type casting) to perform the complex transformations on only the new records. This creates a robust, automated, scalable, and cost-effective solution entirely within Snowflake.
• C. Incorrect. The transformation capabilities within a `COPY INTO` statement are limited to simple operations like column reordering, basic data type conversions, and simple expressions. They are not suitable for the 'complex' logic described, such as flattening nested JSON arrays or handling significant schema evolution. The primary purpose of Snowpipe is efficient data ingestion, not complex transformation.
• D. Incorrect. Performing complex, on-the-fly transformations within a view can lead to severe performance degradation for downstream queries. The complex parsing and flattening logic would be executed every time the view is queried, consuming significant compute resources unnecessarily. It is far more efficient to materialize the transformed data into a separate table, as described in option B, so that downstream queries can access pre-processed, structured data.

Correct answers: B, C — Clones of a primary database are not replicated, but you can create new clones from the secondary (replica) database in the disaster recovery account.; After a failover, the new primary database (previously the secondary) can be cloned for use in DEV/TEST environments in that region.

• A. Incorrect. A clone is a separate and distinct database object from its source. The database replication feature is configured to replicate a specific primary database, not any of its associated clones. Therefore, existing clones of a primary database are not included in the replication process.
• B. Correct. Clones are not part of the replication payload. However, a key feature of Snowflake's architecture is the ability to clone a secondary (replica) database in the target account. This is a powerful and common pattern for creating up-to-date, isolated DEV/TEST environments in the disaster recovery region without interfering with the primary account or the replication process.
• C. Correct. In a failover scenario, a secondary database is promoted to become the new primary database. Once promoted, it functions as a normal, writable database and can be cloned to provision development, testing, or analytical environments in the new primary region, ensuring business and development continuity.
• D. Incorrect. While a secondary (replica) database is indeed read-only, this status does not prevent it from being cloned. The `CLONE` command reads the metadata of the read-only source and creates a new, independent, and fully writable database. This is a supported and intended feature.
• E. Incorrect. Cloning and replication are independent, non-conflicting operations. Creating a clone of the primary database is an instantaneous metadata operation that does not interfere with the ongoing, asynchronous replication process. There is no requirement to pause or suspend replication.

Correct answer: B — The `JUNIOR_ANALYST` role has not been granted the `USAGE` privilege on the `FINANCE_DB` database and the `REPORTING` schema.

• A. Incorrect. The error message 'does not exist or not authorized' is specific to accessing data objects like tables or views. If the role lacked `USAGE` privilege on a warehouse, the error would be different, typically indicating that no active warehouse is selected or available for the session.
• B. Correct. In Snowflake's hierarchical object model, to access an object, a role must have the appropriate privilege on the object itself (e.g., `SELECT` on the view) AND the `USAGE` privilege on all parent containers. In this case, the role needs `USAGE` on the `FINANCE_DB` database and `USAGE` on the `REPORTING` schema to be able to 'see' and access the view located within them.
• C. Incorrect. A key feature of views is to provide a layer of abstraction and security. The role querying the view only needs `SELECT` privilege on the view itself. The privileges of the view's owner are used to access the underlying base table. Granting direct access to the base table would defeat this security purpose.
• D. Incorrect. This describes a misunderstanding of how view permissions work. A view does not get granted privileges. Instead, the view's owner must have `SELECT` on the underlying table. The error message points to the querying role's lack of permissions on the containers (database and schema), not a misconfiguration related to the view's owner or reference grants.

Correct answers: B, C — The `ANALYST_ROLE` does not have `USAGE` on the `MARKETING_DB` database.; The `ANALYST_ROLE` does not have `USAGE` on the `CAMPAIGNS` schema.

• A. This is incorrect. The query uses a fully qualified object name (`MARKETING_DB.CAMPAIGNS.ACTIVE_LEADS`), which means it does not depend on the user's current database context (`SALES_DB`). Privileges on `SALES_DB` are irrelevant for this specific query.
• B. This is a correct potential cause. In Snowflake's object hierarchy, to access any object within a database (like a schema or table), the role must have the `USAGE` privilege on the container database. Without `USAGE` on `MARKETING_DB`, the `ANALYST_ROLE` cannot access any objects within it.
• C. This is a correct potential cause. Continuing down the object hierarchy, to access a table within a schema, the role must have the `USAGE` privilege on the container schema. Even if the role has `USAGE` on `MARKETING_DB`, the query will still fail if it lacks `USAGE` on the `CAMPAIGNS` schema.
• D. This is incorrect. Ownership of an object is not required to query it. The `ANALYST_ROLE` only needs the `SELECT` privilege on the table. This privilege can be granted by the table's owner or any role with the `WITH GRANT OPTION` privilege.
• E. This is incorrect. Warehouses are compute resources and are separate from access control. Privileges are granted to roles, not warehouses. A warehouse does not have database-specific access; it simply executes queries on behalf of a user's session, using the privileges of that user's active role.

Correct answer: B — To instruct the Snowflake query optimizer that the data in the table conforms to the constraint, potentially enabling query rewrites and performance improvements.

• A. Incorrect. The `RELY` property does not enforce constraints on new DML operations. Most Snowflake constraints (except NOT NULL) are not enforced by default. Enforcement is controlled by the `ENFORCED` or `NOENFORCE` property, not `RELY`.
• B. Correct. The primary purpose of the `RELY` property is to inform the Snowflake query optimizer that the data in a table already adheres to the specified constraint. This allows the optimizer to make more informed decisions, which can lead to significant query performance improvements through optimizations like join elimination and other query rewrites.
• C. Incorrect. The `RELY` property does not trigger validation of existing data. It assumes the data is already valid. The `VALIDATE` property is used to check existing data against a constraint.
• D. Incorrect. A constraint without the `RELY` property is purely documentary. By adding `RELY`, you are specifically telling Snowflake services (the query optimizer) to trust and use the constraint information for optimization, not to ignore it.

Correct answer: B — To store a unique list of business keys for a single core business concept (e.g., Customer, Product).

• A. Incorrect. This describes the function of a Satellite table. Satellites are designed to store the descriptive attributes of a business key and track their historical changes over time.
• B. Correct. The core purpose of a Hub table in a Data Vault 2.0 model is to contain an integrated, unique list of business keys for a single core business concept, such as 'Customer' or 'Product'. It acts as the master list for that entity.
• C. Incorrect. This is the primary function of a Link table. Link tables are used to model the relationships, associations, or transactions that occur between two or more core business concepts (which are represented by Hub tables).
• D. Incorrect. This is not the function of a Hub table. In a Data Vault 2.0 model, descriptive data, including low-cardinality codes and flags, would be stored in a Satellite table that is connected to a Hub.

Correct answer: B — The dimensions must be created from the same source data and have identical structures, content, and surrogate keys.

• A. Incorrect. A Slowly Changing Dimension (SCD) Type 2 is a design pattern used to track historical changes in dimension attributes over time. While useful, it is independent of the requirement for consistent filtering across multiple fact tables. The choice of SCD type does not solve the core problem of ensuring the dimensions are identical.
• B. Correct. This describes the concept of a 'conformed dimension'. For a BI tool to apply a single filter across visualizations based on different fact tables, the shared dimensions must be identical. This means they share the same structure, content, and, most importantly, the same surrogate keys for the same business entities. This ensures that when a user filters by a value like 'Product Name', the corresponding key is the same for both the sales and inventory facts, leading to consistent and accurate results.
• C. Incorrect. Clustering in Snowflake is a physical data layout optimization technique used to improve query performance, especially for large tables. While clustering the dimension tables on their primary (surrogate) keys could speed up joins to the fact tables, it is a performance enhancement and has no impact on the logical consistency required for BI tools to filter correctly across multiple fact tables.
• D. Incorrect. Foreign key constraints are used to define relationships and ensure referential integrity. However, Snowflake does not enforce these constraints; they are primarily used for documentation and to aid the query optimizer. Even if they were enforced, they would only validate that a key in a fact table exists in the dimension table, not that the dimension tables used by different facts are themselves identical.

Correct answer: C — The table can only be recovered by contacting Snowflake Support to initiate a Fail-safe recovery, as the Time Travel period has passed.

• A. Incorrect. The `UNDROP TABLE` command is a feature of Snowflake Time Travel. This command only works within the data retention period defined for the object. Since the table was dropped 40 hours ago and the retention period is only 24 hours, the Time Travel window has closed, making this command unusable.
• B. Incorrect. Although the Time Travel period of 24 hours has expired, the table is not permanently lost. Because the account is Enterprise Edition, it benefits from a 7-day Fail-safe period that begins immediately after the Time Travel period ends. The drop occurred 40 hours ago, which is within this Fail-safe window.
• C. Correct. The Time Travel period (24 hours) has expired, so self-service recovery options like `UNDROP` are no longer available. However, since the account is Enterprise Edition, the data enters a 7-day Fail-safe period. Data recovery from Fail-safe is not a self-service operation and must be initiated by contacting Snowflake Support. As the drop happened 40 hours ago, it falls within this Fail-safe period.
• D. Incorrect. Creating a zero-copy clone of a database from a specific point in time using the `AT` clause is also a Time Travel feature. This operation is constrained by the data retention period. Attempting to clone from a point in time 48 hours ago is not possible when the retention period is only 24 hours.

Correct answers: A, B, C — If the event had happened 20 hours ago, an admin could perform an instant, self-service recovery using Time Travel.; Now that 30 hours have passed, the only option is to contact Snowflake Support for a manual Fail-safe recovery.; The Fail-safe recovery process will likely take significantly longer to complete than a Time Travel-based recovery.

• A. This is a correct statement. The hypothetical scenario places the corruption event within the table's 24-hour Time Travel window (`DATA_RETENTION_TIME_IN_DAYS=1`). An administrator can perform a self-service recovery using SQL commands like `CREATE TABLE ... CLONE ... AT(...)` to restore the table's state from before the corruption. This is a fast, user-driven process.
• B. This is a correct statement. The corruption occurred 30 hours ago, which is outside the 24-hour Time Travel window. However, since the account is Enterprise Edition, the data is still within the 7-day Fail-safe period. Fail-safe is not a self-service feature; it requires contacting Snowflake Support to initiate a manual data recovery process.
• C. This is a correct statement. Time Travel is a near-instant, metadata-based operation that users can perform themselves. In contrast, Fail-safe recovery is a manual, 'last resort' process performed by the Snowflake Support team. The documentation states it can take several hours or even days to complete, making it significantly slower than a Time Travel recovery.
• D. This is an incorrect statement. While the Fail-safe feature and its 7-day data storage are included with Enterprise Edition, the actual act of performing a recovery is a manual service provided by Snowflake Support. This service is not free and will likely incur additional costs.
• E. This is an incorrect statement. Time Travel offers multiple ways to recover data. While cloning to a new table is one option, an administrator could also use `INSERT OVERWRITE` with a `SELECT ... AT(...)` clause to repair the existing table, or `UNDROP TABLE` if the table was deleted. It is not mandatory to always restore to a new table.

Correct answer: C — The masked SSN values, because the masking policy and its mappings are also cloned.

• A. Incorrect. A core feature of Snowflake cloning is that it replicates the source object's metadata. This includes any attached governance policies, so the masking policy from `PROD_DB` is cloned and applied to the corresponding table and column in `DEV_DB`. Therefore, an error related to a missing policy will not occur.
• B. Incorrect. Since the masking policy is cloned along with the database and its tables, the policy remains in effect in the new `DEV_DB`. The policy's rules are evaluated at query time. Because the `DEVELOPER` role is not privileged to view the unmasked data, the policy will obscure the plain text values.
• C. Correct. When a database is cloned in Snowflake, all of its contained objects—including tables, masking policies, and the associations between them—are also cloned. This ensures that the security posture of the source is maintained in the clone. When a user with the `DEVELOPER` role queries the column, the cloned policy is enforced, and they see the masked SSN values, just as they would in the production database.
• D. Incorrect. Snowflake's zero-copy cloning creates a logical copy where the cloned database's metadata points to the original data's micro-partitions. The data is immediately and fully available, not replaced with NULLs. The query will access this data, which will then be processed by the cloned masking policy before being returned to the user.

Correct answers: B, D — Using the `APPLY_MASKING_POLICY = TRUE` copy option in the `COPY` command.; Specifying the `ENCRYPTION = (TYPE = 'SNOWFLAKE_KMS')` parameter in the `COPY` command.

• A. Incorrect. `SNOWFLAKE_SSE` refers to Snowflake's standard server-side encryption using Snowflake-managed keys. The requirement is to use a Tri-Secret Secure key, which is customer-managed, necessitating a different encryption type.
• B. Correct. By default, a role with sufficient privileges unloads the raw, unmasked data. To ensure that the masking policy for the 'SSN' column is applied during the unload process, the `APPLY_MASKING_POLICY = TRUE` copy option must be included in the `COPY INTO @stage` command.
• C. Incorrect. The `APPLY MASKING POLICY` global privilege grants a role the ability to set or unset masking policies on tables and views. It is not required for the `COPY` command to apply an existing policy during an unload operation.
• D. Correct. Tri-Secret Secure leverages a customer-managed key stored in a Key Management Service (KMS). To encrypt files unloaded to an internal stage using this customer-managed key, you must specify the `ENCRYPTION = (TYPE = 'SNOWFLAKE_KMS' ...)` parameter within the `COPY INTO @stage` command.
• E. Incorrect. This is not a valid encryption setting for a stage or a `COPY` command. Encryption for data unloaded to an internal stage using a customer key is specified directly in the `COPY` command, not through the stage definition.

Correct answer: B — An External Table pointing to the GCS bucket.

• A. Incorrect. Using `COPY INTO` to load a multi-terabyte dataset would incur significant data ingestion time, compute costs for the virtual warehouse, and ongoing storage costs within Snowflake. This directly contradicts the primary requirements for immediate query access and minimal ingestion cost.
• B. Correct. An External Table allows Snowflake to query data directly in the GCS bucket without ingesting it. This solution perfectly aligns with the requirements by providing immediate query access once the table is defined, incurring no data ingestion costs, and having very low management overhead. It is the ideal choice for ad-hoc, exploratory analysis on large, existing datasets in external cloud storage.
• C. Incorrect. Snowpipe is designed for continuous, micro-batch ingestion of new data as it arrives in an external stage. The scenario describes a dataset that is 'written once and rarely updated,' making a continuous loading solution unnecessary and overly complex for the stated goal.
• D. Incorrect. While a Managed Iceberg Table can work with external data, it introduces more management overhead and complexity compared to a standard External Table. For the specific use case of simple ad-hoc, exploratory queries on existing Parquet files, the advanced features of Iceberg Tables are not necessary, and an External Table is the more direct, simpler, and cost-effective solution.

Correct answers: A, C — The name of the file.; An eTag or hash of the file's contents.

• A. Correct. Snowflake's load history metadata explicitly tracks the file name. This identifier, in combination with the file's content hash, is the primary method used by both Snowpipe and the `COPY INTO` command to determine if a file has already been processed and to prevent duplicate data loading.
• B. Incorrect. The IP address of the user who initiated the load is irrelevant to the identity of the file itself. This information is not stored in the load history for the purpose of preventing duplicate file ingestion.
• C. Correct. Snowflake records a strong hash of the file's contents (such as an ETag for S3 or an MD5 hash for Azure Blob Storage). This ensures that if the file's content changes, even if the name remains the same, it will be treated as a new file. It is a key part of the mechanism to prevent reloading an identical file.
• D. Incorrect. While file size is recorded as part of the load history metadata (and is visible in functions like `COPY_HISTORY`), it is not one of the two primary keys used to uniquely identify a file for de-duplication purposes. The combination of file name and content hash is the definitive mechanism used by Snowflake.
• E. Incorrect. The virtual warehouse is the compute resource used to execute the load operation. It is not an intrinsic property of the data file and is not used in the metadata to prevent reloading the same file.

Correct answer: B — UDFs return a single value for each input row, while UDTFs can return multiple rows for each input row.

• A. Incorrect. This statement is factually wrong. Both scalar UDFs and UDTFs in Snowflake support multiple languages, including SQL, Java, Scala, Python, and JavaScript. The language support is not a point of differentiation between them.
• B. Correct. This is the core, fundamental difference. A scalar UDF is designed to process a single input row and return a single, scalar value for that row (a one-to-one mapping). In contrast, a UDTF processes a single input row and can return a set of rows, forming a table as its output (a one-to-many mapping). This makes UDTFs suitable for tasks like tokenizing a string into multiple rows.
• C. Incorrect. The security model is not a fundamental differentiator. Both UDFs and UDTFs can be created as 'secure' functions. A secure UDF/UDTF is a specific configuration used to prevent the function's logic from exposing data from underlying tables, but this is an optional setting, not a default state for either function type.
• D. Incorrect. This describes a consequence of the fundamental difference, not the difference itself. Because a scalar UDF returns a single value, it can be used anywhere a scalar expression is valid, such as in a SELECT list or a WHERE clause. Because a UDTF returns a table, it must be invoked in the FROM clause of a query, typically using the TABLE() syntax. While you can filter the *results* of a UDTF with a WHERE clause, you cannot call the UDTF itself within the WHERE clause like a scalar function.

Correct answers: A, D — It prevents users who only have USAGE privilege on the function from seeing the function's underlying definition and logic.; It prevents the optimizer from exposing data from underlying tables in query profiles or plans to users who should not see it.

• A. This is a core benefit of a SECURE UDF. By default, users with the USAGE privilege on a non-secure UDF can view its definition using GET_DDL or by querying the INFORMATION_SCHEMA. Marking a UDF as SECURE prevents this, thereby protecting the underlying logic and intellectual property.
• B. Incorrect. The SECURE property is related to data visibility and access control, not encryption. Snowflake's standard end-to-end encryption applies to all data, and this feature does not alter that mechanism or use different keys.
• C. Incorrect. The SECURE property does not improve performance. In fact, it may lead to a slight performance degradation because it instructs the optimizer to avoid certain optimizations (like inlining) that could inadvertently expose underlying data.
• D. This is a primary security benefit. When a UDF accesses data tables, the Snowflake optimizer might expose values from those tables in the query plan or profile. A SECURE UDF prevents this data exposure, ensuring that users who call the function cannot see data from underlying tables for which they lack direct privileges.
• E. Incorrect. While UDFs can be shared via data shares, this capability is not specific to SECURE UDFs. Both secure and non-secure UDFs can be included in a share, so this is not a distinguishing benefit of making a UDF secure.

Correct answers: A, B — A stream becomes stale if not consumed within the source table's data retention period.; Streams can be created on external tables to track new files.

• A. This statement is correct. A stream uses an offset to track changes in a source object's versioning history. Snowflake retains this history for the duration of the object's data retention period. If the stream is not consumed and its offset ages beyond this retention period, the required historical data is purged, causing the stream to become 'stale' and unreadable. It must then be recreated.
• B. This statement is correct. Snowflake supports creating streams on external tables. Such streams track changes to the files in the associated external stage, including new, updated, or removed files. This feature is particularly useful for automating data ingestion pipelines that process files from external cloud storage.
• C. This statement is incorrect. A stream shows the delta of changes from its current offset to the current transactional time. The stream's offset advances to the current timestamp when the stream's data is consumed within a DML transaction that successfully commits. The phrasing 'committed on the stream' is inaccurate; the transaction that consumes the stream is what gets committed.
• D. This statement is incorrect and misleading. While the stream object itself is only metadata with negligible storage size, its existence can lead to increased storage costs. By maintaining an offset, a stream prevents the historical data micro-partitions it references from being purged by Time Travel, potentially extending their retention and thus increasing storage consumption for the source table.
• E. This statement is incorrect. An APPEND_ONLY stream, by definition, only tracks INSERT operations. It completely ignores UPDATE and DELETE operations. A standard stream would capture an UPDATE as a DELETE and an INSERT, and would correctly populate the METADATA$ISUPDATE column for the inserted row.

Correct answer: B — It prevents SQL injection vulnerabilities.

• A. Incorrect. The primary benefit of using prepared statements is security, not performance. While some database systems may cache execution plans for prepared statements, this does not directly improve compilation time and is not the main purpose of this feature in the context of security.
• B. Correct. This is the fundamental reason for using parameter binding. It separates the SQL command from the data. By treating user input strictly as data values rather than executable code, it effectively prevents SQL injection attacks, where malicious SQL could be inserted through user input.
• C. Incorrect. Although the Snowflake Python Connector does handle the conversion of Python data types to Snowflake SQL types, this is a general functionality of the connector for data marshalling, not the primary security benefit derived from using prepared statements.
• D. Incorrect. The use of prepared statements and parameter binding is unrelated to whether a query is executed synchronously or asynchronously. The mechanism for running queries asynchronously is a separate feature of the connector.

Correct answer: B — `SYSTEM$ALLOWLIST`

• A. Incorrect. The system function `SYSTEM$GET_NETWORK_POLICY` does not exist in Snowflake. While an administrator can view a policy's definition using the `DESCRIBE NETWORK POLICY` command, this is not the system function designed to return the consolidated list of all allowed IP addresses.
• B. Correct. The `SYSTEM$ALLOWLIST` system function is the correct tool for this purpose. It returns a JSON array containing all IP addresses from the network policies that are currently active for the account and for the current user, allowing an administrator to verify the active allowlist.
• C. Incorrect. `SYSTEM$VERIFY_IP_ADDRESS` is not a valid Snowflake system function and does not exist in its functionality for managing network policies.
• D. Incorrect. The `SYSTEM$CURRENT_USER_IP_ADDRESS` function returns only the IP address of the client from which the current user is connected. It does not provide the comprehensive list of all IP addresses permitted by the account's network policies.

Correct answers: A, D, E — Setting `enable.idempotence=true` in the Kafka producer configuration.; Setting `acks=all` in the Kafka producer configuration.; The use of Snowpipe Streaming client SDK with appropriate offset management.

• A. Correct. This Kafka producer setting is a fundamental part of achieving exactly-once semantics on the source side. It prevents duplicate messages from being written to the Kafka topic if the producer has to retry a send operation due to a transient network failure, turning at-least-once delivery into exactly-once for the producer.
• B. Incorrect. The data format, whether it's Avro, JSON, or Protobuf, is concerned with data serialization and schema management. It is completely independent of the message delivery guarantees and does not contribute to achieving exactly-once semantics.
• C. Incorrect. While defining a primary key is a best practice for maintaining data integrity within the final Snowflake table, it is not part of the Kafka Connector's core mechanism for guaranteeing exactly-once ingestion. The connector's guarantee comes from its offset management, not from relying on a table constraint to reject duplicates.
• D. Correct. This Kafka producer setting ensures maximum data durability. By setting `acks=all` (or `-1`), the producer waits for confirmation that the message has been successfully replicated to all in-sync replicas in the Kafka cluster. This prevents data loss in case of a broker failure, which is a prerequisite for any exactly-once guarantee.
• E. Correct. This is the key component on the Snowflake consumption side. The Snowflake Kafka Connector leverages the Snowpipe Streaming client SDK's robust offset tracking. It registers data for ingestion with Snowflake and only commits the corresponding Kafka offsets after receiving a firm confirmation from Snowflake. This tight coordination ensures that data is neither missed nor duplicated, even if the connector restarts.

Correct answers: A, C, E — Refactor the query to process data in smaller chunks, for example, by filtering on date ranges and using `UNION ALL`.; Ensure the tables involved have well-defined clustering keys that align with the query's join and filter predicates.; Rewrite correlated subqueries as explicit `JOIN` operations.

• A. This is a highly effective technique to combat spilling. Spilling occurs when an operation's intermediate results exceed the available memory on a warehouse node. By refactoring the query to process smaller, independent sets of data (e.g., by date) and combining them with `UNION ALL`, the peak memory required for any single operation is significantly reduced, thus preventing or minimizing the need to spill to remote storage.
• B. The Query Acceleration Service (QAS) is designed to offload scan-intensive and filter-heavy parts of a query workload to serverless resources provided by Snowflake. It does not provide additional memory for the primary virtual warehouse to perform memory-intensive operations like large joins, aggregations, or sorts where spilling occurs. Therefore, enabling QAS would not directly address the remote spilling issue.
• C. This is a fundamental performance tuning technique that directly helps reduce spilling. Well-defined clustering keys improve partition pruning, meaning Snowflake reads significantly less data from storage to satisfy the query's joins and filters. Processing a smaller volume of data requires less memory for operations like joins and aggregations, directly reducing the likelihood and volume of data spilled to remote storage.
• D. Dropping and recreating tables is a storage management operation related to data organization or reclaiming space from Time Travel and Fail-safe, but it does not affect the amount of memory available to a virtual warehouse during query execution. Spilling is a compute memory issue, not a permanent storage issue, so this action would not mitigate the problem.
• E. Correlated subqueries can be highly inefficient and memory-intensive because they may execute iteratively for each row of the outer query. Rewriting them as explicit `JOIN` operations allows Snowflake's optimizer to choose a much more efficient, set-based execution plan (e.g., a hash join). This approach typically consumes far less memory than a correlated subquery, thereby reducing the risk of spilling.

Correct answer: D — There is a startup time associated with provisioning a new cluster, during which incoming queries may be queued if the existing clusters are fully loaded.

• A. Incorrect. The 'Standard' scaling policy is designed to be reactive to the current workload and queue size. It does not wait for a fixed, arbitrary duration like one minute before initiating the startup of a new cluster.
• B. Incorrect. While unusual delays from a cloud provider are possible, the more direct and expected cause for a brief queuing period during scale-out is the inherent, normal startup time of a new Snowflake cluster. This option suggests an anomaly rather than standard behavior.
• C. Incorrect. If the workload exceeded the total capacity of all 4 maximum clusters, the queuing would persist until the workload decreased. The scenario describes a temporary queue after which performance normalized, indicating that the scaled-up warehouse was eventually able to handle the load.
• D. Correct. When a workload spike saturates the currently running cluster(s), the 'Standard' scaling policy initiates the provisioning of a new cluster. This provisioning process is not instantaneous; there is a brief startup time. During this period, incoming queries that cannot be handled by the existing, busy clusters will be queued until the new cluster comes online and begins processing them. This perfectly explains the temporary queuing phenomenon.

Correct answer: B — The cache is completely dropped and must be rebuilt as new queries run.

• A. Incorrect. When a virtual warehouse is resized, Snowflake provisions a new set of compute resources (nodes) for the new size. The local disk cache from the old nodes is not replicated or transferred to the new nodes.
• B. Correct. Resizing a virtual warehouse, either up or down, involves replacing the existing compute cluster with a new one. As a result, the local disk cache on the old nodes is completely dropped. The new cluster starts with a 'cold' or empty cache, which will be populated as new queries are executed.
• C. Incorrect. The cache is not preserved during a resize operation because the underlying physical compute resources are replaced. The new nodes begin with an empty cache.
• D. Incorrect. The local disk cache is specific to the compute nodes of a running warehouse and is not designed to be archived or restored. When the warehouse is resized, the cache is simply discarded along with the old compute resources.

Correct answer: C — Local spilling uses the local SSD storage of the warehouse nodes, while remote spilling uses the centralized cloud storage layer.

• A. Incorrect. This statement misidentifies the storage locations for both types of spilling. Local spilling writes to the local SSD disk of a warehouse node, not the warehouse's data cache. Remote spilling writes to the remote cloud storage layer, not the result cache, which is used for storing final query results.
• B. Incorrect. While this option correctly identifies that local spilling is triggered by memory exhaustion on a node, its description of remote spilling is imprecise. Remote spilling occurs when the local SSD disk space on the warehouse nodes is insufficient to hold the data being spilled from memory, not simply when the entire warehouse runs out of memory.
• C. Correct. This statement accurately describes the fundamental difference between the two types of spilling. When an operation requires more memory than available, Snowflake first spills intermediate data to the fast, local SSD storage attached to the virtual warehouse's compute nodes (local spilling). If this local storage is also exhausted, Snowflake then spills data to the permanent, remote cloud storage layer (e.g., S3, Azure Blob), which is significantly slower (remote spilling).
• D. Incorrect. Both local and remote spilling are significant performance degradation events that should be investigated and optimized, not ignored. They are mechanisms designed to prevent a query from failing due to memory constraints, but they come at a high performance cost, especially remote spilling.

Correct answers: C, D — Provision and use a Snowpark-optimized virtual warehouse.; Increase the size of the standard warehouse (e.g., to STANDARD_4).

• A. Incorrect. The root cause of the error is insufficient memory on the virtual warehouse node, not the choice of programming language. While language specifics can affect memory usage, changing from Python to Scala does not increase the memory allocated by the warehouse and is not a direct solution to this problem.
• B. Incorrect. The `STATEMENT_TIMEOUT_IN_SECONDS` parameter controls the maximum execution time for a statement before it is cancelled. This is unrelated to memory allocation. An out-of-memory error is a resource constraint issue, not a time constraint issue. Increasing the timeout will not prevent the procedure from running out of memory.
• C. Correct. Snowpark-optimized warehouses are specifically designed for memory-intensive workloads, such as machine learning training or large in-memory transformations. They provide significantly more memory per node compared to standard warehouses of the same size, making them the most suitable choice for resolving this type of out-of-memory error.
• D. Correct. Scaling up a virtual warehouse (e.g., from a STANDARD_2 to a STANDARD_4) directly increases the compute and memory resources available on each node. A larger warehouse size provides more RAM, which can accommodate the large pandas DataFrame and prevent the out-of-memory error.
• E. Incorrect. The Query Acceleration Service (QAS) is designed to improve the performance of specific, eligible SQL queries by offloading parts of the workload to shared compute resources. It does not increase the memory available to the primary virtual warehouse for executing Snowpark code or handling large in-memory data structures.