Question 1

What is the purpose of the --resource-type argument in the dbt ls command?

Accepted Answer

D

Explanation: The dbt ls command is used to list resources within a dbt project. The --resource-type argument specifically filters this list to display only resources of a certain type. Valid resource types include model, test, source, seed, snapshot, exposure, metric, semanticmodel, savedquery, and analysis. For example, using dbt ls --resource-type model will list all models in the project, while dbt ls --resource-type test will list all tests. This argument provides a direct way to categorize and view specific kinds of project assets.

Question 2

Select the valid methods for configuring incremental models in dbt. (Select all that apply)

Accepted Answer

B, C, D

Explanation: Configuring an incremental model in dbt involves several key steps. First, the model must be explicitly defined as incremental by setting materialized='incremental' in its configuration block (C). The core logic for an incremental run is to process only new or changed data. This is achieved by filtering the source data, typically by adding a WHERE clause to the model's SQL that leverages the isincremental() macro (B). To handle updates to existing records and prevent duplicates, a uniquekey can be specified. This key enables dbt to match records between the source and destination, performing an update/merge operation instead of a simple append (D).

Question 3

When fixing a dbt model issue, it is acceptable to skip the testing process in a separate branch if the fix is minor.

Accepted Answer

FALSE

Explanation: The statement is false. A core principle of analytics engineering, as championed by dbt, is the application of software engineering best practices to data transformation workflows. This includes a rigorous and consistent development lifecycle for all changes, regardless of their perceived size. Every modification, even a "minor" one, should be made in a separate development branch, followed by a comprehensive testing process (dbt test) before being merged into the main branch. A seemingly minor fix can have unforeseen and significant downstream impacts on dependent models, dashboards, or business intelligence tools. Skipping the testing process introduces a high risk of deploying breaking changes, degrading data quality, and eroding trust in the data platform.

Question 4

You want to run tests on only the models that have been modified since your last dbt run. Which command should you use?

Accepted Answer

D

Explanation: The command dbt test --select state:modified leverages dbt's state comparison feature to identify and test only the models that have been modified. This requires providing a comparison manifest from a previous run (e.g., your last production run) using the --state flag. The state:modified selector then instructs dbt to find all models whose configurations or code have changed relative to that manifest. By default, dbt will also select and test the children of these modified models to ensure downstream dependencies are not broken. This method is fundamental to implementing "Slim CI" for faster, more efficient testing in development and deployment pipelines.

Question 5

You discover that a dbt model is directly referencing a raw object in the database without using the {{ source() }} function. What potential risks does this pose to the project?

Accepted Answer

D

Explanation: Bypassing the {{ source() }} function and directly referencing raw database objects breaks two core dbt principles. First, it severs the dependency link between the model and its raw data source within dbt's Directed Acyclic Graph (DAG). This prevents dbt from tracking lineage, running source-specific commands (e.g., dbt test source:), and checking for data freshness. Second, it harms maintainability. If the location or name of the raw data changes, developers must manually find and replace every hardcoded reference across the project. Using source() centralizes this definition in a single sources.yml file, making updates simple and reliable.

Question 6

What is the purpose of the dbt deps command in the dbt workflow?

Accepted Answer

B

Explanation: The dbt deps command is a crucial part of managing dbt projects that rely on external code. Its primary purpose is to download and install packages, which are collections of dbt code (like macros and models) created by the community or other teams. These dependencies are declared in the packages.yml file within a dbt project. When dbt deps is executed, dbt reads this file, retrieves the specified packages from their sources (such as dbt Hub or a Git repository), and installs them into the dbtpackages directory, making them available for use in the project.

Question 7

Which of the following commands will generate compiled SQL code and save it to the "target" folder, but not execute it in the database?

Accepted Answer

D

Explanation: The dbt compile command is specifically designed to generate executable SQL from your project's source files, which include models, tests, and other Jinja-templated code. It processes all the Jinja logic and saves the resulting pure SQL files into the target/ directory. A key characteristic of this command is that it does not execute the compiled SQL against your data warehouse. This allows developers to inspect the generated SQL for correctness or debugging purposes without altering the database state.

Question 8

True or False: In a modular dbt project, using the {{ source() }} function to reference raw data sources helps adhere to DRY principles.

Accepted Answer

TRUE

Explanation: The statement is true. The {{ source() }} function in dbt is a core component for building modular and maintainable data projects that adhere to the "Don't Repeat Yourself" (DRY) principle. By defining raw data tables (including their database and schema) in a central sources.yml file, you create a single source of truth for these upstream dependencies. Downstream models then use the {{ source() }} function to reference these tables instead of hardcoding their full paths. If the location of a raw data table changes, the update only needs to be made in the single sources.yml file, rather than in every model that references it, thus avoiding repetition and reducing the risk of errors.

