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.

Practice

1. What is the main purpose of a model contract in dbt?

easy

A. To schedule when models run in dbt Cloud

B. To define rules that ensure data quality in models

C. To control who can access the dbt project files

D. To create visual dashboards from model data

Solution

1. Step 1: Understand what a model contract does

   A model contract sets rules that data must follow to be considered valid in dbt models.

2. Step 2: Compare options to the definition

   Only To define rules that ensure data quality in models describes defining rules for data quality, which matches the purpose of model contracts.

3. Final Answer:

   To define rules that ensure data quality in models -> Option B

4. Quick Check:

   Model contract = data quality rules [OK]

Hint: Model contracts = rules for data quality [OK]

Common Mistakes:

• Confusing access control with model contracts
• Thinking contracts schedule runs
• Mixing contracts with visualization tools

2. Which of the following is the correct syntax to define a model contract in a dbt model's schema.yml file?

easy

A. models:
- name: my_model
access_control:
users:
- admin

B. models:
- name: my_model
schedule:
cron: '0 0 * * *'

C. models:
- name: my_model
contract:
columns:
- name: id
tests:
- not_null

D. models:
- name: my_model
dashboard:
enabled: true

Solution

1. Step 1: Identify the syntax for model contracts in schema.yml

   Model contracts are defined under the contract key with column tests inside schema.yml.

2. Step 2: Match options to this syntax

   models: - name: my_model contract: columns: - name: id tests: - not_null correctly shows contract with columns and tests. Others show unrelated keys like access_control, schedule, or dashboard.

3. Final Answer:

   models: - name: my_model contract: columns: - name: id tests: - not_null -> Option C

4. Quick Check:

   Contract syntax = models: - name: my_model contract: columns: - name: id tests: - not_null [OK]

Hint: Look for 'contract' key with column tests in schema.yml [OK]

Common Mistakes:

• Confusing access control syntax with contract syntax
• Using schedule or dashboard keys incorrectly
• Missing indentation in YAML

3. Given this dbt model contract snippet in schema.yml:

models:
  - name: sales
    contract:
      columns:
        - name: order_id
          tests:
            - not_null
            - unique
        - name: amount
          tests:
            - not_null

What happens if the order_id column contains duplicate values when the model runs?

medium

A. The model run is skipped automatically

B. The model runs successfully but logs a warning

C. The model ignores the duplicates and completes without error

D. The model run fails because the contract test for uniqueness fails

Solution

1. Step 1: Understand contract tests behavior

   Contract tests like unique enforce data rules strictly. If violated, the model run fails.

2. Step 2: Apply to duplicate order_id values

   Since order_id must be unique, duplicates cause the test to fail, stopping the model run.

3. Final Answer:

   The model run fails because the contract test for uniqueness fails -> Option D

4. Quick Check:

   Contract test fail = model run fails [OK]

Hint: Contract tests fail stop model runs immediately [OK]

Common Mistakes:

• Thinking model logs warning but continues
• Assuming duplicates are ignored
• Believing model run is skipped automatically

4. You wrote this access control in your dbt_project.yml:

access_control:
  - role: analyst
    models:
      - sales
      - customers
  - role: viewer
    models:
      - sales

But analysts report they cannot see the customers model. What is the likely error?

medium

A. The access_control block is not supported in dbt_project.yml

B. The role name 'analyst' is misspelled

C. The models list must be under each model, not role

D. The customers model does not exist in the project

Solution

1. Step 1: Check where access controls are defined in dbt

   dbt does not support an access_control block in dbt_project.yml. Access controls are managed outside or via external tools.

2. Step 2: Analyze other options

   Role spelling or model existence might cause issues, but the main problem is unsupported syntax in the config file.

3. Final Answer:

   The access_control block is not supported in dbt_project.yml -> Option A

4. Quick Check:

   Access control block unsupported = The access_control block is not supported in dbt_project.yml [OK]

Hint: Access controls not set in dbt_project.yml [OK]

Common Mistakes:

• Trying to set access control inside dbt_project.yml
• Assuming role names cause access issues
• Not verifying model existence

5. You want to enforce that only users with the 'data_engineer' role can modify the finance model, and that the model must always have a non-null transaction_id. How can you combine model contracts and access controls to achieve this?

hard

A. Define a model contract in schema.yml for transaction_id not null, and manage access control via your data platform's permissions for 'data_engineer' role

B. Add an access control block in schema.yml restricting 'finance' model to 'data_engineer', and add a contract test for transaction_id

C. Use a dbt macro to check user role inside the model SQL and raise error if not 'data_engineer', plus contract test for transaction_id

D. Set transaction_id as primary key in the database and assign 'data_engineer' role ownership on the model

Solution

1. Step 1: Use model contracts for data quality

   Define a contract test in schema.yml to ensure transaction_id is not null, enforcing data quality.

2. Step 2: Manage access control outside dbt

   dbt does not handle user permissions directly; access control is managed by your data platform or warehouse permissions for the 'data_engineer' role.

3. Final Answer:

   Define a model contract in schema.yml for transaction_id not null, and manage access control via your data platform's permissions for 'data_engineer' role -> Option A

4. Quick Check:

   Contracts for data rules + external access control = Define a model contract in schema.yml for transaction_id not null, and manage access control via your data platform's permissions for 'data_engineer' role [OK]

Hint: Use contracts for data, platform for access control [OK]

Common Mistakes:

• Trying to set access control inside dbt schema.yml
• Using macros to check user roles inside SQL
• Assuming dbt manages user permissions directly