Snowflake AI Data Cloud Features and Architecture practice questions

Increase the MAX_CLUSTER_COUNT to 10

Why it fails: Increasing the maximum cluster count only expands the capacity ceiling. If the current workload is suffering because existing clusters are not spinning up fast enough due to the Economy policy, simply adding more potential headroom will not solve the underlying latency issues caused by the current scaling strategy.

Increase the warehouse size from X-SMALL to SMALL

Why it fails: Increasing the warehouse size addresses 'scale-up' needs for individual complex queries. However, the scenario indicates performance degradation during peak concurrency, which is a 'scale-out' problem. Scaling out by adding more clusters is the appropriate architectural approach for managing high concurrency rather than increasing individual compute node size.

Decrease the auto_suspend value to 60

Why it fails: Reducing auto_suspend is a cost management strategy that forces the warehouse to shut down faster during inactivity. It does not impact the responsiveness or execution time of queries while the warehouse is active. This change would likely lead to more frequent start-ups, potentially increasing latency for the users.

Create separate databases for each department.

Why it fails: Creating separate databases would require duplicating the data, which leads to increased storage costs and data synchronization challenges. It does not provide compute isolation, as users could still end up sharing the same warehouse depending on the configuration, failing to solve the underlying performance contention problem.

Use a single warehouse for all departments.

Why it fails: A single warehouse would cause resource contention between departments. When one department runs a complex query, it would consume the compute resources, potentially starving other departments of performance. This fails to provide the isolation required for predictable, high-performance workloads across different business units in an enterprise.

Use virtual private Snowflake instances.

Why it fails: Virtual Private Snowflake (VPS) is a high-level security configuration for complete network and account isolation, which is overkill for simple departmental isolation. It is an expensive and complex solution that is not intended for managing compute workload contention between teams within the same organization.

It is persistent across warehouse restarts.

Why it fails: The local disk cache is tied to the lifecycle of the virtual warehouse compute nodes. When a warehouse is suspended or resized, the SSDs are wiped or decommissioned. Therefore, the cache is not persistent across warehouse restarts, unlike the Query Result Cache which is held in Cloud Services.

It is shared across different virtual warehouses.

Why it fails: The local disk cache is local to the specific virtual warehouse that is currently running. It cannot be shared across different warehouses, as each warehouse has its own dedicated compute nodes with its own isolated set of SSDs, ensuring strict resource and cache separation.

It is used to cache query result sets.

Why it fails: This is a common misconception. The Query Result Cache (in Cloud Services) stores the final output of a query. The Local Disk Cache stores the raw micro-partition data blocks read from storage to speed up the processing of the execution plan for the query.

The engineer must manually rebuild the table daily to re-sort the data for pruning.

Why it fails: Manual maintenance like table rebuilding is not required in Snowflake's architecture. The system manages micro-partitions automatically, and the Automatic Clustering service handles re-sorting if the clustering depth becomes inefficient. This automation reduces the administrative burden on data engineers and ensures that the system scales effectively with growing data volumes.

The engineer must create a clustered index on the 'Region' column to ensure performance.

Why it fails: Snowflake does not use traditional indexes like B-trees or clustered indexes found in legacy databases. Instead, it relies on micro-partitioning and metadata for data retrieval. Users can define a Clustering Key if natural clustering is insufficient, but this is a metadata-driven instruction for the background service, not a physical index object.

The 'Region' column must be part of a primary key to enable partition pruning.

Why it fails: In Snowflake, primary keys are primarily used for documentation and compatibility with BI tools; they are not enforced and do not influence how data is physically stored or pruned. Partition pruning depends entirely on the micro-partition metadata and the actual distribution of data values, regardless of whether a primary key exists.

SNOWFLAKE.CORTEX.FORECAST

Why it fails: While FORECAST is an AI-powered function in Snowflake, it is part of the ML Functions suite rather than the LLM-based natural language processing tools in Cortex. It uses machine learning algorithms to predict future values based on historical time-series data. It is distinct from the generative and language-based tasks handled by Cortex LLM functions.

SNOWFLAKE.CORTEX.VECTOR_SEARCH

Why it fails: Vector Search is a capability used to find similar items in a high-dimensional space, often used in RAG (Retrieval-Augmented Generation) patterns. While related to AI architectures, the specific function for performing NLP tasks like summarization or translation is separate from the underlying vector storage and search mechanisms used for building custom AI applications.

SNOWFLAKE.CORTEX.TRAIN_MODEL

Why it fails: Snowflake Cortex focuses on providing pre-trained, managed models for immediate use via functions. Users generally do not call a 'TRAIN_MODEL' function within Cortex to build their own LLMs from scratch. Instead, they leverage the 'COMPLETE' function or specific task-based functions that use models already optimized and maintained by Snowflake on their behalf.

