Snowflake SnowPro Core Exam Dumps and Practice Test Questions Set 3 Q41-60

Visit here for our full Snowflake SnowPro Core exam dumps and practice test questions.

Question 41 

In Snowflake, which feature allows customers to reduce storage costs by automatically moving cold data to a lower-cost storage layer while keeping it fully queryable?

A) External Tables
B) Materialized Views
C) Automatic Clustering
D) Search Optimization Service

Answer: A

Explanation 

External tables represent data that stays in cloud object storage while remaining fully queryable from Snowflake. They provide a way to avoid storing older or less frequently queried data inside Snowflake’s internal micro-partitions. This allows organizations to significantly reduce storage expenses because the data resides in inexpensive cloud storage tiers without sacrificing accessibility. When queried, external tables operate through metadata-based mechanisms that map external files to table structures and allow standard SQL access. They are therefore well-suited for scenarios where cold data must be retained but not frequently processed.

Materialized views store precomputed data to accelerate performance for repeated query patterns. Although useful for performance, they increase storage consumption rather than reduce it. Since materialized views physically persist query results, they are unsuitable for lowering storage cost or managing cold data economically.

Automatic clustering optimizes the physical layout of table micro-partitions so that data remains well-organized for filtering and pruning. While this feature improves performance and reduces the need for manual maintenance, it does not manage cold data tiers or minimize storage costs through offloading. It simply controls structural organization rather than relocating aged data.

Search optimization service is designed to accelerate highly selective lookup patterns by improving micro-partition indexing. It enhances search-heavy workloads but does not manage storage tiers. Instead, it increases performance at the cost of additional storage.

External tables enable customers to store archived or infrequently queried datasets outside Snowflake’s storage layer, resulting in lower costs while maintaining query accessibility through metadata mapping. This makes them the correct answer.

Question 42 

Which Snowflake feature enables writing Python code directly in the warehouse to process data without external compute infrastructure?

A) Streams
B) Snowpark
C) Replication
D) Resource Monitors

Answer: B

Explanation 

Streams track changes to tables by recording row-level inserts, updates, and deletes. Although they are essential for building CDC pipelines and incremental processing, they do not allow execution of Python code or in-warehouse programming logic. Streams serve as metadata change trackers rather than compute frameworks.

Replication synchronizes databases, shares, and other objects across regions or cloud platforms for business continuity and failover. While critical for resilience, replication does not support programmatic processing, nor does it provide a compute runtime for programming languages. It focuses solely on synchronizing metadata and data snapshots.

Resource monitors help control and track credit usage across warehouses. They enable setting thresholds, sending notifications, and optionally suspending warehouses. Their purpose is cost governance, not data processing or the execution of user-defined code.

Snowpark is Snowflake’s developer framework that allows processing data directly within Snowflake using Python, Java, and Scala. With Snowpark, developers can push transformations and business logic into Snowflake’s compute engine and avoid external clusters. It integrates with UDFs, stored procedures, and DataFrame APIs, enabling end-to-end data engineering within the Snowflake platform. Because Snowpark provides the ability to execute Python inside Snowflake, it is the correct answer.

Question 43 

Which Snowflake feature helps prevent accidental credit overuse by allowing administrators to set thresholds and receive alerts?

A) Time Travel
B) Resource Monitors
C) Fail-safe
D) Clustering Keys

Answer: B

Explanation 

Time Travel allows queries and recovery of data as it existed in the past. It protects against accidental data modification or deletion but does not help with credit management, alerting, or controlling warehouse costs. Its function is data restoration rather than financial governance.

Fail-safe offers a final layer of data recovery for disaster scenarios but is not controllable by users and does not involve notifications, threshold monitoring, or credit oversight. It remains strictly a disaster recovery safeguard and cannot regulate compute usage.

Clustering keys improve micro-partition organization for tables with large filtering patterns. They may help performance but have no relationship to financial thresholds, billing alerts, or preventing credit overuse. Their influence is technical rather than budget-related.

Resource monitors are designed specifically for credit governance. They allow Snowflake administrators to define credit thresholds and receive alerts when usage exceeds predefined levels. When configured, they can trigger actions such as sending notifications or automatically suspending warehouses. Because resource monitors provide explicit protection against unintended credit consumption, they are the correct answer.

