Model contracts and access controls in dbt - Mini Project: Build & Apply

Model Contracts and Access Controls in dbt

📖 Scenario: You work as a data analyst in a company that uses dbt to manage data transformations. Your team wants to ensure that data models meet certain quality standards and that only authorized users can access sensitive data.To achieve this, you will create model contracts to enforce data quality and set up access controls to restrict who can query specific models.

🎯 Goal: Build a dbt project that defines a model with a contract enforcing a non-null constraint on a column and configure access controls to limit access to this model.

📋 What You'll Learn

Create a dbt model named orders with sample data

Define a model contract that enforces order_id is not null

Set a config variable allowed_roles with roles allowed to access the model

Apply access control logic in the model using the allowed_roles variable

Print the final SQL code of the model showing the contract and access control

💡 Why This Matters

🌍 Real World

Model contracts help ensure data quality by enforcing rules on data columns. Access controls protect sensitive data by restricting who can query models.

💼 Career

Data engineers and analysts use dbt model contracts and access controls to maintain trustworthy data pipelines and comply with data governance policies.

1

Create the orders model with sample data

Create a dbt model file named orders.sql with a simple SELECT statement that returns these columns and values: order_id with values 1, 2, 3 and customer_name with values 'Alice', 'Bob', 'Charlie'.

dbt

-- Create the orders model with sample data
select 1 as order_id, 'Alice' as customer_name
union all
select 2, 'Bob'
union all
select 3, 'Charlie'

Need a hint?

Use a SELECT statement with UNION ALL to create three rows with order_id and customer_name.

2

Define the allowed_roles config variable

In the same orders.sql file, add a dbt config block at the top that defines a variable called allowed_roles with the list ['analyst', 'data_engineer'].

dbt

{{ config(allowed_roles=['analyst', 'data_engineer']) }}

select 1 as order_id, 'Alice' as customer_name
union all
select 2, 'Bob'
union all
select 3, 'Charlie'

# Add the config block above the SELECT statement

Need a hint?

Use the config Jinja function at the top of the model file to set allowed_roles.

3

Add a model contract to enforce order_id is not null

Below the config block, add a contract block that enforces the order_id column is not null using not_null: true.

dbt

{{ config(allowed_roles=['analyst', 'data_engineer']) }}

{{ contract(
    columns=[
        {"name": "order_id", "not_null": true}
    ]
) }}

select 1 as order_id, 'Alice' as customer_name
union all
select 2, 'Bob'
union all
select 3, 'Charlie'

# Add the contract block above the SELECT statement

Need a hint?

Use the contract Jinja function with a columns list specifying order_id with not_null: true.

4

Print the final model SQL code

Print the entire content of the orders.sql model file to show the config, contract, and SELECT statement.

dbt

# Print the full model SQL code below
print('''{{ config(allowed_roles=['analyst', 'data_engineer']) }}

{{ contract(
    columns=[
        {"name": "order_id", "not_null": true}
    ]
) }}

select 1 as order_id, 'Alice' as customer_name
union all
select 2, 'Bob'
union all
select 3, 'Charlie''')

Need a hint?

Use a print statement to output the full model SQL content exactly as written.