Model contracts and access controls in dbt - Deep Dive

Overview - Model contracts and access controls

What is it?

Model contracts and access controls in dbt are ways to define clear rules about what data models should look like and who can see or change them. A model contract sets expectations for the data shape and quality, like a promise between teams. Access controls decide who can run, edit, or view these models to keep data safe and trustworthy. Together, they help teams work smoothly and avoid mistakes.

Why it matters

Without model contracts, teams might use data models that change unexpectedly, causing errors or wrong decisions. Without access controls, sensitive data could be seen or changed by the wrong people, risking privacy and trust. These tools make sure data is reliable and secure, which is crucial for businesses that depend on accurate information to make smart choices.

Where it fits

Before learning this, you should understand basic dbt concepts like models, tests, and how dbt runs SQL transformations. After mastering model contracts and access controls, you can explore advanced data governance, automated testing, and deployment pipelines to manage data at scale.

Mental Model

Core Idea

Model contracts define what data should look like, and access controls decide who can interact with that data, together ensuring reliable and secure data workflows.

Think of it like...

It's like a rental agreement for an apartment: the contract says what condition the apartment must be in, and the landlord controls who can enter or make changes.

┌───────────────────────────────┐
│         Model Contracts        │
│  (Define data shape & quality) │
└──────────────┬────────────────┘
               │
               ▼
┌───────────────────────────────┐
│        Data Models in dbt       │
│  (SQL transformations & tables) │
└──────────────┬────────────────┘
               │
               ▼
┌───────────────────────────────┐
│        Access Controls          │
│  (Who can view/edit/run models) │
└───────────────────────────────┘

Build-Up - 6 Steps

1

Foundation

Understanding dbt Models Basics

Concept: Learn what dbt models are and how they transform raw data into useful tables.

dbt models are SQL files that define how to transform raw data into cleaned, organized tables. When you run dbt, it runs these SQL queries and creates tables or views in your database. Models are the building blocks of your data pipeline.

Result

You can create a simple model that selects and cleans data, producing a new table in your database.

Knowing what models do is essential because contracts and access controls apply directly to these data outputs.

2

Foundation

Basics of Data Contracts

Concept: Introduce the idea of a contract as a promise about data shape and quality.

A data contract in dbt means you define what columns a model must have, their types, and sometimes constraints like uniqueness. This acts like a checklist to ensure the model meets expectations before others use it.

Result

You can write tests that check if a model matches its contract, catching errors early.

Understanding contracts helps prevent surprises when data changes unexpectedly, protecting downstream users.

3

Intermediate

Implementing Model Contracts in dbt

🤔Before reading on: do you think model contracts are enforced automatically or require explicit tests? Commit to your answer.

Concept: Learn how to use dbt tests and schema files to enforce model contracts.

In dbt, you define contracts by writing schema.yml files with tests for columns, like checking data types, null values, or uniqueness. These tests run every time you build models, ensuring contracts hold.

Result

Running dbt test shows if your model meets the contract or if there are issues to fix.

Knowing that contracts are enforced by tests clarifies how dbt helps maintain data quality continuously.

4

Intermediate

Basics of Access Controls in dbt

🤔Before reading on: do you think dbt controls access at the model level or only at the database level? Commit to your answer.

Concept: Understand how access controls limit who can run or change models.

dbt itself does not manage user permissions directly but works with your database's access controls. You set permissions in your database to control who can read or write tables created by dbt. Additionally, dbt Cloud offers role-based access to control who can run jobs or edit projects.

Result

Only authorized users can modify or run models, protecting data integrity.

Recognizing the split between dbt and database permissions helps you design secure data workflows.

5

Advanced

Combining Contracts with Access Controls

🤔Before reading on: do you think contracts alone are enough to ensure data trust, or is access control also necessary? Commit to your answer.

Concept: Learn why both contracts and access controls are needed together for reliable data.

Contracts ensure data meets expectations, but without access controls, anyone could change models or data, breaking contracts. By combining both, you ensure data quality and prevent unauthorized changes, creating a trustworthy data environment.

Result

A robust system where data is both correct and secure.

Understanding the synergy between contracts and access controls is key to professional data governance.

6

Expert

Advanced Contract Patterns and Access Strategies

🤔Before reading on: do you think contracts can evolve safely over time without breaking users? Commit to your answer.

Concept: Explore how to manage contract changes and fine-grained access in complex projects.

In large teams, contracts evolve as data needs change. Use versioning and incremental tests to update contracts safely. For access, implement least privilege principles and use dbt Cloud roles combined with database grants. Automate contract validation in CI/CD pipelines to catch issues before deployment.

Result

A scalable, maintainable system that adapts to change without surprises.

Knowing how to evolve contracts and access controls prevents downtime and builds trust in data systems.

Under the Hood

Model contracts in dbt work by defining tests in schema files that run SQL queries checking data properties like types and uniqueness. These tests run after model builds and report failures. Access controls rely on the database's permission system, where user roles define who can read or write tables. dbt Cloud adds a layer of project-level permissions controlling who can edit or run dbt jobs, integrating with identity providers for secure authentication.

