Free Snowflake SnowPro Advanced: Data Engineer (DEA-C02) Sample Questions

Correct answer: A — A pre-signed URL, generated with `GET_PRESIGNED_URL`, which is a plain HTTPS link that grants time-limited access to the file without requiring the requester to hold any Snowflake session or role.

• A. Pre-signed URLs are designed exactly for this scenario: they are ordinary HTTPS URLs that grant temporary access to a single file to anyone who has the link, with no Snowflake login or role required, making them suitable for anonymous external sharing.
• B. A scoped URL ties access to the specific query result that produced it and expires with that result cache, and it is meant for roles that lack direct stage privileges within an authenticated Snowflake context, not for anonymous public access.
• C. A file URL is a persistent reference to a staged file, but retrieving it still requires the caller to authenticate to Snowflake's REST API with a valid token and hold sufficient stage privileges, which anonymous external users do not have.
• D. Referencing a stage path in a SQL statement only works for a client already connected and authenticated to Snowflake with the right privileges; it does not produce a shareable browser link and is unusable for anonymous external users.

Correct answer: A — Compress the files with gzip and split them into roughly 100-250 MB chunks so more files load in parallel across warehouse threads

• A. Correct: Snowflake recommends compressed files in roughly the 100-250 MB range because each file is processed by a single thread, and right-sized compressed files let the warehouse parallelize loading across many threads instead of bottlenecking on a few large or uncompressed files.
• B. Incorrect: consolidating into one large file removes the ability to parallelize across threads, since a single file can only be processed by one thread at a time, which typically slows the load rather than speeding it up.
• C. Incorrect: table type (permanent vs. transient) affects storage retention and Time Travel/Fail-safe behavior, not the mechanics of how COPY INTO reads and parallelizes file scanning during a load.
• D. Incorrect: SIZE_LIMIT caps how many bytes a single COPY statement will load before stopping, it does not change how files are parallelized across threads, so raising it does not address a throughput bottleneck caused by file sizing.

Correct answer: A — Have the ETL job call the Snowpipe REST `insertFiles` endpoint immediately after it finishes writing each file, authenticating with a JWT signed by the pipe owner's key pair.

• A. The REST API path is exactly for cases without a supported cloud event source: the client application explicitly notifies Snowpipe of new files by calling `insertFiles` with the pipe name and file list, authenticated with a JWT built from the pipe owner's RSA key pair, so loading starts immediately after the write.
• B. Auto-ingest depends on the underlying cloud storage (S3, Azure Blob, or GCS) publishing native event notifications to a queue Snowflake consumes; an internal stage has no such cloud event source, so `AUTO_INGEST = TRUE` cannot be wired up for it.
• C. Switching to Snowpipe Streaming changes the ingestion model entirely to row-level writes through an SDK channel, discarding the files already written to the stage rather than solving how to trigger loading of those existing files.
• D. A serverless task on a one-minute schedule still polls the stage on a timer using Snowflake-managed compute rather than being notified the instant a file appears, so it cannot guarantee within-seconds latency and adds unnecessary scheduled compute.

Correct answer: A — Query the `NOTIFICATION_HISTORY` table function, which returns a queryable record of the error notifications Snowpipe has sent through the configured cloud messaging service.

• A. This is correct. `NOTIFICATION_HISTORY` is the table function documented specifically to let you query the history of notifications Snowpipe has sent through cloud messaging, which is exactly the audit trail the auditors are asking for.
• B. `COPY_HISTORY` tracks load status and error messages for files processed by COPY or Snowpipe, but it does not log whether or when an outbound SNS notification was actually delivered for those errors.
• C. `SYSTEM$PIPE_STATUS` returns a snapshot of the pipe's current state, such as execution state and recent message timestamps, not a historical log of every notification sent over a rolling week.
• D. `LOAD_HISTORY` in `INFORMATION_SCHEMA` reports load activity metadata and does not include a notification-delivery column tracking SNS publish confirmations, so it cannot answer this audit question.

Correct answer: A — Create reader accounts for each customer, because a reader account lets the provider manage a lightweight account for a consumer with no existing account