Permanent Tables

Why it fails: Permanent tables are the default type in Snowflake and include a Fail-safe period of 7 days after the Time Travel retention period ends. This makes them more expensive for short-lived data because the organization is billed for storage until the Fail-safe period expires, even if the table is dropped or the data is deleted.

External Tables

Why it fails: External tables store metadata about data files residing in external cloud storage (like S3 or Azure Blob) but do not store the data within Snowflake micro-partitions. While they avoid Snowflake storage costs, they often result in slower query performance and do not support the same DML capabilities as internal table types for transformation tasks.

Secure Views

Why it fails: Secure Views are not a storage object; they are metadata-based logic that defines how data from underlying tables should be presented to users. They do not store data themselves and therefore cannot be used to save intermediate transformation results. Using a view would result in the transformation logic being re-executed every time the view is queried.

Snowpark Optimized warehouses have faster SSDs for local caching of data.

Why it fails: While all warehouses use local SSDs for caching, the primary differentiator for Snowpark Optimized warehouses is the increased RAM (memory), not the speed of the disk. These warehouses are tailored for workloads where the entire dataset or model state must fit into memory for efficient processing, which is common in advanced analytics and ML.

Only Snowpark Optimized warehouses can execute Python and Scala code.

Why it fails: Both Standard and Snowpark Optimized warehouses can execute Python, Scala, and Java code via the Snowpark API. The difference is purely in the resource allocation. Users should choose Snowpark Optimized warehouses only for those specific scripts or models that require large amounts of memory, as they are more expensive in terms of credit consumption.

Snowpark Optimized warehouses are the only ones capable of multi-cluster scaling.

Why it fails: The Multi-cluster Warehouse (MCW) feature is available for both Standard and Snowpark Optimized warehouses, provided the account is Enterprise edition or higher. MCW is used for managing concurrency, whereas the 'Snowpark Optimized' designation is about providing more memory to individual nodes within a single cluster for complex, data-heavy computations.

Micro-partitions are manually created by users using clustering keys.

Why it fails: Micro-partitioning happens automatically when data is loaded into Snowflake. Users do not manually create them. While clustering keys can be defined to organize data within these partitions, the physical creation and management of the micro-partition files are handled entirely by the Snowflake background services, not the end user.

Micro-partitions range in size from 500 MB to 1 GB before compression.

Why it fails: Micro-partitions are smaller, typically ranging from 50 MB to 500 MB of uncompressed data. This smaller size is intentional to enable efficient pruning and granular retrieval. Larger sizes would diminish the effectiveness of metadata-based pruning, which is essential for fast query execution on large datasets in cloud storage.

Users must run an ANALYZE command to update partition metadata.

Why it fails: Snowflake architecture automatically collects and maintains metadata about every micro-partition during the data load process. This metadata includes min/max values and null counts, which are used for partition pruning. Consequently, there is no need for manual ANALYZE commands or index management, simplifying administration and ensuring optimal query performance.

Increase the MIN_CLUSTER_COUNT to 5.

Why it fails: Increasing the minimum cluster count forces all five clusters to run constantly. While this eliminates wait times, it is inefficient and costly, as it does not scale based on demand. This approach ignores the elasticity benefits of Snowflake and leads to unnecessarily high compute expenses during off-peak hours.

Resize the warehouse to 2X-LARGE.

Why it fails: Resizing to a larger warehouse increases the compute power for a single query, which is useful for complex, heavy-duty queries. However, it does not resolve concurrency-related queuing issues. If the performance issue is caused by too many users, scaling out via multi-cluster settings is the correct architectural approach.

Set the auto-suspend time to 0.

Why it fails: Setting auto-suspend to 0 keeps the warehouse running indefinitely, which consumes credits constantly. This does not help with concurrency-based queuing, as a single running cluster has a fixed limit on concurrent queries. It only ensures the warehouse is always ready, failing to address the underlying scaling bottleneck in this scenario.

Data compression and encryption

Why it fails: Data compression and encryption are performed by the storage layer or the compute layer during the data ingestion process. While the Cloud Services layer coordinates these activities, the actual physical transformation and encryption of the data occur during the load or processing phase, not within the service layer itself.

Large-scale data storage persistence

Why it fails: Persistence is handled by the Storage layer, which leverages cloud-native object storage. The Cloud Services layer provides metadata about the data but does not store the data itself. Separating storage from the metadata and processing layers is what allows Snowflake to scale independently and provide virtually unlimited storage capacity.

Reduced storage costs due to on-demand compute.

