DBT-ANALYTICS-ENGINEERING Practice Questions

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.

A. Incorrect. Selecting resources by specific configurations (e.g., materialized, schema) is done using the --select argument with the config: selector, not --resource-type.

B. Incorrect. While materialization is a configuration, it is selected using dbt ls --select config.materialized:, not the --resource-type argument.

C. Incorrect. Selecting resources by their assigned tags is accomplished using the --select argument with the tag: selector (e.g., dbt ls --select tag:nightly).

1. dbt Labs. (n.d.). dbt ls command. dbt Core documentation. Retrieved from https://docs.getdbt.com/reference/commands/ls.

Reference Details: The "Arguments" section explicitly lists --resource-type and describes its function: "Only list resources of the specified type." It also provides the list of valid resource types.

2. dbt Labs. (n.d.). Node selection syntax. dbt Core documentation. Retrieved from https://docs.getdbt.com/reference/node-selection/syntax.

Reference Details: This page details the various selection methods. It shows that selecting by tag and config (which includes materialization) are done using the --select flag

distinguishing them from the --resource-type flag's purpose.

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).

A. partitionby is a data warehouse-specific configuration for performance optimization that physically organizes data. It is not a primary method for defining the incremental logic of which records to process.

1. dbt Labs Documentation

"Incremental models":

Reference for C: Under the "How do I use incremental models?" section

the first step listed is: "To use incremental models

you need to set the materialized configuration for a model to incremental."

Reference for B: The same section explains the second step: "You also need to tell dbt how to filter the rows for an incremental run... using the isincremental() macro." This macro is used within a WHERE clause.

Reference for D: The section "How do I use uniquekey?" states: "The optional uniquekey parameter enables updating existing rows instead of just appending new rows. If a uniquekey is specified

dbt will use a merge statement to build the model."

2. dbt Labs Documentation

"About materializations":

Reference for C: This page defines the incremental materialization: "Models can be configured as incremental... dbt will insert new records and update existing records in the table." This confirms materialized='incremental' is the foundational configuration.

3. dbt Labs Documentation

"BigQuery configs" (as an example of adapter-specific configs):

Reference for A: The partitionby configuration is listed as a specific configuration for warehouses like BigQuery to improve query performance by partitioning tables. It is described as a physical layout property

distinct from the logical filtering (WHERE clause) or update strategy (uniquekey) of the incremental model itself.

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.

The "True" option is incorrect because it advocates for a practice that directly contradicts the principles of reliability and maintainability in data engineering. The subjectivity of what constitutes a "minor" fix makes this an unreliable and dangerous standard. The dbt workflow is designed to ensure that every change is validated to prevent regressions and maintain data integrity.

1. dbt Labs Documentation

"How we use dbt Core": This official best practice guide outlines the standard development workflow. It explicitly states the process is to: "1. Create a new branch... 2. Make your changes... 3. Test your changes... 4. Create a pull request." This sequence is presented as the standard for all changes

with no exception made for minor fixes. The guide emphasizes

"This workflow ensures that the main branch is always in a good state."

Source: dbt Labs Official Documentation

Best Practices Section. Retrieved from https://docs.getdbt.com/best-practices/how-we-use-dbt-core

2. dbt Labs Documentation

"Continuous integration": This document details how to set up CI jobs to automate testing. The entire premise is that tests should be run automatically whenever new code is proposed for merging via a pull request. It states

"When a new pull request is opened... your CI provider will kick off a job. This job will... run your dbt project's tests." This institutionalizes testing for every single change

reinforcing that it is not an optional step.

Source: dbt Labs Official Documentation

Deployment Section. Retrieved from https://docs.getdbt.com/docs/deploy/continuous-integration

3. dbt Labs Documentation

"A beginner's guide to data testing": This guide explains the fundamental importance of testing in dbt. It frames testing not as an optional step for complex changes

but as a foundational practice for building trust and reliability. The guide notes

"By adding tests to your project

you can be confident that your models are working as you expect

" which applies to every change committed to the codebase.

Source: dbt Labs Official Documentation

Guides Section. Retrieved from https://docs.getdbt.com/guides/getting-started/learning-more/testing-and-documentation

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.

A. dbt test --models

This is an incomplete command. The --models flag is a selector that requires an argument (e.g., a model name) to specify which models to test; it does not inherently select based on modification status.

B. dbt test --state

The --state flag is necessary to provide the path to a comparison manifest, but it does not perform any selection on its own. It must be used with a state: selector like state:modified.

