dbt Analytics Engineering Certification (DBT-AE) Practice Questions: Data Modeling and Transformations Domain

Correct Answer: B

Explanation: In dbt, Jinja templating can be used to dynamically calculate date ranges. Option B correctly uses Jinja to subtract 30 days from the `execution_date`, which is a common pattern in dbt for dynamic date filtering. Option A is incorrect because it uses SQL syntax without Jinja. Option C is incorrect because it mixes Jinja with SQL in an unsupported manner. Option D incorrectly uses a non-existent function in dbt_utils; instead, date manipulation should be done using Jinja.

Correct Answer: D

Explanation: After refactoring models, the `dbt run --select state:modified+` command will run the modified models and all their downstream dependencies. Option A uses a tag selector, which doesn't automatically include all dependencies. Option B (`dbt deps`) installs dependencies from `packages.yml` but doesn't run models. Option C (`dbt compile`) compiles models but doesn't execute them. For more information, refer to the [dbt CLI documentation](https://docs.getdbt.com/reference/commands/run).

Correct Answer: B

Explanation: Selecting only the necessary columns (option B) reduces the amount of data processed, which can significantly improve performance, especially with large tables. Using `SELECT *` can be inefficient as it processes all columns, many of which may not be needed. Option A (`LIMIT`) is not suitable for production models as it only reduces the number of rows temporarily for testing purposes. Option C (incremental models) is effective when dealing with large datasets that do not change often, but it requires additional setup and is not directly related to column selection. Option D (adding a `WHERE` clause) can improve performance by filtering rows, but it does not address the issue of unnecessary columns. Refer to the dbt documentation on [model performance optimization](https://docs.getdbt.com/docs/guides/best-practices/performance-optimization) for more details.

Correct Answer: B

Explanation: Option B is correct because `dbt run --select state:modified+` selects the modified models and their immediate downstream dependencies. The `+` operator is used to include dependencies. Option A (`state:modified`) selects only the modified models without dependencies. Option C (`tag:modified`) is incorrect because it uses a tag, which is not related to state-based selection. Option D (`state:modified++`) includes both downstream and upstream dependencies, which is unnecessary if only downstream dependencies are needed. Refer to the dbt documentation on [state-based selection](https://docs.getdbt.com/reference/node-selection/syntax#state-based-selection) for more information.

Correct Answer: C

Explanation: The correct answer is C. Hardcoding database credentials in model files is a security risk and not a best practice. Instead, credentials should be managed through environment variables or dbt profiles. Option A is a best practice as organizing models by business domain improves project structure. Option B is recommended to avoid code duplication by using Jinja macros for repeated logic. Option D is a best practice because the `ref` function helps manage dependencies and ensures models are built in the correct order. For best practices on project organization, refer to the [dbt documentation on project structure](https://docs.getdbt.com/docs/build/projects).

Correct Answer: A

Explanation: The correct answer is A. The command `dbt test --select state:modified+` is used to run tests on models that have been modified since the last successful run. The `state:modified+` selector identifies these models. Option B is incorrect because 'modified+' is not a valid selector without the 'state:' prefix. Option C is incorrect because 'state:changed+' is not a valid state-based selector in dbt. Option D is incorrect because 'state:modified+' needs to be used with the `--select` flag, not `--models`. For more information, refer to the [dbt documentation on state-based selectors](https://docs.getdbt.com/docs/guides/best-practices/using-state).

Correct Answer: D

Explanation: Option D is correct because it uses the built-in `unique` test directly on the `order_id` column, which is the standard way to test for uniqueness in dbt using `schema.yml`. Option B incorrectly tries to use `dbt_utils.unique`, which is not a valid test in dbt-utils; instead, `unique` is a built-in test. Option A attempts to use `dbt_utils.unique_combination_of_columns`, which is used for testing uniqueness across multiple columns, not a single column. Option C is incorrect because it misuses the `unique` test as a model-level test instead of a column-level test. [dbt Documentation on Tests](https://docs.getdbt.com/docs/build/tests)

Correct Answer: C

Explanation: Option C correctly uses the `run_query` Jinja function to execute a SQL query that extracts the current year. Option A and B use non-existent functions `current_year()` and `get_current_year()`, respectively. Option D incorrectly references `macros.current_year()`, which is not a built-in macro. It's important to use existing Jinja functions and SQL expressions to achieve dynamic logic. For more details, refer to the [dbt Jinja documentation](https://docs.getdbt.com/docs/building-a-dbt-project/jinja-context).

Correct Answer: B

Explanation: The correct answer is B. The `dbt_utils.expect_column_values_to_match_regex` test is used to validate that column values match a specific regex pattern. In this case, the regex `'^\S+@\S+\.\S+$'` checks for a valid email format. Option A is incorrect because there is no test named `dbt_utils.email_format`. Option C and D are incorrect because they test for uniqueness and non-null values, respectively, which do not ensure email format validity. Using regex-based tests is a common practice for validating complex patterns. See the [dbt-utils documentation](https://github.com/dbt-labs/dbt-utils) for more custom test examples.

Correct Answer: B

Explanation: Option B suggests using a Common Table Expression (CTE) to pre-aggregate data before performing the join, which can reduce the amount of data processed and improve performance. Option A (ephemeral models) is not suitable for large datasets as they are in-memory. Option C (adding a unique constraint) does not directly improve performance but ensures data integrity. Option D (increasing compute resources) may help but does not address the underlying inefficiency in the query. For best practices, see the [dbt performance documentation](https://docs.getdbt.com/docs/guides/best-practices/performance).