Question 44 

In Snowflake, which feature enables efficient identification of rows matching highly selective search filters?

A) Query Acceleration Service
B) Search Optimization Service
C) Clustering Keys
D) External Functions

Answer: B

Explanation 

Query acceleration service improves performance for large, complex queries using additional compute overlays behind the scenes. It does not specifically target selective filtering scenarios or indexing-like operations, nor does it optimize micro-partition lookup paths.

Clustering keys enhance micro-partition organization for tables with predictable filtering patterns. While they help reduce scanned data, they do not provide the highly granular metadata structures required for ultra-selective search operations. Clustering improves partition layout but does not guarantee fine-grained search acceleration for pinpoint lookups.

External functions allow Snowflake to call out to external services or APIs during query execution. They extend Snowflake’s capabilities but do not optimize search paths or micro-partition metadata. Their purpose is integration, not query performance for selective lookups.

Search optimization service provides specialized metadata indexing that enables Snowflake to rapidly identify matching micro-partitions for selective queries. It significantly reduces the amount of data scanned for conditions involving unique values, semi-structured searches, and lookup-heavy workloads. Because this service is designed explicitly for high-selectivity search patterns, it is the correct answer.

Question 45 

Which Snowflake feature enables incremental ingestion by tracking changes to source tables?

A) Streams
B) Tasks
C) Stages
D) Data Sharing

Answer: A

Explanation 

Tasks automate scheduled execution of SQL statements, pipelines, and other automated operations inside Snowflake. While tasks can orchestrate incremental ingestion processes, they do not track underlying changes themselves. They depend on another mechanism to detect data modifications.

Stages function as storage locations for data files before ingestion. They can be internal or external but do not identify modified rows or monitor inserts, updates, or deletes within tables. Their role is file staging, not change detection.

Data sharing distributes live datasets to other Snowflake accounts, enabling zero-copy access. Although powerful for collaboration, sharing does not track modifications or support incremental ingestion logic. It simply exposes data for consumption.

Streams capture row-level changes in tables by recording inserts, updates, and deletes since the last time the stream was consumed. They form a CDC mechanism that enables incremental pipelines and eliminates the need for full table reloads. Because streams provide direct change tracking, they are the correct answer.

Question 46 

Which Snowflake feature allows scheduling of SQL statements to run automatically without external orchestration tools?

A) Streams
B) Tasks
C) Zero-Copy Cloning
D) File Formats

Answer: B

Explanation 

Streams provide change tracking for incremental ingestion by recording row-level modifications, but they cannot execute SQL on a schedule. Their purpose is strictly to supply the delta data that another process will consume. They do not function as automation or scheduling components.

Zero-copy cloning creates instant, metadata-based copies of databases, schemas, and tables. This feature is intended for environment isolation, testing, recovery, and experimentation, not job orchestration. Cloning does not trigger any automatic execution or scheduling functionality.

File formats define the structure and interpretation rules for staged files. They include settings such as type, delimiter, compression, and field behavior. Although essential for data ingestion, they play no role in scheduling or automating SQL processing steps.

Tasks enable Snowflake to run SQL statements on a recurring schedule or in a dependency-driven chain. Tasks can execute queries, transformations, and pipeline operations without needing an external scheduler. They support cron-like scheduling, event-driven models, and orchestration of multiple tasks. Because tasks are designed explicitly for automated execution, they are the correct answer.

Question 47 

Which Snowflake object stores credentials and connection information for integrating with external cloud storage?

A) External Function
B) Storage Integration
C) Resource Monitor
D) Masking Policy

Answer: B

Explanation 

External functions allow Snowflake to call external APIs or services during query execution. Although they require network integration, they do not manage credentials for cloud storage platforms such as AWS, Azure, or GCP. Their focus is remote computation, not storage connectivity.

Resource monitors track compute credit usage and enforce thresholds. They help control spending but do not store authentication details or manage data access to third-party storage systems. Their primary goal is governance, not connectivity.

Masking policies apply dynamic data protection for sensitive fields. They are used during query execution to mask values such as PII based on user roles. They have no relation to authentication or integration with external storage locations.

