I85PREP
⌘ K
SnowPro Advanced Architect study materials
SnowPro Advanced Architect study resources

SnowPro Advanced Architect practice questions

One question at a time. Build your SnowPro Advanced Architect understanding.

First 50 of 200 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1Snowflake Architecture and Features

Which Snowflake architecture layer handles authentication, metadata management, and query parsing and optimization?

  1. The storage layer
  2. The replication layer
  3. The cloud services layer Correct answer.
  4. The virtual warehouse layer

Explanation

The cloud services layer is a collection of services that coordinates the account: it authenticates users, stores and manages metadata, and parses and optimizes every submitted statement before compute executes it. Virtual warehouses only run the resulting execution plan against data held in cloud storage.

Question 2Snowflake Architecture and Features

What is the underlying storage unit into which Snowflake automatically divides table data?

  1. Extents
  2. Pages
  3. Micro-partitions Correct answer.
  4. Data blocks

Explanation

Snowflake automatically and transparently partitions every table into micro-partitions, which are contiguous units of storage held in a compressed, columnar format. Metadata about the value ranges in each micro-partition is what enables pruning.

Question 3Snowflake Architecture and Features

What is the primary role of a virtual warehouse in Snowflake?

  1. It maintains account metadata and access control policies
  2. It supplies the compute resources that execute queries and DML Correct answer.
  3. It provides persistent storage for query result sets
  4. It stores table data durably across cloud regions

Explanation

A virtual warehouse is a cluster of compute resources, including CPU, memory, and local SSD, that executes queries and DML statements. It holds no durable table data; storage is handled separately by the cloud storage layer.

Question 4Snowflake Architecture and Features

Snowflake meters virtual warehouse usage in credits. What is the minimum amount of time a warehouse is charged for each time it starts or resumes?

  1. 1 hour
  2. 5 minutes
  3. 60 seconds Correct answer.
  4. 10 seconds

Explanation

Warehouse usage is billed per second, but each start or resume is subject to a 60-second minimum charge. After that minimum, billing continues per second until the warehouse suspends.

Question 5Snowflake Architecture and Features

With no configuration changes, what is the default Time Travel retention period for a permanent table in Snowflake?

  1. 7 days
  2. 0 seconds
  3. 1 hour
  4. 1 day Correct answer.

Explanation

Permanent tables default to a 24-hour (1 day) Time Travel retention, which can be raised to as much as 90 days at the table, schema, or database level. Fail-safe is a separate, non-configurable period that begins after Time Travel ends.

Question 6Snowflake Architecture and Features

How long does the Fail-safe period last for a permanent table whose Time Travel period has expired?

  1. 90 days, configurable per account
  2. 7 days, non-configurable Correct answer.
  3. 1 day, configurable per table
  4. Until the next billing cycle

Explanation

Fail-safe provides a fixed 7-day period of protection after Time Travel ends. It cannot be changed by users, and data in Fail-safe can be recovered only by Snowflake Support.

Question 7Snowflake Architecture and Features

Which capability is a direct benefit of Snowflake's multi-cluster shared data architecture?

  1. Each virtual warehouse maintains its own copy of table data
  2. Storage and compute must be scaled together
  3. Storage costs increase linearly with each warehouse added
  4. Storage and compute scale independently of each other Correct answer.

Explanation

All virtual warehouses read the same central storage, so compute can be added, resized, or removed without copying or relocating data, and storage grows only as data is written. This separation is the defining characteristic of the shared-data architecture.

Question 8Snowflake Architecture and Features

Which statement about the Snowflake result cache is accurate?

  1. It is stored on the local disk of the warehouse that ran the query
  2. It is invalidated only when the table's clustering key changes
  3. It lives in the cloud services layer across warehouses Correct answer.
  4. It requires an active virtual warehouse to return cached results

Explanation

The result cache is an account-level cache held by the cloud services layer, retained for up to 24 hours and extended each time a matching query reuses it. It is independent of any single warehouse and is invalidated when the underlying table data changes.