• A. Reader accounts are purpose-built for exactly this scenario: the provider creates and administers a reader account for a consumer who has no Snowflake account, and the provider's own account absorbs the compute costs, so customers can query shared data without a separate Snowflake relationship.
• B. Adding a consumer account to a share requires the consumer to already have their own Snowflake account with its own billing; it does not automatically create or manage that account under the provider's billing, so it does not fit customers who lack an account.
• C. Requiring every customer to independently purchase a Business Critical account before they can even see the data contradicts the goal of onboarding customers who don't already have Snowflake accounts, and edition is not the gating factor for receiving a listing.
• D. Cross-account cloning of a live database is not a supported data-distribution mechanism; cloning operates within a single account's object hierarchy, so it cannot be used to hand a database to a separate customer account.

Correct answer: A — Key pair authentication, passing the service account's private key to `connect()` so no interactive prompt is ever required.

• A. This is correct because key pair authentication lets the script prove its identity with a private key file, requiring no interactive step and fitting an unattended, scheduled service-account workload.
• B. This is incorrect because external browser authentication needs a human to complete an SSO login in a browser each session; it is not designed to run unattended on a schedule.
• C. This is incorrect because push-based multi-factor authentication still requires a person to approve the prompt on a device, which an unattended scheduled job cannot provide.
• D. This is incorrect because password authentication can still trigger interactive multi-factor verification depending on account policy, and it is not the recommended unattended method for service accounts.

Correct answer: A — Open the connection with a `with` statement and `autocommit=False`, so an exception rolls back the transaction and the connection closes automatically.

• A. This is correct because the context manager form with `autocommit=False` groups the statements into one transaction, rolls it back automatically on an unhandled exception, and closes the connection when the block exits.
• B. This is incorrect because `autocommit=True` commits each INSERT independently, so a later failure cannot roll back statements that already committed, defeating the all-or-nothing requirement.
• C. This is incorrect because `fetchall()` retrieves query results and has no role in committing a transaction; it does nothing to group INSERT statements into a single atomic unit.
• D. This is incorrect because the authenticator argument only controls how the connection authenticates and has no bearing on transaction commit or rollback behavior.

Correct answer: A — Iceberg tables have no temporary or transient variant, and streaming support for externally-managed Iceberg tables is limited to insert-only, so both patterns need to be redesigned before migrating.

• A. Snowflake documents that Iceberg tables have no temporary or transient counterpart, and that streaming into externally-managed Iceberg tables only supports insert-only operations, which directly conflicts with a transient-copy ETL pattern and a general-purpose stream.
• B. Interoperability with external engines such as Spark and Trino is one of the main reasons teams adopt Iceberg tables, so this claim about being Snowflake-only misstates the actual limitation being tested here.
• C. Iceberg tables support structured and semi-structured column types much like native tables, so a blanket claim that VARIANT is unsupported does not reflect an actual constraint on this migration.
• D. Clustering is unsupported specifically for Iceberg tables that use an external catalog, but Snowflake-catalog managed Iceberg tables do support clustering, so this option overstates the limitation.

Correct answer: A — Run ALTER ICEBERG TABLE ... ADD COLUMN on the managed table, relying on Iceberg's schema evolution model to make the new column available going forward without rewriting existing data files.

• A. Iceberg's schema evolution model, which Snowflake-managed Iceberg tables support natively, allows adding a nullable column through an ALTER statement without rewriting existing data files, matching exactly what the team wants to avoid.
• B. A full export-and-reload defeats the purpose of Iceberg's schema evolution feature, which exists precisely so that column additions do not require rewriting historical data files.
• C. Recreating the table and copying every row is unnecessary work; Iceberg tables support in-place schema evolution for operations like adding a nullable column, so a new table is not required.
• D. Snowflake requires an explicit ALTER statement to register a new column in the table's schema; columns are not silently inferred from new files without a corresponding schema change being applied first.

Correct answer: A — Create the Iceberg table with `EXTERNAL_VOLUME = SNOWFLAKE_MANAGED`, so Snowflake owns the storage, bills it under the account, and applies Fail-safe protection

• A. Setting EXTERNAL_VOLUME to SNOWFLAKE_MANAGED tells Snowflake to own and bill the underlying storage for the Iceberg table under the customer's Snowflake account, and Snowflake-managed storage is eligible for Fail-safe protection like other Snowflake-owned data.
• B. Iceberg tables can also use Snowflake-managed storage via EXTERNAL_VOLUME = SNOWFLAKE_MANAGED, so a customer-owned bucket is not the only option, and a customer-owned volume would not give the Snowflake-billed, Fail-safe-covered storage the team wants.
• C. Polaris is a catalog implementation for interoperability with external engines; using it as the catalog does not grant Fail-safe protection, which depends on whether the storage itself is Snowflake-managed, not on the catalog choice.
• D. Iceberg table format is a distinct table type created explicitly with CREATE ICEBERG TABLE; it cannot be applied retroactively to an existing native permanent table as a toggle after the fact.

