Pass the Snowflake SnowPro Advanced Certification DAA-C01 Questions and answers with Dumpstech

In Snowsight, managing dashboard layouts requires an understanding of how tiles (queries or visualizations) are stored versus how they are displayed. When an analyst wants to remove a tile from the visible dashboard grid without destroying the underlying query logic or historical configuration, the Unplace action is the correct functional choice.

When a tile is unplaced, it is removed from the dashboard's active layout but remains part of the dashboard's "library" of available content. This is a critical distinction from the Delete action (Option C), which permanently removes the tile and its associated SQL code from the dashboard object. Unplacing allows the analyst to "archive" the work temporarily. Because the tile still technically exists within the dashboard's metadata, any new data added to the underlying tables will still be processed by the query whenever the tile is eventually placed back onto the grid.

Evaluating the Options:

Option A (Show/Hide) is not a standard standalone command for dashboard tile management in Snowsight; visibility is typically managed through placement on the grid.

Option B (Duplicate) creates a second copy of the tile. While this preserves the data, it does not satisfy the requirement to "hide" the current tile; it actually adds more clutter to the dashboard.

Option C (Delete) is incorrect because the prompt specifies that the data and tile will need to be available in the future. Deleting would require the analyst to rewrite the SQL and reconfigure the visualization from scratch.

Option D is the 100% correct answer. Unplacing is the "soft-remove" feature of Snowsight. It preserves the tile in the "Unplaced Tiles" sidebar, allowing for quick restoration at a later date. This feature is essential for analysts who need to manage evolving reporting requirements where certain metrics may only be relevant seasonally or during specific business cycles.

To determine the correct output, a Data Analyst must understand the behavior of the STRTOK_SPLIT_TO_TABLE table function and how it interacts with different delimiters.

1. Functionality of STRTOK_SPLIT_TO_TABLE:

The STRTOK_SPLIT_TO_TABLE function tokenizes a string based on a set of specified delimiters. Unlike simple SPLIT functions that often look for a literal string as a delimiter, STRTOK treats the delimiter string as a set of individual characters. Any character present in the delimiter string will trigger a split. Crucially, it skips empty tokens—if multiple delimiters appear consecutively, it does not produce a row for the space between them.

2. Evaluating the Data and Delimiters ('*-'):

'JOHN*DOE': The * character is a delimiter. This results in two tokens: JOHN and DOE.

'ALICE-SMITH': The - character is a delimiter. This results in two tokens: ALICE and SMITH.

'BOB*-JOHNSON': Both * and - are delimiters. Because they appear consecutively (*-), the function splits at each but ignores the empty space between them. This results in two tokens: BOB and JOHNSON.

3. Analyzing the Resulting Rows:

The LATERAL join produces a row for every token generated. The columns SEQ (identifying the source row sequence) and INDEX (position of the token within that sequence) are used for sorting.

Student 1: 2 tokens (JOHN, DOE) $\rightarrow$ 2 rows.

Student 2: 2 tokens (ALICE, SMITH) $\rightarrow$ 2 rows.

Student 3: 2 tokens (BOB, JOHNSON) $\rightarrow$ 2 rows.

Evaluating the Options (image_8233d8.png):

Option A incorrectly only shows results for Student 3.

Option C incorrectly implies Student 1 and 2 produce three tokens each.

Option D incorrectly interprets the consecutive delimiters in Student 3's name as creating a different sequence count.

Option B is the 100% correct result set. It accurately shows that each student’s name is split into exactly two parts, properly handling the varying delimiters and the consecutive characters in the third record.

When working with "Big Data" at the scale of trillions of rows, calculating an exact count of unique values using COUNT(DISTINCT column) is extremely resource-intensive. This is because Snowflake must keep track of every unique value encountered to ensure no duplicates are counted, leading to high memory usage and long execution times (often referred to as "spilling to disk").

To solve this, Snowflake provides HyperLogLog (HLL) functions. HLL(*) (or specifically HLL_ACCUMULATE and HLL_ESTIMATE) allows an analyst to estimate the cardinality (the number of unique elements) with a very small, known margin of error (typically around 1%). This is significantly faster and uses far fewer credits than an exact count because it uses a probabilistic algorithm rather than a state-heavy tracking mechanism.

Evaluating the Options:

Option A is technically correct for small datasets but is highly inefficient for trillions of rows, directly contradicting the "efficiently" requirement of the question.

Option C is a distractor; while Snowflake has various SYSTEM$ functions, SYSTEM$ESTIMATE is not a standard function for cardinality.

Option D is a formula that doesn't target cardinality but rather a ratio (density).

Option B is the correct answer. The HLL family of functions is the industry standard within Snowflake for high-performance cardinality estimation on massive datasets.

To determine the correct output, a Data Analyst must understand the behavior of the ILIKE operator, wildcard characters, and the ESCAPE clause in Snowflake.

