Model contracts and access controls in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Model Contracts in dbt

What is the primary purpose of a model contract in dbt?

A To schedule dbt runs based on time intervals.

B To control user access permissions to the dbt project files.

C To automate the deployment of dbt models to production environments.

D To define a fixed schema that a model must adhere to, ensuring consistent data structure.

💡 Hint

Think about how contracts help maintain data quality and structure.

Model contracts specify the expected schema of a model, helping to enforce data consistency and catch schema changes early.

❓ Predict Output

intermediate

Output of a dbt model contract test

Given a dbt model with a contract specifying columns id INT and name STRING, what will be the result if the model returns an extra column age INT?

dbt

version: 2
models:
  - name: customers
    columns:
      - name: id
        tests:
          - not_null
      - name: name
        tests:
          - not_null
    contract: true

A The dbt run will fail due to schema mismatch because of the extra 'age' column.

B The dbt run will succeed and ignore the extra 'age' column.

C The dbt run will fail because 'name' column is missing.

D The dbt run will succeed but the contract test will warn about missing columns.

💡 Hint

Contracts enforce exact schema matching, including no extra columns.

Model contracts require the model output to exactly match the defined schema. Extra columns cause a failure.

❓ data_output

advanced

Result of access control configuration in dbt

Consider a dbt project where access controls restrict the finance schema to only the finance_team role. If a user without this role runs a model targeting the finance schema, what will be the outcome?

A The model run will be queued until the user gains access.

B The model run will fail with a permission denied error.

C The model run will succeed and write data to the <code>finance</code> schema.

D The model run will succeed but the data will be masked.

💡 Hint

Think about how database permissions affect write operations.

Without the proper role, the user cannot write to the restricted schema, causing a permission error.

🔧 Debug

advanced

Identify the cause of contract test failure

A dbt model contract test fails with the error: Column 'id' missing in model output. The model SQL is:

select id as customer_id, name from customers

Why does the contract test fail?

A The model output includes 'customer_id' but the contract expects 'customer_id' with different data type.

B The model output is missing the 'name' column required by the contract.

C The model renames 'id' to 'customer_id' but the contract expects 'id'.

D The model SQL has a syntax error causing incomplete output.

💡 Hint

Check if the contract column names match the model output exactly.

The contract expects a column named 'id' but the model renames it to 'customer_id', causing a mismatch.

🚀 Application

expert

Designing access controls for multi-team dbt project

You manage a dbt project with multiple teams: sales, marketing, and finance. Each team should only access their own schema data. How should you configure model contracts and access controls to enforce this?

A Define model contracts per schema and assign database roles with write/read permissions only to their respective schemas.

B Use a single model contract for all schemas and grant all teams full access to all schemas.

C Grant all teams access to a shared schema and filter data by team in SQL.

D Disable model contracts and rely on dbt run schedules to separate team data.

💡 Hint

Think about combining schema-level permissions with contracts for data integrity.

Defining contracts per schema ensures data structure consistency, and role-based permissions restrict access properly.