Correct answer: A — `COPY INTO @stage FROM (SELECT ...) FILE_FORMAT = (TYPE = PARQUET)`, since Parquet defaults to Snappy compression, which Spark reads natively

• A. COPY INTO a location with FILE_FORMAT TYPE = PARQUET unloads query results as columnar Parquet files, and Parquet's default Snappy compression is exactly the codec Spark's Parquet readers expect out of the box, matching the stated requirement.
• B. CSV is a row-based text format regardless of compression codec; GZIP-compressed CSV is not Parquet and would not give Spark the columnar file structure it needs to read the export efficiently.
• C. JSON unloads produce semi-structured text files, not a columnar binary format, so a Spark job expecting Parquet input would need to parse and reshape the data rather than reading it natively and efficiently.
• D. GET copies files from an internal stage down to a local machine; it does not perform a query unload and has no role in producing Parquet output from a SELECT statement's results.

Correct answer: A — `SYSTEM$CLUSTERING_INFORMATION`, which reports clustering depth and partition overlap statistics for one column so pruning effectiveness can be judged before adding a key

• A. Correct: `SYSTEM$CLUSTERING_INFORMATION` reports clustering depth and related partition overlap metrics for a given column, exactly the pruning-effectiveness data needed to decide whether adding a clustering key is worthwhile.
• B. Incorrect: `SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS` estimates the cost of a different feature, Search Optimization Service, and says nothing about how well a column currently prunes for range or equality filters.
• C. Incorrect: `TABLE_STORAGE_METRICS` reports storage consumption figures like active, Time Travel, and Fail-safe bytes, which describe how much space the table occupies, not how its rows are physically organized.
• D. Incorrect: `SYSTEM$ESTIMATE_AUTOMATIC_CLUSTERING_COSTS` estimates the future maintenance cost of a clustering key decision already being planned, not the current pruning effectiveness of the existing organization.

Correct answer: A — The oversized join output is the root cause: excessive intermediate rows overwhelm available memory and force the spill to remote storage, so fixing the join predicate addresses both symptoms

• A. Correct: an oversized, poorly filtered join produces a huge intermediate result that no longer fits in available memory, and that memory pressure is exactly what causes spilling, so tightening the join predicate removes the underlying cause of both symptoms.
• B. Incorrect: treating these as unrelated ignores the direct causal link between intermediate result size and memory pressure, and a mid-query suspension would abort the query rather than continue on to produce a completed spill metric.
• C. Incorrect: Snowflake does not switch join strategies to Cartesian as a fallback in response to spilling; the row count reflects the join predicate's actual selectivity, not a defensive re-execution.
• D. Incorrect: the result cache only serves an identical repeated query with unchanged data; it has no mechanism to prevent a join predicate from producing too many rows or to avoid memory-pressure spilling.

Correct answer: A — SUSPEND_IMMEDIATE, which suspends the assigned warehouse and cancels statements currently executing as soon as the configured threshold is reached

• A. Correct: configuring a minimum and maximum cluster count with an auto-scaling policy lets Snowflake add clusters only when concurrent demand actually rises and remove them as demand falls, which is precisely the mechanism that trades off queuing against idle credit spend across a variable-load day.
• B. Incorrect: fixing the maximum cluster count at the busiest-hour level and running it always-on eliminates queuing but keeps that many clusters active even during quiet hours, which is the excessive idle-cluster credit consumption the team is trying to avoid.
• C. Incorrect: increasing warehouse size scales up compute per node for individual queries; it does not add the ability to run more queries concurrently across separate clusters, so queuing during high-concurrency peaks would remain unaddressed.
• D. Incorrect: a resource monitor's SUSPEND_IMMEDIATE action is a credit-spending safeguard that halts a warehouse entirely once a quota is reached; it does not manage how many clusters scale in or out in response to concurrent query demand.

Correct answer: A — Search optimization service incurs ongoing serverless maintenance costs to keep its access structure current as the table changes, rising with a high rate of DML