Storage integrations provide a secure and controlled method for Snowflake to access cloud storage. They encapsulate cloud authentication, access roles, and configuration details, ensuring secure loading and unloading of data. They also reduce the need for users to manage sensitive credentials directly. Because storage integrations are specifically designed for secure cloud storage connectivity, they are the correct answer.

Question 48 

Which Snowflake feature is used to monitor and maintain performance for tables with large filtering workloads?

A) Clustering Keys
B) Time Travel
C) Snowpipe
D) Row Access Policies

Answer: A

Explanation 

Time Travel enables historical data retrieval and object restoration. It helps recover from accidental modifications but has no influence on query performance or micro-partition organization. It operates at the temporal rather than structural level.

Snowpipe automates continuous data ingestion. It makes new files available for querying quickly but does not manage micro-partition structure or help improve filtering performance on large tables. It focuses solely on loading, not organizing.

Row access policies enforce row-level security by controlling which records are visible to specific users. These policies strictly govern access rather than optimize performance or improve pruning efficiency. Their purpose is security, not speed.

Clustering keys define expressions that guide Snowflake’s micro-partition layout. Well-defined clustering keys improve pruning efficiency, reduce scanned data, and significantly speed up queries involving large filtering operations. They help maintain consistent partition organization for large or evolving tables where natural clustering degrades. Because they directly support performance optimization for selective filters, they are the correct answer.

Question 49 

Which Snowflake capability enables the execution of external API calls within SQL statements?

A) External Functions
B) Materialized Views
C) Dynamic Tables
D) Fail-safe

Answer: A

Explanation 

Materialized views in Snowflake are designed to store precomputed query results, allowing the system to accelerate performance for repeated or computationally expensive queries. Their primary purpose is to reduce the workload on virtual warehouses by caching results that would otherwise need to be recalculated every time a user runs the underlying query. While they are extremely effective for optimizing analytical workloads and improving query response time, materialized views operate entirely within Snowflake’s internal environment. They do not interact with external systems, nor do they provide any mechanism for integrating with APIs, invoking remote services, or executing code outside Snowflake. Their role is strictly tied to performance enhancement and query optimization.

Dynamic tables serve a different but equally important internal role. They provide automated, incremental data pipeline logic by keeping downstream transformation tables up to date based on changes in upstream data. Dynamic tables constantly evaluate dependent objects and refresh themselves efficiently without requiring the user to manually orchestrate complex ELT operations. Although this makes them excellent for continuous data transformation pipelines, they remain confined to Snowflake’s internal compute and storage environment. They cannot perform external communication, access external APIs, or integrate with services outside Snowflake. Their function is limited to internal automation, not extending Snowflake’s capability to call or interact with remote systems.

Fail-safe, on the other hand, is a system-level recovery feature designed to protect against catastrophic data loss. It provides an additional timing window beyond time travel to ensure that deleted or corrupted historical data can still be recovered in extreme scenarios. Fail-safe is therefore focused entirely on data protection, durability, and recovery. It does not participate in computation, data transformation, automation, or any form of external service integration. It operates at the platform durability layer, not the data-processing layer, and cannot be used to call APIs or perform remote operations of any kind.

External functions uniquely enable Snowflake to securely interact with services outside its environment. They allow Snowflake SQL queries to send data to an external endpoint—such as a custom API, cloud function, machine learning model, or proprietary external service—process the information remotely, and return the results directly into Snowflake. Using secure network policies such as API integrations, proxies, and role-based access, external functions extend Snowflake’s processing capabilities far beyond what can be achieved internally. Because they are specifically designed to call external APIs and incorporate remote computation into SQL workflows, external functions are the correct answer.

Question 50 

Which Snowflake feature supports querying semi-structured data using SQL without requiring transformation?

A) Stored Procedures
B) VARIANT Data Type
C) Virtual Warehouses
D) Tasks

Answer: B

Explanation 

Stored procedures in Snowflake are designed to encapsulate procedural or multi-step logic, typically written in languages such as JavaScript or Snowpark Python. Their primary purpose is to automate workflows, orchestrate complex operations, and implement conditional or iterative logic that would be difficult to achieve using SQL alone. While they are powerful for tasks like data pipeline coordination, automated transformations, and administrative processes, they do not play any role in defining how data is physically represented or stored. Specifically, stored procedures do not influence how semi-structured data is managed, interpreted, or accessed within Snowflake. Their domain focuses purely on programmatic execution, not data modeling or storage structures.