Question 9Snowflake Architecture and Features

A virtual warehouse is set to size Large. How many credits per hour does it consume while it is running?

  1. 8 Correct answer.
  2. 16
  3. 4
  4. 2

Explanation

Credit consumption doubles with each size step starting from X-Small at 1 credit per hour: Small is 2, Medium is 4, Large is 8, and X-Large is 16. The rate applies per hour of running time, billed per second.

Question 10Snowflake Architecture and Features

A multi-cluster warehouse is configured with MIN_CLUSTER_COUNT = 1, MAX_CLUSTER_COUNT = 4, and the STANDARD scaling policy. How does Snowflake respond when queries begin to queue?

  1. It adds clusters as soon as queries queue Correct answer.
  2. It starts additional clusters only after queries have been queued for at least six minutes
  3. It resizes the existing cluster to the next larger warehouse size
  4. It routes queued queries to other warehouses in the account

Explanation

Under the STANDARD policy, Snowflake provisions an additional cluster as soon as queries are queued, up to the maximum cluster count, and shuts extra clusters down after a period of inactivity. The ECONOMY policy is the one that waits roughly six minutes before adding capacity.

Question 11Snowflake Architecture and Features

Which column is the best candidate for a clustering key on a very large fact table?

  1. A timestamp column used in range filters Correct answer.
  2. A unique identifier column never used in filters
  3. A low-cardinality status column used in every query
  4. A Boolean flag recording row activity status

Explanation

Clustering delivers the most benefit on columns that queries filter on, particularly with range and equality predicates, and that have enough distinct values for pruning metadata to eliminate micro-partitions. Low-cardinality columns and columns that are never filtered provide little or no pruning gain.

Question 12Snowflake Architecture and Features

A table containing 2 TB of data is cloned with CREATE TABLE ... CLONE. What storage is consumed immediately after the clone completes?

  1. Essentially none, because the clone references the same micro-partitions Correct answer.
  2. 2 TB, because the data is fully copied at clone time
  3. Twice the original, because both tables keep independent copies
  4. 1 TB, because storage is split between the source and the clone

Explanation

A clone is a metadata-only operation: the new table points at the same micro-partitions as the source, so no additional storage is consumed at creation. New storage accrues only as data in either table is modified and new micro-partitions are written.

Question 13Snowflake Architecture and Features

A developer dropped a table 30 minutes ago and the table has a 24-hour Time Travel retention period. What is the most direct way to recover it?

  1. Re-run the last SELECT statement to repopulate the table
  2. Recreate the table and reload it from an earlier clone
  3. Run UNDROP TABLE on the dropped table Correct answer.
  4. Restore the table from Fail-safe using Snowflake Support

Explanation

Within the Time Travel retention window, UNDROP TABLE restores a dropped table in place along with its privileges and its role in any existing clones. Fail-safe is available only after Time Travel has expired and requires Snowflake Support.

Question 14Snowflake Architecture and Features

An account-level resource monitor reaches its credit quota and is configured with the SUSPEND_IMMEDIATE action. What occurs?

  1. Only new queries are blocked while running queries finish normally
  2. Assigned warehouses suspend and running queries are cancelled Correct answer.
  3. A notification is sent and warehouses continue running
  4. Warehouses are resized down to the smallest size

Explanation

SUSPEND_IMMEDIATE suspends the warehouses monitored by the resource monitor and cancels any statements still in progress. The SUSPEND action instead lets running statements complete before suspending, and NOTIFY only sends an alert.

Question 15Snowflake Architecture and Features

A provider shares a large table with a consumer account through a Snowflake share. Which statement describes the storage behavior?

  1. No data is copied; the consumer reads the provider's micro-partitions Correct answer.
  2. Data is copied once at share creation, then refreshed nightly
  3. The consumer is billed for a full copy of the shared data in its own storage
  4. The consumer must create a warehouse inside the provider's account

Explanation

Secure data sharing is metadata-based: the consumer's account is granted access to the provider's existing micro-partitions, so nothing is copied and the provider continues to pay storage costs. The consumer pays only for the compute used to query the shared data.