• A. Correct: search optimization service's access structure is maintained continuously by serverless compute as the base table changes, so a table under constant streaming DML incurs ongoing maintenance costs proportional to that change volume, which is the real tradeoff to weigh against the lookup speedup.
• B. Incorrect: enabling search optimization service does not change the table's underlying columnar micro-partition storage format to a row-oriented structure; it adds a separate maintained access structure alongside the existing storage.
• C. Incorrect: search optimization service does not require a clustering key to be defined first; it is an independent feature that can be enabled on a table regardless of whether that table has an explicit clustering key configured.
• D. Incorrect: enabling search optimization service on a table has no effect on the query result cache; identical repeated queries against that table can still be served from the result cache exactly as they would be without it.

Correct answer: A — Define the task with a SCHEDULE of 1 minute and add a WHEN clause that calls `SYSTEM$STREAM_HAS_DATA` on the source stream so the task body only executes when the stream actually contains unconsumed change rows

• A. Correct: a scheduled task combined with a `WHEN SYSTEM$STREAM_HAS_DATA(...)` condition still runs on a cadence but skips the task body entirely when the stream has no unconsumed rows, which is the documented pattern for event-driven pipelines without constant full processing.
• B. Incorrect: Snowflake tasks do not support a continuous polling loop mode; every task execution is triggered by a schedule (interval or cron) or a stream/finalizer trigger, so a manual row-count polling loop is not how the service is designed to run.
• C. Incorrect: running every minute unconditionally still executes the MERGE body on every trigger regardless of whether new data arrived, and raising an exception on empty input wastes compute and produces noisy failed task runs instead of skipping cleanly.
• D. Incorrect: the serverless statement size parameters only control how much compute Snowflake allocates to a serverless task run, they do not add any logic that detects whether source data changed, so the task would still execute on every schedule tick.

Correct answer: A — Use Snowpipe Streaming, which lets a client application write rows directly to a Snowflake table through an API without staging files first, achieving latency measured in seconds for continuous row-level ingestion

• A. Correct: Snowpipe Streaming is designed for row-level ingestion directly from a client application into a table without a file-staging step, and it targets latency measured in seconds, which matches the described requirement for near-real-time queryability of individual readings.
• B. Incorrect: classic Snowpipe still requires data to first be written to files in an external stage before an event notification triggers a load, which adds a file-creation and notification round trip that does not match the continuous, sub-file-granularity ingestion described.
• C. Incorrect: batching into hourly files and loading on an hourly schedule introduces latency measured in up to an hour between event production and queryability, which is far outside the few-seconds requirement stated for this workload.
• D. Incorrect: external tables reflect files already present in cloud storage and require either automatic refresh via event notifications or manual refresh to pick up new files, they do not ingest individual streaming rows directly and still depend on file creation first.

Correct answer: A — Time Travel and Fail-safe data are maintained independently on the secondary database and are not replicated from the primary, so historical queries there can return different results than the same query.

• A. Snowflake documents that Time Travel and Fail-safe data are maintained independently for a secondary database and are not replicated from the primary, which is exactly why historical query results can diverge between the two.
• B. History is not fully synchronized on every refresh; a refresh only carries over the current state at refresh time, so identical historical results between primary and secondary should not be assumed.
• C. Fail-safe is not replicated to the secondary either, and the distinction is not that Fail-safe operates at the account level while Time Travel operates at the database level; both are independently maintained per database.
• D. Time Travel and Fail-safe are not disabled on the secondary before promotion, and promotion does not instantly backfill full primary-equivalent history; the secondary's history remains whatever it accumulated independently.

Correct answer: A — Define the clustering key as the expression itself, for example `ALTER TABLE t CLUSTER BY (v:"Data":"region"::string)`, since keys can be arbitrary expressions.

• A. Snowflake clustering keys support expressions, including paths into VARIANT columns cast to a scalar type, so defining the key directly on the nested attribute expression is the documented approach for improving pruning on that value.
• B. Clustering key expressions can reference paths inside VARIANT columns directly, so extracting the value into a separate physical column first is unnecessary extra work rather than a requirement.
• C. Clustering key expressions are not limited to flat top-level columns; VARIANT path expressions cast to a scalar type are explicitly supported as clustering key definitions.
• D. FLATTEN produces a lateral view for query-time row expansion and does not create a new physical table, and VARIANT columns can be referenced directly by a clustering expression without this transformation.

Correct answer: A — The vast majority of partitions have reached a constant state for that key where no other partition overlaps their value range, indicating strong clustering.

