Free Snowflake SnowPro Advanced: Data Analyst (DAA-C01) Sample Questions

Correct answer: B — `XMLGET`

• A. `PARSE_XML` converts a string into an XML-typed VARIANT. It does not pick a child element from a value that is already parsed.
• B. `XMLGET` returns the child element with the given tag name from an XML value, for example `XMLGET(doc, 'customer')`, and reads the content with `:"$"`.
• C. `CHECK_XML` only validates whether text is well-formed XML and returns an error message or NULL, so it extracts nothing.
• D. `STRIP_NULL_VALUE` converts JSON null in a VARIANT to SQL NULL and has no concept of XML tags.

Correct answer: A — SELECT SEQ4() AS order_id, UNIFORM(10, 500, RANDOM()) AS amount FROM TABLE(GENERATOR(ROWCOUNT => 5000000));

• A. `GENERATOR(ROWCOUNT => ...)` inside `TABLE()` produces the rows, `SEQ4()` supplies sequential IDs, and `UNIFORM` fed with `RANDOM()` yields a new value between 10 and 500 per row.
• B. `RANDOM` accepts only an optional seed, not a range, so passing `10, 500` is invalid. A bounded range requires `UNIFORM`.
• C. A constant seed of 42 as the third `UNIFORM` argument gives every row the same amount, so the values do not vary randomly across rows.
• D. The table function must be wrapped as `TABLE(GENERATOR(...))` in the `FROM` clause, so this form is rejected by Snowflake.

Correct answer: B — Grant IMPORT SHARE and CREATE DATABASE on the account to the role, so it can mount provider data as a database.

• A. CREATE SHARE and CREATE LISTING are provider-side privileges used to publish data, not to consume it, so they do not let a role mount a listing as a database.
• B. A role with IMPORT SHARE and CREATE DATABASE on the account can get a listing and create the database from it. ACCOUNTADMIN holds these by default, but they can be delegated to an analyst role.
• C. MANAGE GRANTS and warehouse USAGE do not include the right to create a database from a share. Without IMPORT SHARE the Get action on the listing is refused.
• D. A consumer cannot be granted USAGE on a provider's database directly, because the provider's database does not exist in the consumer account until it is created from the share. IMPORT SHARE is still missing.

Correct answers: A, B — Load the files into a staging table first, then run `MERGE INTO sales.orders` keyed on `order_id` with a `WHEN NOT MATCHED THEN INSERT` clause only.; Insert from staging using `QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY loaded_at DESC) = 1` after excluding keys already present in the target.

• A. Correct: a MERGE that only inserts unmatched keys applies the uniqueness rule inside the load itself, so replayed rows find a match and are not inserted again.
• B. Correct: deduplicating the staged rows with ROW_NUMBER and filtering out keys already in the target guarantees that only one new row per `order_id` is inserted.
• C. Incorrect: Snowflake has no `ENFORCED` keyword for standard-table key constraints, so this statement is not valid and would not add enforcement.
• D. Incorrect: `RELY` only tells the optimizer it may trust the key for query rewrites. It never validates or rejects rows during a load.
• E. Incorrect: no `CONSTRAINT_ENFORCEMENT` session parameter exists, and standard-table keys stay informational regardless of session settings.

Correct answer: C — `SHOW IMPORTED KEYS IN TABLE sales.order_lines;`

• A. Incorrect: exported keys show foreign keys in other tables that reference this table's primary key, so for a child table it describes the wrong direction.
• B. Incorrect: this lists the primary key columns of the child table itself, not the parents it references.
• C. Correct: imported keys list the foreign keys defined on the table together with the parent table and columns they reference.
• D. Incorrect: this lists the unique constraint columns of the child itself and shows nothing about foreign key relationships.

Correct answer: C — Add `WHEN SYSTEM$STREAM_HAS_DATA('RAW_ORDERS_STRM')` to the task definition so runs with an empty stream are skipped before the warehouse resumes.