Virtual warehouses in Snowflake provide the compute engine required for running SQL queries, loading data, performing transformations, and supporting analytical workloads. They supply scalable, on-demand processing power that can be adjusted dynamically based on workload requirements. Although virtual warehouses are central to performance, concurrency, and query execution efficiency, they are not involved in how data formats—structured or semi-structured—are stored or represented. Their role is purely computational. They do not determine schema flexibility, indexing methods, or how nested JSON or other semi-structured formats are interpreted. Instead, they simply execute whatever query logic is submitted, relying on Snowflake’s underlying storage layer for data structure management.

Tasks in Snowflake automate the execution of SQL code on a defined schedule or in a dependency-driven chain. They are typically used for orchestrating data pipelines, incremental loads, materialized view maintenance, and periodic processing. Tasks themselves do not alter anything related to the storage, parsing, or representation of semi-structured data. Their focus is on automation, not on enabling or enhancing Snowflake’s ability to store or query JSON, XML, Avro, Parquet, or similar formats. While tasks are valuable for managing repeatable operations, they are not connected to semi-structured data support.

The VARIANT data type, however, is Snowflake’s native solution for handling semi-structured data. It allows ingestion of formats such as JSON, Avro, ORC, and Parquet without requiring transformation or schema definition upfront. Snowflake automatically organizes this data within micro-partitions, optimizes storage, and applies pruning techniques to accelerate queries. Once stored as VARIANT, nested elements can be queried using SQL combined with dot notation and built-in semi-structured functions. Because it directly enables flexible schema-on-read access and efficient storage of semi-structured content, the VARIANT data type is the correct answer.

Question 51 

Which Snowflake feature enables data engineers to build declarative, continuously updated transformation pipelines without managing orchestration logic?

A) Dynamic Tables
B) Search Optimization
C) External Tables
D) Zero-Copy Cloning

Answer: A

Explanation 

Search optimization accelerates highly selective lookup operations by enhancing metadata structures. Although useful for performance, it does not provide transformation logic, pipeline automation, or data refresh capabilities. Its focus is search efficiency rather than continuous transformations.

External tables reference data stored in cloud object storage and allow querying external files as if they were Snowflake tables. While they are essential for cost-effective data lake integration, they do not automate transformations nor track dependent refresh cycles. External tables serve as a way to access data, not to maintain transformation pipelines.

Zero-copy cloning creates instant metadata-only copies of tables, databases, and schemas without duplicating storage. Clones are valuable for testing, experimentation, and recovery, but they do not perform ongoing transformations or provide mechanisms for automated refresh operations. They offer isolated environments, not transformation pipelines.

Dynamic tables enable a declarative transformation design where Snowflake manages refresh logic automatically. Instead of manually creating tasks or incremental logic, engineers define what the table should contain, and Snowflake refreshes it based on upstream changes. This eliminates orchestration complexity and provides an intelligent, maintenance-free pipeline system. Because dynamic tables automate incremental transformations without requiring user-defined scheduling or dependencies, they are the correct answer.

Question 52 

Which Snowflake capability allows multiple consumers to query shared data without creating physical copies?

A) Data Sharing
B) Materialized Views
C) File Formats
D) Acceleration Service

Answer: A

Explanation 

Materialized views in Snowflake are designed to physically store the results of a predefined query so that repeated executions become significantly faster. They are extremely effective for optimizing analytical workloads that involve aggregation, filtering, or joining large datasets. However, their usefulness is entirely limited to compute acceleration within the same Snowflake account. They do not provide any functionality for distributing data to other accounts, nor do they allow separate consumers to access or analyze shared information without replication. Every materialized view requires additional storage because Snowflake must maintain the precomputed output, and the feature lacks any mechanism for cross-account live data access. Thus, materialized views cannot be used for the type of zero-copy data consumption described in the question.

File formats define how staged data files—such as CSV, JSON, Parquet, Avro, or ORC—should be interpreted during loading. They include instructions such as delimiters, header rows, null representations, compression algorithms, character encodings, and other ingestion rules. These settings ensure accurate parsing and ingestion, but they do not influence who can access a dataset, how many accounts can query it, or how the data is distributed. File formats are purely ingestion-focused metadata objects and offer no capabilities related to multi-consumer analytics or shared access.