• A. A constant partition is one whose value range for the clustering key does not overlap with any other partition, meaning it is essentially as well-organized as it can be for that key; a high ratio of constant to total partitions signals strong, stable clustering.
• B. Constant partitions still contain rows; the term describes non-overlapping value ranges for the clustering key, not emptiness, so this figure says nothing about how many partitions are unpopulated.
• C. A constant partition does not imply the underlying column values inside it are identical; it means the partition's min/max range for the key does not overlap with other partitions, which is the opposite of a clustering failure.
• D. total_constant_partition_count is a normal part of the clustering information output returned alongside total_partition_count and is unrelated to Fail-safe retention state, which governs data recovery windows, not clustering metrics.

Correct answer: D — `CREATE TABLE orders_before CLONE orders AT(OFFSET => -2700);` to clone the table as it existed 45 minutes ago, using Time Travel to rebuild that state.

• A. `BEFORE(STATEMENT => ...)` reconstructs the state immediately prior to the referenced statement's query ID, so passing a later `SELECT` query ID does not point at the faulty `UPDATE` and would not reliably exclude its effect.
• B. `UNDROP` only restores objects that were explicitly dropped; `orders` was never dropped, so `UNDROP TABLE orders` has no applicable target here and cannot reconstruct a prior in-place state.
• C. Manually reversing an `UPDATE` row by row requires knowing the exact prior values and logic to undo them, which is error-prone and unnecessary when Time Travel already preserves the historical state directly.
• D. An offset of -2700 seconds corresponds to 45 minutes ago, and cloning with `AT(OFFSET => -2700)` reconstructs `orders` exactly as it stood at that timestamp without modifying the live table, which is the correct approach.

Correct answer: A — Privileges on child objects (tables, views, and similar) carry over to their clones, but the schema itself needs `USAGE` granted separately.

• A. Child object grants (like `SELECT` on individual tables) carry over to a schema clone, but the schema container's own privileges, such as `USAGE`, are not inherited and must be granted separately for roles to actually reach the tables.
• B. Child-level privileges do transfer for a schema clone; it is not accurate that no privileges of any kind carry over, since the table-level `SELECT` grants described in the scenario are preserved.
• C. The schema container itself does not inherit its own source privileges, including `USAGE`, so it is inaccurate to say every level of privilege, including the schema-level grant, transfers automatically.
• D. Child object grants transfer on a schema clone by default without requiring the `COPY GRANTS` keyword to be specified; `COPY GRANTS` applies to table-level clone statements copying grants from a single source table, not this schema-level behavior.

Correct answer: A — A single object supports a maximum of 50 tags, and its columns share a separate 50-tag limit counted across all columns combined.

• A. Snowflake enforces a 50-tag limit per object and a separate 50-tag limit spanning all of a table's columns combined, which is the constraint the engineer needs to plan around.
• B. Tags are not unlimited; Snowflake enforces a documented quota per object, so assuming no cap could lead to failed tag assignments once the limit is reached.
• C. Ten is not the documented tag quota, and it conflates an unrelated masking-policy limit with the actual object-level tag capacity, which is 50.
• D. Tags are not restricted to one per column or one per table; multiple tags can be assigned at each level up to the 50-tag quota.

Correct answer: A — `OBJECTS_MODIFIED`, which includes `directSources` and `baseSources` arrays showing which columns fed the value written into `total_price`.

• A. OBJECTS_MODIFIED captures write operations along with directSources and baseSources arrays that trace which source columns contributed to each modified column, which is the column-level lineage this UPDATE needs.
• B. DIRECT_OBJECTS_ACCESSED records objects referenced by a query but is oriented toward reads and object-level references, not the column-to-column source tracing that a write's OBJECTS_MODIFIED field provides.
• C. BASE_OBJECTS_ACCESSED reflects objects read during query resolution and is not the field that records write operations or column-level modification lineage.
• D. ROWS_INSERTED is a simple count of affected rows in Query History; it carries no information about which source columns fed the values written into total_price.

Correct answer: A — Write a masking policy once and apply it broadly, including through tag-based assignment, so one reusable definition can protect thousands of columns.