• A. Incorrect. A CRON expression only defines when runs are attempted; Snowflake does not merge or skip runs based on whether a stream holds data.
• B. Incorrect. Disabling auto-suspend keeps the warehouse billing continuously, which increases credit usage rather than reducing it.
• C. Correct. The `WHEN` condition is evaluated by the cloud services layer before compute is started, so an empty stream skips the run and the warehouse never resumes.
• D. Incorrect. The stored procedure executes on the task's warehouse, so the warehouse still resumes every minute just to discover that there is nothing to merge.

Correct answer: C — Give `LOAD_RAW` the `SCHEDULE` and declare each later task with an `AFTER` clause naming its predecessor, so only the root task carries a schedule.

• A. Incorrect. Scheduling the last task would run only that task; Snowflake does not walk backward to start predecessors, so the graph needs the schedule on the root.
• B. Incorrect. Tasks that use `AFTER` cannot have their own schedule, and staggered CRON times would not guarantee that the predecessor had completed.
• C. Correct. In a task graph only the root task has a schedule, and the `AFTER` clause makes each child run once its predecessor has finished successfully.
• D. Incorrect. `WHEN` is a Boolean condition such as a stream-has-data check; it does not declare a dependency, which is the job of `AFTER`.

Correct answer: B — All IDs from `legacy_ids` plus the IDs found in both `new_a` and `new_b`, de-duplicated, because INTERSECT is evaluated before UNION

• A. INTERSECT returns only rows common to both inputs, and UNION removes duplicates, so duplicates are not retained and the operators do not behave as UNION ALL.
• B. INTERSECT has higher precedence than UNION, EXCEPT and MINUS in Snowflake, so new_a and new_b are intersected first and the result is then unioned with legacy IDs. Parentheses can be added to force a different order.
• C. That reading would apply when the UNION is parenthesised first. Without parentheses, INTERSECT is evaluated first, so the grouping differs.
• D. Snowflake does not evaluate these operators strictly left to right. INTERSECT binds tighter, so the result is not the three-way intersection.

Correct answer: D — `STRIP_OUTER_ARRAY = TRUE`, which removes the enclosing brackets so each object inside the array is loaded as a separate row

• A. `STRIP_NULL_VALUES` removes object fields and array elements that are null. It does not split the outer array into rows.
• B. `ALLOW_DUPLICATE` controls whether duplicate field names inside an object are kept. It has no effect on row splitting.
• C. `ENABLE_OCTAL` only changes how numbers with leading zeros are parsed. It has no bearing on array handling.
• D. Correct. `STRIP_OUTER_ARRAY = TRUE` removes the outer brackets, so each element of the array becomes a separate row in the VARIANT column. Alternatively, `LATERAL FLATTEN` could split the array after loading.

Correct answers: A, B — Use `AVG(COALESCE(amount, 0))` so that pending rows enter the denominator with a value of zero instead of being skipped by the aggregate.; Use `COUNT(amount)` so that only rows holding a non-NULL amount are counted, which excludes the pending payments from the tally.

• A. Correct: COALESCE replaces NULL with 0 before AVG runs, so pending rows count in the denominator as zero, which is exactly the requested treatment.
• B. Correct: COUNT with a column argument counts only non-NULL values, so it returns the number of payments that have a recorded amount.
• C. Incorrect: AVG ignores NULL inputs, so pending rows are excluded from both sum and denominator and the average is higher than the business wants.
• D. Incorrect: COUNT(*) counts every row including those with a NULL amount, so it does not isolate payments with a recorded amount.
• E. Incorrect: SUM ignores NULLs and COUNT(amount) skips them, so this ratio equals a plain AVG(amount) and never treats pending payments as zero.

Correct answer: A — Join with `LATERAL FLATTEN(input => e.payload:items) f` and read `f.value:sku::STRING` and `f.value:qty::NUMBER` for each element row.

