Free Practice Questions for Snowflake DAA-C01 Exam

In statistical analysis, when you want to measure the strength and direction of a relationship between two continuous variables, you use Correlation. The CORR() function in Snowflake calculates the Pearson product-moment correlation coefficient for a set of pairs.

The correlation coefficient ranges from -1 to +1:

A result near +1 indicates a strong positive relationship (both variables increase together).

A result near -1 indicates a strong negative (inverse) relationship, which is exactly what the analyst is looking for: as the number of active drivers increases, the waiting time decreases.

A result near 0 indicates no linear relationship.

Evaluating the Options:

Option A only provides the sum of one variable and the variance (spread) of another. It does not show how they move together.

Option B is incorrect because VARIANCE() is a univariate function (takes one argument) measuring data dispersion; it cannot compare two variables.

Option C provides descriptive statistics for each variable independently but does not quantify the relationship between them.

Option D is the 100% correct answer. By calculating the CORR(), the analyst will get a single value for each city that proves (or disproves) the hypothesis that more drivers lead to shorter wait times. This is a vital skill for the Data Analysis domain, specifically for performing bivariate statistical analysis.

In the Snowflake ecosystem, Parquet is treated as a semi-structured data format. When you stage a Parquet file, Snowflake does not automatically parse it into multiple columns like it might with a flat CSV file. Instead, the entire content of a single row or record is loaded into a single VARIANT column, which is referenced in SQL using the positional notation $1.

The fundamental mistake often made—and represented in Option A—is treating Parquet as a delimited format where $1, $2, and $3 refer to different columns. In Parquet ingestion, columns $2 and beyond will return NULL because the schema is contained within the object in $1.

To successfully "shred" or flatten this semi-structured data into a relational table with separate columns, an analyst must use path notation. This involves referencing the root object ($1), followed by a colon (:), and then the specific element key (e.g., $1:o_custkey). Furthermore, because the values extracted from a Variant are technically still Variants, they must be explicitly cast to the correct data type using the double-colon syntax (e.g., ::number, ::date) to ensure they land in the target table with the correct data types.

Evaluating the Options:

Option A is incorrect because it uses positional references ($2, $3, etc.) which are only valid for structured files like CSVs.

Option B is incorrect because it attempts to reference keys directly without the required stage variable ($1) and colon separator.

Option D is incorrect as it uses a non-standard parse() function that does not exist for this purpose in Snowflake SQL.

Option C is the 100% correct syntax. It correctly identifies that the Parquet data resides in $1, utilizes the colon to access internal keys, and applies the necessary type casting. This specific method is known as "Transformation During Ingestion" and is a core competency for any SnowPro Advanced Data Analyst.

Manually defining table structures for complex semi-structured files like Parquet can be error-prone and time-consuming. Snowflake provides a specific automation workflow to handle this, involving the detection of the file's internal schema and the dynamic creation of a matching table.

The process starts with the INFER_SCHEMA function. Because Parquet files are self-describing, they contain metadata about their columns and data types. INFER_SCHEMA reads this metadata from files in a stage and returns a list of column names and types. To turn this list into an actual table, the analyst uses the CREATE TABLE ... USING TEMPLATE syntax. This command takes the output of INFER_SCHEMA as an input and automatically builds a table with the corresponding definition.

Evaluating the Options:

Option A is incorrect because CREATE TABLE LIKE is used to copy the structure of an existing table, not to build a new one from file metadata.

Option C and D are incorrect because GENERATE_COLUMN_DESCRIPTION is a helper function used to create a formatted string of column definitions, but it is not the primary command used with USING TEMPLATE for automated table creation.

Option B is the Correct answer. The combination of INFER_SCHEMA (to find the columns) and USING TEMPLATE (to build the table) is the standard Snowflake pattern for schema-on-read automation in Data Ingestion workflows.

In Snowsight, the Scorecard chart type is specifically designed to highlight a single key metric (the "Value") and optionally compare it against a static or dynamic benchmark (the "Comparison"). This visualization is a core component of the Data Presentation and Data Visualization domain, as it provides an immediate "at-a-glance" status for high-level KPIs.

1. Interpreting the Scorecard Components:

Primary Value: The large, bold number (38) represents the actual value calculated by the underlying query. In this scenario, it is the company's average employee age.

Secondary Metric (The Percentage): When a scorecard is configured with a comparison value, Snowsight automatically calculates the percentage difference between the primary value and that comparison.

Visual Indicators: The green upward arrow indicates that the primary value is higher than the comparison value. If the value were lower, the arrow would point downward and typically appear in red.

2. The Mathematical Calculation:

The 12% shown in the exhibit is the result of the percentage change formula:

Evaluating the Options:

Options A and D are incorrect because they misidentify the large number (38) as the "comparison." In Snowflake's UI, the large number is always the primary metric being tracked, not the target it is being measured against.

Option B is incorrect because the percentage in a scorecard represents relative difference, not a statistical measure like standard deviation.