• A. Snowflake's documented best practice is writing a masking policy once and reusing it broadly, including via tag-based assignment, so thousands of columns across databases and schemas can be protected from a single policy definition rather than one-off work per column.
• B. Having every team invent its own separate policy logic from scratch abandons the reusability that makes column-level security workable at scale, and increases the chance of inconsistent or conflicting masking behavior across the organization.
• C. Table-level RBAC alone cannot express column-specific masking behavior, so disabling column-level security would remove the ability to show different values to different roles within the same table, which does not solve the scale problem.
• D. Manually attaching a distinct policy to every column one at a time is exactly the unscalable, per-column effort the scenario describes as unrealistic, and does not take advantage of reusable or tag-based assignment.

Correct answer: A — Scala UDF handlers do not currently support table return types, so the one-row-to-many-rows logic must be written as a UDTF in a different handler language, such as Python or Java.

• A. This is correct because Scala is the one language among Snowflake's UDF handlers that does not support table (UDTF) return types, so multi-row output for this logic needs Python, Java, JavaScript, or SQL instead.
• B. This is incorrect because `RETURNS TABLE` is a UDF/UDTF clause, not something limited to procedures, and Snowflake procedures are not the required vehicle for row-multiplying logic.
• C. This is incorrect because the `SECURE` keyword controls visibility of the function's logic and is unrelated to whether a function is allowed to return tabular results.
• D. This is incorrect because Snowflake does not read a JAR's manifest to determine table-valued behavior; the limitation is a language-level restriction on Scala UDFs, not a packaging detail.

Correct answer: A — Lower the `MAX_BATCH_ROWS` value in the function definition so Snowflake groups fewer rows into each outbound request sent to the remote service.

• A. The batch row limit directly controls how many rows are packed into a single outbound call, so lowering it is the setting that reduces batch size and helps avoid timeouts on the remote service.
• B. Compression shrinks the number of bytes transmitted for a given set of rows, but it does not change how many rows are grouped into each request, so oversized batches would still be sent.
• C. Context headers pass metadata like warehouse information to the remote service for its own use, but they do not change how Snowflake groups rows into outbound requests.
• D. Usage privilege on the integration controls whether a role may create or reference the function at all; it has no effect on the row batching behavior of requests already being sent.

Correct answer: A — Each procedure runs its own scoped transaction independently, and `proc_outer` cannot commit or roll back the transaction that `proc_inner` already completed internally

• A. Correct — Snowflake supports scoped transactions for nested procedure calls: each procedure's transaction is independent and self-contained, so the outer procedure has no ability to commit or roll back a transaction the inner procedure already finished.
• B. Snowflake does not merge nested procedure transactions into a single shared transaction; each procedure's BEGIN/COMMIT pair is scoped to that procedure, so a rollback inside the inner call does not reach back into the outer procedure's earlier work.
• C. Nested calls do not suppress the inner procedure's transaction statements — the inner procedure's own BEGIN TRANSACTION and COMMIT execute and take effect within its own scope.
• D. Opening a transaction inside a procedure that was itself called from another procedure is a supported, common pattern in Snowflake Scripting; it does not raise an error on its own.

Correct answer: A — A permanent file URL produced by `BUILD_STAGE_FILE_URL`, since it identifies the database, schema, stage, and relative path and resolves through the REST endpoint whenever the caller's role holds the needed stage privilege.

• A. BUILD_STAGE_FILE_URL returns a permanent, non-expiring reference encoding the database, schema, stage, and path, and resolving it through the REST API still checks that the caller's role holds READ or USAGE on the stage, matching a long-lived stored reference with privilege-based access.
• B. A scoped URL is intentionally short-lived, tied to the generating user, and expires around the result cache period, so it is not suitable for a reference meant to be stored and resolved indefinitely later.
• C. A presigned URL deliberately skips Snowflake role checks entirely so it can be opened without authentication; it does not enforce or reflect stage-level role privileges at resolution time.
• D. RELATIVE_PATH is only a metadata string describing a file's location within the stage and is not accepted by the REST API as a substitute for one of the generated URL types.

Correct answer: A — An implicit object layered on top of a stage that catalogs file-level metadata for the staged files, rather than a separate database object created with its own DDL and privilege set.

• A. A directory table is described as an implicit object layered on a stage, exposing file metadata for querying without being a separately created database object with its own DDL and distinct privilege set.
• B. There is no `CREATE DIRECTORY TABLE` command; directory tables are enabled through the DIRECTORY parameter on CREATE STAGE or ALTER STAGE, not created as an independent object.
• C. Directory tables store metadata such as path, size, and timestamps about staged files, not a materialized copy of the file bytes themselves, so query engines still resolve URLs to read actual content.
• D. There is no account-wide default view enumerating all stages and their byte totals; directory tables are per-stage metadata catalogs that must be explicitly enabled and queried per stage.

