SnowPro Advanced: Data Analyst (DAA-C01) Practice Tests

Explanation

The CUBE function generates every possible combination of the specified grouping columns, including a grand total row with no columns grouped. With n columns, CUBE produces 2^n distinct grouping sets. For three columns (region, category, quarter), CUBE generates 2^3 = 8 grouping sets: (region, category, quarter), (region, category), (region, quarter), (category, quarter), (region), (category), (quarter), and an empty set representing the overall grand total across all dimensions. If the analyst only needed hierarchical subtotals progressing left to right — such as region down to region-category-quarter — ROLLUP would be the appropriate choice, producing only n+1 = 4 grouping combinations: (region, category, quarter), (region, category), (region), and the grand total. GROUPING SETS allows explicit manual selection of any desired subset of groupings when neither CUBE nor ROLLUP matches the exact requirement.

Explanation

TO_NUMBER(amount_str, 10, 2) explicitly sets precision to 10 and scale to 2, preserving two decimal places and returning 49.99. TO_DOUBLE(amount_str) converts the string to a double-precision floating-point value, which also returns 49.99 and is acceptable when approximate arithmetic is sufficient. TO_NUMBER(amount_str) with no arguments defaults to scale 0, which rounds '49.99' to 50 rather than truncating to 49. CAST(amount_str AS NUMBER) resolves to NUMBER(38, 0) in Snowflake, which also defaults to scale 0 and rounds to 50. CAST(amount_str AS DECIMAL) without explicit precision and scale similarly resolves to DECIMAL(38, 0) and rounds to 50. For financial workloads requiring exact decimal representation, either TO_NUMBER with explicit scale or CAST(amount_str AS DECIMAL(10, 2)) should be used instead of the bare TO_NUMBER call.

Explanation

The PACKAGES clause is the correct way to declare third-party libraries from Snowflake's Anaconda channel for Python UDFs and stored procedures. Specifying PACKAGES = ('numpy') instructs Snowflake to make numpy available in the UDF execution environment without requiring the developer to upload or stage any files. IMPORTS is a separate, valid clause used to reference staged files such as custom Python modules, data files, or pre-built wheel files — it is not used for Anaconda channel packages. DEPENDENCIES and LIBRARIES are not valid Snowflake SQL clauses for UDF definitions and would produce a syntax error if used.

Explanation

PATTERN (DOWN+ UP+) is correct because the '+' quantifier means 'one or more', which precisely matches the requirement for one or more consecutive declining days followed by one or more consecutive recovering days. PATTERN (DOWN* UP*) uses '*' meaning 'zero or more', which would match rows with no price movement at all and produce spurious results including zero-length matches — a pattern with no declining or increasing days would still qualify. PATTERN (DOWN UP) requires exactly one declining day and exactly one increasing day; it would miss multi-day declines or multi-day recoveries. PATTERN (DOWN? UP?) uses '?' meaning 'zero or one', allowing at most one day of each type and also permitting empty matches, neither of which satisfies the requirement for a meaningful V-shape. The correct quantifier for 'one or more' in MATCH_RECOGNIZE is always '+'.

Explanation

CUBE(region, product_category) generates all 2^2 = 4 grouping combinations: (region, product_category), (region), (product_category), and the empty grouping () for the grand total. This is precisely the set of all possible subsets of the two dimensions. ROLLUP(region, product_category) produces only three groupings — (region, product_category), (region), and () — omitting the standalone product_category subtotal entirely. GROUPING SETS with explicit entries for (region, product_category), (region), and (product_category) covers three groupings but excludes the grand total row. A plain GROUP BY without modifiers produces only detail-level rows with no subtotals or grand total.