Why it fails: Storage costs are independent of compute usage; they are billed based on the amount of data stored per terabyte. While compute is billed on-demand, linking storage cost reductions to compute demand is incorrect. Each component has a distinct cost model tailored to its specific resource usage profile.

Automatic data partitioning based on hardware capacity.

Why it fails: Snowflake's micro-partitioning is a logical process handled by the Snowflake service, not a function of underlying hardware capacity. The system manages partitioning automatically to optimize query performance, regardless of the physical server configuration, making the concept of 'hardware-based partitioning' irrelevant in the Snowflake architectural model.

Compute resources must be equal to storage size.

Why it fails: There is no requirement for compute resources to be proportional to storage size. A small warehouse can query petabytes of data, and a large warehouse can query gigabytes. This independence is exactly why the architecture is so powerful, allowing organizations to optimize for performance or cost without artificial constraints.

It performs physical data transformation and heavy join operations.

Why it fails: The Cloud Services layer does not perform heavy data processing or transformations. These tasks are strictly handled by the Virtual Warehouses (compute layer). The Cloud Services layer focuses on metadata management, query compilation, and orchestration, leaving the heavy lifting to the compute-intensive resources provided by warehouses.

It stores the permanent, encrypted copies of user tables.

Why it fails: Permanent user data is stored exclusively in the Database Storage layer, which leverages cloud object storage. The Cloud Services layer does not house user tables or the raw data itself; its primary data-related function is maintaining the metadata repository required for query execution and account administration.

It handles query execution scaling automatically.

Why it fails: While the Cloud Services layer orchestrates the query process, it does not handle the auto-scaling of compute resources. Scaling is the responsibility of the Virtual Warehouse layer, which can spin up additional clusters (multi-cluster warehouses) to handle increased concurrency based on the settings defined by the administrator.

Users must manually define the size of micro-partitions.

Why it fails: Micro-partition management is entirely automated by Snowflake. The system determines the optimal size (typically between 50MB and 500MB uncompressed) based on the data patterns. Users do not need to manually configure partitioning strategies or adjust them as data grows, reducing the administrative burden on data engineers.

Data within each micro-partition is stored in row-oriented format.

Why it fails: Data within micro-partitions is stored in a columnar format, not row-oriented. Columnar storage is highly efficient for analytical queries because it allows Snowflake to read only the specific columns required for a calculation, rather than loading entire rows, which significantly optimizes I/O and reduces memory consumption.

Warehouses store the data they process on their local SSDs permanently.

Why it fails: Warehouses are compute-only; they do not store data permanently. While they do use local SSDs for caching data and for spilling large operations to disk, all persistent data is stored in the cloud storage layer. This separation is what enables the warehouse to be suspended without losing data.

Suspending a warehouse immediately deletes all cached data.

Why it fails: When a warehouse is suspended, the local disk cache on the compute nodes is eventually cleared. However, this doesn't happen instantly, and more importantly, it does not affect the persistence of the actual data, which resides in the storage layer. Suspended warehouses incur no compute costs.

Only one warehouse can be active in a Snowflake account at a time.

Why it fails: Snowflake allows for an unlimited number of virtual warehouses to be active simultaneously. This allows different teams or workloads to have their own dedicated compute resources, ensuring that a resource-heavy data science model training does not interfere with the performance of a critical executive dashboard reporting query.

The Cloud Services layer automatically replicates the small table to all compute nodes.

Why it fails: The Cloud Services layer handles metadata, security, and query parsing, but it does not replicate user data to compute nodes. Replication of data for joins is managed by the compute layer during query execution, not by Cloud Services. This option misattributes a compute-layer function to Cloud Services, which would not directly move table data for join processing.

The virtual warehouse's compute nodes share a distributed file system that allows direct access to any micro-partition.

Why it fails: Snowflake does not use a shared distributed file system for compute nodes; storage is decoupled and remote. Compute nodes access micro-partitions via the storage layer, but they do not share a local file system that enables broadcast joins. This option incorrectly describes the underlying storage architecture and would not explain the broadcast mechanism.

The small table is automatically cached in the warehouse's local memory, and all nodes read from that cache.

Why it fails: While warehouse caching can improve performance, it does not guarantee that a small table is cached across all nodes for a broadcast join. The optimizer's decision to broadcast is based on metadata and does not rely on prior caching. This option confuses the result cache or local disk cache with the join execution strategy.

Use object tagging on the sensitive columns and grant the external role the TAG_READ privilege.

Why it fails: Object tagging is a metadata classification mechanism used for governance, discovery, and policy targeting; it does not alter or hide column values during queries. Granting TAG_READ only allows a role to see tag metadata, not to bypass or enforce masking, so this option would leave the sensitive data fully visible and fail the security objective.