Question 16Snowflake Architecture and Features

A team needs files arriving in cloud storage to be loaded into Snowflake within minutes without managing any warehouse. Which feature fits best?

  1. An external table queried on demand
  2. Bulk loading with a dedicated warehouse kept running
  3. A scheduled task that runs COPY INTO every hour
  4. Snowpipe with cloud storage event notifications Correct answer.

Explanation

Snowpipe loads files continuously using Snowflake-managed serverless compute, and cloud event notifications trigger loads shortly after each file arrives. No user-managed warehouse is required, and latency is measured in minutes rather than hours.

Question 17Snowflake Architecture and Features

What does a standard Snowflake stream maintain for its source table?

  1. A snapshot of the table taken at stream creation
  2. Change tracking metadata plus an offset for the stream Correct answer.
  3. The results of the last statement executed on the table
  4. A full physical copy of every changed row at capture time

Explanation

A stream stores change tracking metadata, including a current offset, and exposes the delta of inserts, updates, and deletes made since that offset. It does not duplicate table rows; consuming the stream advances the offset.

Question 18Snowflake Architecture and Features

Which statement is true of a Snowflake external table?

  1. It requires Time Travel to be enabled on the external stage
  2. It supports inserting and updating rows directly
  3. It stores data in Snowflake-managed micro-partitions
  4. It is read-only and reads data stored in an external stage Correct answer.

Explanation

External tables are read-only and leave the data in its original location in external cloud storage, reading it through stage metadata. Because Snowflake does not own the files, DML, Time Travel, and Fail-safe do not apply to them.

Question 19Snowflake Architecture and Features

Analysts run highly selective equality lookups on a single high-cardinality column of a table with billions of rows. Which feature most directly improves this access pattern?

  1. A materialized view refreshed on every change
  2. A multi-cluster warehouse with ECONOMY scaling
  3. A larger virtual warehouse size
  4. The Search Optimization Service Correct answer.

Explanation

The Search Optimization Service builds a persistent search access path that lets highly selective point lookups skip scanning most micro-partitions. It targets equality and IN predicates rather than broad range scans, so it matches this workload closely.

Question 20Snowflake Architecture and Features

WH_A runs a query that scans a table. WH_B then runs a different query that scans the same table. What is true about WH_B's use of the local disk cache?

  1. WH_B reads from storage because the disk cache is per warehouse Correct answer.
  2. WH_B reuses WH_A's cached micro-partitions automatically
  3. WH_B reads the cache held in the cloud services layer
  4. WH_B cannot access the table until WH_A releases the cache

Explanation

The local disk cache holds micro-partitions on the SSD of the warehouse nodes that read them, so it is scoped to a single warehouse and is not shared between warehouses. A different warehouse must read the micro-partitions from storage again, though it may benefit from remote disk or metadata services.

Question 21Snowflake Architecture and Features

In the output of SYSTEM$CLUSTERING_INFORMATION, what does a high average clustering depth indicate?

  1. Micro-partitions for the same key range overlap heavily Correct answer.
  2. The clustering key is perfectly aligned and no reclustering is needed
  3. The table's micro-partitions are close to the optimal 16 MB target size
  4. The table has too many columns for the clustering key to be stored

Explanation

Clustering depth counts how many micro-partitions contain overlapping values for a given clustering key range. A depth of one means the data is perfectly clustered, while higher averages mean many partitions must be scanned for a key value, so pruning is less effective.

Question 22Snowflake Architecture and Features

Which change makes previously cached results for a query unusable by the Snowflake result cache?

  1. The query is re-executed on a larger virtual warehouse
  2. A single row is updated in one of the tables referenced by the query Correct answer.
  3. The warehouse is suspended and later resumed
  4. The session's current role is changed to another role with access to the same tables

Explanation

Any DML that modifies a referenced table causes the cached results for queries against that table to be invalidated, because the underlying micro-partitions have changed. Warehouse size, suspension, and role changes (when the new role retains equivalent privileges) do not invalidate the cache.