Why designed this way?

dbt separates contract enforcement from access control to leverage existing database security and keep dbt focused on transformation logic. This design allows flexibility across different databases and environments. Contracts as tests fit naturally into dbt's build-test workflow, making quality checks automatic and visible. Access controls rely on mature database systems to handle security, avoiding reinventing complex permission systems.

┌───────────────┐       ┌─────────────────────┐
│  dbt Model    │──────▶│  Contract Tests Run  │
│  SQL Query    │       │  (Check data shape)  │
└──────┬────────┘       └─────────┬───────────┘
       │                          │
       │                          ▼
       │                 ┌─────────────────┐
       │                 │ Test Results    │
       │                 │ Pass or Fail    │
       │                 └─────────────────┘
       │
       ▼
┌───────────────┐       ┌─────────────────────┐
│ Database      │◀──────│ Access Controls      │
│ Tables/Views  │       │ (Permissions on data)│
└───────────────┘       └─────────────────────┘

Myth Busters - 3 Common Misconceptions

Quick: Do you think dbt automatically restricts who can see or edit models by default? Commit to yes or no.

Common Belief: dbt automatically controls who can access or modify data models it creates.

Reality: dbt relies on the database's permission system and external tools like dbt Cloud for access control; it does not enforce access restrictions by itself.

Why it matters: Assuming dbt controls access can lead to security gaps where unauthorized users can see or change sensitive data.

Quick: Do you think model contracts prevent all data errors without any manual checks? Commit to yes or no.

Common Belief: Once you define a model contract, data errors are impossible because the contract enforces everything.

Reality: Contracts help catch many errors but depend on well-written tests and cannot prevent all issues, especially logic errors inside SQL transformations.

Why it matters: Overreliance on contracts can cause missed errors and false confidence in data quality.

Quick: Do you think changing a model's contract always breaks downstream users? Commit to yes or no.

Common Belief: Any change to a model contract will break all users relying on that data.

Reality: Contracts can evolve safely with versioning, communication, and incremental testing to minimize disruption.

Why it matters: Believing all changes break users can cause teams to avoid necessary improvements, leading to stale or inaccurate data.

Expert Zone

1

Contracts can be layered: base contracts define minimal requirements, while extended contracts add stricter rules for specific consumers.

2

Access controls should be aligned with contracts so that only users who understand and respect the contract can modify or consume the data.

3

Automating contract tests in CI/CD pipelines helps catch contract violations before they reach production, reducing downtime.

When NOT to use

Model contracts and access controls are less effective if your data environment lacks robust testing or permission systems. In such cases, consider using dedicated data governance platforms or data catalog tools that provide stronger enforcement and auditing.

Production Patterns

Teams use contracts to document and enforce SLAs on data freshness and quality, integrating tests into automated pipelines. Access controls are managed via database roles combined with dbt Cloud permissions, ensuring separation of duties between data engineers and analysts.

Connections

Software API Contracts

Model contracts in dbt are similar to API contracts that define how software components communicate.

Understanding API contracts helps grasp why defining clear expectations for data models prevents integration errors.

Role-Based Access Control (RBAC)

Access controls in dbt projects rely on RBAC principles to assign permissions based on user roles.

Knowing RBAC concepts clarifies how to design secure and manageable data access policies.

Legal Contracts

Model contracts resemble legal contracts that set obligations and rights between parties.

Seeing data contracts as legal agreements highlights the importance of clarity, enforcement, and trust in data collaboration.

Common Pitfalls

#1 Ignoring to write tests for model contracts.

Wrong approach:

schema.yml without any tests:
models:
- name: customers
columns:
- name: id
- name: email

Correct approach:

schema.yml with tests:
models:
- name: customers
columns:
- name: id
tests:
- unique
- not_null
- name: email
tests:
- not_null

Root cause:

Misunderstanding that contracts require explicit tests to enforce data quality.

#2 Assuming dbt controls database permissions directly.

Wrong approach:

Trying to set access control inside dbt model SQL or config without database grants.

Correct approach:

Manage access via database GRANT statements and dbt Cloud roles separately.

Root cause:

Confusing dbt's role as a transformation tool with database security responsibilities.

#3 Changing contracts without notifying downstream users.

Wrong approach:

Modifying schema.yml tests or model columns silently and deploying immediately.

Correct approach:

Communicate changes, version contracts, and run tests in CI before deployment.

Root cause:

Underestimating the impact of contract changes on data consumers.

Key Takeaways

Model contracts in dbt are explicit promises about data shape and quality enforced through tests.

Access controls rely on database permissions and dbt Cloud roles to secure who can view or modify data models.

Together, contracts and access controls build trust and reliability in data pipelines.

Contracts must be actively maintained and evolved carefully to avoid breaking users.

Understanding the separation of concerns between transformation, testing, and security is key to effective data governance.

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