Option C is the 100% correct interpretation. It correctly identifies 38 as the current company value and 12% as the calculated difference from the industry benchmark of 34. This level of visual literacy is expected of a SnowPro Advanced: Data Analyst to ensure dashboard insights are communicated accurately to stakeholders.

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.

While Snowflake provides various ways to encapsulate logic, Stored Procedures are the primary tool for procedural programming and dynamic SQL execution. Unlike standard UDFs, which are generally designed to calculate and return values within a query, stored procedures can execute administrative commands (DDL) and data manipulation (DML) that are not possible in a simple SELECT statement.

Using Snowflake Scripting (or languages like Python, Java, or JavaScript), an analyst can write a procedure that builds a SQL string as a variable and then executes it using the EXECUTE IMMEDIATE command. This is demonstrated in procedural logic flows where inputs determine specific execution paths. While Tasks can be used to schedule the execution of these procedures, the task itself is a scheduling agent and does not provide the logic-driven dynamic string construction inherent to the procedure.

In Snowflake, a Stored Procedure is the primary object used for procedural logic and the encapsulation of multiple SQL statements. Unlike standard functions that are typically restricted to returning a single value or a table based on an input, Stored Procedures are designed to perform administrative tasks, wrap complex business logic, and execute a sequence of operations—such as DDL (Data Definition Language) and DML (Data Manipulation Language) commands—within a single callable object.

Modular code development is facilitated by Stored Procedures because they allow analysts and developers to write logic once and reuse it across various workflows. For example, a procedure can be written to truncate a staging table, call a COPY INTO command, and then execute several INSERT statements into a final fact table. This "wrapper" approach ensures consistency, as any change to the logic only needs to be updated in the procedure definition rather than in every script or task that uses it.

Evaluating the Options:

Option B is incorrect because a Materialized View is a pre-computed result set used to improve query performance; it does not "encapsulate a series of statements" for procedural execution.

Option C is incorrect because while User-Defined Functions (UDFs) provide reusability, they are generally intended for calculations or data transformations that return a result. They are much more restricted than procedures; for instance, a UDF cannot execute DDL commands like CREATE TABLE.

Option D is incorrect because a Common Table Expression (CTE) is a temporary named result set defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. It is not a persistent database object and cannot be reused across different sessions or scripts.

Option A is the correct answer. Snowflake Stored Procedures can be written in multiple languages (JavaScript, Snowflake Scripting/SQL, Python, Java, or Scala), providing the flexibility needed for sophisticated, modular automation in a data pipeline.

Understanding how Snowflake handles integrity constraints is vital, as it differs significantly from traditional transactional databases like PostgreSQL or SQL Server. In Snowflake, most constraints are not enforced by the system, with one major exception.

Enforcement vs. Documentation: Snowflake does not enforce PRIMARY KEY, FOREIGN KEY, or UNIQUE constraints during data loading or updates. If you define a primary key, Snowflake will still allow duplicate values to be inserted. The primary reason for including these is for documentation and metadata (Option C), allowing data analysts and BI tools to understand the intended relationships and schema design.

The NOT NULL Exception: The only integrity constraint that Snowflake actively enforces is NOT NULL (Option B). If a column is defined as NOT NULL, any attempt to insert or update a record with a null value in that column will result in an error.

Evaluating the Options:

Options A and D are incorrect because Snowflake does not actually enforce these constraints; it merely stores them as metadata.

Option E is incorrect because while keys can be used for clustering, defining them as constraints is not a prerequisite for specifying them as clustering keys.

Options B and C are the 100% correct reasons. They represent the practical application (enforcing data quality for nulls) and the architectural application (providing context for the data model).

To determine the output of the final statement, a Data Analyst must perform a step-by-step trace of the code execution, following standard order of operations (inner-to-outer) and procedural logic within the Snowflake Scripting environment.

Step 1: Evaluating the innermost function, func2(10)

The definition for func2 takes a number p and returns p + 10.

Input: 10

Calculation: $10 + 10 = 20$

Result: 20

Step 2: Evaluating the next function, func1(20)

The definition for func1 takes a number v and returns v + 10.

Input: 20 (the result from Step 1)

Calculation: $20 + 10 = 30$

Result: 30

Step 3: Evaluating the procedure call, proc1(30)

The procedure proc1 takes an input variable named result. It then executes a conditional IF statement:

Conditional Check: IF (result >= 30)

Since the input is 30, the condition evaluates to True.

Action: The procedure executes RETURN result - 10.

Calculation: $30 - 10 = 20$

Summary of Logic:

The nested calls flow as follows: call proc1(30) $\rightarrow$ proc1 evaluates $30 \ge 30$ $\rightarrow$ proc1 returns $30 - 10$, which is 20. If the initial input had been lower, such that the final result passed to the procedure was less than 30, it would have followed the ELSE branch and returned the original value. This question evaluates the analyst's ability to interpret modular code and procedural control flow, which are key components of the Data Transformation and Data Modeling domain in the SnowPro Advanced: Data Analyst exam.