dbt Analytics Engineering Certification (DBT-AE) Practice Questions: Testing and Data Quality Domain

Correct Answer: A

Explanation: The correct answer is A. The `dbt_utils.expression_is_true` test allows you to define a custom SQL expression that must evaluate to true for all rows, in this case, ensuring `amount >= 0`. Option B tests for non-null values, which does not address the requirement for non-negative amounts. Option C tests for uniqueness, which is unrelated to the given requirement. Option D incorrectly uses a relationships test, which is intended for referential integrity checks between models. For more on custom tests, see the [dbt-utils documentation](https://github.com/dbt-labs/dbt-utils#expression_is_true-source).

Correct Answer: B

Explanation: The correct answer is B. Incremental models in dbt are configured with `materialized='incremental'` and often use an `incremental_strategy` such as `merge` to handle updates. The SQL query filters data using a condition like `WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})` to only process new or changed records. Option A lacks the necessary filtering logic for incremental updates. Options C and D configure the model as a full table or view, respectively, which do not support incremental processing. For more guidance, refer to the [dbt documentation on incremental models](https://docs.getdbt.com/docs/building-a-dbt-project/building-models/configuring-incremental-models).

Correct Answer: B

Explanation: To ensure that a column contains unique values, you should use the `unique` test macro in the `schema.yml` file. This is the standard way to define column-level tests in dbt, leveraging built-in test macros for data validation. Option A is incorrect because dbt provides built-in test macros, and custom SQL is unnecessary for this common test. Option C is incorrect because dbt operations are not used for testing. Option D is incorrect as it involves database-level changes outside of dbt's testing framework. Refer to the [dbt documentation on tests](https://docs.getdbt.com/docs/building-a-dbt-project/tests) for more information.

Correct Answer: C

Explanation: Option C is correct because it applies both `unique` and `not_null` tests to the `customer_id` column within the `columns` section, ensuring the column is both unique and non-null. Option A only ensures uniqueness, and Option B only ensures non-null values. Option D incorrectly applies the tests at the model level rather than the column level. For more information, refer to the [dbt documentation on schema testing](https://docs.getdbt.com/docs/building-a-dbt-project/tests).

Correct Answer: A

Explanation: Option A is correct because refactoring Jinja macros into SQL expressions can improve performance by leveraging the database's native SQL processing capabilities, which are typically more efficient than Jinja processing. Option B is incorrect because increasing Jinja complexity can further degrade performance. Option C is incorrect because adding more Jinja macros can introduce additional overhead. Option D is incorrect because while removing Jinja might simplify the SQL, it also removes the benefits of templating and reusability. For best practices, refer to the [dbt documentation on Jinja and SQL](https://docs.getdbt.com/docs/building-a-dbt-project/jinja-context).