Question 23Snowflake Architecture and Features

Snowflake encrypts data with a hierarchical key model. Which sequence describes that hierarchy correctly?

  1. Root key, account master key, table master key, file key Correct answer.
  2. Account master key, root key, file key, table master key
  3. Root key, file key, table master key, account master key
  4. File key, table master key, account master key, root key

Explanation

Encryption is layered so that each level protects the level beneath it: the root key encrypts the account master key, which encrypts table master keys, which in turn encrypt the per-file keys used to encrypt data. This hierarchy lets keys be rotated or rekeyed without re-encrypting all stored data.

Question 24Snowflake Architecture and Features

A database is replicated to a secondary account for disaster recovery. What is the state of the secondary database before a failover is performed?

  1. It is read-only and must be promoted before it can accept writes Correct answer.
  2. It is refreshed continuously and accepts writes from local users
  3. It is writable only by ACCOUNTADMIN of the primary account
  4. It is fully read-write and shares storage with the primary

Explanation

A secondary database in a replication configuration is read-only and is refreshed from the primary account. It becomes writable only after it is promoted, which is part of the failover process.

Question 25Snowflake Architecture and Features

A large table has a clustering key and automatic clustering is enabled. How is the reclustering work billed?

  1. It runs on the user's virtual warehouse and consumes its credits
  2. It is charged as cloud services credits only
  3. It is free because clustering is part of the storage layer
  4. Serverless compute billed separately from warehouse credits Correct answer.

Explanation

Automatic clustering runs on Snowflake-managed serverless compute and appears as its own billing line rather than consuming credits from a user warehouse. Clustering remains effective for pruning even when no warehouse is running.

Question 26Snowflake Architecture and Features

Which layer of Snowflake's architecture handles query parsing and optimization, access control, and metadata management?

  1. The client layer, provided by Snowflake drivers and connectors
  2. The storage layer, provided by cloud object storage
  3. The cloud services layer Correct answer.
  4. The compute layer, provided by virtual warehouses

Explanation

The cloud services layer is the control plane of the account: it authenticates sessions, stores metadata, and parses and optimizes every statement. Virtual warehouses only execute the plan, and object storage only holds the persisted data.

Question 27Snowflake Architecture and Features

Which statement describes how table data is stored in Snowflake micro-partitions?

  1. Each column is stored in its own separate file with a fixed size of 1 GB.
  2. Rows are stored uncompressed in 16 MB blocks that are never reorganized.
  3. Rows are stored in a compressed, columnar form within contiguous 50-500 MB units of storage. Correct answer.
  4. Rows are stored in a row-based format on the virtual warehouse's local disk only.

Explanation

Snowflake automatically divides table data into micro-partitions of roughly 50 to 500 MB of uncompressed data. Each micro-partition stores data in a columnar layout and is compressed before being written to cloud storage.

Question 28Snowflake Architecture and Features

A warehouse has AUTO_RESUME = TRUE and is currently suspended. A user submits a query that needs the warehouse. What happens?

  1. The query is queued until a scheduled task resumes the warehouse.
  2. Snowflake resumes the warehouse automatically and the query runs once compute is provisioned. Correct answer.
  3. The query fails with an error until an administrator resumes the warehouse.
  4. Snowflake runs the query on a shared serverless pool of compute instead.

Explanation

With AUTO_RESUME enabled, Snowflake detects an incoming query against a suspended warehouse and starts it automatically. The statement waits briefly while compute is provisioned and then executes normally.

Question 29Snowflake Architecture and Features

How does Snowflake calculate on-demand storage charges for an account?

  1. From the number of credits consumed by warehouses that read the stored data
  2. From the total number of rows stored across all databases in the account
  3. From the peak uncompressed size of all tables at any point in the billing month
  4. From the average daily amount of compressed data stored, measured in terabytes Correct answer.

Explanation

Storage is billed monthly based on the average number of terabytes stored per day, measured on compressed data. The total includes table data plus Time Travel and Fail-safe storage and any files held in stages.