Acceleration Service is intended to improve the performance of compute-intensive workloads by providing additional behind-the-scenes processing help. It works in Snowflake-managed background compute clusters to accelerate highly selective queries, especially on large tables or workloads with complex filtering. However, this service is not designed for data distribution, collaboration, or multi-account usage. It strictly affects query execution performance and has nothing to do with granting access to shared datasets.

Data sharing is the only Snowflake capability that directly enables multiple consumers—across different accounts, clouds, or regions—to query the same live dataset without the need to create physical copies. Providers expose objects such as databases, schemas, tables, and secure views through secure shares, while consumers can immediately query the shared content without performing any ETL, ingestion, or replication. The provider maintains control over permissions, object definitions, and updates, ensuring that the shared data remains consistent at all times. Because this mechanism allows multiple consumers to access the same data simultaneously without duplication, data sharing is the only feature that satisfies the requirement described.

Question 53

Which Snowflake feature allows developers to extend SQL with custom logic using Python, Java, or Scala directly in the data platform?

A) Stages
B) Search Optimization
C) Snowpark
D) Warehouses

Answer: C

Explanation 

Stages in Snowflake function purely as storage locations for holding files before they are loaded into database tables or used for unloading operations. They may reside inside Snowflake (internal stages) or outside Snowflake, such as in AWS S3, Azure Blob Storage, or Google Cloud Storage (external stages). Their job is centered on ingesting or exporting data in file form, and they provide a convenient way to interact with cloud storage systems. However, stages have no capacity to execute custom logic, perform transformations, or extend SQL with additional programming languages. They simply store files and play no role in enabling Python, Java, or Scala execution within Snowflake.

Search Optimization is designed to speed up highly selective point-lookups on large tables by creating optimized search access paths. It is especially useful when queries frequently filter on non-clustering-key columns or look for very specific rows. Although the feature improves speed for pinpoint queries, it does not offer any programmatic logic, data transformation operations, or support for external languages. Its purpose is purely performance optimization of SQL queries, not expansion of Snowflake’s compute or programming capabilities.

Warehouses provide the compute engine for executing SQL statements within Snowflake. They can scale up or down and automatically suspend when idle. However, warehouses do not provide language runtimes for Python, Java, or Scala. They are responsible only for delivering compute horsepower, not supporting advanced programming frameworks or custom logic environments. A warehouse processes queries, but it cannot extend SQL with user-defined code written in modern programming languages.

Snowpark is the framework within Snowflake that allows developers to work with Python, Java, and Scala using familiar DataFrame APIs. It lets users write custom logic that Snowflake pushes directly into its compute engine. Snowpark supports the creation of UDFs, UDTFs, and stored procedures in these languages. It also enables more flexible transformation pipelines, bringing computation to the data rather than requiring data movement. With Snowpark, developers can write advanced code natively inside Snowflake while leveraging the platform’s scalability, governance, and performance. Because Snowpark specifically enables multi-language programmability in SQL workflows, it is the correct answer.

Question 54

Which Snowflake feature helps reduce unnecessary costs by automatically suspending the computer when it becomes idle?

A) Resource Monitors
B) Auto-Suspend Setting in Virtual Warehouses
C) Stream Retention Period
D) Fail-safe

Answer: B

Explanation 

Resource monitors in Snowflake are designed to track and control credit consumption. They allow administrators to create quotas and thresholds that trigger alerts or automatically suspend warehouses when consumption rises too high. While resource monitors help prevent cost overruns, they do not control warehouse behavior based on idleness. They respond only to credit usage, not to inactivity. Therefore, they do not provide automatic suspension when no queries are running, which is the key requirement described in the question.

Stream retention periods govern how long change-tracking metadata is preserved for tables that use Snowflake streams. The feature ensures that downstream systems have a sufficient window to read incremental changes but has no influence on warehouse compute behavior. Stream retention does not play a role in cost management, compute suspension, or warehouse efficiency. It strictly affects CDC workflows, not warehouse idleness.

