Mixed Deck — All DBT Topics Flashcards

What does `{{ dbt_utils.union_relations(relations=[ref('a'), ref('b')]) }}` do?

Answer: UNIONs all columns from multiple relations into a single result set

`dbt_utils.union_relations()` generates a UNION ALL query across a list of relations, aligning columns by name and filling missing ones with NULLs.

Which command is used to validate the structure and syntax of a dbt project?

Answer: dbt debug

The `dbt debug` command is used to validate the structure and syntax of a dbt project, as well as test the connection to your data warehouse. It provides detailed output about your profile configuration, project setup, and any potential issues that might prevent dbt from running successfully. This helps in troubleshooting and ensuring your environment is correctly configured.

What does `{# This is a comment #}` do in a dbt SQL file?

Answer: Creates a Jinja comment stripped from compiled SQL

Jinja comments (`{# ... #}`) are stripped entirely during compilation and do not appear in the generated SQL sent to the warehouse.

What dbt Cloud feature lets a CI job use prod table results for unmodified models instead of rebuilding them?

Answer: Deferral

Deferral (`--defer`) tells dbt to use a different environment's tables for refs that are not being rebuilt in the current run.

In a dbt Mesh setup, a model is configured with access: private inside a group. Who can ref() it?

Answer: Only models in the same group

Private models can only be referenced by models belonging to the same group.

Why is using CTEs (Common Table Expressions) beneficial for query performance and maintenance?

Answer: Organizes complex queries and aids optimization

Common Table Expressions (CTEs) improve query performance and maintenance by breaking down complex SQL queries into smaller, more readable, and manageable logical blocks. While CTEs themselves don't always directly optimize execution plans, they allow the database optimizer to potentially reuse intermediate results and can make queries easier to understand and debug. This modularity often leads to better-structured and more efficient queries.

Which property declares multiple versions of a model in YAML?

Answer: versions:

The versions property, with latest_version and optional defined_in, supports model versioning.

A small CSV of country codes needs to be version-controlled and loaded into the warehouse. What dbt feature fits best?

Answer: A seed loaded with dbt seed

Seeds are CSV files in the project loaded as tables via dbt seed and referenced with ref().

What does the selector +orders+ do in dbt run --select +orders+?

Answer: Runs orders plus all its ancestors and all its descendants

A leading + includes upstream parents and a trailing + includes downstream children.

Which config pushes model and column descriptions into the warehouse as comments?

Answer: persist_docs

persist_docs with relation and/or columns set to true writes descriptions to the database objects.

Custom schemas are producing names like dbt_jsmith_marketing instead of just marketing in production. Which macro should you override to change this behavior?

Answer: generate_schema_name

generate_schema_name controls how target.schema and custom schema configs combine.

Which version control system is most commonly used with dbt projects?

Answer: Git

Git is the most widely adopted version control system for software development, and it is also the standard for dbt projects. It allows teams to track changes to code, collaborate effectively, and manage different versions of their dbt models, tests, and configurations. Its distributed nature and robust branching/merging capabilities make it ideal for managing complex data transformation workflows.

Which command shows the compiled SQL without executing it against the warehouse as a model build?

Answer: dbt compile

dbt compile renders Jinja into SQL in target/compiled without materializing.

Which is a typical drawback of using the view materialization for a heavily queried, complex model?

Answer: It reruns the underlying query every time it is queried

Views store only the query, so each downstream query pays the compute cost.

A source table occasionally gets new columns, and you want your incremental model to add them automatically without failing. Which config achieves this?

Answer: on_schema_change='append_new_columns'

append_new_columns adds new columns to the existing target table while leaving removed columns in place.

What config key specifies the column(s) used to match existing rows in an incremental merge strategy?

Answer: unique_key

`unique_key` tells dbt which column(s) to use as the join condition when merging new rows into the existing incremental table.

Which dbt command loads seed CSV files into the target database?

Answer: dbt seed

`dbt seed` reads CSV files from the seeds/ directory and creates or replaces the corresponding tables in the warehouse.

Which practice best keeps an incremental model from scanning the entire source on every run?

Answer: Wrapping a filter on a timestamp column inside an is_incremental() block

Filtering new rows against the max timestamp in the target inside is_incremental() limits the data read each run.

Why should 'SELECT *' be avoided in dbt models for performance?

Answer: Because it retrieves unnecessary columns

Using `SELECT *` in dbt models, or any SQL query, is generally discouraged for performance reasons because it retrieves all columns from a table, even those that are not needed. This can lead to increased data transfer, higher memory usage, and slower query execution, especially with wide tables. Explicitly selecting only the required columns reduces the data processed, improving efficiency and clarity.

What does `dbt run --target prod` do?

Answer: Switches to the `prod` target defined in profiles.yml

The `--target` flag selects a named output target from profiles.yml, allowing the same project to run against dev or prod warehouses.