• A. Correct: FLATTEN is a table function, and LATERAL lets it reference the outer row, producing one row per array element with `value` holding each object.
• B. Incorrect: PARSE_JSON converts a string into a VARIANT but returns a single value per input row, so the array is not expanded into separate rows.
• C. Incorrect: ARRAY_SIZE is a scalar function that returns the number of elements as one number per row, and it cannot be used in FROM to produce rows.
• D. Incorrect: SPLIT_TO_TABLE splits a string on a delimiter, so it is meant for text and does not iterate the elements of a VARIANT array of objects.

Correct answers: A, C — INDEX; VALUE

• A. Correct. INDEX holds the zero-based position of the element when the input is an array. It gives the numeric position the analyst needs.
• B. KEY holds the property name when flattening an object, and is NULL when the input is an array. It cannot give the position of an array element.
• C. Correct. VALUE contains the element at the current position as a VARIANT. It can be cast or navigated with path notation.
• D. SEQ is a unique sequence number for each flattened input row, not the position of an element within the array. It would not distinguish elements inside one array.
• E. THIS holds the whole collection being flattened, so every output row from one array repeats the same value. It does not provide an element or its position.

Correct answer: B — `PARSE_JSON`

• A. TO_JSON does the reverse operation: it serializes a VARIANT into JSON text. It does not parse text.
• B. Correct. PARSE_JSON interprets a JSON string and returns a VARIANT. The result supports path notation and FLATTEN.
• C. OBJECT_CONSTRUCT builds an object from key and value arguments. It does not interpret a JSON string.
• D. TO_ARRAY converts a single value into an array. It does not parse JSON text into objects.

Correct answers: B, C — Divide the difference by `NULLIF(prev_revenue, 0)` so a zero baseline yields NULL instead of a division error.; Fetch the prior month with `LAG(revenue) OVER (ORDER BY month)`, which returns NULL for the first month automatically.

• A. LEAD returns the following month's revenue, so the formula would compare each month to the future and the sign of the change would be reversed.
• B. NULLIF turns a zero denominator into NULL, and the division then returns NULL rather than failing, which is acceptable for a month with no meaningful baseline.
• C. LAG reads the previous row in month order, and because no earlier row exists for the first month it yields NULL, which flows through to a NULL change.
• D. Replacing the missing baseline with zero makes the denominator zero for the first month, which causes the very division-by-zero error the analyst wants to avoid.
• E. Partitioning by month puts every row alone in its partition, so LAG finds no previous row and returns NULL for every month.

Correct answer: A — Buckets 1 and 2 receive three rows each, and buckets 3 and 4 receive two rows each, because remainder rows go to the lowest-numbered buckets.

• A. When the row count does not divide evenly, NTILE assigns the extra rows to the earliest buckets, giving sizes of 3, 3, 2 and 2.
• B. NTILE places the leftover rows in the first buckets, not the last, so the larger groups are at the low end of the ordering.
• C. NTILE spreads the remainder one row at a time across buckets, so no bucket can differ from another by more than one row.
• D. NTILE does not require divisibility; it handles uneven counts by giving some buckets one extra row, and the query runs normally.

Correct answer: A — The join condition is missing or matches on a non-unique key, producing a Cartesian-like row explosion between the two tables

• A. A Join node whose output is far larger than both inputs signals a row explosion, typically from a missing, partial or non-unique join predicate.
• B. A cached result would be returned instead of running the join, and it could not cause a multiplied intermediate output.
• C. Cache misses slow the scan nodes but do not multiply row counts. The output size from the Join node would remain bounded by the inputs.
• D. Clustering never creates or duplicates rows. It changes only the physical arrangement of micro-partitions.

Correct answer: B — `EXPLAIN USING TEXT SELECT ...` returns the plan with `partitionsAssigned` and `partitionsTotal` without executing it