Fail-safe is a long-term data protection mechanism that provides an additional window beyond time travel for recovering historical data in catastrophic situations. It is purely a recovery feature and does not monitor compute utilization or responsiveness. Because fail-safe operates at the storage layer and not at the compute layer, it cannot affect warehouse suspension or cost optimization.

The Auto-Suspend feature of virtual warehouses is the only Snowflake capability designed specifically to minimize unnecessary compute costs by suspending a warehouse when it becomes idle. Users can configure the auto-suspend duration, and Snowflake will automatically halt compute resources once the warehouse detects no running or queued queries. Auto-resume ensures that the warehouse starts back up immediately when new queries arrive, maintaining a seamless user experience. This setting is essential for organizations looking to optimize cost efficiency, especially for intermittent workloads, development environments, or ad-hoc analysis. Because auto-suspend directly addresses idle warehouse behavior and eliminates wasted spending on unused compute, it satisfies the requirement stated in the question.

Question 55

Which Snowflake object helps enforce file parsing rules such as delimiters, compression types, and data structure during data ingestion?

A) Task
B) File Format
C) Stream
D) External Function

Answer: B

Explanation 

Tasks are Snowflake automation objects that execute SQL statements or procedural code on a defined schedule or dependency chain. Their purpose is to orchestrate data pipelines, maintain continuously updated tables, or automate refresh operations. While tasks play a critical role in automating workflows, they do not influence how files are parsed, interpreted, or loaded. Their scope is limited to automation logic, not ingestion configuration, file interpretation, or parsing rules.

Streams allow Snowflake to track table-level data changes—such as inserts, updates, and deletes—to support incremental ingestion and downstream processing. This change-tracking metadata lets pipelines efficiently identify deltas without scanning the full table. However, streams do not handle file parsing, do not interpret staged files, and play no part in describing file structure. Their purpose is focused entirely on change tracking within Snowflake tables.

External functions extend Snowflake’s SQL engine by enabling the execution of remote logic through defined API integrations. They send data to external services such as cloud functions, machine learning models, or custom APIs, then receive results back into Snowflake SQL queries. While external functions significantly enhance Snowflake’s integration capabilities, they do not manage file structure, data parsing, delimiters, encoding, or ingestion properties. Their purpose is remote computation, not ingestion definition.

File formats are Snowflake’s dedicated objects for defining how files should be interpreted during loading or unloading. They include settings for delimiters, skip headers, field separators, compression types, character encoding, null handling, and the structural attributes of various file types. When COPY INTO is executed, Snowflake uses the file format definition to understand how to ingest and parse data correctly. File formats ensure accuracy, consistency, and repeatability during ingestion, making them indispensable for ETL/ELT pipelines. Because file formats explicitly control how files are read during ingestion, they are the correct answer.

Question 56

What happens when a Snowflake stream reaches its retention limit for change tracking?

A) It permanently deletes all historical CDC data from the table
B) It stops tracking changes until the retention period is increased
C) It continues tracking only new changes but loses unconsumed older change records
D) It automatically recreates a new stream with extended retention

Answer: C

Explanation: 

A statement claiming that Snowflake permanently deletes historical CDC data from the table when a stream reaches its retention limit is inaccurate. Stream retention applies only to the change-tracking metadata associated with streams. It does not modify, delete, or alter the actual data stored in the underlying table. Snowflake stores table data in micro-partitions, and those micro-partitions remain intact regardless of stream retention expiration. Therefore, the expiration of retention does not affect table-level historical data.

Another incorrect interpretation is that a stream stops tracking new changes once the retention limit is reached. Snowflake streams continue to capture new CDC records automatically as long as the table remains active. The expiration of older change records does not pause or suspend the stream’s ability to record new events. The stream continues functioning normally, but older changes fall out of the retention window and become inaccessible.

Similarly, Snowflake does not automatically recreate a new stream or modify retention settings when the limit is reached. Snowflake never replaces user-defined objects automatically. Managing stream retention and lifecycle is entirely the user’s responsibility. If a user requires extended retention, they must explicitly adjust the configuration or create a new stream manually.