Question 30Snowflake Architecture and Features

A table with a 24-hour Time Travel retention was modified incorrectly 90 minutes ago. Which query returns the table contents as they were two hours ago?

  1. SELECT * FROM t AT (OFFSET => -90);
  2. SELECT * FROM t AT (OFFSET => 7200);
  3. SELECT * FROM t AT (OFFSET => -7200); Correct answer.
  4. SELECT * FROM t AT (TIMESTAMP => '-7200');

Explanation

The OFFSET parameter of the AT clause takes a number of seconds relative to the present and must be negative to look into the past. Two hours equals 7200 seconds, so -7200 targets the desired point within the retention window.

Question 31Snowflake Architecture and Features

Which feature set becomes available only with the Enterprise Edition of Snowflake?

  1. Time Travel with a 1-day retention period
  2. Virtual warehouses and micro-partition storage
  3. Secure data sharing and the result cache
  4. Multi-cluster warehouses, 90-day Time Travel, and materialized views Correct answer.

Explanation

Enterprise Edition adds capabilities such as multi-cluster warehouses, Time Travel retention up to 90 days, and materialized views. Core features such as virtual warehouses, micro-partitions, secure data sharing, and 1-day Time Travel exist in Standard Edition.

Question 32Snowflake Architecture and Features

Which Snowflake object is a named location in cloud storage used to load and unload data files?

  1. A directory table
  2. A file format
  3. A stage Correct answer.
  4. A pipe

Explanation

A stage references a storage location, either internal Snowflake-managed storage or an external bucket, and is the source or target of loading and unloading operations. Pipes and file formats support loads but do not define a storage location.

Question 33Snowflake Architecture and Features

What is the default Time Travel retention period for a permanent table in an account using Standard Edition?

  1. 7 days
  2. 1 day Correct answer.
  3. 0 seconds
  4. 90 days

Explanation

Permanent tables default to DATA_RETENTION_TIME_IN_DAYS = 1, which is also the maximum retention in Standard Edition. Transient and temporary tables default to 0, and retention up to 90 days requires Enterprise Edition.

Question 34Snowflake Architecture and Features

Which stream type records only inserted rows and ignores updates and deletes on the source table?

  1. A transient stream
  2. An append-only stream Correct answer.
  3. A cloned stream
  4. A standard stream

Explanation

An append-only stream exposes only the rows inserted into the source table, omitting update and delete change records. A standard stream records inserts, updates, and deletes with their change metadata.

Question 35Snowflake Architecture and Features

A column of type VARIANT holds a JSON document containing an array of order objects. Which construct expands that array into one row per element?

  1. TO_VARCHAR applied to the array column
  2. PARSE_JSON applied with the RECURSIVE option
  3. A LATERAL FLATTEN of the array column Correct answer.
  4. OBJECT_CONSTRUCT applied to the array column

Explanation

FLATTEN is a table function that explodes an array or object into one row per element, and it is normally used in the FROM clause with LATERAL so that each source row keeps its correlation. PARSE_JSON only converts text into VARIANT, and the other functions are scalar conversions.

Question 36Snowflake Architecture and Features

Which statement about Snowflake materialized views is correct?

  1. A materialized view can join several tables and include GROUP BY with a HAVING clause.
  2. It stores precomputed results that Snowflake keeps current as the base table changes. Correct answer.
  3. The query optimizer silently rewrites every query against the base table to use the materialized view.
  4. A materialized view is refreshed only when a user runs ALTER MATERIALIZED VIEW ... REFRESH.

Explanation

A materialized view persists its result and the maintenance service refreshes it as the base table changes. Queries benefit only when they reference the materialized view explicitly, because Snowflake does not transparently redirect base-table queries.

Question 37Snowflake Architecture and Features

What does the query acceleration service do for a warehouse?

  1. It caches the result of every query for 24 hours for later reuse.
  2. It adds clusters to the warehouse whenever its query queue exceeds a threshold.
  3. It rewrites poorly written SQL into more efficient execution plans.
  4. It offloads parts of eligible large table scans to shared compute. Correct answer.

