Free Practice Questions for Snowflake DAA-C01 Exam

One of the most powerful features of the Snowsight interface for a Data Analyst is the automatic data profiling provided in the results pane. Snowflake automatically calculates statistics and visual distributions for the result set of any query executed in a worksheet, provided the result set is not excessively large.

When the Analyst views the query results, they can simply click on the column header for the "total amount" column. This action opens a summary pane that displays key descriptive statistics such as the mean, sum, and a histogram showing the frequency distribution of the values in that specific column. This allows for immediate visual analysis of data skew, outliers, or common ranges without requiring the analyst to write additional SQL or move the data to an external visualization tool.

Evaluating the Options:

Option A is the Correct answer. This is the fastest, built-in way to perform "exploratory data analysis" (EDA) on a result set within the UI.

Option B and C are incorrect because while Snowsight does have a "Chart" tab, creating a chart requires manual configuration and is a separate step from the automatic profiling features found in the column headers.

Option D is incorrect because calling the WIDTH_BUCKET function would require the Analyst to run the query again with modified SQL logic, which explicitly contradicts the requirements of the question.

This feature significantly enhances the Data Analysis workflow by providing "at-a-glance" insights into data quality and distribution directly within the development environment.

When visualizing "active routes" or the relationship between two categorical dimensions (like a Source and a Destination), the goal is to identify patterns of intensity. A Heat grid (also known as a Heatmap) is the ideal visualization for this scenario.

In a heat grid, one dimension (e.g., Fulfillment Warehouse) is placed on the X-axis, and the other dimension (Destination Warehouse) is placed on the Y-axis. The individual cells at the intersection of these two dimensions are colored based on a metric, such as the total volume of transfers. High-intensity colors (e.g., dark red) immediately draw the analyst's eye to the "most active" routes, while lighter colors represent less frequent transfers.

Evaluating the Options:

Option A (Scatterplot) is used for identifying clusters or correlations between two numeric variables, not categorical locations.

Option B (Scorecard) is a high-level KPI visualization that usually shows a single number (e.g., Total Transfers) and does not provide the multi-dimensional breakdown required to see specific routes.

Option C (Line chart) is designed for time-series data to show trends over time, which would not effectively illustrate the static relationship between two geographic points.

Option D is the 100% correct answer. It provides the best spatial representation of density and activity between discrete origins and destinations, which is a core skill in the Data Presentation and Data Visualization domain.

In Snowsight, worksheets and dashboards are tightly integrated to facilitate a smooth transition from data exploration to formal reporting. When a Data Analyst has already done the "heavy lifting"—cleaning and preparing data within standalone worksheets—Snowflake provides a direct way to convert those worksheets into dashboard tiles.

The "least operational overhead" method is to Move the existing worksheets into the dashboard. When you are in the dashboard view, you can select the option to add a tile and then choose an existing worksheet from your folders. This "Move" or "Add from Worksheet" action preserves the entire SQL statement, any formatting applied to the results, and the visualization settings (charts) that were already configured in the worksheet.

Evaluating the Options:

Option B and C are technically possible but require significantly more effort (manual copy-pasting, switching windows, re-naming tiles). This increases the risk of human error and is not considered a high-efficiency workflow in Snowsight.

Option D is the most complex and overhead-intensive option, requiring the analyst to sift through system logs to recover code they already have access to in their UI.

Option A is the 100% correct answer. By moving or importing the worksheet directly into the dashboard as a tile, the analyst maintains the link between the development work and the final presentation with just a few clicks. Note that once a worksheet is moved into a dashboard, it essentially becomes a "tile" and is managed as part of the dashboard object.

Snowflake provides a powerful feature called Result Caching. When a query is executed, the results are persisted for 24 hours. To access the result set of a previously executed query without re-running the heavy computation (and thus saving credits), a Data Analyst can use the RESULT_SCAN table function.

The function RESULT_SCAN requires a Query ID as its input. The most common way to programmatically retrieve the ID of the most recently executed statement in the current session is by using the LAST_QUERY_ID() function. By wrapping this in the TABLE() operator, Snowflake treats the cached results as a virtual table that can be queried, filtered, or even joined with other tables.

Evaluating the Options:

Option B is incorrect because QUERY_HISTORY returns metadata about queries (like start time, user, and status) but does not return the actual data records produced by those queries.

Option C is incorrect as DESC (Describe) is used to see the columns and data types of an object, not to fetch its row data.

Option D is incorrect; while LAST_QUERY_ID(-1) might successfully retrieve a string representing a previous ID in some contexts, it does not actually "access the results" or display the data.

Option A is the 100% correct syntax. It is the standard method used by Data Analysts to perform post-processing on a large result set or to recover a result set if the worksheet was accidentally cleared. This is a key part of the Data Analysis domain, specifically regarding performance optimization and the utilization of Snowflake's unique caching layers.

In the field of data warehousing and business intelligence (BI), choosing the right data model is crucial for long-term maintainability and user accessibility. While a single flat table might seem simple initially, dimensional modeling (typically using Star or Snowflake schemas) provides distinct advantages for enterprise analytics.

1. Scalability and Flexibility (Option C)

Combining all attributes into a single flat table creates a highly rigid structure. Every time a new attribute is added to a dimension (e.g., adding a "Promotion Category" to a product), the entire flat table must be rewritten or altered, which is inefficient for large datasets. Furthermore, flat tables often contain redundant data, leading to "update anomalies" where a change in a dimension attribute must be propagated across millions of rows. A dimensional model separates changing business processes (Facts) from the context of those processes (Dimensions), allowing the schema to scale and evolve independently.