• A. DESCRIBE RESULT returns the column definitions of a previous result and not the operator-level plan.
• B. EXPLAIN compiles the statement and shows the plan with partition estimates, with no execution and no result.
• C. This command lists parameter values only. It carries no plan or partition information.
• D. That function reports clustering depth statistics for a table and columns. It does not provide a query's execution plan.

Correct answers: C, D — TRY_TO_NUMBER(amount_txt, 12, 2); TRY_CAST(amount_txt AS NUMBER(12,2))

• A. Incorrect. The double-colon shorthand is equivalent to CAST and fails on non-numeric text.
• B. Incorrect. TO_NUMBER raises an error as soon as it meets 'N/A', so the query fails rather than returning NULL.
• C. Correct. The TRY_ conversion returns NULL for strings that cannot be parsed, so 'N/A' becomes NULL while '19.99' converts normally.
• D. Correct. TRY_CAST accepts a string input and returns NULL instead of raising an error when the conversion fails.
• E. Incorrect. A plain CAST is the strict form and errors on a non-numeric string.

Correct answers: D, E — Clone with `CREATE TABLE invoices_recovered CLONE invoices AT (TIMESTAMP => '2026-10-05 13:59:00'::TIMESTAMP_LTZ)`, then merge missing rows; Insert rows from `invoices BEFORE (STATEMENT => '<delete query id>')` whose `invoice_id` is absent from the current table

• A. Incorrect. Fail-safe only applies after the Time Travel period ends and is not self-service, so it is irrelevant while retention is still active.
• B. Incorrect. Snowflake has no RESTORE TABLE command; recovery is done through cloning or querying with AT/BEFORE.
• C. Incorrect. UNDROP restores dropped objects, not deleted rows in a table that still exists.
• D. Correct. The clone captures the table from just before the delete, within the retention window, and the missing rows can then be copied across.
• E. Correct. Time Travel can query the table as it was before the delete statement, and filtering on missing keys restores only the lost rows.

Correct answer: C — Insert a new customer row per change with surrogate key, validity dates and current flag; facts load with the key in effect

• A. A type 2 slowly changing dimension keeps each version under its own surrogate key, so a fact row points at the version valid at sale time. Aggregations by region then reproduce history correctly.
• B. Overwriting is a type 1 approach and rewrites history, so old sales would be reported under the new region. That contradicts the finance requirement.
• C. A type 3 design holds only one earlier value, so a customer who moves twice loses the first region. It cannot give the region as of each sale date.
• D. Time Travel retention is limited and querying it per fact row is impractical for reporting. History needed for BI belongs in the model itself.

Correct answers: A, C — Aggregate SHIPMENTS to one row per order in a subquery or CTE before joining it to ORDERS at order grain; Model each process as its own fact at its own grain, aggregate each, then combine via shared dimensions

• A. Pre-aggregating the many-side to the order grain makes the join one-to-one, so order_total is no longer repeated. This removes the fan-out at its source.
• B. SUM(DISTINCT) drops legitimate repeated values, such as two different orders with the same total. It hides duplication by corrupting the sum.
• C. Facts at different grains should not be joined row to row. Aggregating each fact and joining on conformed dimensions avoids multiplying measures.
• D. A LEFT JOIN keeps unmatched orders but still returns one row per matching shipment. The duplication of order_total remains.
• E. Clustering affects micro-partition pruning and not join cardinality. The join would still produce multiple rows for orders with multiple shipments.

Correct answer: D — Splitting the aggregate by demographic and plan can reveal opposing segment trends that cancel out overall.

• A. Incorrect: GROUP BY does not rebuild statistics or change how the overall rate is calculated; it only adds finer grouping.
• B. Incorrect: counts show segment size, not cause; the result describes association only and does not prove causation.
• C. Incorrect: AVG ignores nulls, but that behavior is not the reason for a flat total, and it does not diagnose segments.
• D. Correct: segment-level rates can show that one demographic worsens while another improves, a pattern hidden when only the combined rate is viewed.