Explanation

The query acceleration service moves portions of eligible scan-heavy queries onto Snowflake-managed compute, which reduces the impact of outlier statements on the warehouse. It is enabled per warehouse and does not change the SQL text or cache results.

Question 38Snowflake Architecture and Features

Which characteristic distinguishes a hybrid table from a standard Snowflake table?

  1. It keeps every row in one micro-partition so that keys stay unique.
  2. It stores all data in object storage with no local component for performance.
  3. It can be queried only through external functions rather than standard SQL.
  4. It enforces primary key constraints and serves high-concurrency row lookups. Correct answer.

Explanation

Hybrid tables pair row-oriented storage with enforced primary and foreign keys and secondary indexes, targeting high-concurrency, low-latency transactional access. They are queried with standard SQL and are not restricted to a single micro-partition.

Question 39Snowflake Architecture and Features

Which statement describes a Snowflake-managed Apache Iceberg table?

  1. It stores metadata in the Snowflake database while keeping no schema information.
  2. Its data can be read only by Snowflake and uses an internal proprietary file format.
  3. Its data and metadata live in an external volume and are readable by other Iceberg-compatible engines. Correct answer.
  4. It is a view over Parquet files that must be refreshed manually before each query.

Explanation

A Snowflake-managed Iceberg table keeps its data files and Iceberg metadata in an external volume, with Snowflake maintaining the table state. Because the format is open, engines outside Snowflake can read the same files.

Question 40Snowflake Architecture and Features

How does an external function call code that runs outside Snowflake?

  1. Through an API integration that invokes a remote HTTPS service and returns a scalar value. Correct answer.
  2. By registering a stored procedure that runs on the client machine that invoked it.
  3. By embedding Python code that Snowflake executes inside the virtual warehouse.
  4. Through a task that copies rows to a stage and loads the processed rows back.

Explanation

An external function uses an API integration to call a remote service over HTTPS, typically behind a proxy, and returns a scalar value per row. The code executes outside Snowflake and is not run by the warehouse or the client.

Question 41Snowflake Architecture and Features

Who pays for the compute and storage consumed by a reader account created by a data provider?

  1. The provider that created and manages the reader account Correct answer.
  2. The reader account's users, through credits purchased for their own account
  3. Costs are split evenly between provider and consumer by Snowflake
  4. The cloud provider that hosts the reader account's region

Explanation

A reader account is created and administered by the provider, which is billed for all storage and compute the reader account consumes. Readers can query shared data but cannot load their own data or share outward.

Question 42Snowflake Architecture and Features

How does a resource monitor attached to a specific virtual warehouse differ from an account-level resource monitor?

  1. It tracks credits used by that warehouse only and can suspend it independently. Correct answer.
  2. It can only notify users and cannot suspend or resize the warehouse.
  3. It applies to all warehouses in the account and overrides the account-level monitor.
  4. It monitors storage consumed by the warehouse rather than credits consumed.

Explanation

A warehouse-level monitor meters the credits consumed by that single warehouse and enforces its own quota and actions. An account-level monitor meters all compute in the account and can trigger account-wide actions.

Question 43Snowflake Architecture and Features

A running warehouse has built up a local disk cache from repeated queries. An administrator then suspends the warehouse. What happens to that cache?

  1. It is converted into result cache entries that survive the suspension.
  2. It is preserved and reused when the warehouse is resumed.
  3. It is dropped, so later queries must read the data from storage again. Correct answer.
  4. It is copied to a shared cache that any warehouse in the account can read.

Explanation

The local disk cache lives on the compute nodes of a running warehouse, so suspending the warehouse discards it. After resumption, the warehouse rebuilds the cache as queries read data from storage again.

Question 44Snowflake Architecture and Features

A masking policy is assigned to a column of a table. When is the masking applied?

  1. At query time, evaluated against the role of the user issuing the query. Correct answer.
  2. When the data is loaded into the table, so masked values are stored on disk.
  3. When a user is granted the masking policy object privilege on the schema.
  4. Only when the table is queried through a secure view owned by ACCOUNTADMIN.