C. dbt test --modified

This is a shorthand flag for --select state:modified. While it produces the same result, option D is the more explicit and canonical syntax recommended for clarity and consistency in modern dbt projects.

---

1. dbt Labs Official Documentation

"State selection method": This document explicitly details the state:modified selector. It states

"The modified value will select all resources that have been modified since the state manifest was generated." It also clarifies the prerequisite: "You must pass the --state flag for any state: selection to work."

Source: dbt Docs

dbt-core -> Reference -> Node selection -> State selection method.

2. dbt Labs Official Documentation

"About state": This page explains the purpose of the --state flag and the manifest.json file

which are the foundation for state-based selection. It notes

"By comparing your current project's files against the manifest.json from a previous run

dbt can determine what has changed."

Source: dbt Docs

dbt-core -> Guides -> Best practices -> About state.

3. dbt Labs Official Documentation

"test command": The documentation for the dbt test command lists the available flags and selectors

including --select and --state

confirming their role in constructing the correct command. It also lists --modified as a "selector flag."

Source: dbt Docs

dbt-core -> Reference -> dbt command reference -> test command.

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.

A. Reduced execution time: The source() function is a compile-time Jinja macro; it has no material impact on the final query's execution performance.

B. Increased complexity and poor maintainability: This is correct but incomplete, as it omits the critical issue of dependency management and lineage.

C. Dependency management issues: This is also correct but incomplete, as it overlooks the significant negative impact on project maintainability.

1. dbt Labs Official Documentation

"Sources": This document explains that using the source function is the standard way to refer to raw data. It states

"dbt builds the DAG of your project by parsing all of the ref and source calls in your models." By not using source()

the dependency is omitted from the DAG

leading to dependency management issues. The document also highlights benefits like testing and freshness checks

which are lost without proper source declaration.

Source: dbt Labs Documentation

docs/build/sources

Section: "Referring to sources".

2. dbt Labs Official Documentation

"Best practices": The guide on structuring dbt projects explicitly states that raw data should be declared as sources. This practice promotes maintainability by abstracting the raw table names and schemas into configuration files

preventing hardcoded values that are difficult to manage.

Source: dbt Labs Documentation

best-practices/how-we-structure/1-guide-overview

Section: "Sources".

3. dbt Labs Official Documentation

"source function": This page details how the source function renders a relation into your compiled SQL. It is the mechanism that allows dbt to understand the upstream dependencies of a model on raw data

which is fundamental for building the project's lineage graph.

Source: dbt Labs Documentation

reference/dbt-jinja-functions/source.

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.

A. Deploying models involves executing them against the data warehouse, which is typically done using the dbt run command, not dbt deps.

C. Checking for syntax errors and compiling Jinja into SQL without executing it is the function of the dbt compile command.

D. Generating the project's documentation website is accomplished by running the dbt docs generate command.

1. dbt Labs Documentation

"dbt deps command". This official source states

"The dbt deps command pulls the most recent version of the dependencies listed in your packages.yml file."

2. dbt Labs Documentation

"About package management". This page explains the concept of packages and explicitly mentions the workflow: "Add a packages.yml file to your dbt project

declare the package(s) you want to use... and then run dbt deps to install the package(s)."

3. dbt Labs Documentation

"packages.yml". Under the "Usage" section

it is noted that after configuring the packages.yml file

you must run dbt deps for dbt to download and install the packages.

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.

A. dbt run: This command compiles the SQL and then immediately executes it against the target database to create or update tables and views.

B. dbt debug: This is a utility command used for troubleshooting project setup and database connectivity; it does not compile models into SQL.

C. dbt test: This command executes predefined data tests by running their corresponding SQL queries against the database to validate assumptions about your models.

1. dbt Labs Documentation

dbt compile command: "Generates executable SQL from your project's source files. You can find these compiled SQL files in the target/ directory of your project. This command does not run the SQL against your database." (Source: dbt Labs

dbt Command reference

section on dbt compile).

2. dbt Labs Documentation

dbt run command: "dbt will compile the model code into SQL

then execute that SQL against the configured target database." (Source: dbt Labs

dbt Command reference

section on dbt run).

3. dbt Labs Documentation

dbt debug command: "A helpful command for debugging your dbt project and your connection to the database. This command does not run any models or tests." (Source: dbt Labs

dbt Command reference

section on dbt debug).

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.