1. Pattern Matching with ILIKE: The ILIKE operator performs case-insensitive pattern matching. The provided pattern is 'p%^_j%'.

p: The string must start with the letter 'P' or 'p'.

%: This wildcard matches any sequence of zero or more characters.

^_: The ESCAPE '^' clause identifies the caret symbol as an escape character. This means the underscore (_) immediately following it is treated as a literal character rather than its usual wildcard function (which matches any single character).

j: The letter 'J' or 'j' must follow the literal underscore.

%: Matches any trailing sequence of characters.

2. Evaluating the Data: Applying this logic to the values in the cert_dem table:

'Peter*John': While it starts with 'P' and contains 'J', it lacks the required literal underscore. No match.

'Peter John': Contains a space instead of an underscore. No match.

'Peter_John': Starts with 'P', contains a literal underscore, and is followed by 'J'. Match.

'Peter_john': Because the operator is ILIKE (case-insensitive), the lowercase 'j' is accepted. Match.

null: Comparisons with NULL using LIKE or ILIKE always return NULL (unknown), so it is excluded from the results.

3. Ordering the Results: The query includes ORDER BY 1, which sorts the results alphabetically based on the first column. Between 'Peter_John' and 'Peter_john', the standard sort order typically places uppercase before lowercase in most Snowflake collations.

Evaluating the Options (image_829269.png):

Option A incorrectly excludes the case-insensitive match.

Option B incorrectly identifies values that lack the literal underscore.

Option D incorrectly includes values that do not meet the escaped underscore requirement.

Option C is the 100% correct result set, displaying both versions of 'Peter_John' that contain the literal underscore.

Snowflake Cortex ML functions, such as FORECAST, return a tabular result set when called using the instance method syntax (e.g., CALL modelX!FORECAST(...)). While this output is visible in the Snowsight results pane, the CALL statement itself cannot be used directly as a subquery within a standard INSERT INTO or CREATE TABLE AS SELECT (CTAS) statement.

To persist the results of a model's prediction into a permanent table (tableX), the Data Analyst must utilize the RESULT_SCAN table function. Snowflake stores the results of every query and function call in a temporary cache for 24 hours. The RESULT_SCAN function allows you to treat that cache as a queryable table.

The standard workflow is:

Execute the forecast: CALL modelX!FORECAST(FORECASTING_PERIODS => 12);

Immediately after, use the LAST_QUERY_ID() function to identify the query that generated the forecast results.

Create the table by querying that result set: CREATE TABLE tableX AS SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Evaluating the Options:

Option A is incorrect because the CALL syntax does not support a direct INTO clause for table creation.

Option B is incorrect because passing a table as an argument is part of the training or input phase, not the output persistence phase.

Option D is overly complex and contains non-standard terminology ("List the cache content").

Option C is the 100% correct answer. It reflects the required "post-processing" step in the Snowflake Data Cloud to bridge the gap between procedural model calls and relational table storage.

To achieve the desired result, an analyst must understand the fundamental behavior of different JOIN types within Snowflake and how they affect the retention of records from the "left" or primary table. The goal here is to list all brokers, even those who have zero customers (like "Drew") or customers with zero orders (like "Debby").

In SQL, an INNER JOIN only returns rows when there is a match in both tables. If we were to use an INNER JOIN between BROKER and CUSTOMER, Drew would be excluded from the results because he has no associated records in the CUSTOMER table. Similarly, an INNER JOIN with the ORDERS table would exclude any broker whose customers haven't placed an order.

Evaluating the Join Logic:

Option C is the correct solution because it utilizes a chain of LEFT JOINs. A LEFT JOIN (or LEFT OUTER JOIN) ensures that every record from the left table (BROKER) is preserved in the result set. If no matching record exists in the joined table (CUSTOMER or ORDERS), Snowflake populates the columns with NULL. This is why "Drew" appears with a CUST_COUNT of 0 and "Debby" appears with a NULL for the total order amount.

Option A fails because it uses an INNER JOIN for the CUSTOMER table, which would immediately filter out "Drew."

Option B and Option D fail because they use INNER JOINs at different stages of the query, which would strip away brokers or customers that do not have matching order activity.

Additionally, the query correctly uses COUNT(DISTINCT c.customer_id) to ensure that customers are not double-counted if they have multiple orders, and GROUP BY 1 (referencing b.broker_name) to aggregate the data at the broker level. This pattern is essential for accurate Data Analysis in Snowflake when dealing with "optional" relationships in a star or snowflake schema.

Working with nested semi-structured data requires a deep understanding of Snowflake's FLATTEN function and path notation. In the provided JSON exhibit, the data is hierarchical: the root contains a key named "events", which is an array containing multiple objects. Each object within that array contains the target field "f".