Correct answers: C, D — `AVG(amount) OVER (PARTITION BY merchant_id)` to get each merchant's mean on every transaction row.; `STDDEV_SAMP(amount) OVER (PARTITION BY merchant_id)` to get each merchant's spread on every row.

• A. Incorrect: MEDIAN is not a mean, and GROUP BY cannot be placed inside a select expression like that; it would also collapse the rows.
• B. Incorrect: NTILE creates equal-size buckets by rank and does not count standard deviations from a mean.
• C. Correct: a window average partitioned by merchant supplies the mean needed for the z-score without collapsing rows.
• D. Correct: a partitioned sample standard deviation supplies the denominator, so (amount - mean) / stddev can be filtered at three.
• E. Incorrect: CORR measures the relationship between two numeric columns, and a merchant identifier is not a meaningful numeric input.

Correct answers: B, D — Anyone in the account can view and use a custom filter once it exists, regardless of the role associated with the filter; The role associated with a filter determines which users are allowed to edit or delete that filter after it was created

• A. A query-based filter runs its options query on compute, so it is associated with a role and warehouse.
• B. Visibility and use are account-wide, so a filter created by one team can be picked up by others.
• C. Selecting values is not limited to the owning role. Using a filter is open to the account.
• D. The attached role controls modification rights, which is how ownership is expressed for these objects.
• E. Filters exist independently of any single dashboard, so removing one dashboard leaves the definition intact.

Correct answer: C — One million rows can be displayed in the results panel, after which the data must be exported or aggregated

• A. `UI_ROW_LIMIT` does not exist.
• B. The display cap is far higher, and no temporary table is created for overflow.
• C. Worksheet results tables show up to 1 million rows for most accounts.
• D. A cap applies, so very large results must be aggregated or exported.

Correct answer: C — SERIES, TS, FORECAST, LOWER_BOUND and UPPER_BOUND, with SERIES holding NULL when the model was trained on a single series.

• A. Incorrect. Importance is available through its own method, and the output does not carry model name or importance columns.
• B. Incorrect. Those column names are invented; the real output uses TS, FORECAST, LOWER_BOUND and UPPER_BOUND.
• C. Correct. These are the standard output columns, and the series label is only populated for multi-series models.
• D. Incorrect. Error metrics come from SHOW_EVALUATION_METRICS and cannot be computed for future dates.

Correct answer: D — A 95 percent prediction interval, because the default prediction_interval value is 0.95 for a forecast call.

• A. Incorrect. The band is not defined as one standard deviation and defaults to 95 percent coverage.
• B. Incorrect. The default is 0.95 and does not depend on the method.
• C. Incorrect. Enabling evaluation does not change the interval level, which defaults to 0.95.
• D. Correct. The default level is 0.95, and it can be overridden through the config object.

Correct answer: C — `RETURNS NULL ON NULL INPUT`, so a NULL argument returns NULL without executing the body

• A. CALLED ON NULL INPUT is the default and does the opposite: the body is executed even when arguments are NULL.
• B. IMMUTABLE only declares that the same input always yields the same output; it does not alter NULL handling.
• C. This setting (equivalent to STRICT) short-circuits NULL inputs and returns NULL immediately, avoiding wasted evaluation.
• D. COMMENT is documentation metadata and has no effect on execution behavior.

Correct answer: B — Start each statement as a child job with `ASYNC`, then run `AWAIT ALL` so the procedure waits for them

• A. Transactions group statements atomically but still run them sequentially.
• B. ASYNC starts child jobs without blocking, so independent statements run concurrently, and AWAIT ALL ensures the procedure continues only after all complete.
• C. UDFs cannot run DML, so the inserts could not be performed that way.
• D. Execution rights control privileges, not concurrency.