The statement is not false because the primary architectural benefit of the source() macro is to abstract and centralize the definition of raw data locations. Hardcoding the full path of a source table (e.g., raw.jaffleshop.orders) in multiple models would be a direct violation of the DRY principle, as the location information is repeated in each model. The source() function is designed specifically to prevent this repetition.

1. dbt Labs Official Documentation

"Sources": "By declaring sources in dbt

you can then select from source tables in your models using the {{ source() }} function... This provides a single place for you to define your source data

and all downstream models can reference it." This directly describes the centralization of information

which is the core of the DRY principle.

Source: dbt Labs Documentation

dbt Core > dbt-core fundamentals > Sources.

2. dbt Labs Official Documentation

"How we structure our dbt projects": This best practice guide emphasizes creating a clean interface between raw data and the rest of the dbt project. It states

"The purpose of [staging] models is to be representations of the source data... Each staging model should correspond to one source table." This one-to-one mapping and clean referencing is enabled by the source() function

promoting a modular and DRY structure.

Source: dbt Labs Documentation

Best practices > How we structure our dbt projects > Staging: preparing our raw data for modeling.

3. dbt Labs Official Documentation

"source function": The documentation for the function itself explains its role in referencing pre-defined sources. "The source function is the primary way to reference a source table in a model." This reinforces its role as the standardized method for referencing

which is essential for a DRY implementation.

Source: dbt Labs Documentation

Jinja & macros > Functions > source.

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.

A. This option incorrectly places a comma between the FOREIGN KEY and REFERENCES keywords, which is a syntax error and will cause the statement to fail.

C. This option attempts to define an out-of-line (table-level) constraint but fails to enclose the foreign key column name (customerid) in parentheses, which is required.

D. This option correctly uses the inline REFERENCES clause but then adds a dangling, incomplete FOREIGN KEY keyword at the end, which is syntactically invalid.

1. Microsoft Transact-SQL Documentation: The official documentation for CREATE TABLE in T-SQL shows the syntax for a column-level foreign key constraint as: [ FOREIGN KEY ] REFERENCES [ schemaname . ] referencedtablename [ ( refcolumn ) ]. This confirms that the FOREIGN KEY keyword is optional but valid in an inline definition

making option B correct.

Source: Microsoft

CREATE TABLE (Transact-SQL)

SQL Server Documentation

"columnconstraint" syntax section.

2. PostgreSQL Documentation: The PostgreSQL documentation illustrates the standard syntax for an out-of-line foreign key constraint

which is FOREIGN KEY ( columnname [

... ] ) REFERENCES reftable [ ( refcolumn [

... ] ) ]. This highlights the missing parentheses error in option C.

Source: PostgreSQL

PostgreSQL 16 Documentation

Section 5.4.5. "Foreign Keys".

3. University Courseware (Stanford): Course materials for database systems typically distinguish between attribute-level (inline) and table-level (out-of-line) constraints. The standard out-of-line syntax requires parentheses around the key

confirming that option C is incorrect.

Source: Stanford University

CS 145 Introduction to Databases

"SQL Constraints" lecture notes. (Illustrates standard syntax FOREIGN KEY () REFERENCES ()).

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.

B. DROP TABLE tablename is incorrect because it is a DDL command that permanently removes the entire table object, including its structure and all associated data.

D. EMPTY TABLE tablename is incorrect because it is not a valid or standard SQL command in any major database system used with dbt.

---

1. Snowflake Documentation

TRUNCATE TABLE: "Removes all rows from a table

but leaves the table structure

including any column definitions

constraints

and sequence generators intact." (Snowflake Inc.

SQL Command Reference

DML Commands

TRUNCATE TABLE section).

2. Snowflake Documentation

DELETE: "Deletes rows from a table. If the WHERE clause is omitted

all rows in the table are deleted..." (Snowflake Inc.

SQL Command Reference

DML Commands

DELETE section).

3. PostgreSQL 16 Documentation

TRUNCATE: "TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table

but since it does not actually scan the tables it is faster." (The PostgreSQL Global Development Group

PostgreSQL 16 Documentation

Part III. SQL Commands

TRUNCATE section).

4. dbt Labs Documentation

pre-hook and post-hook: The dbt documentation illustrates the use of DML and DDL commands like TRUNCATE and DELETE within hooks to manage table data before or after a model run

confirming their relevance in dbt workflows for data manipulation without altering model-defined structures. (dbt Labs

dbt Docs

Reference

pre-hook and post-hook section

Examples).