To transform this into a "consumable, tabular format" where each instance of "f" appears as a separate row, the Data Analyst must "explode" the array. Simply using path notation like data:events:f (Option A) will fail or return NULL because the engine cannot directly map a single path through an array structure to multiple scalar results without an explicit relational transformation.

The LATERAL FLATTEN function is the standard tool for this task. It takes a semi-structured column (or a specific path within it) as an input and returns a relational result set. By specifying input => data:events, the Analyst instructs Snowflake to look specifically at the array. For every element found in that array, FLATTEN generates a new row in the result set. The output of the flatten operation includes a column named VALUE, which represents the specific element (in this case, an object) currently being processed.

Evaluating the Options:

Option A and C are incorrect because they attempt to use direct pathing on an array. Without flattening, the query engine sees the array as a single block of data and cannot reach the "f" key inside individual array elements.

Option B is incorrect because it flattens the root data object. While this works for top-level keys, it wouldn't automatically iterate through the "events" array to expose the "f" values inside it; it would treat the entire array as a single value.

Option D is the 100% correct answer. It correctly targets the "events" array for flattening and then utilizes path notation (value:f) followed by the appropriate type casting (::number) to produce the final tabular output. This is a core competency for any SnowPro Advanced Data Analyst handling modern telemetry or event-based data.

This scenario highlights the rules governing Shared Databases and Cross-Database Joins in Snowflake. When a user acquires data from the Snowflake Marketplace, it arrives in their account as a "Shared Database."

A critical restriction of shared databases is that they are read-only. Users (including Data Analysts and even AccountAdmins) cannot create new objects—such as tables, schemas, or views—directly inside a database created from a share. Therefore, Options A, C, and D are architecturally impossible because they all involve trying to write a new view into the ADDRESS_DATA database.

To combine the shared data with internal data (like the ORDERS table in GLOBAL_DWH), the Analyst must create the view in a database where they have CREATE VIEW privileges and that is writeable. Since GLOBAL_DWH is an internal database owned by the organization, it is the appropriate location to host the logic that joins local data with external enrichment data.

Evaluating the Options:

Option A and C are incorrect because you cannot create objects in a database created from a share.

Option D is incorrect because while IMPORTED_PRIVILEGES are necessary to view the data, they do not grant the ability to create objects within the share.

Option B is the 100% correct answer. By creating the view in the GLOBAL_DWH database (or any other local, non-shared database), the Analyst can successfully reference ADDRESS_DATA.OPENADDRESS and GLOBAL_DWH.PUBLIC.ORDERS in a single SQL statement, effectively creating an enriched data layer for the organization.

In Snowflake, "spilling" occurs when the intermediate data required to process a query exceeds the available memory (RAM) of the virtual warehouse. When this happens, Snowflake "spills" the data first to the warehouse's local SSD and, if that fills up, to remote storage (e.g., S3 or Azure Blob). This significantly degrades performance due to increased I/O latency.

To resolve this without increasing costs (i.e., without scaling up to a larger warehouse size), an analyst must reduce the memory footprint of the query.

CTEs vs. Temporary Tables: While Common Table Expressions (CTEs) are excellent for readability, they are often treated as logical entities that the optimizer may re-evaluate multiple times or struggle to keep in memory during massive joins.

Batch Processing: By splitting a single monolithic query into multiple steps and storing intermediate results in temporary tables, the analyst forces the engine to clear its memory between steps. This process acts as a "checkpoint," effectively processing the data in smaller, more manageable batches that fit within the current warehouse's memory limits.

Evaluating the Options:

Option A is incorrect because spilling is a compute-resource issue, not a storage-lock or contention issue.

Option B is incorrect because materialized views and clustering incur background credit costs for maintenance, violating the "without increasing costs" constraint.

Option C is incorrect because external tables are generally slower than native Snowflake tables due to the lack of proprietary metadata and micro-partitioning.

Calculating a moving average (or rolling average) is a standard time-series analysis technique used to smooth out short-term fluctuations and highlight longer-term trends. In Snowflake, this is accomplished using Window Functions and the ROWS framing clause.

To calculate a 7-day moving average when you have one record per day, the "window" or "frame" must encompass exactly 7 rows. In SQL windowing syntax, the CURRENT ROW counts as one of those days. Therefore, to reach a total of 7, you need to look back at the 6 preceding rows ($6 + 1 = 7$).

Evaluating the Options:

Options A and B use the SUM() function. While the sum is part of an average, the question specifically asks for the average itself.

Option D is incorrect because 7 PRECEDING AND CURRENT ROW actually creates an 8-day window (the current day plus the seven days before it).

Option C is the 100% correct answer. It uses the AVG() aggregate function and correctly defines the frame as 6 PRECEDING AND CURRENT ROW, ensuring the calculation reflects exactly one week of data including the current day.