Explanation

Masking policies are evaluated at query time against the current role and context of the session. The stored data stays unchanged, so unauthorized roles see masked output while authorized roles see the real values.

Question 45Snowflake Architecture and Features

Snowflake prunes micro-partitions while executing a query. What information does it use to decide which partitions to skip?

  1. Statistics gathered by the optimizer during the previous 24 hours
  2. A hash index built on every column when the table is loaded
  3. The clustering key definition alone, regardless of data values
  4. Per-column minimum and maximum values recorded in each partition's metadata Correct answer.

Explanation

Snowflake records metadata such as minimum and maximum values and distinct counts for the columns of every micro-partition. At query time the optimizer compares predicates to those ranges and skips partitions that cannot contain matching rows, without any user-built index.

Question 46Snowflake Architecture and Features

A single row in a 100-million-row table is updated. How does Snowflake handle the affected micro-partition?

  1. It rewrites every micro-partition in the table to preserve a global sort order.
  2. It marks the old partition as deleted and writes new partitions holding the changed rows. Correct answer.
  3. It moves the row into a separate delta table that is merged on the next read.
  4. It modifies the row in place inside the existing micro-partition file.

Explanation

Micro-partitions are immutable once written, so DML creates new micro-partitions containing the affected rows and marks the previous versions as deleted. Unaffected partitions are left untouched, and the old versions remain available for Time Travel until retention expires.

Question 47Snowflake Architecture and Features

A developer runs CREATE TABLE t_clone CLONE t AT (TIMESTAMP => '2024-05-01 12:00:00'::timestamp). What does this produce?

  1. An error, because CLONE cannot be combined with an AT or BEFORE clause.
  2. A table containing current data that is independent of the source's Time Travel history.
  3. A view over the source's historical data that stays valid only while that history exists.
  4. A table holding the source's data as of that time, sharing those micro-partitions. Correct answer.

Explanation

Cloning accepts an AT or BEFORE clause, so the clone is created from the source table's state at that point in time. The clone is zero-copy and shares the referenced micro-partitions, and the timestamp must fall inside the source table's retention period.

Question 48Snowflake Architecture and Features

Which capability is specific to Snowpipe streaming rather than file-based Snowpipe?

  1. It runs inside a virtual warehouse and executes COPY statements on a schedule.
  2. It relies on cloud storage event notifications to trigger each individual load.
  3. It loads data from files in a stage as soon as the files arrive, without a warehouse.
  4. It ingests rows via a client SDK channel with offsets for exactly-once delivery. Correct answer.

Explanation

Snowpipe streaming writes rows directly from an application through an SDK channel, using offset tokens so each row is applied exactly once. It does not depend on files, stages, or event notifications the way file-based Snowpipe does.

Question 49Snowflake Architecture and Features

Why is a secure view recommended when sharing data with a consumer?

  1. Because only secure views can be granted to a share, while standard views cannot.
  2. Because a secure view materializes its results, so the consumer never runs a warehouse.
  3. Because the view definition and internal optimizer details are hidden from the consumer. Correct answer.
  4. Because a secure view encrypts the underlying data with a separate key for each consumer.

Explanation

A secure view prevents the consumer from seeing the view definition and limits the internal information the optimizer can expose about underlying objects. Standard views can also be shared, but their definitions may leak details of the provider's schema.

Question 50Snowflake Architecture and Features

In a business continuity setup with a primary and secondary account, which object lets a client reconnect to the secondary after a failover?

  1. A connection object that lists the primary and secondary account URLs Correct answer.
  2. A network policy that lists the allowed IP addresses for both accounts
  3. A replication group that contains the databases of both accounts
  4. A storage integration that points to the secondary account's external volume

Explanation

A connection object stores the account URLs for the primary and secondary deployments. Clients that support connection objects and client redirect can then follow a failover to the secondary account without changing their configuration.