Snowpro Advanced Architect Practice Questions

The total_constant_partition_count represents the number of micro-partitions for which the min/max values of the clustering key columns are the same, meaning they contain no overlapping values with other partitions. The high ratio of constant partitions (450,000 out of 500,000, or 90%) to the total partitions indicates that the table is very well-clustered. This state allows for highly effective micro-partition pruning because when a query filters on the clustering key, Snowflake can eliminate a large number of partitions from the scan.

The ACCOUNTADMIN role is the most powerful role in Snowflake, encapsulating the SYSADMIN and SECURITYADMIN roles. It has permissions to perform any action in the account, including modifying users, roles, grants, and viewing all data. Granting this role widely violates the principle of least privilege and creates a massive security risk, as a compromised developer account could lead to a full account takeover. Best practice dictates that this role should be used sparingly, only for initial setup, and day-to-day activities should be performed using less privileged roles like SYSADMIN (for object creation) and SECURITYADMIN (for user/role management).

This architecture perfectly meets all requirements. Using a separate account per customer provides the ultimate level of data isolation (Requirement 1). Within each account, customer-specific settings like Time Travel and Network Policies can be configured independently (Requirement 2). Cost tracking is straightforward, as usage is reported on a per-account basis (Requirement 3). Finally, Snowflake Organizations allow a central administrator to manage all these accounts from a single interface, create new accounts programmatically, and view aggregated usage, ensuring operational efficiency (Requirement 4). The other options fail to provide the required level of isolation and customization.

Zero-Copy Cloning is the feature that allows for the creation of near-instantaneous copies of databases, schemas, or tables without duplicating the underlying storage. It's a metadata-only operation, which is why it's so fast. The clone is fully independent and writable. Storage costs are only incurred for the delta of changes made to the clone, satisfying all the requirements of speed, cost-efficiency, and isolation for a sandbox environment.

The recommended best practice for a scalable and manageable RBAC hierarchy is to grant privileges on objects to access roles. These access roles are then granted to functional roles, which represent business functions. Finally, functional roles are granted to users. This creates a clear separation of concerns, where access roles define 'what' can be accessed and functional roles define 'who' (in terms of business role) gets the access.

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.