Visit here for our full Snowflake SnowPro Core exam dumps and practice test questions.
Q121. A retail company wants to combine historical sales data with real-time inventory updates for analytics. Data volumes are massive, and the schema of incoming inventory messages changes frequently. Which Snowflake architecture best handles this scenario?
A Load inventory into VARIANT columns in a raw table and merge with historical tables downstream
B Flatten inventory data into structured columns at ingestion
C Enforce strict schemas to prevent schema drift
D Store inventory externally and query with external tables
Answer: A
Explanation:
Option A is correct because Snowflake’s VARIANT data type is designed to store semi-structured data, including JSON, Avro, Parquet, and XML, without requiring predefined schemas. This is crucial for real-time inventory data, which often arrives in varying structures due to different devices, vendors, or message formats. By ingesting this data into a raw table using VARIANT columns, the company preserves all original information, enabling analysts to handle schema evolution flexibly. This approach aligns with the schema-on-read principle, which allows querying without restructuring the table every time a new field appears. Downstream, data can be merged with historical sales tables using Snowflake’s MERGE command, ensuring incremental updates are handled efficiently. This minimizes storage costs and leverages Snowflake’s micro-partitioning and clustering optimizations for fast query performance.
Option B, flattening data into structured columns at ingestion, might seem appealing for traditional ETL, but it introduces rigidity. Any addition or removal of fields in the incoming payload requires schema alterations, potentially causing ingestion failures and operational delays. Option C, enforcing strict schemas, is counterproductive in scenarios where payloads evolve frequently. It risks rejection of valid incoming data, increasing manual maintenance. Option D, storing data externally and querying with external tables, bypasses Snowflake’s native optimizations such as automatic partition pruning and clustering, leading to slower query performance and higher costs.
In summary, A offers the optimal combination of flexibility, scalability, and efficiency. Using VARIANT columns for raw data ensures no loss of detail, supports schema evolution, and integrates seamlessly with downstream ETL pipelines. Snowflake’s ability to handle semi-structured data natively provides a robust solution for combining historical and real-time analytics in large-scale environments. By contrast, B, C, and D either increase operational complexity or compromise performance. The architecture in A ensures that both analysts and engineers can access reliable, query-ready data while maintaining agility to adapt to changing inventory formats. This approach aligns perfectly with modern data lakehouse strategies and best practices for hybrid structured and semi-structured workloads.
Q122. An insurance company needs to provide different analysts with access to sensitive claims data, but some fields should be hidden based on the user’s role. Which Snowflake feature provides the most secure and flexible solution?
A Secure views with dynamic data masking and row access policies
B Read-only access to raw tables
C Materialized views with aggregated metrics
D Store anonymized copies externally
Answer: A
Explanation:
Option A is correct because Snowflake combines secure views, dynamic data masking (DDM), and row access policies to enforce sophisticated, role-based access controls. Secure views prevent users from bypassing permissions by blocking underlying table access, while DDM dynamically masks sensitive columns—such as social security numbers, medical identifiers, or financial details—based on the querying user’s role. Row access policies further restrict visibility at the record level, enabling multi-tiered access. This layered approach ensures that analysts can work with usable data without ever exposing confidential information, which is critical for insurance companies handling personally identifiable information (PII) or other sensitive records. It also ensures compliance with regulatory standards, including HIPAA, GDPR, and local data privacy regulations.
Option B, read-only access to raw tables, is insufficient because it does not hide sensitive data. Even if users cannot modify records, they can still see confidential fields, potentially violating compliance requirements. Option C, materialized views with aggregated metrics, might reduce exposure but only works for summarized data. Analysts requiring row-level insights or detailed information cannot rely on aggregated views. Option D, storing anonymized copies externally, introduces additional pipelines, storage costs, and latency while complicating security auditing. It does not leverage Snowflake’s native capabilities for access control and is less maintainable at scale.
Overall, A offers the most robust combination of security, flexibility, and maintainability. Secure views, dynamic data masking, and row-level policies work together to protect sensitive claims data while providing analysts with necessary access. This approach minimizes operational overhead, avoids redundant data copies, and leverages Snowflake’s cloud-native capabilities for both data governance and analytics efficiency. By contrast, B, C, and D are either insecure, operationally complex, or limiting in terms of analytical flexibility. Implementing A ensures that sensitive information is protected without compromising the agility of the analytics teams, making it ideal for regulated industries like insurance, healthcare, or finance.
Q123. A logistics company wants to track incremental changes in large order tables for downstream analytics. They need high reliability and minimal scanning. Which Snowflake feature is optimal for capturing changes?
A Streams on source tables
B Time Travel
C Secure views
D Manual audit tables
Answer: A
Explanation:
Option A is correct because streams in Snowflake are specifically designed to track data changes in real-time, capturing inserts, updates, and deletes on source tables. By using a stream on the order table, the logistics company can query only the delta changes, instead of scanning the full dataset repeatedly. This approach dramatically reduces compute costs and improves pipeline efficiency, especially when handling large tables with high transaction volumes. Streams maintain transactional consistency, ensuring that downstream ETL processes see a coherent view of changes. When combined with Snowflake tasks, streams can automate incremental ETL processes, scheduling transformations or merges without manual intervention.
Option B, Time Travel, allows querying past versions of tables but is not intended for incremental ETL. Using Time Travel to detect changes requires scanning and comparing historical snapshots, which is resource-intensive and less efficient. Option C, secure views, focus on access control and do not track table changes, making them unsuitable for incremental data processing. Option D, manual audit tables, require additional ETL logic to track inserts, updates, and deletes, increasing complexity and the risk of inconsistencies. Maintaining such tables at scale is error-prone and operationally expensive.
Streams in Snowflake provide exactly-once delivery semantics, meaning that each change is captured once and only once, ensuring reliable incremental processing. Analysts or data engineers can query streams, perform merges into downstream fact tables, and implement real-time analytics without disrupting the source system. This method aligns with modern data lakehouse architecture principles, combining flexibility, scalability, and minimal latency. Using streams also allows historical auditing when paired with Time Travel, providing both incremental processing and temporal analytics.
Therefore, option A is the most suitable for capturing incremental changes in high-transaction tables, offering operational simplicity, cost efficiency, and robust data consistency. Options B, C, and D either fail to provide true incremental capture, add unnecessary complexity, or do not scale for large datasets. Leveraging streams is considered best practice for Snowflake-native ETL pipelines dealing with high-volume, continuously changing data.
Q124. Analysts often filter large retail tables by region, product_category, and order_date. Query performance has degraded due to data growth. Which Snowflake optimization best improves performance?
A Define clustering keys on region, product_category, and order_date
B Increase virtual warehouse size
C Store data externally
D Flatten columns
Answer: A
Explanation:
Option A is correct because clustering keys in Snowflake physically organize micro-partitions based on frequently filtered columns, such as region, product_category, and order_date. By clustering on these columns, queries can efficiently prune irrelevant micro-partitions, drastically reducing the amount of data scanned and improving performance. Snowflake maintains clustering automatically during inserts, updates, and merges, ensuring that query performance remains consistent even as tables grow over time. Clustering is particularly effective for large, multi-terabyte tables where filter predicates are highly selective, enabling faster query results while controlling compute costs.
Option B, increasing warehouse size, temporarily adds compute resources but does not reduce scanned data. Queries still process the same volume, leading to higher costs without fully addressing performance degradation. Option C, storing data externally, bypasses Snowflake’s micro-partitioning and clustering optimizations, leading to slower queries and higher latency. Option D, flattening columns, does not improve query pruning and can increase storage and complexity, particularly if the dataset is already structured.
Clustering also provides long-term benefits for analytical workloads. As Snowflake automatically maintains clustered tables, data ingestion does not require constant manual reorganization. Analysts gain faster query execution for common patterns like filtering by region, aggregating by product_category, or analyzing trends over order_date. Additionally, clustering helps with materialized view maintenance and can reduce the cost of downstream dashboards and BI queries. By defining clustering keys on columns that represent frequently queried dimensions, organizations ensure predictable performance even with growing data volumes.
Therefore, option A is the optimal Snowflake optimization. It is more sustainable and cost-effective than simply scaling compute or flattening data, providing a Snowflake-native solution for large table performance challenges. Options B, C, and D either fail to address the root cause or increase operational overhead without guaranteeing improved query speed. Clustering ensures query efficiency, cost optimization, and long-term maintainability for complex analytics.
Q125. A financial company wants to maintain historical snapshots of transaction tables for audit purposes. Analysts need to query exactly as it existed months ago, but storage costs must be minimized. Which Snowflake strategy is optimal?
A Zero-copy clones combined with Time Travel
B Full physical copies for each snapshot
C Store historical data externally and reload as needed
D Maintain manual history tables during ETL
Answer: A
Explanation:
Option A is correct because combining zero-copy clones with Time Travel enables the financial company to maintain historical snapshots without duplicating storage. Zero-copy clones create a point-in-time copy of a table instantaneously, and only changes made after the clone are stored separately, minimizing storage costs. Time Travel allows querying the table as it existed at any previous timestamp within the retention period. Together, these features provide a fully functional, queryable snapshot for auditing, compliance, or reporting purposes without requiring full physical copies, which would be expensive and operationally cumbersome.
Option B, full physical copies, duplicates all historical data for every snapshot. This significantly increases storage costs, especially for large, high-transaction tables. Option C, storing data externally and reloading on demand, introduces latency and operational complexity, making it difficult for auditors or analysts to query historical data quickly. Option D, manual history tables in ETL, requires additional logic and maintenance, increasing the risk of errors and inconsistencies.
Snowflake’s zero-copy clones maintain transactional integrity and work seamlessly with Time Travel to enable point-in-time queries, supporting regulatory compliance with minimal administrative effort. Analysts can access any snapshot efficiently, without impacting performance or operational costs. Zero-copy clones also integrate with downstream pipelines, allowing transformations or aggregations on historical snapshots while preserving the original state for audit purposes. This approach is highly scalable, adaptable, and aligns with modern cloud data architecture best practices.
Therefore, option A is the optimal strategy for maintaining historical snapshots. Options B, C, and D are either costly, operationally complex, or slow, whereas A leverages Snowflake-native capabilities for efficient, secure, and cost-effective historical data access, ensuring compliance and operational simplicity.
Q126. A company wants multiple teams to query large datasets concurrently without affecting each other’s performance. Which Snowflake feature is most suitable?
A Multi-cluster warehouses with auto-scaling
B Increase warehouse size manually for each team
C Use a single warehouse for all teams
D Store data in external tables for separation
Answer: A
Explanation:
Option A is correct because multi-cluster warehouses in Snowflake allow multiple compute clusters to handle concurrent queries independently. When multiple teams run queries simultaneously, Snowflake automatically provisions additional clusters as needed through auto-scaling, ensuring that query performance remains consistent. This approach prevents workload contention and guarantees high concurrency without requiring manual intervention. The separation of compute clusters aligns with Snowflake’s elastic compute architecture, providing predictable performance while minimizing cost inefficiencies.
Option B, manually increasing warehouse size, temporarily allocates more compute but cannot isolate workloads effectively. Heavy queries from one team can still slow down others. Option C, using a single warehouse, leads to resource contention and degraded performance during peak workloads, particularly with large datasets. Option D, storing data externally, does not separate compute resources and therefore cannot prevent interference between teams, reducing performance and increasing latency.
Multi-cluster warehouses also integrate with Snowflake’s pay-per-use model, scaling compute dynamically only when needed. This ensures cost-effectiveness while supporting high-concurrency analytics. By leveraging Snowflake-native concurrency scaling, teams can query large datasets in parallel without impacting others, maintaining operational efficiency, SLA adherence, and BI dashboard performance. Option A aligns with best practices for managing concurrent workloads in cloud data platforms, ensuring reliability, scalability, and maintainability.
Thus, option A is the most suitable solution. Options B, C, and D either fail to isolate workloads, increase operational complexity, or reduce performance, making them suboptimal for high-concurrency environments.
Q127. An IoT company receives JSON payloads from thousands of devices. Payload structures change frequently, but analysts need both raw and curated datasets. Which Snowflake design pattern is most appropriate?
A Store payloads in VARIANT columns in a raw table and transform downstream
B Flatten payloads into structured columns at ingestion
C Enforce strict schemas on ingestion
D Store data externally and query via external tables
Answer: A
Explanation:
Option A is correct because VARIANT columns in Snowflake store semi-structured data like JSON efficiently, supporting dynamic and evolving schemas without requiring table modifications. Raw tables retain the original device payloads for audit, exploration, and future transformations. Downstream ETL pipelines can extract, transform, and load curated datasets into structured tables for analytical queries. Functions such as FLATTEN, OBJECT_INSERT, and ARRAY_AGG allow flexible processing of nested JSON, supporting both detailed analytics and aggregation. This design pattern ensures flexibility, scalability, and cost efficiency.
Option B, flattening at ingestion, introduces schema rigidity, requiring table alterations for new fields and risking ingestion failures. Option C, enforcing strict schemas, is incompatible with IoT pipelines where device data formats frequently evolve. Option D, external tables, adds latency and prevents leveraging Snowflake’s micro-partitioning, clustering, and automatic optimization, reducing performance for frequent analytics queries.
Storing IoT payloads as raw VARIANT allows engineers to retain the full fidelity of source data, supporting troubleshooting, anomaly detection, and historical analysis. Curated downstream tables provide structured, query-ready data for analysts while minimizing operational overhead. This pattern represents best practices for IoT analytics pipelines in modern data platforms.
Q128. Analysts frequently filter a large sales table by multiple columns such as region, product_category, and order_date. Query performance has degraded over time. Which optimization should be applied?
A Define clustering keys on frequently filtered columns
B Increase warehouse size
C Use external tables
D Flatten all columns
Answer: A
Explanation:
Option A is correct because clustering keys physically organize Snowflake’s micro-partitions by selected columns, improving query pruning. Queries filtering on region, product_category, and order_date will scan fewer partitions, enhancing performance. Automatic maintenance ensures clustering persists as new data is ingested. Option B temporarily increases compute but does not reduce scanned data. Option C, external tables, bypass Snowflake’s partitioning, slowing queries. Option D does not improve pruning or efficiency.
Q129. A healthcare organization needs to provide analysts access to patient data while complying with HIPAA. Certain columns must be masked based on role. Which Snowflake solution is ideal?
A Secure views with dynamic data masking and row access policies
B Materialized views with aggregated metrics
C Read-only access to raw tables
D Store anonymized copies externally
Answer: A
Explanation:
Option A is correct because the combination of secure views, dynamic data masking (DDM), and row access policies delivers comprehensive, flexible, and maintainable role-based data governance that precisely controls both record-level visibility and field-level sensitivity according to user roles and responsibilities within healthcare organizations. Row access policies implement granular row-level security by evaluating user context including current role, session parameters, or organizational attributes to dynamically filter query results, ensuring that clinical staff see only patients within their assigned departments or care teams, researchers access only de-identified cohorts relevant to approved studies, and billing personnel view only records requiring payment processing, all without requiring separate physical copies of data or complex view hierarchies for each user segment. Dynamic data masking complements row filtering by selectively obscuring sensitive patient identifiers including Social Security numbers, medical record numbers, precise addresses, and birth dates based on querying user’s role, transforming these protected health information (PHI) elements into masked representations like partial strings, hashed values, or generalized categories that preserve analytical utility while preventing unauthorized identification of specific individuals, ensuring strict HIPAA compliance that mandates minimum necessary access principles. Secure views encapsulate these policies within reusable database objects that abstract complexity from end users and applications, enabling governance teams to centrally define and maintain security rules that automatically enforce across all consuming queries, reports, and analytical tools without requiring application-level security logic or risking inconsistent implementations across diverse access patterns.
Option B restricts users to only pre-aggregated metrics and summary statistics, eliminating access to detailed record-level data entirely, which proves excessively restrictive for legitimate clinical workflows requiring individual patient details for care coordination, detailed research analyses examining specific case characteristics, quality improvement initiatives investigating particular adverse events, or operational processes like appointment scheduling and insurance verification that fundamentally depend on accessing complete patient information within appropriate authorization boundaries rather than aggregated summaries. Option C employs only row-level filtering without masking sensitive identifier fields, leaving protected health information fully exposed to any user granted access to filtered records, creating unacceptable HIPAA violation risks where personnel viewing records within their authorized scope still see unnecessary PHI elements beyond minimum necessary requirements for their specific job functions. Option D proposes creating separate database instances or schemas for each role, introducing overwhelming operational complexity requiring duplicate data storage consuming excessive cloud costs, complex synchronization mechanisms ensuring consistency across copies, fragmented governance where security policies must be redundantly defined and maintained across multiple isolated environments, and administrative burden managing proliferating database objects that scales unsustainably as organizations add roles or modify access requirements. Therefore, secure views incorporating dynamic data masking and row access policies provide the most flexible, comprehensively secure, operationally maintainable, and Snowflake-native governance framework specifically designed for healthcare environments requiring sophisticated multi-dimensional access controls that simultaneously enforce role-based record filtering and context-sensitive field masking while maintaining centralized policy management and ensuring consistent HIPAA-compliant data protection across diverse user populations and analytical use cases.
Q130. A company wants to implement an incremental ETL pipeline that only processes changed data in a high-transaction table. Which Snowflake feature is optimal?
A Streams on source tables
B Time Travel
C Secure views
D Manual audit tables
Answer: A
Explanation:
Option A is correct because Snowflake streams provide purpose-built change data capture (CDC) functionality that efficiently tracks all data manipulation operations—inserts, updates, and deletes—occurring at source tables, enabling streamlined incremental ETL processes that process only modified data rather than performing full table scans or complete data reloads on every execution cycle. Streams maintain metadata recording change information between defined points in time, allowing downstream transformation queries to consume exclusively the delta of modifications since the last successful processing, dramatically reducing compute resource consumption, accelerating ETL execution times, and minimizing data transfer volumes compared to approaches requiring complete dataset reprocessing. This delta-based processing proves particularly valuable in high-transaction environments where source tables experience continuous modifications but only small percentages of total records change between ETL cycles, making full table processing wastefully inefficient. Streams guarantee transactional consistency through Snowflake’s ACID compliance, ensuring that all changes within a transaction are captured atomically and presented cohesively to consuming processes, while providing exactly-once semantics that prevent duplicate processing or missed changes even when ETL processes experience failures and require restarts, maintaining data integrity throughout complex multi-stage transformation pipelines without requiring custom checkpointing logic or complex state management implementations.
Option B incorrectly identifies Time Travel as an incremental ETL solution, when Time Travel actually enables querying historical table states at specific timestamps or before specific statements, serving purposes like auditing past data values, recovering accidentally deleted records, or analyzing historical trends, but lacking the change tracking metadata and delta identification capabilities that streams provide for efficient incremental processing workflows. Option C mischaracterizes secure views, which implement row-level or column-level access control policies restricting data visibility based on user roles, session contexts, or query attributes to enforce data governance and privacy requirements, but secure views offer no functionality for tracking data changes, identifying modifications, or facilitating incremental ETL operations. Option D proposes manual audit tables requiring custom trigger-like implementations or application-level logic to explicitly record change events, introducing significant development complexity, ongoing maintenance burden, potential reliability risks from implementation errors, performance overhead from additional write operations, and consistency challenges ensuring audit records perfectly synchronize with source table modifications across concurrent transactions and failure scenarios. Therefore, streams represent the most reliable, operationally efficient, and Snowflake-native solution specifically architected for incremental ETL workflows in high-transaction database environments requiring change data capture capabilities.
- A Snowflake user wants to optimize storage for large historical data that is infrequently queried but must be retained for compliance. Which Snowflake feature is most suitable?
A Time Travel
B Fail-safe
C Transient tables
D External tables
Correct Answer: C
Explanation:
Option C is correct because transient tables are designed for data that does not require long-term Time Travel or Fail-safe capabilities. They eliminate the additional storage overhead associated with Fail-safe, making them ideal for infrequently queried historical data where cost optimization is crucial. By using transient tables, organizations can maintain compliance-level data without incurring the higher costs of standard tables while still benefiting from the full Snowflake query capabilities.
Option A is not correct because Time Travel provides historical query access, but storing large datasets with full Time Travel retention increases storage costs. For infrequently queried data, this would be inefficient.
Option B is not correct because Fail-safe is a recovery mechanism that retains data for seven days after deletion. It is designed for disaster recovery and cannot be used as a cost-saving feature for infrequently queried historical data.
Option D is not correct because external tables store data outside of Snowflake, typically in cloud object storage. While this may save compute costs, it does not provide the same seamless query capabilities or integration as transient tables and can complicate security and governance for compliance data.
Using transient tables balances storage cost optimization and compliance retention requirements while providing full query functionality without unnecessary overhead.
- A Snowflake administrator notices frequent contention in a multi-user warehouse environment during peak hours. Which approach is the most effective to reduce concurrency issues?
A Multi-cluster warehouse scaling
B Increasing Time Travel retention
C Using transient tables
D Clustering keys
Correct Answer: A
Explanation:
Option A is correct because multi-cluster warehouse scaling allows Snowflake to automatically add or remove compute clusters based on query concurrency. When multiple users submit queries simultaneously, additional clusters handle the load, reducing wait times and resource contention. Multi-cluster warehouses ensure that each query receives sufficient compute resources without impacting other users, making it the most effective concurrency optimization strategy.
Option B is not correct because increasing Time Travel retention affects storage but has no impact on concurrent query execution.
Option C is not correct because transient tables are focused on cost optimization and retention policies; they do not resolve query concurrency issues.
Option D is not correct because clustering keys optimize scan efficiency for large tables but do not address the problem of multiple simultaneous queries vying for the same compute resources.
Therefore, configuring a multi-cluster warehouse provides the optimal method to handle peak-hour concurrency while maintaining query performance and user experience.
- A company wants to track incremental changes in a large table for downstream ETL pipelines without querying the entire table. Which Snowflake-native feature should be used?
A Streams
B Time Travel
C Tasks
D Cloning
Correct Answer: A
Explanation:
Option A is correct because streams track changes (inserts, updates, and deletes) on a table efficiently. This allows downstream ETL pipelines to consume only the delta rather than scanning the entire dataset. Streams maintain metadata about which rows have changed, ensuring minimal compute usage and low-latency processing. They can be combined with Snowflake tasks for automated, scheduled processing, enabling a fully incremental pipeline that is both cost-effective and performant.
Option B is not correct because Time Travel provides access to historical versions of the data but requires scanning large tables for incremental differences, which is less efficient for ETL.
Option C is not correct because tasks automate query execution but do not inherently track data changes; they need streams or other mechanisms to identify deltas.
Option D is not correct because cloning creates a point-in-time copy of the table, which duplicates storage and does not provide an incremental tracking mechanism.
By using streams, companies can ensure ETL pipelines remain efficient, reducing query latency and storage costs while maintaining data accuracy and lineage.
- A Snowflake user wants to mask sensitive data dynamically based on the user’s role without modifying the underlying table. Which feature should be implemented?
A Dynamic data masking policies
B Row access policies
C Transient tables
D Streams
Correct Answer: A
Explanation:
Option A is correct because dynamic data masking policies in Snowflake allow sensitive columns to be obfuscated depending on the user’s role or context. Unlike static masking, which permanently changes the data, dynamic masking is transparent to the query and preserves the underlying table. This ensures that authorized users can still see unmasked values while unauthorized users see masked versions. Dynamic masking is crucial for compliance scenarios such as GDPR or HIPAA.
Option B is not correct because row access policies restrict access to rows, not specific column values, and cannot selectively mask PII data.
Option C is not correct because transient tables affect storage retention and cost but do not enforce data masking.
Option D is not correct because streams track changes for ETL purposes but have no role in access control or masking.
Dynamic masking provides a secure, flexible solution that does not require structural changes to tables while maintaining compliance and operational transparency.
- A data engineer needs to optimize join performance on two large tables with predictable query patterns. Which approach offers the greatest reduction in scan costs?
A Clustered tables on frequently joined columns
B Multi-cluster warehouse scaling
C External tables
D Transient tables
Correct Answer: A
Explanation:
Option A is correct because clustering tables on frequently joined columns physically sorts the data within micro-partitions. This allows Snowflake to prune unnecessary partitions during joins, reducing the number of scanned bytes and improving query performance. This is particularly effective for large datasets with predictable access patterns and repeated queries. Clustering can dramatically reduce I/O, compute time, and query latency.
Option B is not correct because multi-cluster warehouses manage concurrency, not I/O optimization. While useful for multiple users, they do not reduce scan costs for individual queries.
Option C is not correct because external tables query data stored outside Snowflake, which often introduces latency and higher scan costs.
Option D is not correct because transient tables only reduce storage overhead, not query efficiency or scan cost.
Clustering frequently joined columns is the optimal approach for query efficiency in large-scale analytics scenarios.
- A Snowflake administrator wants to implement row-level security so that users can only see data relevant to their department. Which feature should be used?
A Row access policies
B Masking policies
C Time Travel
D Transient tables
Correct Answer: A
Explanation:
Option A is correct because row access policies enforce dynamic, fine-grained row-level security based on user roles, session variables, or attributes. They filter rows transparently without duplicating data, allowing users to only access data permitted for their department. This method is highly scalable and maintains a single source of truth for organizational data.
Option B is not correct because masking policies only obfuscate column values and cannot restrict row-level visibility.
Option C is not correct because Time Travel is for historical data access and does not provide security controls.
Option D is not correct because transient tables focus on storage lifecycle, not access control.
Row access policies are therefore the most effective way to implement secure, department-based data access while maintaining operational simplicity.
- A data team wants to reduce compute costs while ensuring warehouses suspend automatically during idle periods. What is the recommended approach?
A Enable auto-suspend with a defined idle timeout
B Increase warehouse size
C Convert all tables to transient
D Use network policies to block idle users
Correct Answer: A
Explanation:
Option A is correct because Snowflake’s auto-suspend feature automatically stops a warehouse after a defined period of inactivity, reducing unnecessary compute charges. By combining auto-resume, warehouses restart automatically when queries arrive. This approach balances cost optimization and availability, particularly in environments with sporadic query patterns. Administrators can fine-tune the idle timeout to minimize costs without impacting performance.
Option B is not correct because increasing warehouse size only increases compute cost and does not control idle usage.
Option C is not correct because transient tables impact storage retention but do not reduce compute costs related to idle warehouses.
Option D is not correct because network policies manage access but cannot suspend idle compute resources.
Auto-suspend is the most effective method for controlling warehouse costs while maintaining seamless user experience.
- A Snowflake engineer wants to maintain near real-time data pipelines from S3 with minimal latency. Which solution is best?
A Snowpipe with event notifications
B Scheduled COPY INTO
C External tables with manual refresh
D Transient tables
Correct Answer: A
Explanation:
Option A is correct because Snowpipe with event notifications allows event-driven ingestion. Whenever files arrive in S3, notifications trigger Snowpipe to ingest them automatically, achieving near real-time data availability. This minimizes latency compared to scheduled jobs or manual refresh and reduces compute costs by processing only new files. Snowpipe can also integrate with Snowflake tasks for downstream ETL pipelines.
Option B is not correct because scheduled COPY INTO introduces fixed-latency ingestion based on the schedule and is inefficient for real-time data needs.
Option C is not correct because external tables rely on manual refresh or polling, which adds delay and reduces pipeline responsiveness.
Option D is not correct because transient tables optimize storage costs but do not enable near real-time ingestion.
Snowpipe with event notifications offers the most efficient real-time ingestion strategy for Snowflake pipelines.
- A developer wants to query historical snapshots of a table without duplicating storage. Which Snowflake feature should be used?
A Time Travel
B Streams
C External tables
D Transient tables
Correct Answer: A
Explanation:
Option A is correct because Time Travel represents Snowflake’s native versioning capability that provides seamless access to historical versions of tables, enabling users to query previous data states at specific timestamps or before particular data manipulation statements without requiring duplicate storage infrastructure, manual snapshot creation, or complex versioning schemes that would exponentially increase storage costs and administrative complexity. Time Travel leverages Snowflake’s underlying micro-partition architecture where the platform maintains metadata tracking changes to micro-partitions over time rather than duplicating entire table copies for each modification, creating a storage-efficient versioning system where historical access incurs minimal additional storage overhead beyond the actual changed data within modified micro-partitions. This capability enables critical use cases including rollback operations that restore tables to previous states after accidental deletions, erroneous updates, or problematic data loads; comprehensive auditing workflows that examine how data values evolved over time to satisfy compliance requirements, investigate data quality issues, or verify transformation logic correctness; and sophisticated analytical comparisons where business analysts or data scientists query multiple historical snapshots to analyze trends, detect anomalies, understand seasonal patterns, or validate machine learning model predictions against actual historical outcomes. Time Travel operates transparently through standard SQL query syntax using AT or BEFORE clauses that specify temporal reference points, allowing users to access historical data through familiar query patterns without learning specialized APIs or managing separate historical data repositories.
Option B incorrectly characterizes streams as providing historical table access, when streams actually serve the fundamentally different purpose of tracking incremental changes—inserts, updates, and deletes—between defined processing points to facilitate efficient change data capture for ETL workflows, but streams do not enable querying complete historical table states at arbitrary past timestamps since they maintain only forward-looking change metadata rather than preserving full table snapshots or enabling temporal queries against previous data versions. Option C misunderstands external tables, which establish virtual table definitions referencing data files stored in external cloud object storage locations like S3, Azure Blob Storage, or Google Cloud Storage, providing query access to externally managed datasets without importing data into Snowflake’s native storage, but external tables possess no inherent historical versioning capabilities since they reflect the current state of referenced external files without tracking file modifications over time or maintaining historical file versions unless external storage systems independently implement versioning features. Option D incorrectly suggests transient tables provide historical access, when transient tables actually represent a storage class optimization that reduces Time Travel retention from the standard seven days to only one day and eliminates Fail-safe data protection entirely, reducing storage costs for temporary or intermediate data that does not require extended historical retention, but transient tables fundamentally lack the capability to provide historical snapshots or extended temporal queries precisely because their reduced retention periods eliminate access to older data versions. Therefore, Time Travel provides the most storage-efficient, operationally flexible, and comprehensively Snowflake-native mechanism for querying historical data while maintaining complete table lineage, change tracking, and point-in-time recovery capabilities that support diverse business requirements spanning operational recovery, regulatory compliance, and analytical insight generation across enterprise data warehouse environments.
- A company needs to track incremental ETL changes from a production table and trigger transformations automatically in Snowflake. Which approach is optimal?
A Streams with tasks
B Scheduled COPY INTO
C Time Travel with manual extraction
D External tables with periodic refresh
Correct Answer: A
Explanation:
Option A is correct because the strategic combination of Snowflake streams with Snowflake tasks creates a powerful, fully automated incremental ETL architecture where streams continuously track all data manipulation operations—inserts, updates, and deletes—occurring on source tables, while tasks execute scheduled or event-driven transformation logic that processes exclusively the captured delta changes rather than reprocessing entire datasets. Streams maintain lightweight metadata recording change information with minimal storage overhead, capturing row-level modifications along with metadata indicating operation types and timestamps, enabling downstream processes to efficiently identify and consume only records that have changed since the last successful processing cycle.
Tasks complement streams by providing serverless orchestration capabilities that automatically execute SQL statements, stored procedures, or transformation pipelines on defined schedules or triggered by specific conditions such as stream data availability, creating self-managing pipelines that require no external orchestration tools, manual intervention, or custom monitoring implementations. This integrated approach results in low-latency incremental pipelines that process changes within minutes of their occurrence in source systems, avoiding the inefficiency of repeatedly scanning entire production tables to detect modifications—an approach that becomes prohibitively expensive and slow as table sizes grow into billions of rows. By processing only deltas, organizations dramatically reduce compute resource consumption since transformation queries execute against small change sets rather than full datasets, significantly improve pipeline performance through reduced data volumes and accelerated query execution, and enable near-real-time analytics where downstream reporting tables, dashboards, and machine learning features reflect source system changes with minimal propagation delay.
Option B incorrectly suggests scheduled COPY INTO commands as a real-time solution, when COPY INTO fundamentally operates as a batch-oriented data loading mechanism designed for ingesting files from external stages into Snowflake tables at scheduled intervals, inherently introducing latency between when source data changes occur and when those changes become available in analytical environments, making this approach unsuitable for scenarios requiring continuous incremental ETL with rapid change propagation rather than periodic bulk loads. Option C misapplies Time Travel functionality, which enables querying historical table states at specific past timestamps for auditing, recovery, or historical analysis purposes but requires computationally expensive full table scans comparing current state against historical snapshots to identify differences, lacking the efficient change tracking metadata that streams maintain specifically for incremental processing, making Time Travel-based change detection impractical for operational ETL pipelines processing frequent updates across large tables.
Option D proposes external tables as an incremental solution, but external tables reference data residing in cloud object storage accessed through virtual table definitions without automatically tracking which files contain new or modified data, requiring manual implementation of file tracking mechanisms, watermarking schemes, or filename conventions to identify incremental changes, introducing operational complexity, potential reliability issues from custom logic errors, and processing latency from batch-oriented file detection rather than continuous change capture. Therefore, combining streams with tasks provides the optimal, most operationally efficient, and comprehensively Snowflake-native solution for implementing automated incremental ETL pipelines that deliver low-latency change propagation, minimize compute costs through delta processing, eliminate manual intervention through self-managing orchestration, and scale seamlessly as data volumes and change rates increase across enterprise data integration architectures.
