Snowpro Advanced Architect Free Sample Questions

This is the most secure and correct architecture. Using a Snowflake Organization allows for central management and billing. Creating separate accounts for each hedge fund provides the strongest data isolation, as objects and compute are completely segregated. Applying unique Network Policies within each account ensures that network traffic is restricted to the specific IP addresses designated for that fund, fulfilling the network isolation requirement. Row Access Policies in a single account are complex to manage for true multi-tenancy and don't provide compute or network traffic isolation. Applying network policies at the master account level would enforce the same policy for all funds, which contradicts the requirement for fund-specific IP whitelists.

The RELY property tells the Snowflake optimizer that the data in the table conforms to the constraint, allowing it to use this information for query rewrites and optimizations (like join elimination) without actually validating the data. This meets the requirement of improving performance without incurring validation overhead. NOT ENFORCED is the default and simply declares the constraint without validation or reliance by the optimizer. VALIDATE would force validation, which is what the architect wants to avoid. ENABLE is not a standalone constraint property; it's used with VALIDATE.

A stream's offset advances only when the DML statement that consumes it is part of a successful transaction. If the MERGE statement runs as a standalone, auto-committed transaction, the stream consumption is not guaranteed to be part of that same transaction. To ensure the stream is consumed atomically with the MERGE operation, both the SELECT from the stream and the MERGE into the target table must be enclosed within an explicit BEGIN...COMMIT transaction block. This guarantees that if the MERGE is successful, the stream's offset is advanced, and the records are consumed.

The query profile indicates poor micro-partition pruning, as almost all partitions are being scanned. Since the data is naturally ordered by the timestamp column used in the WHERE clause, defining a clustering key on this column will formalize this organization. Snowflake can then use the micro-partition metadata to prune (skip) the vast majority of partitions that do not contain data relevant to the query's time range. This directly addresses the root cause of the excessive table scan. Increasing warehouse size would only brute-force the scan faster at a higher cost. Search Optimization is best for highly selective point lookups on high-cardinality columns, not range scans on a naturally ordered key. Creating a materialized view is a heavier solution and might not be as effective as fixing the base table's physical layout.

Dynamic Data Masking allows defining a policy that conditionally alters the data returned from a query based on the user's role. A masking policy can be created to show the real value for the AUDITOR role but a masked value (e.g., 'XXX-XX-XXXX') for the ANALYST role.

RBAC is the foundation for this solution. The masking policy's logic will explicitly check the user's current role (IS_ROLE_IN_SESSION('AUDITOR')) to decide whether to show masked or unmasked data. The distinct roles (ANALYST, AUDITOR) are essential for the policy to function as required.

The statement is false. Snowflake's Secure Data Sharing is built on its unique architecture that separates storage and compute. Data is never physically copied to the consumer's account. Instead, the consumer gets secure, live, read-only access to the provider's data. Because the data remains in the provider's account, the provider is responsible for all storage costs. The consumer is only responsible for the compute costs incurred from querying the shared data using their own virtual warehouses.

The FLATTEN function is a table function specifically designed to explode semi-structured data, like JSON arrays, into a relational representation. It produces a lateral view of a VARIANT, OBJECT, or ARRAY column, effectively converting each element of the array into a separate row in the result set. PARSE_JSON is used to convert a string into a VARIANT, which is done at ingestion. JSON_EXTRACT_PATH_TEXT is used to extract scalar values from a specific path within the JSON, not to un-nest an entire array. CHECK_JSON is for validation.

The ECONOMY scaling policy is designed to conserve credits by starting new clusters only when the system estimates there's enough query load to keep the new cluster busy for at least 6 minutes. This can lead to queuing even if the current clusters are not fully utilized, as the policy waits to ensure the new cluster will be used efficiently. The low CPU utilization suggests the existing clusters are handling their current load, but the queuing indicates that the policy is too conservative for the bursty, high-concurrency workload of the holiday season. Changing the policy to STANDARD would start new clusters more aggressively, reducing queue time.

This is the optimal solution. Using Secure Data Sharing meets requirements 1, 2, and 3: it provides live, real-time access to data across regions and clouds without copying it, so the central BI team only pays for compute. For requirement 4, the regional accounts must share their data with both the primary (us-east-1) and secondary (us-west-2) NA accounts. The databases created from these shares are not replicated via Account Replication. Instead, a Failover Group should be used to replicate the central account's own objects like users, roles, and warehouses. In a failover event, the secondary account is promoted, and it already has access to the live regional data via the pre-configured shares, allowing it to resume operations quickly.

The POLICY_REFERENCES function (or the SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES view for historical lookups) is the correct tool for this task. It is specifically designed to show which security policies (like masking or row access) are set on which objects. Querying this function with the policy name will return the objects it is attached to, or querying with the object name will return the policies attached to it. The TABLES view does not contain policy attachment details. GET_DDL shows the table's DDL but not the policy attachments, which are managed separately via ALTER TABLE ... ADD ROW ACCESS POLICY. The APPLICABLE_ROLES view shows role information, not policy attachments.