The correct understanding is that when a stream reaches its retention limit, any unconsumed historical change data outside that window becomes inaccessible. Snowflake continues to track new changes as they occur, but older CDC entries are no longer available for downstream pipelines. This means that if consumers fail to process stream records within the retention window, those records effectively “age out,” making it impossible to reconstruct the full sequence of changes from the stream. Importantly, this affects only the stream’s metadata, not the underlying table data. Snowflake enforces this behavior to prevent unchecked growth of stream metadata and to ensure efficient CDC operations. For this reason, the correct statement is that new CDC records continue to be captured, but unconsumed older records are lost once the retention window expires.

Question 57 

Which Snowflake feature allows automatic rerun of failed tasks based on a defined retry policy?

A) Snowflake Streams
B) Snowflake Tasks
C) Snowflake Warehouses
D) Snowflake Clustering

Answer: B

Explanation: 

A statement describing Snowflake Streams does not match the behavior required to support automatic task retries. Streams are purely designed to track table-level changes using change-data-capture metadata. They record inserts, updates, and deletes occurring on a table so downstream pipelines can process only the delta instead of scanning the full dataset. However, Streams do not execute operations, cannot schedule activities, and have no built-in ability to determine whether a downstream process failed or succeeded. Therefore, they provide no mechanism for retrying failed operations or orchestrating workflow execution.

When referring to Snowflake Warehouses, the explanation also does not align with orchestration retry behavior. Warehouses provide compute capacity to run SQL but do not manage the logic, scheduling, or error-handling of processing tasks. A warehouse cannot detect whether a scheduled job failed, nor can it automatically trigger a retry. Warehouses simply supply compute cycles; they do not contain logic for dependency management, workflow recovery, or error handling. Thus, warehouses are not the feature that supports retry policies.

Clustering is another feature that is unrelated to workflow execution or retries. Clustering improves query performance by maintaining micro-partition structure based on defined clustering keys. Its function is to help Snowflake prune partitions more efficiently, reducing the amount of data scanned. It neither schedules tasks nor reruns them. Clustering has no role in workflow recovery, error detection, or execution management, meaning it cannot support any retry functionality.

The correct capability that provides configurable retry behavior is Snowflake Tasks. Tasks are designed explicitly to automate SQL execution, run scheduled jobs, and orchestrate DAGs (directed acyclic graphs). A task can be configured with parameters such as a retry limit and retry interval. When a task encounters an error, Snowflake automatically attempts to rerun it within the defined retry policy. This ensures resilience, especially for pipelines that may temporarily fail due to transient errors, object contention, or dependent operations not yet available. Additionally, tasks support conditional execution, task trees, and iterative reruns, making them the only Snowflake construct capable of implementing automated recovery and retries. Their built-in logic allows complex workflows to remain reliable and self-healing, which is essential for production-grade data automation.

Question 58 

Which warehouse mode is best suited for unpredictable, bursty workloads with intermittent processing requirements?

A) Single Dedicated Warehouse
B) Multi-Cluster Warehouse
C) Serverless Compute Pool
D) Suspended Warehouse

Answer: B

Explanation: 

A single dedicated warehouse is not ideal for unpredictable, bursty, or intermittent workloads because it operates with fixed compute capacity. When sudden spikes in concurrency occur, the warehouse cannot automatically scale out to create additional clusters. Instead, queries may queue, increasing latency and slowing overall processing. While administrators can scale the warehouse up manually, this approach does not address real-time variability in demand. Therefore, a single dedicated warehouse lacks the dynamic adaptability required for bursty workloads.

A serverless compute pool offers flexible compute capacity, but it is primarily used for specific Snowflake-managed services such as Snowpipe, search optimization maintenance, dynamic tables, and background operations. It is not the standard compute model for general-purpose SQL query execution. Serverless pools are optimized for behind-the-scenes maintenance and automated features rather than unpredictable interactive workloads. They cannot replace a warehouse for broad, user-driven analytical queries, making them unsuitable for the workload described.

A suspended warehouse cannot serve bursty workloads either because a suspended warehouse has no active compute capacity. Although Snowflake can automatically resume a warehouse when new queries arrive, resumption only provides a single compute cluster. The warehouse still cannot scale out to handle bursty concurrency. It resumes quickly but cannot handle multiple simultaneous spikes in query volume, making it insufficient for unpredictable, high-variance workloads.

