dbt Analytics Engineering Certification (DBT)-AE Practice Questions: Analytics Engineering Best Practices Domain

Correct Answer: A

Explanation: Option A is correct because splitting the model into smaller SQL files and using the `ref` function to reference them maintains modularity and adheres to dbt best practices. This approach allows for easier testing, documentation, and maintenance. Option B is incorrect because hard-coding table names goes against dbt's dynamic referencing capabilities, which can lead to maintenance challenges. Option C is incorrect because using CTEs in a single file does not effectively refactor the model into manageable pieces. Option D is incorrect because creating separate dbt projects is unnecessary and overly complex for this scenario. [Refer to dbt documentation on refactoring models](https://docs.getdbt.com/docs/building-a-dbt-project/building-models).

Correct Answer: A

Explanation: Option A is correct because dbt-utils provides a `email_validity` test that can be defined in `schema.yml` to check for valid email formats. This approach leverages existing utilities and integrates well with dbt's testing framework. Option B, while possible, is not the best practice as it requires custom SQL logic that could be error-prone and less maintainable. Option C is incorrect because `not_null` only checks for non-null values, not format validity. Option D is incorrect because writing a Jinja macro for this purpose is unnecessarily complex and not the intended use of Jinja in dbt. [Refer to dbt-utils documentation for custom tests](https://github.com/dbt-labs/dbt-utils).

Correct Answer: A

Explanation: The command `dbt test --select state:modified+` is used to run tests on models that have been modified or are downstream of modified models, which is an effective strategy for continuous integration. Option B is incorrect because it runs models, not tests. Option C is incorrect because `modified+` is not a valid selector without the `state:` prefix. Option D is incorrect because it uses an invalid syntax `--state:` instead of `--select state:`. For more information, refer to the dbt documentation on [state-based selectors](https://docs.getdbt.com/reference/node-selection/state-based-selection).

Correct Answer: C

Explanation: The `dbt_utils.expression_is_true` test is used to validate that an expression holds true for all rows in a model. In this case, you can use it to ensure that all sales values are positive by passing an expression like `sales > 0`. Option A is incorrect because `unique` checks for uniqueness, not positivity. Option B is incorrect because `not_null` ensures values are not null, but doesn't check if they are positive. Option D is incorrect because `dbt_utils.not_constant` checks for variability in a column, not positivity. For more information, refer to the dbt-utils documentation on [custom tests](https://github.com/dbt-labs/dbt-utils#expression_is_true-source).

Correct Answer: A

Explanation: The command `dbt run --select state:modified+` allows you to run models that have been modified since the last run. The `state:modified+` selector is part of dbt's state-based selection feature, which is useful in CI/CD pipelines to avoid unnecessary model runs. Option B is incorrect because `--models modified` is not a valid syntax for selecting modified models. Option C is incorrect because `dbt test` is used for running tests, not models. Option D is incorrect because `state:new+` would select newly created models, not modified ones. For more details, see the dbt documentation on [state-based selectors](https://docs.getdbt.com/reference/node-selection/syntax#state-based-selection).

Correct Answer: A

Explanation: Option A is correct because `dbt test --select state:modified+` selects all modified models and their downstream dependencies for testing. This ensures comprehensive testing of changes and their impacts. Option B is incorrect because it only selects modified models without their dependencies. Option C is incorrect because it redundantly tries to select modified models twice. Option D is incorrect because `state:modified*` selects both upstream and downstream dependencies, which is not necessary for testing purposes. For more details, refer to the dbt documentation on [state-based selection](https://docs.getdbt.com/reference/node-selection/syntax#state-based-selection).

Correct Answer: A

Explanation: Option A is correct because `dbt_utils.not_null` is a schema test specifically designed to check that a column does not contain null values. Option B is incorrect because `dbt_utils.unique` checks for unique values, not null values. Option C is incorrect because `accepted_values` is used for checking specific allowed values, not nullity. Option D, while technically possible, is not the most straightforward or idiomatic way to enforce non-null constraints in dbt. For more information, see the dbt documentation on [schema tests](https://docs.getdbt.com/docs/building-a-dbt-project/tests#schema-tests).

Correct Answer: B

Explanation: Option B is correct because optimizing the SQL logic can significantly improve the performance of an incremental model. This might involve refining joins, filtering data earlier, or using indexes effectively. Option A is incorrect because full refresh runs counteract the benefits of incremental processing. Option C might help in some cases, but it depends on the data source and is not guaranteed to solve the issue. Option D is incorrect because disabling incremental logic would increase the time needed to process the entire dataset every time. [Refer to dbt documentation on incremental models](https://docs.getdbt.com/docs/building-a-dbt-project/building-models/incremental-models).

Correct Answer: B

Explanation: The expression `{% if start_date and end_date %}WHERE date BETWEEN '{{ start_date }}' AND '{{ end_date }}'{% endif %}` uses Jinja control flow to conditionally add a `WHERE` clause based on the presence of `start_date` and `end_date` variables. This is a common pattern for dynamic filtering in dbt models. Option A is incorrect because `{{ date_range }}` is a variable substitution, not a conditional logic statement. Option C is incorrect because `date_range` is not defined as a valid condition in the SQL context. Option D is incorrect because setting a Jinja variable without using it in a conditional or loop does not affect the SQL logic. For more information, see the dbt documentation on [Jinja in dbt](https://docs.getdbt.com/docs/building-a-dbt-project/jinja-context/).

Correct Answer: D

Explanation: Option D is correct because it uses Jinja to set a variable `filter_condition` and then correctly applies it in the SQL `where` clause using Jinja templating syntax. Option A is technically correct but does not demonstrate the use of Jinja, which is the focus of this question. Option B is incorrect because it misuses the Jinja `if` statement in a context where it is not needed. Option C is incorrect because it fails to correctly apply the Jinja variable within quotes in the SQL context. For more guidance, see the dbt documentation on [Jinja](https://docs.getdbt.com/docs/building-a-dbt-project/jinja-context).