2. Ad-hoc Analysis for Power Users (Option D)

Dimensional models are specifically designed to be intuitive for business users and BI tools. By organizing data into Facts (measurable metrics) and Dimensions (descriptive attributes), power users can easily "slice and dice" data across different hierarchies. For example, a user can quickly run an ad-hoc query to compare "Total Sales" (Fact) by "Store Region" (Dimension) and "Calendar Month" (Dimension). This structure provides a predictable and standardized "language" for the data, making it easier for users to build their own reports without needing a Data Analyst to create a custom flat table for every specific request.

Evaluating the Distractors:

Option A and E: These are common misconceptions. Modern cloud data warehouses like Snowflake are often highly optimized for wide "flat" tables due to columnar storage and sophisticated pruning. In many cases, a flat table may actually outperform a multi-table join (dimensional model) because it avoids the computational overhead of the join itself.

Option B: This is factually incorrect. Flat tables are denormalized (repeating data), which generally takes more storage space. Dimensional modeling is a form of normalization that saves space by storing descriptive strings once in a dimension table rather than repeating them for every transaction in a fact table.

When dealing with massive datasets containing billions of rows, efficiency is paramount. Snowflake provides two primary ways to retrieve a small subset of data: Sampling and Result Set Limiting.

The SAMPLE clause is used to return a subset of rows based on a specific count or percentage. The correct syntax for specifying a fixed number of rows is SAMPLE ( ROWS). This is highly effective for exploratory data analysis where you need to see the shape of the data without the overhead of scanning the entire table. It is important to note that SAMPLE does not guarantee a truly random distribution unless specified, but it is much faster than a full table scan.

The TOP <n> or LIMIT <n> clauses are used to restrict the number of rows returned in the output. The TOP syntax is commonly used in many SQL dialects and is fully supported in Snowflake to return the first "n" rows the query engine encounters. While TOP is often used with an ORDER BY clause to get the highest or lowest values, using it alone on a massive table like FCT_SALES is a standard way to quickly peak at the data.

Evaluating the Options:

Option A is incorrect syntax; the ROWS keyword and parentheses are required for a fixed count.

Option B is Correct; it uses the standard Snowflake SAMPLE syntax for a specific row count.

Option C and Option E are incorrect because ROWS is not a valid keyword within a TOP clause in Snowflake.

Option D is Correct; it uses the standard TOP syntax to limit the result set.

By using these methods, a Data Analyst ensures they are not wasting compute resources (Credits) by pulling billions of unnecessary rows into the UI or a local environment.

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.

The provided image shows a specific section of a Snowflake Query Profile, which is a visual representation of the execution plan and the actual performance of a query. Analyzing this profile is a critical skill for a Data Analyst to identify performance bottlenecks.

1. Identifying the Exploding Join: The most striking evidence in this profile is the relationship between the input and output row counts of the Join [5] operator.

Input Row Counts: The join receives approximately 95.26k rows from one branch and 150k rows from the other.

Output Row Count: The join produces a staggering 571.6M rows.

When a join operation produces a significantly larger number of rows than the sum of its inputs, it is known as an exploding join (or a "Cartesian product-like" behavior). This typically occurs when the join condition is not restrictive enough or when there are many-to-many relationships with duplicate keys in both joining tables. In this specific case, joining on C1.C_NATIONKEY = C2.C_NATIONKEY has caused the row count to balloon from thousands to over half a billion.

2. Impact on Performance: Exploding joins consume excessive CPU and memory resources to process the massive intermediate result set. This often leads to secondary problems like spilling (Option A), where the data exceeds the virtual warehouse's memory and must be written to disk. However, the profile clearly identifies the join itself as the root cause.

Evaluating the Options:

Option A is a symptom, but the visual evidence of row expansion directly points to the join logic itself.

Option C (inefficient pruning) would be identified by a high percentage of partitions scanned in the Table Scan nodes, not by a row count explosion after a join.

Option D is irrelevant; while foreign keys can help the optimizer, their absence doesn't cause this specific visual profile on its own.

Option B is the 100% correct answer. The "exploded" arrow indicating 571.6M rows leaving a join fed by significantly smaller inputs is the textbook definition of an exploding join.

Snowsight provides special System Keywords that allow analysts to create dynamic, interactive dashboards without hard-coding dates. Two of the most critical keywords are :daterange and :datebucket.

The :daterange keyword is designed to filter the volume of data based on a time period selected by the user in the dashboard UI (e.g., "Last 30 days" or "Current Year"). Because it acts as a filter on the underlying data, it must be placed in the WHERE clause of the SQL statement (e.g., WHERE created_at = :daterange). This ensures that only the records within the selected timeframe are processed by the query.

The :datebucket keyword is used for time-series aggregation. It allows the user to dynamically change the granularity of the data—for example, switching a chart from "Daily" to "Monthly" views without rewriting the query. To achieve this, :datebucket is used inside a date-truncation function in the SELECT list and, crucially, must be included in the GROUP BY clause to correctly aggregate the metrics (e.g., GROUP BY 1 or GROUP BY :datebucket).

Evaluating the Options:

Option A is incorrect because :datebucket is for grouping/truncation, not for filtering in a WHERE clause.

Option B is incorrect because :daterange is a filter (returning a range), not a scalar value suitable for a SELECT list.

Option C is incorrect for the same reasons as A and B.

Option D is the 100% correct answer. It follows the standard Snowsight design pattern: :daterange restricts the data rows in the WHERE clause, while :datebucket defines the temporal aggregation level in the GROUP BY clause.