Visit here for our full Snowflake SnowPro Core exam dumps and practice test questions.
Question 1:
Which Snowflake component is responsible for query parsing, optimization, and generating query execution plans?
A) Storage Layer
B) Cloud Services Layer
C) Virtual Warehouse
D) Metadata Layer
Answer: B
Explanation:
Snowflake is a cloud-native data platform designed with a unique multi-layered architecture that separates compute, storage, and services to enable scalability, performance, and flexibility. A key component of this architecture is the Cloud Services Layer, which plays a central role in query processing, metadata management, and overall system orchestration. Understanding the Cloud Services Layer is critical for anyone managing or using Snowflake, as it ensures that queries are executed efficiently while maintaining the platform’s security and operational consistency.
The Cloud Services Layer acts as the brain of Snowflake, coordinating operations between the storage layer, where data resides, and the compute layer, represented by virtual warehouses that execute queries. When a user submits a SQL query, the Cloud Services Layer begins by parsing the SQL to understand its intent. This parsing step includes syntax validation, semantic analysis, and identification of the objects involved, such as tables, views, or stored procedures. Once the query is parsed, the Cloud Services Layer generates an optimized execution plan. This plan determines how best to distribute tasks across compute nodes to maximize performance and minimize resource usage.
In addition to query parsing and optimization, the Cloud Services Layer handles critical metadata management. Metadata includes information about table definitions, column types, clustering keys, and table statistics. By maintaining an up-to-date metadata repository, Snowflake can make intelligent decisions about query execution, such as determining which columns need to be scanned, which partitions to prune, and how to leverage caching to reduce latency. This metadata-driven optimization is central to Snowflake’s ability to deliver high performance on large-scale datasets without requiring manual tuning.
Security is another essential responsibility of the Cloud Services Layer. Access control, authentication, and role-based permissions are enforced at this layer. When a user submits a query, the Cloud Services Layer checks their privileges to ensure they have the appropriate access rights to the requested datA) This centralized security enforcement ensures consistent policy application across all virtual warehouses and prevents unauthorized access to sensitive information.
While the Storage Layer physically holds the data in a columnar format on cloud object storage—optimized for efficient retrieval—it does not execute queries. The compute layer, consisting of virtual warehouses, provides the processing power to execute query tasks. The Cloud Services Layer orchestrates interactions between these two layers, ensuring that virtual warehouses receive the necessary data blocks from storage and that results are returned efficiently. It also monitors the status of virtual warehouses, scaling compute resources up or down based on workload demands and optimizing cost and performance.
Furthermore, the Cloud Services Layer manages system-level functions such as transaction management, caching, query result reuse, and workload monitoring. It coordinates concurrency to allow multiple users and queries to operate simultaneously without resource contention or inconsistent results.
Therefore, the Cloud Services Layer is the central component of Snowflake’s architecture. It handles query parsing, optimization, execution coordination, metadata management, security enforcement, and system monitoring, ensuring that compute and storage resources are used effectively. By separating these functions from the physical storage and compute layers, Snowflake achieves a flexible, scalable, and secure architecture that can handle complex queries on large datasets while maintaining high performance and operational efficiency. This design is a core reason why Snowflake is widely regarded as a modern, cloud-native data platform.
Question 2:
Which Snowflake feature allows seamless sharing of live data across different Snowflake accounts without copying or moving data?
A) Database Replication
B) Data Sharing
C) Data Cloning
D) Stage Objects
Answer: B
Explanation:
Data Sharing in Snowflake is a powerful feature that enables secure and real-time access to data across different Snowflake accounts without requiring data duplication or movement. Unlike traditional methods where datasets must be copied, exported, and imported into another system, Snowflake Data Sharing allows a provider account to make specific databases, schemas, or tables accessible to consumer accounts directly. This architecture ensures that consumers always access the most up-to-date data, while the provider maintains complete control over the data and its usage.
The mechanism behind Snowflake Data Sharing relies on the concept of a “share,” which is a logical object in the provider’s account. A share defines which databases, schemas, and tables are made available to specific consumers. Once a share is created, the provider grants access to one or more consumer accounts. Importantly, the shared data does not leave the provider’s account. Instead, consumers can query it directly using their own virtual warehouses, benefiting from Snowflake’s compute separation without consuming the provider’s compute resources. This approach reduces storage redundancy, optimizes resource usage, and lowers operational overheaD)
Data Sharing supports cross-region and cross-cloud scenarios, making it ideal for organizations that need to collaborate across geographically distributed teams or with external partners. Consumers can query the shared data as if it were local to their account, including joining it with their own datasets, creating views, or integrating it into their analytical workflows. The provider, meanwhile, retains full control over the share, including the ability to add or remove tables, revoke access, or change permissions at any time. Access can also be limited to read-only, ensuring that the integrity of the original data is maintaineD)
Data Sharing differs fundamentally from other Snowflake features. Database Replication, for example, involves copying entire databases to other regions or accounts, primarily for disaster recovery or high availability. While replication ensures a fully independent copy of the data, it consumes additional storage and requires ongoing synchronization. Similarly, Data Cloning creates zero-copy clones within the same account, which is useful for development, testing, or experimentation without duplicating datA) Clones are writable and independent of the original dataset, whereas Data Sharing provides read-only access. Stage objects, on the other hand, are temporary storage locations used for loading data into Snowflake tables or unloading data from tables to external storage; they are not designed for cross-account collaboration or live data access.
By enabling direct access to live data, Snowflake Data Sharing facilitates secure collaboration across departments, subsidiaries, or business partners. Organizations can share operational data, analytical datasets, or aggregated metrics without creating multiple copies, ensuring consistency and accuracy in reporting and analysis. Additionally, Data Sharing integrates seamlessly with Snowflake’s security and governance frameworks, including role-based access control and masking policies, so providers can enforce strict data usage policies while still enabling broad access.
In conclusion, Snowflake Data Sharing is a unique capability that allows providers to share live data with multiple consumer accounts securely and efficiently. It eliminates the need for data duplication, reduces storage costs, ensures consumers always access current data, and provides fine-grained control over access and permissions. By comparison, replication, cloning, and staging serve different purposes such as disaster recovery, testing, or temporary storage. Data Sharing is particularly valuable in scenarios requiring collaboration, analytics, and multi-tenant access, making it a cornerstone of Snowflake’s cloud-native data platform and its modern approach to data management.
Question 3:
What is the best practice for optimizing Snowflake query performance on a large fact table with frequent filtering on a specific column?
A) Create a clustered index on the column
B) Use a materialized view with clustering keys
C) Partition the table manually
D) Store the column in a separate table
Answer: B
Explanation:
Snowflake’s architecture differs fundamentally from traditional relational databases in its approach to data storage, indexing, and query optimization. One of the most notable distinctions is that Snowflake does not rely on conventional indexes—such as B-tree or hash indexes—to speed up query execution. In traditional systems, database administrators often create and maintain indexes to improve query performance, particularly for selective filtering and join operations. Maintaining these indexes can be cumbersome because they require additional storage, periodic rebuilding, and careful management as data changes. Snowflake avoids this complexity by leveraging its automatic data organization, which fundamentally changes how performance optimization is approacheD)
Instead of conventional indexes, Snowflake stores data in immutable micro-partitions. Each micro-partition typically contains between 50MB and 500MB of compressed data and includes metadata such as column statistics, min/max values, and counts. This metadata allows Snowflake’s query optimizer to prune irrelevant micro-partitions efficiently, reducing the amount of data scanned for queries. Because this pruning is automatic, administrators do not need to manually partition tables for performance purposes. The system inherently supports large-scale analytics without the overhead of traditional index maintenance.
For tables that are extremely large or frequently queried using selective filters, Snowflake offers the option of defining clustering keys. Clustering keys organize data within micro-partitions based on specified columns, such as a timestamp, customer ID, or region. By clustering data along one or more dimensions, queries that filter or aggregate on those columns can scan fewer micro-partitions, improving performance. Clustering is especially beneficial for time-series data or datasets that are queried by a predictable range of values. Snowflake can automatically maintain clustering as data is inserted or updated, or administrators can trigger reclustering manually to optimize storage organization.
Materialized views provide another powerful tool for performance optimization. Materialized views store precomputed results of a query and automatically update themselves as the underlying data changes. This can dramatically speed up queries that are repeatedly executed with the same filters, joins, or aggregations, as the computation is effectively pre-done and stored in a ready-to-query form. For example, a frequently accessed sales summary by region or product category can be stored as a materialized view, reducing the computational cost and latency of repeated aggregation queries.
It is also worth noting that manual columnar storage design is generally unnecessary in Snowflake because the platform is natively columnar. Each column is stored separately, enabling efficient compression and selective column scanning. This columnar design, combined with micro-partitioning, automatic clustering, and metadata-driven pruning, allows Snowflake to execute queries with minimal data scanning and maximal performance efficiency.
By combining clustering keys with materialized views, Snowflake provides administrators and analysts with powerful mechanisms to optimize query performance on large tables. Clustering ensures selective queries and aggregations scan fewer micro-partitions, while materialized views precompute expensive operations for repeated queries. Together, these features minimize query latency without requiring the complex, manual index maintenance common in traditional databases.
Question 4:
Which type of Snowflake table stores data temporarily for the duration of a session and is automatically dropped at the end?
A) Permanent Table
B) Temporary Table
C) Transient Table
D) External Table
Answer: B
Explanation:
In Snowflake, temporary tables are session-scoped objects that exist only for the duration of the user session in which they are createD) Unlike permanent or transient tables, temporary tables are automatically dropped when the session ends, eliminating the need for explicit cleanup. This behavior makes them particularly useful for storing intermediate results during complex computations, performing ad-hoc analyses, or maintaining session-specific data that does not need to persist beyond the current session.
Permanent tables, by contrast, persist in the database until they are explicitly droppeD) They are fully recoverable and benefit from Snowflake’s fail-safe mechanism, which allows recovery from accidental data loss within a specified retention perioD) Transient tables also persist until explicitly dropped, but they do not have fail-safe protection, making them suitable for temporary production data or intermediate pipelines where storage cost and recovery overhead are a concern. External tables are distinct in that they do not store data in Snowflake itself; instead, they provide a way to query structured data stored in external cloud storage systems such as Amazon S3, Azure Blob Storage, or Google Cloud Storage.
Using temporary tables reduces storage costs because the data exists only for the session’s lifetime and does not consume permanent storage. They also simplify data workflows by allowing users to stage intermediate results, perform transformations, or break down complex queries into smaller steps without impacting production tables. Temporary tables can be indexed, clustered, and queried like regular tables within the session, providing the flexibility of permanent tables without long-term storage implications.
Moreover, temporary tables are session-isolateD) Multiple users can create temporary tables with the same name in different sessions without conflicts, since each temporary table is scoped to its session. This isolation ensures that temporary computations do not interfere with other users or processes and supports safe parallel development or testing workflows.
In summary, temporary tables in Snowflake provide a lightweight, session-limited mechanism for managing intermediate or session-specific datA) They automatically clean up at the end of the session, reducing storage costs and administrative overheaD) Permanent tables persist until explicitly dropped and offer full recovery features, while transient tables persist without fail-safe protection, and external tables reference data outside Snowflake. Temporary tables are therefore ideal for short-lived computations, intermediate results, and ad-hoc analyses that do not require long-term retention, offering both efficiency and simplicity for session-based processing.
Question 5:
Which Snowflake feature allows zero-copy cloning of databases, schemas, or tables for testing or development?
A) Data Sharing
B) Cloning
C) Replication
D) Stage Objects
Answer: B
Explanation:
Cloning in Snowflake is a powerful feature that allows users to create a zero-copy replica of databases, schemas, or tables almost instantly. Unlike traditional copying methods, cloning does not duplicate the underlying data; instead, the clone shares the same storage as the source object. This approach is highly cost-efficient because no additional storage is consumed at the time of creation. Only when changes are made—whether to the source or the clone—does Snowflake allocate new storage for the modified micro-partitions. This means users can experiment, test, or develop with large datasets without worrying about the storage overhead or lengthy copy operations.
Zero-copy cloning enables agile development workflows. Developers can quickly spin up a clone of a production database or table to run tests, develop new features, or validate queries without impacting the source datA) Since the clone is fully independent from a logical perspective, any modifications to it do not affect the original, and vice versA) This makes it ideal for safe experimentation and parallel development. Clones can also be used for reporting or analytics, allowing teams to work with snapshots of production data while maintaining strict separation from operational systems.
Cloning differs from other Snowflake features such as Data Sharing, Replication, and Stage Objects. Data Sharing allows one account to provide secure, real-time access to datasets in another account without moving or copying the datA) Replication, on the other hand, physically copies databases to other regions for disaster recovery or high availability purposes. Stage Objects serve as temporary storage locations for files to be loaded into Snowflake tables. While all these features deal with data movement or accessibility, cloning is unique in providing instant, cost-efficient copies within the same account for development, testing, and experimentation.
Additionally, cloning integrates seamlessly with Snowflake’s time-travel feature. Users can create a clone as of a previous point in time, effectively capturing a historical snapshot of the datA) This capability enables rollback testing, auditing, or data analysis on prior states without affecting ongoing operations. Combined with zero-copy storage efficiency, this makes cloning a versatile tool for both operational and analytical workflows.
In summary, cloning in Snowflake offers an instant, zero-copy method to replicate databases, schemas, or tables for development, testing, and analytics purposes. It minimizes storage costs, supports safe experimentation, and allows modifications without affecting the source. Unlike Data Sharing, Replication, or Stage Objects, cloning provides in-account, fully independent copies while leveraging the same underlying storage, making it a critical feature for agile, cost-effective data operations.
Question 6:
Which Snowflake storage format provides columnar storage and automatic compression for efficient analytics?
A) JSON Files
B) Parquet Files
C) Internal Table Storage
D) CSV Files
Answer: C
Explanation:
Snowflake uses a unique, fully managed internal storage architecture that organizes table data in a columnar format. This columnar storage is highly compressed, enabling both storage efficiency and fast analytical query performance. By storing data in columns rather than rows, Snowflake can scan only the relevant columns for a given query, significantly reducing I/O and speeding up operations such as aggregations, filtering, and joins. This design is particularly well-suited for analytical workloads, where queries often target specific fields across large datasets.
Snowflake internally handles the storage management, including automatic partitioning through micro-partitions. Each table is divided into contiguous units of storage called micro-partitions, which contain between 50 MB and 500 MB of compressed datA) These micro-partitions are immutable, meaning that when updates or inserts occur, new micro-partitions are created rather than rewriting the existing ones. Snowflake also maintains metadata about each micro-partition, such as min/max column values, number of distinct values, and null counts. This metadata enables the query optimizer to prune irrelevant micro-partitions, drastically improving query performance by scanning only the necessary datA)
In addition to micro-partitioning, Snowflake supports clustering keys, which allow users to define a logical sort order for large tables to further improve query efficiency for selective filtering and aggregations. The combination of columnar storage, micro-partitions, and clustering ensures that queries are executed efficiently without manual intervention for partitioning or indexing.
While Snowflake can ingest external data formats such as Parquet, JSON, CSV, or Avro via stages, once loaded into internal tables, the data benefits from Snowflake’s optimized storage engine. The system automatically compresses, manages, and replicates the data across multiple cloud storage locations to ensure durability and high availability. Users do not need to worry about physical storage management, scaling, or tuning for query performance—Snowflake abstracts these operational concerns, providing a serverless experience for analytics.
In summary, Snowflake’s internal table storage provides a fully managed, compressed, and columnar architecture optimized for analytical workloads. With automatic micro-partitioning, clustering support, and built-in metadata for query pruning, Snowflake ensures high performance, scalability, and efficient resource utilization. While external files can be loaded into Snowflake, the internal storage engine maximizes query speed, reduces storage costs, and simplifies data management, allowing users to focus on insights rather than infrastructure.
Question 7:
What is the purpose of Virtual Warehouses in Snowflake?
A) Store data permanently
B) Execute queries and provide compute resources
C) Manage metadata and security
D) Enable zero-copy cloning
Answer: B
Explanation:
In Snowflake, Virtual Warehouses are the compute engines responsible for executing queries, performing data transformations, and loading data into tables. Each Virtual Warehouse is essentially an independent, MPP (massively parallel processing) compute cluster that scales horizontally by adding or removing compute nodes. This separation of compute from storage allows users to scale resources dynamically based on workload demands, ensuring efficient processing without impacting data storage. For example, multiple Virtual Warehouses can operate on the same dataset simultaneously without contention, enabling concurrent reporting, ETL, and analytics tasks.
A key feature of Virtual Warehouses is elasticity. Warehouses can be resized on demand—either manually or automatically via multi-cluster warehouses—to handle spikes in query volume. They can also be suspended when idle, minimizing costs, and resumed instantly when needeD) This pay-for-use model allows organizations to optimize cloud compute expenses while maintaining high performance for time-sensitive workloads.
Virtual Warehouses operate independently of Snowflake’s Storage Layer, which is responsible for storing table data in a highly compressed, columnar format across micro-partitions. The Cloud Services Layer manages metadata, security, query parsing, optimization, and execution coordination, ensuring that queries submitted to a Virtual Warehouse are efficiently planned and routeD) Importantly, operations like cloning or data sharing occur at the storage level and do not consume compute resources from Virtual Warehouses unless a query is executed against them.
By isolating compute from storage, Snowflake enables flexible workload management. Organizations can allocate dedicated warehouses for ETL pipelines, interactive analytics, or reporting without one workload affecting the performance of another. Multi-cluster warehouses further improve concurrency by automatically spinning up additional clusters to handle parallel queries when needed, then scaling down as demand decreases.
Question 8:
Which Snowflake feature allows automatic tracking of historical data for querying previous states?
A) Time Travel
B) Fail-safe
C) Stages
D) Streams
Answer: A
Explanation:
Time Travel in Snowflake is a powerful feature that allows users to access historical data at a specific point in the past. It enables querying, restoring, or cloning tables, schemas, or databases as they existed at a previous timestamp or system-defined offset. Depending on the Snowflake edition, Time Travel can retain historical data for up to 1 day (Standard Edition), 7 days (Enterprise Edition), or even 90 days in higher tiers. This capability is particularly valuable for recovering from accidental data deletions, unintended updates, or logical errors without relying on manual backups.
Using Time Travel, users can issue queries against historical versions of data, perform point-in-time restores, or create clones of tables and databases as they existed at a previous state. These zero-copy clones share underlying storage with the original data, providing a cost-efficient mechanism for creating development, testing, or auditing environments. For example, if a user accidentally deletes rows from a table, Time Travel allows recovery by simply querying or restoring the table as of a timestamp before the deletion occurreD)
While Time Travel handles short- to medium-term data recovery and auditing, Snowflake also provides Fail-safe, a separate seven-day period beyond Time Travel, designed for disaster recovery by Snowflake support. Fail-safe protects against catastrophic events and provides an additional safety net for critical datA) Unlike Time Travel, Fail-safe is not user-accessible for standard queries and cannot be used for point-in-time cloning or querying—it is strictly for recovery support.
Other features, such as Stages and Streams, serve different purposes. Stages temporarily store files for loading or unloading data, while Streams track changes for change-data-capture (CDC) scenarios. These complement Time Travel but do not replace its point-in-time recovery capabilities.
Time Travel simplifies operational tasks by providing built-in historical access without requiring complex backup scripts or additional storage management. It enhances auditing and compliance workflows by allowing users to review past states of datA) Organizations can confidently perform risky operations, knowing they can recover previous versions if needeD)
In summary, Time Travel is Snowflake’s built-in temporal feature that enables querying, restoring, and cloning historical data efficiently. Paired with Fail-safe, it provides robust protection for accidental or catastrophic data loss, while Stages and Streams address related but distinct operational needs. This makes Snowflake highly resilient and flexible for data management, recovery, and compliance.
Question 9:
Which Snowflake feature enables near real-time Change Data Capture (CDC) on tables?
A) Streams
B) Tasks
C) Stages
D) Materialized Views
Answer: A
Explanation:
Streams in Snowflake are a powerful feature designed to track data changes—specifically, inserts, updates, and deletes—on one or more tables, making these changes available for incremental processing. Unlike traditional mechanisms that require full-table scans to detect changes, Streams record only the delta, enabling efficient and accurate Change Data Capture (CDC) within Snowflake. This capability is essential for organizations that need to propagate data modifications to downstream systems, maintain data warehouses in sync, or build real-time ETL/ELT pipelines with minimal overheaD)
A Stream is associated with a source table and automatically maintains a record of all DML operations performed on that table. When a query or task consumes the Stream, it sees the changes since the last consumption, known as the “offset.” This ensures that data is processed exactly once, preventing duplicates and providing a reliable foundation for incremental data processing. Snowflake supports two primary types of Streams: standard Streams and append-only Streams. Standard Streams track all inserts, updates, and deletes, while append-only Streams only track new row inserts, which can be useful for immutable datasets.
Streams integrate seamlessly with Snowflake Tasks to automate ETL or ELT workflows. Tasks allow users to schedule SQL statements or procedural code, which can consume data from Streams and transform or load it into target tables. For example, a Task can be configured to run every few minutes, reading the changes from a Stream and applying them to an aggregated reporting table. This combination allows organizations to implement near-real-time data pipelines entirely within Snowflake, eliminating the need for external CDC tools or complex orchestration frameworks.
It is important to distinguish Streams from other Snowflake features. Stages are designed for temporary file storage, either for bulk loading data into tables or exporting data from Snowflake. Materialized Views, on the other hand, precompute query results to improve performance for frequently executed queries, but they do not track DML changes incrementally. Streams specifically focus on capturing and exposing row-level changes to support incremental processing, auditing, and replication scenarios.
Using Streams provides several advantages. First, it reduces the compute cost associated with scanning entire tables, since only the changes are processeD) Second, it supports accurate CDC pipelines, enabling organizations to maintain consistency between Snowflake tables and downstream systems like data marts, BI platforms, or operational databases. Third, Streams simplify development workflows by providing a declarative, native approach to change tracking, without requiring complex triggers or external middleware.
In addition, Streams are fully compatible with Snowflake’s Time Travel and cloning features. Because Snowflake retains historical data through Time Travel, Streams can be consumed at specific points in time, enabling recovery and auditing of changes. Similarly, zero-copy cloning allows developers to test incremental processing logic on cloned datasets without affecting production tables.
Question 10:
Which Snowflake feature automatically compresses data and organizes it into micro-partitions?
A) Cloud Services Layer
B) Storage Layer
C) Virtual Warehouse
D) External Tables
Answer: B
Explanation:
The Storage Layer in Snowflake is a foundational component that manages the physical persistence of all table data in a highly optimized, columnar format. This columnar organization allows Snowflake to store each column separately, improving compression efficiency and enabling analytic queries to scan only the necessary columns, rather than entire rows. Snowflake automatically compresses data using sophisticated algorithms tailored to each data type, which reduces storage footprint and enhances I/O performance. Unlike traditional databases where administrators must manually partition or index tables, Snowflake handles all storage management transparently, allowing users to focus on querying and analysis rather than tuning storage.
Within the Storage Layer, Snowflake organizes data into micro-partitions. Each micro-partition contains between 50 MB and 500 MB of uncompressed data and includes extensive metadata such as min/max values for each column, the number of distinct values, and clustering information. This metadata allows Snowflake to prune irrelevant partitions during query execution, dramatically reducing the amount of data scanneD) For example, if a query filters on a date range, Snowflake can skip all micro-partitions whose min/max values fall outside that range, minimizing unnecessary I/O and improving query performance. Clustering keys can further optimize this pruning by maintaining logical ordering in frequently filtered columns.
The Storage Layer also supports Snowflake’s zero-copy cloning and Time Travel features. When a table or database is cloned, Snowflake does not physically duplicate the datA) Instead, the clone references the same underlying micro-partitions, conserving storage and allowing rapid provisioning of test or development environments. Changes to either the source or the clone create new micro-partitions only for modified data, preserving efficiency. Similarly, Time Travel leverages the Storage Layer to maintain historical versions of micro-partitions, enabling queries, restores, or clones of past data states without manual backup processes.
It is important to distinguish the Storage Layer from other Snowflake components. The Cloud Services Layer is responsible for metadata management, query parsing, optimization, and coordinating execution across virtual warehouses. Virtual Warehouses handle compute tasks such as executing queries and transformations. External Tables allow access to files stored outside Snowflake, such as in S3, ADLS, or GCS, but do not change how the internal Storage Layer manages datA)
By combining columnar storage, automatic compression, and intelligent micro-partitioning, Snowflake achieves high performance and low storage costs while providing seamless support for advanced features like Time Travel, cloning, and efficient query pruning. This architecture allows organizations to store vast amounts of data efficiently while maintaining fast analytic query performance without the administrative overhead required in traditional databases.
Question 11:
Which Snowflake feature enables automated execution of SQL statements on a scheduled basis for tasks like ETL pipelines?
A) Streams
B) Tasks
C) Materialized Views
D) Cloning
Answer: B
Explanation:
Tasks in Snowflake allow users to schedule and automate SQL execution. They are commonly used to build ETL/ELT pipelines by combining Streams and Tasks for incremental processing. For example, a task can run every hour to load data from a staging table into production tables.
Streams track changes but do not execute SQL. Materialized Views precompute query results but do not have scheduling capabilities. Cloning is for creating zero-copy clones of datA)
Tasks provide the foundation for automated data workflows, ensuring near real-time or batch updates without manual intervention.
Question 12:
Which Snowflake object temporarily stores staged files for loading into tables?
A) Stage
B) Table
C) Stream
D) Virtual Warehouse
Answer: A
Explanation:
A Stage is a location where files (CSV, Parquet, JSON) are temporarily stored before being loaded into Snowflake tables. Stages can be internal (managed by Snowflake) or external (on S3, Azure Blob Storage, or GCS).
Tables store structured data, Streams track changes, and Virtual Warehouses execute queries.
Stages simplify ETL processes, enabling batch or continuous ingestion and reducing the complexity of moving data from external sources into Snowflake.
Question 13:
Which feature allows Snowflake to restore a dropped table within the allowed retention period?
A) Fail-safe
B) Time Travel
C) Streams
D) Cloning
Answer: B
Explanation:
Time Travel enables users to query, clone, or restore data as it existed at any previous point in time, including recovering dropped tables, schemas, or databases. This powerful feature operates within a configurable retention period that extends up to 90 days for Enterprise Edition and higher, with Standard Edition supporting up to one day of retention.
Through Time Travel, organizations can execute queries against historical data states using AT or BEFORE clauses, undoing accidental modifications, deletions, or updates without requiring traditional backup restoration processes. This capability provides immediate access to prior data versions through standard SQL syntax.
It’s crucial to distinguish Time Travel from related Snowflake features. Fail-safe provides an additional seven-day recovery period following Time Travel expiration, but this protection is accessible only through Snowflake support for disaster recovery purposes, not directly by end users. Streams capture and track incremental changes to tables for downstream processing. Cloning creates instant, zero-copy duplicates of existing objects but cannot restore previously deleted objects.
Time Travel proves essential for multiple use cases including accidental deletion recovery, compliance auditing, and debugging historical data issues. Data analysts can investigate anomalies by examining data states before problems occurreD) Compliance teams can demonstrate data lineage and access historical records for regulatory reporting. Development teams can recover from erroneous data transformations or ETL failures without complex restore procedures, significantly reducing data loss risks and recovery time objectives.
Question 14:
Which Snowflake feature enforces row-level security policies on tables?
A) Row Access Policies
B) Materialized Views
C) Streams
D) Stages
Answer: A
Explanation:
Row Access Policies enable Snowflake administrators to implement granular row-level security by controlling which table rows are visible to specific users based on predefined conditions. This powerful security mechanism evaluates user attributes, roles, or contextual information to dynamically filter data, ensuring individuals only access rows they’re authorized to view.
These policies attach directly to tables and views, applying filtering logic transparently whenever users query the protected objects. The filtering occurs automatically at query execution time, eliminating the need for complex application-level security logic or multiple table versions for different user groups.
It’s important to differentiate Row Access Policies from other Snowflake features. Materialized Views store precomputed query results to accelerate performance for repetitive analytical queries. Streams capture and track incremental data changes within tables, supporting change data capture workflows. Stages serve as temporary storage locations for data files during loading and unloading operations.
Row Access Policies strongly support Zero Trust data security principles, ensuring that sensitive information remains accessible only to authorized personnel even when multiple users share the same table. Organizations can enforce data privacy regulations, protect personally identifiable information, and implement need-to-know access controls without data duplication or architectural complexity. This capability proves essential for multi-tenant environments, regulatory compliance scenarios, and situations requiring strict data segregation while maintaining operational efficiency and centralized data management.
Question 15:
Which Snowflake feature allows pre-computation of query results to improve performance on frequently queried datasets?
A) Streams
B) Tasks
C) Materialized Views
D) Stages
Answer: C
Explanation:
Materialized Views store precomputed query results, dramatically reducing execution time for frequently run or computationally intensive queries. Unlike standard views that execute the underlying query each time they’re accessed, Materialized Views maintain persistent, physical copies of query results that update automatically when underlying base tables change, delivering near real-time performance improvements.
This automatic refresh mechanism ensures that Materialized Views remain current without manual intervention, providing users with up-to-date results while eliminating repetitive computation overheaD) The balance between freshness and performance makes them ideal for operational analytics and reporting scenarios.
It’s essential to distinguish Materialized Views from other Snowflake features. Streams capture and track incremental data changes within tables, enabling change data capture workflows for downstream processing. Tasks provide scheduling capabilities for automated SQL statement execution and workflow orchestration. Stages serve as temporary storage locations for data files during loading and unloading operations.
Materialized Views prove particularly effective for complex aggregations, multi-table joins, and analytical queries on large datasets. Dashboard queries, summary reports, and analytical workloads that repeatedly execute expensive calculations benefit significantly from precomputed results. By storing these results physically, Snowflake eliminates redundant processing, delivering sub-second response times for queries that might otherwise take minutes to execute. This optimization enables faster analytics, improved user experience, and reduced compute costs for repetitive analytical workloads.
Question 16:
Which Snowflake security feature provides multi-factor authentication for user accounts?
A) SSO Integration with SAML
B) Network Policies
C) Row Access Policies
D) Time Travel
Answer: A
Explanation:
Single Sign-On (SSO) with SAML enables Snowflake accounts to integrate seamlessly with enterprise identity providers such as Okta, Azure Active Directory, and other SAML 2.0-compliant systems. This integration facilitates multi-factor authentication (MFA) and centralizes user management, allowing organizations to enforce consistent security policies across their entire technology stack.
Through SAML-based authentication, users access Snowflake using their corporate credentials without maintaining separate passwords, streamlining the login experience while enhancing security. Identity providers handle authentication, applying organizational security requirements including MFA, password complexity rules, and access controls.
It’s important to differentiate SSO from other Snowflake security features. Network Policies restrict account access based on IP address ranges, controlling which networks can connect to Snowflake. Row Access Policies implement row-level security, determining which data rows individual users can view based on their attributes or roles. Time Travel provides data recovery capabilities by maintaining historical versions of data for querying and restoration purposes.
SAML-based SSO strengthens authentication mechanisms significantly, reducing unauthorized access risks to Snowflake environments. By leveraging enterprise-grade identity management systems, organizations eliminate password-related vulnerabilities, enforce stronger authentication requirements, and maintain centralized control over user provisioning and deprovisioning. This integration ensures that access management aligns with corporate security standards, audit requirements, and compliance mandates while improving user experience through unified authentication.
Question 17:
Which type of Snowflake table is optimized for short-lived data and has lower storage costs compared to permanent tables but without fail-safe protection?
A) Transient Table
B) Temporary Table
C) External Table
D) Permanent Table
Answer: A
Explanation:
Transient Tables are specifically designed for storing temporary or intermediate datasets that don’t require the same level of data protection as permanent tables. They offer reduced storage costs by excluding fail-safe protection—a seven-day recovery period that follows Time Travel retention—while still persisting data beyond individual user sessions.
Unlike Temporary Tables, which exist only for the duration of a specific session and are automatically dropped when the session ends, Transient Tables remain available across multiple sessions until explicitly droppeD) This persistence makes them suitable for multi-step workflows where data needs to survive beyond a single connection.
It’s crucial to understand how Transient Tables differ from other table types. External Tables reference data stored in external cloud storage platforms without importing it into Snowflake. Permanent Tables include comprehensive data protection with both Time Travel and fail-safe recovery capabilities, ensuring maximum data durability but incurring higher storage costs.
Transient Tables excel in ETL staging environments and intermediate computation scenarios where data protection requirements are lower. During data transformation pipelines, staging tables hold intermediate results that can be regenerated if lost, making fail-safe protection unnecessary. By eliminating fail-safe overhead, organizations significantly reduce storage expenses for these workloads without sacrificing the persistence needed for complex, multi-stage data processing operations. This cost-effective approach balances functionality with economy for appropriate use cases.
Question 18:
Which Snowflake feature allows querying external cloud data without loading it into Snowflake?
A) External Tables
B) Cloning
C) Streams
D) Tasks
Answer: A
Explanation:
External Tables enable users to query data residing in external cloud storage platforms—including Amazon S3, Azure Blob Storage, and Google Cloud Storage—directly without requiring data ingestion into Snowflake. This capability leverages metadata definitions that describe the structure and location of external files, allowing Snowflake to treat external data as queryable tables within its environment.
When querying External Tables, Snowflake accesses the data in its original cloud storage location, executing SQL operations as though the data were native Snowflake tables. This seamless integration provides analytical capabilities while the data remains in its source location.
It’s important to distinguish External Tables from other Snowflake features. Cloning creates instant, zero-copy duplicates of internal Snowflake tables, schemas, or databases but operates only on data already stored within Snowflake. Streams capture and track incremental changes to table data, supporting change data capture patterns. Tasks provide scheduling functionality for automated SQL statement execution and workflow orchestration.
External Tables prove particularly valuable for integrating cloud data lakes with Snowflake’s analytical engine without duplicating storage. Organizations can maintain their data lake architectures while leveraging Snowflake’s powerful query capabilities, avoiding the time, cost, and complexity of copying massive datasets. This approach supports hybrid architectures where raw data remains in cost-effective object storage while benefiting from Snowflake’s performance and analytical features.
Question 19:
Which Snowflake feature provides automatic scaling of compute resources to handle concurrent workloads efficiently?
A) Multi-cluster Virtual Warehouses
B) Stages
C) Materialized Views
D) Streams
Answer: A
Explanation:
Multi-cluster Virtual Warehouses provide automatic, dynamic scaling capabilities that adapt to fluctuating query loads and varying numbers of concurrent users. This intelligent scaling mechanism adds or removes compute clusters automatically, ensuring consistently high performance without requiring manual intervention or warehouse resizing.
When query volumes surge or user concurrency increases, additional clusters spin up seamlessly to distribute the workloaD) Conversely, during quieter periods, clusters scale down to minimize unnecessary compute costs. This elasticity is fundamental to Snowflake’s architecture, enabling organizations to handle unpredictable demand patterns efficiently.
It’s essential to differentiate Multi-cluster Warehouses from other Snowflake components. Stages serve as storage locations for data files during loading and unloading operations. Materialized Views store precomputed query results, accelerating performance for repetitive analytical queries. Streams capture and track incremental data changes within tables, supporting change data capture workflows.
Multi-cluster Virtual Warehouses uniquely address the performance-cost optimization challenge. By dynamically scaling compute resources in response to actual demand, Snowflake maintains low query latency and prevents resource contention during peak workloads while avoiding wasteful spending during low-activity periods. This automatic scaling ensures that users experience consistent performance regardless of workload variability, making it a cornerstone of Snowflake’s ability to deliver enterprise-grade data warehousing with optimal resource utilization and cost efficiency.
Question 20:
Which Snowflake feature ensures high availability and disaster recovery across cloud regions?
A) Database Replication
B) Streams
C) Materialized Views
D) Cloning
Answer: A
Explanation:
Database Replication is a critical feature that replicates entire databases across different regions or accounts, providing robust disaster recovery and business continuity capabilities. This mechanism ensures that organizations can maintain operations even during catastrophic failures by creating synchronized copies of their databases in geographically separate locations.
When primary systems experience outages, replicated databases serve as immediate failover targets, minimizing downtime and data loss. This cross-region replication is essential for meeting stringent business continuity requirements and regulatory compliance standards.
It’s important to distinguish Database Replication from other Snowflake features. Streams capture and track data changes within tables, enabling incremental processing. Materialized Views store pre-computed query results to optimize performance for frequently executed queries. Cloning creates instant, zero-copy duplicates of databases, schemas, or tables, but operates only within the same account and isn’t designed for disaster recovery purposes.
Database Replication stands apart as Snowflake’s primary solution for geographic redundancy. By maintaining consistent database copies across multiple regions, it protects against region-level failures, natural disasters, or infrastructure problems. This architectural approach directly supports Snowflake’s high availability guarantees, ensuring that critical data remains accessible and business operations continue uninterrupted regardless of localized disruptions or outages.