Question 9

Which of the following is the correct syntax for creating a new table named 'orders' with two columns: 'id'
as an integer primary key and 'customer_id' as an integer foreign key referencing 'customers' table?

Accepted Answer

B

Explanation: This option presents a syntactically valid SQL statement for creating the 'orders' table with the specified constraints. The id INT PRIMARY KEY clause correctly defines the primary key inline. The customerid INT FOREIGN KEY REFERENCES customers(id) clause correctly defines an inline foreign key constraint. This specific syntax, including the FOREIGN KEY keyword directly in the column definition, is valid in certain SQL dialects like Transact-SQL (used by Microsoft SQL Server). Although other dialects might omit the FOREIGN KEY keyword for inline definitions, this syntax is correct and the other options contain definitive errors.

Question 10

Which of the following SQL statements can be used to remove all rows from a table without deleting the table structure?

Accepted Answer

A, C

Explanation: Both DELETE and TRUNCATE can be used to remove all rows from a table without altering its underlying structure (i.e., columns and constraints). TRUNCATE TABLE is a Data Definition Language (DDL) command designed specifically for this purpose. It is highly efficient as it deallocates the data pages, bypassing individual row deletions, and typically cannot be rolled back. DELETE FROM is a Data Manipulation Language (DML) command. When used without a WHERE clause, it also removes all rows. However, it does so row-by-row, which is slower and generates significant transaction log data, but allows the operation to be rolled back. Both commands satisfy the question's requirements.

Question 11

True or False: Creating separate models for each transformation step is an effective way to apply modularity and DRY principles in a dbt project.

Accepted Answer

TRUE

Explanation: This statement is true. Breaking down complex data transformations into a series of smaller, discrete models is a core tenet of dbt development and a direct application of software engineering best practices. Each model represents a logical step in the transformation process. This approach enhances modularity, as each model becomes a self-contained, understandable, and testable unit. It also promotes the DRY (Don't Repeat Yourself) principle, because a common transformation (e.g., cleaning a specific data source, calculating a business-specific metric) can be built once in an intermediate model and then referenced (ref) by multiple downstream models, eliminating the need to rewrite the same logic. This results in a more maintainable, scalable, and debuggable dbt project.

Question 12

After implementing a fix for a dbt model issue, it is not necessary to test the fix in a separate branch if the issue is well-documented.

Accepted Answer

FALSE

Explanation: The statement is false. According to established analytics engineering and software development best practices, which are central to the dbt workflow, all code changes, including bug fixes, must be developed and tested in an isolated environment. This is typically done in a separate Git branch. This practice ensures that the fix correctly resolves the documented issue and, crucially, does not introduce any unintended side effects or regressions into the main codebase. The quality or thoroughness of an issue's documentation does not replace the need for empirical validation through testing. Pushing untested code to a primary branch is a high-risk practice that can compromise data integrity and pipeline stability.

Question 13

You want to run tests on only the models that have been modified since your last dbt run. Which command should you use?

Accepted Answer

C

Explanation: To limit testing to only those models whose code or configuration has changed since the previous run, you must use the state comparison selector state:modified. The selector is supplied through the --select flag: dbt test --select state:modified The selector evaluates “modified” by comparing the current project files with the artifact manifest (manifest.json) produced by the last dbt run. When the manifest from the prior run is located in the default target/ directory, dbt can resolve the state automatically; therefore the minimal, working command is the one shown in option C.

Question 14

The HAVING clause in SQL is used to filter the results of a GROUP BY clause based on a condition that applies to the result of an aggregate function.

Accepted Answer

TRUE

Explanation: The statement accurately describes the function of the HAVING clause in SQL. The HAVING clause is processed after the GROUP BY clause and is used specifically to filter the resulting groups based on conditions that involve aggregate functions (such as COUNT(), SUM(), AVG(), MAX(), MIN()). The WHERE clause, in contrast, is used to filter individual rows before any grouping or aggregation occurs and therefore cannot operate on the results of aggregate functions.

Question 15

In SQL, a self-join is a regular join where a table is joined to itself.

Accepted Answer

TRUE

Explanation: The statement is a correct and precise definition of a self-join in SQL. A self-join is not a distinct type of join with its own keyword (like INNER JOIN or OUTER JOIN). Instead, it is a standard join operation where a table is joined to itself. To make this possible, the query must use table aliases to create two distinct, logical instances of the same table, which can then be compared based on a join condition. This technique is commonly used for querying hierarchical data (e.g., an employee and their manager in the same table) or for making row-to-row comparisons within a single table.

Free DBT-Analytics-Engineering Actual Exam Questions