Correct answers: A, B — The warehouse chosen in the context selector is one the selected role can USAGE-operate, so tile queries have compute to run on.; The role selected in the dashboard context selector holds SELECT privileges on every table or view the tiles query.

• A. Correct. Every tile query needs a warehouse, and the selected role must hold USAGE on it, otherwise the tile fails or never starts.
• B. Correct. Tile queries run under the role chosen in the context selector, so that role needs SELECT on each referenced object or the tile returns an authorization error.
• C. Incorrect. ACCOUNTADMIN is not required and would violate least privilege; any role with the right object grants is enough.
• D. Incorrect. Snowsight has no per-viewer profile database setting that is applied to dashboard tiles; context comes from the dashboard and tile settings.
• E. Incorrect. A standard single-cluster warehouse can run dashboard tiles; multi-cluster only helps with heavy concurrency and is not a requirement.

Correct answers: C, E — Every custom filter is associated with a role, and that role can edit or delete it, so ANALYST_ROLE cannot change it.; ACCOUNTADMIN grants other roles the permission to create custom filters, so the new team needs this grant first.

• A. Incorrect. System filters such as :daterange are built in and can't be edited or removed by dashboard owners.
• B. Incorrect. Warehouse privileges do not govern filter editing; the associated role does.
• C. Correct. Filters belong to a role, and only that role can modify or remove them.
• D. Incorrect. Ownership is tied to a role, not to an individual user or to a manager.
• E. Correct. Creating custom filters is controlled by a privilege that ACCOUNTADMIN grants to roles.

Correct answer: B — Add `WHEN SYSTEM$STREAM_HAS_DATA('ORDERS_STREAM')` to the task definition together with a five-minute schedule.

• A. Smaller compute reduces cost per run but still resumes the warehouse every five minutes, which the requirement rules out.
• B. The `WHEN` condition is evaluated in cloud services before compute starts, so empty checks do not consume warehouse credits.
• C. The task has already started its warehouse by then, so credits are consumed for every empty run.
• D. An alert is an extra object that also needs scheduling, and it duplicates what the task `WHEN` clause does natively.

Correct answers: A, C — The masking policy is evaluated at query time using the active role, so the tile returns masked values for `BI_READER`.; Changing the tile's chart type or renaming the column has no effect on the masking, since the policy sits on the table.

• A. Masking depends on the role in the session context, which for the dashboard is the context role.
• B. Dashboards do not run as the creator; the context role governs which version of the data is returned.
• C. Policies are attached to the column at the data layer, so presentation changes cannot expose clear text.
• D. Masking is not tied to link sharing; it applies wherever the role in use is not allowed to see clear text.
• E. Masking policies return transformed values rather than errors, so tiles load normally with masked content.

Correct answer: A — A bar chart with `response_ms` bucketed on the X-axis using an integer bucket size and `COUNT` as the aggregated Y-axis measure

• A. Numeric bucketing groups values into ranges, and counting rows per bucket produces a histogram-style distribution.
• B. Alphabetical ordering of numbers is not binning and gives a misleading sort such as 10 before 9.
• C. An average hides the shape of the distribution, including any slow tail.
• D. Putting the same variable on both axes with MAX shows extremes, not frequency of values.

Correct answers: A, D, F — `USAGE` on a virtual warehouse that the viewer selects as the context for running the dashboard's tile queries; `USAGE` on the database `SALES_DB` and on the schema `SALES_DB.MART` so the role can resolve the objects the tiles query; `SELECT` on the `ORDERS_SUMMARY` table or view so the tile queries can read its rows when the role runs them

• A. A warehouse is needed to execute queries, so the role must be able to use one.
• B. Ownership is not required to view and run a shared dashboard.
• C. Tiles read data; they do not need permission to create tables.
• D. Object resolution needs USAGE on both the containing database and schema.
• E. Resizing is not needed to run tiles; USAGE is enough.
• F. Tiles execute SELECT statements, so the role must hold SELECT on the object.