Correct answer: A — Add `OUTER => TRUE` to the `LATERAL FLATTEN` call so that products whose `tags` path is missing or empty still produce one output row with NULL flattened values instead of being dropped.

• A. `OUTER => TRUE` is exactly the parameter designed for this scenario: when the input to flatten evaluates to NULL, missing, or an empty array, it still emits one row with the flattened columns set to NULL, so rows are not silently dropped.
• B. This is incorrect because `RECURSIVE => TRUE` controls whether flatten descends into nested sub-structures under the given path, not whether it preserves rows when the path is absent; it does not solve the missing-row problem.
• C. This is incorrect because a plain `CROSS JOIN` against a table function is not valid Snowflake syntax for this pattern, and without `LATERAL`, the join cannot reference the correlated `attributes:tags` expression from the outer row.
• D. This is incorrect because coalescing to an empty array still produces zero flattened rows for that product under default `OUTER => FALSE` behavior, since flattening an empty array yields no rows either way.

Correct answer: A — `OBJECT_CONSTRUCT('customer_id', customer_id, 'email', email, 'signup_date', signup_date)`, since it builds a VARIANT object from explicit key/value pairs drawn directly from relational columns.

• A. `OBJECT_CONSTRUCT` takes alternating key and value arguments and returns a single VARIANT OBJECT, which is exactly the tool for assembling several relational columns into one JSON-shaped object per row.
• B. This is incorrect because `ARRAY_AGG` takes a single expression and aggregates its values across multiple rows into an array; it does not accept several columns from one row and does not produce keyed object output.
• C. This is incorrect because `FLATTEN` is a table function that explodes an existing ARRAY or OBJECT into multiple rows; it has no reverse mode for combining scalar columns into a nested structure.
• D. This is incorrect because `GET_PATH` extracts a value from an existing VARIANT at a given path string; it takes a VARIANT and a path as its two arguments, not a list of scalar columns to combine.

Correct answer: A — ```python filtered_df = orders_df.filter((col("order_status") == "SHIPPED") & (col("order_total") > 100)) ```

• A. Using the `col()` function to reference columns and combining conditions with the `&` operator inside a `filter()` call is the correct Snowpark pattern for a multi-condition WHERE-style filter, and it returns a new lazily evaluated DataFrame.
• B. Snowpark's `where()` method (an alias for `filter()`) takes a Column expression built from `col()` comparisons, not a raw string with a `parse_string` keyword argument; that parameter and calling convention do not exist in the API.
• C. `select()` projects columns and does not accept keyword arguments like `order_status` or `order_total` to restrict rows, and `limit()` only caps the row count returned; neither method performs conditional row filtering.
• D. `group_by()` aggregates rows into groups and does not have a `having()` method that takes a raw filter string in this form; grouping is the wrong tool for filtering individual order rows before any aggregation is needed.

Correct answer: C — Configure a Snowsight Workspace connected to the Git repository so engineers can switch branches and push or pull file changes in the browser.

• A. Manually copying finished statements into a branch happens after the fact and loses the real-time diffing, review, and sync that a version-controlled workflow needs.
• B. A scheduled notebook run tracks execution results, not source code changes, so it does not substitute for a version-controlled editing surface for general SQL development.
• C. A Git-connected Workspace is a file-based development surface in Snowsight where engineers switch branches and push or pull changes, matching the collaborative, browser-based requirement.
• D. A named CLI target configures a connection profile for deployments; it does not provide a browser-based collaborative editing or branching surface for day-to-day SQL work.

Correct answer: B — An API integration authorized for the Git provider's endpoint, plus a secret holding the credentials when the repository is private.

• A. A storage integration authorizes access to a cloud storage bucket for external stages; it has no role in authenticating a Git provider connection.
• B. Creating a Git repository object requires an API integration authorized for the provider's endpoint, plus a secret for credentials when the repository is private, which is exactly what the statement needs to succeed.
• C. Resource monitors cap warehouse credit consumption; Git fetch operations against a repository stage are not gated by a resource monitor.
• D. No such materialized view is required to create or query a repository stage; the stage is browsed with standard stage listing and Git commands instead.