Create a secure view that excludes the sensitive columns and grant the external role access to the view.

Why it fails: A secure view can restrict which columns are exposed, but it does not dynamically mask values based on the querying role, and it cannot be shared to a different Snowflake account without a share. The requirement to enforce role-based masking on the underlying data while allowing direct queries is better met by a masking policy, so this approach only partially addresses the scenario.

Apply a row access policy to the table so the external team only sees rows that do not contain sensitive data.

Why it fails: Row access policies filter which rows a role can see, not which column values are visible. They cannot replace the contents of email or phone columns with masked values, so they do not satisfy the need to hide specific field contents while still allowing access to other columns. This makes them unsuitable for the described column-level protection requirement.

A secure view that selects a constant placeholder value in place of the phone number column.

Why it fails: A secure view can hide logic and restrict access, but it replaces the table for consumers and does not automatically apply across every table that has the same column name. It also cannot vary the output by role as flexibly as a masking policy without duplicating logic.

A row access policy attached to each table with a mapping table of authorized roles.

Why it fails: Row access policies filter which rows a role can see, not which column values are shown. They are designed for row-level security, so applying one to the phone number column would not mask the value and would not meet the requirement of hiding the column contents.

An object tag applied to the phone number column with a value of 'PII'.

Why it fails: Tags classify objects and can drive tag-based masking when a masking policy is attached to the tag, but the tag alone does not transform values. Without an associated masking policy, tagging the column only documents sensitivity and does not enforce masking for unauthorized roles.

Multi-cluster warehouse with auto-scaling enabled

Why it fails: Multi-cluster warehouses automatically add or remove clusters to handle concurrency, but they operate within a single region and do not provide cross-region failover. If the entire region experiences an outage, the multi-cluster warehouse is unavailable. This feature addresses performance scaling, not disaster recovery or high availability across regions. It does not replicate data or redirect queries to another region.

Database replication with failover enabled

Why it fails: Database replication replicates data and metadata to a secondary account in another region, and failover can promote the secondary to primary. However, this feature handles data availability, not compute warehouse failover. The question specifically asks about automatic failover for the warehouse, not the database. While replication is part of a broader disaster recovery strategy, it does not provide automatic failover for the virtual warehouse itself.

Snowflake's built-in high availability within a single region

Why it fails: Snowflake's architecture provides high availability within a region through redundancy, but this does not protect against a region-wide outage. The scenario requires failover to a different region, which is not achieved by intra-region HA. Built-in HA ensures that individual components are fault-tolerant, but it does not automatically redirect workloads to another region. Therefore, it does not meet the requirement for cross-region automatic failover.

Add a clustering key to the large fact table on the join column.

Why it fails: Clustering can improve pruning for filtered queries, but it does not directly affect join performance when joining on a non-clustered column. The join operation still requires shuffling or broadcasting data, and clustering on the join column may not help unless the join is highly selective or the table is queried with filters on that column.

Enable the USE_CACHED_RESULT parameter to force the use of cached results.

Why it fails: USE_CACHED_RESULT controls whether the query can use the result cache. It does not influence join strategies or performance for a query that is already running. Disabling or enabling it would not change the join method or improve the performance of a poorly performing join.

Ensure that statistics are up to date by running ANALYZE TABLE on both tables.

Why it fails: Snowflake automatically maintains statistics for all tables, and manual statistics gathering is not required or supported. The query optimizer uses metadata and micro-partition statistics to make decisions, so running ANALYZE TABLE would have no effect and is not a valid command in Snowflake.

Increase the warehouse size to a larger size.

Why it fails: Increasing warehouse size provides more compute resources, which can speed up queries, but it does not improve the underlying data clustering. For very large tables, scanning unnecessary micro-partitions due to poor clustering can still be slow even with a larger warehouse. Clustering reduces the amount of data scanned, complementing compute scaling. Thus, warehouse size alone is not a clustering action.

Manually sort the table by the desired columns using ORDER BY in a CREATE TABLE AS SELECT statement.

Why it fails: Manually sorting a table once does not maintain clustering over time as new data is added. Snowflake's automatic clustering may initially benefit from sorted data, but without a clustering key, subsequent DML operations will cause disorganization. Clustering keys are needed to maintain order. Thus, a one-time sort is not a sustainable solution for ongoing query performance.

Use the RECLUSTER command to manually recluster the table.

Why it fails: There is no RECLUSTER command in Snowflake. Clustering is managed automatically when a clustering key is defined. You can manually trigger reclustering by using ALTER TABLE ... RECLUSTER, but that is not a command name. The correct approach is to define a clustering key and let automatic clustering handle maintenance. Thus, this option is invalid.