A multi-cluster warehouse is specifically engineered to handle workloads with erratic concurrency. It allows Snowflake to automatically add additional clusters when query demand increases and remove clusters when demand falls. This horizontal scaling ensures that workloads experience consistent performance even during unexpected spikes. With auto-scale enabled, Snowflake intelligently adjusts cluster count based on real-time load, eliminating queuing and ensuring fast execution during busy periods. When activity slows, extra clusters spin down to reduce costs. This elasticity makes multi-cluster warehouses uniquely suited for unpredictable, bursty workloads, providing both performance stability and cost efficiency.

Question 59

What is the primary advantage of using Snowflake’s query acceleration service?

A) Reducing warehouse credit consumption through automatic warehouse downscaling
B) Offloading portions of large queries to an auxiliary compute layer for faster processing
C) Eliminating the need for materialized views entirely
D) Automatically rewriting SQL to more efficient forms

Answer: B

Explanation: 

An assumption that Snowflake’s query acceleration service reduces warehouse credit consumption by scaling down compute automatically misinterprets the feature. Query acceleration does not resize or alter warehouse configuration. Scaling decisions remain entirely user-controlled. Snowflake’s service supplements compute, not substitute or resize it. Therefore, cost reduction through automatic downscaling is not one of its capabilities.

Another incorrect interpretation suggests that query acceleration eliminates the need for materialized views. These two features solve different performance challenges. Materialized views optimize repeated queries by storing precomputed results and reducing query workload for frequently queried datasets. Query acceleration, by contrast, increases parallelism and compute power behind the scenes for large, complex, one-time or varied queries. Materialized views handle workload reduction for predictable patterns, while query acceleration helps heavy ad-hoc workloads. Thus, acceleration does not replace the use of materialized views.

A suggestion that the service automatically rewrites SQL statements is also inaccurate. Snowflake’s optimizer already rewrites SQL into efficient execution plans, but query acceleration does not modify logical SQL. It focuses entirely on compute parallelism by offloading certain operations. It plays no role in rewriting user queries or changing SQL structure for efficiency.

The correct behavior of the query acceleration service is that Snowflake offloads resource-intensive parts of large queries—such as scanning, filtering, or complex joins—to an auxiliary compute layer. This allows Snowflake to process large queries more quickly without the user needing to scale the primary warehouse. By adding supplemental compute resources on demand, Snowflake enables faster execution while maintaining predictable performance, especially for workloads that scale inefficiently with warehouse resizing. This additional compute helps ensure large queries complete faster without requiring customers to permanently upgrade warehouse size. This offloading mechanism is the primary advantage of the service.

Question 60 

What occurs when a Snowflake reader account is shared with a database via a secure share?

A) It duplicates all data physically into the reader account
B) It allows full write access to modify shared data
C) It provides read-only access without copying underlying micro-partitions
D) It automatically creates a separate warehouse for the reader

Answer: C

Explanation: 

A statement suggesting that Snowflake physically duplicates data when shared with a reader account is incorrect. Snowflake’s secure sharing mechanism is metadata-based and does not copy micro-partitions into the reader account’s storage. Instead, the reader account receives access pointers to the provider’s micro-partitions. This design eliminates the inefficiency and cost of duplication, making secure sharing a near-instant and highly scalable method for distributing data.

Another incorrect interpretation is that a reader account gains write permissions on the shared data. Reader accounts are strictly read-only. They cannot modify provider data, create objects inside the shared database, or alter table definitions. Their sole purpose is to allow external consumers—who may not have their own Snowflake account—to read data without impacting the provider’s environment. Allowing write access would violate Snowflake’s security model and governance boundaries, making such an interpretation impossible.

The assumption that Snowflake automatically creates a warehouse for the reader is also inaccurate. Snowflake does not create compute resources automatically. The provider may choose to allow the reader to run queries using the provider’s warehouse, or the reader may operate its own warehouse depending on billing agreements. However, Snowflake never creates a warehouse automatically during the sharing process. Compute remains fully under user control.

The correct explanation is that a reader account receives read-only access to the shared data without any physical duplication. The reader queries the provider’s data directly through metadata-level references to micro-partitions. The provider retains full control over permissions and can revoke access instantly. This mechanism allows external organizations to analyze real-time data without ETL, replication, or copying. Because reader accounts access live shared data without duplicates and with read-only restrictions, this option correctly describes Snowflake’s secure sharing architecture.