dbt in CI/CD pipelines - Mini Project: Build & Apply

dbt in CI/CD pipelines

📖 Scenario: You work in a data team that uses dbt (data build tool) to transform data in your warehouse. Your team wants to automate testing and deployment of dbt models using a CI/CD pipeline. This project will guide you through setting up a simple dbt project, configuring a test command, running the core dbt commands, and finally printing the test results.

🎯 Goal: Build a basic dbt project setup and simulate running dbt commands in a CI/CD pipeline script. You will create the initial dbt project structure, add a configuration for the test command, run the core dbt commands in sequence, and output the test results.

📋 What You'll Learn

Create a dbt project dictionary with key details

Add a configuration variable for the test command

Write a sequence of dbt commands to run in the pipeline

Print the final test results message

💡 Why This Matters

🌍 Real World

Data teams use dbt to transform and test data models. Automating dbt commands in CI/CD pipelines ensures data quality and faster deployments.

💼 Career

Understanding how to script and automate dbt commands is valuable for data engineers and analytics engineers working with modern data stacks.

1

Create the initial dbt project dictionary

Create a dictionary called dbt_project with these exact keys and values: 'name' set to 'my_dbt_project', 'version' set to '1.0', and 'models' set to a list containing 'customers' and 'orders'.

dbt

# Create the dbt_project dictionary with name, version, and models keys
# Your code here

Hint

Use curly braces {} to create a dictionary and square brackets [] for the list of models.

2

Add a test command configuration

Add a variable called test_command and set it to the string 'dbt test' to represent the dbt test command in the pipeline.

dbt

dbt_project = {
    'name': 'my_dbt_project',
    'version': '1.0',
    'models': ['customers', 'orders']
}
# Add the test_command variable below
# Your code here

Hint

Assign the string 'dbt test' to the variable test_command.

3

Write the core dbt pipeline commands

Create a list called pipeline_commands with these exact strings in order: 'dbt deps', 'dbt seed', 'dbt run', and the variable test_command as the last item.

dbt

dbt_project = {
    'name': 'my_dbt_project',
    'version': '1.0',
    'models': ['customers', 'orders']
}
test_command = 'dbt test'
# Create the pipeline_commands list below
# Your code here

Hint

Use a list with the exact strings and include the variable test_command as the last element.

4

Print the test results message

Write a print statement that outputs exactly: 'All dbt tests passed successfully!'.

dbt

dbt_project = {
    'name': 'my_dbt_project',
    'version': '1.0',
    'models': ['customers', 'orders']
}
test_command = 'dbt test'
pipeline_commands = ['dbt deps', 'dbt seed', 'dbt run', test_command]
# Print the test results message below
# Your code here

Hint

Use print() with the exact message inside quotes.

Practice

1. What is the main purpose of using dbt in a CI/CD pipeline?

easy

A. To create user interfaces for data visualization

B. To manually update data models in production

C. To automate building and testing data models before deployment

D. To store raw data without transformation

Solution

1. Step 1: Understand dbt's role in CI/CD

   dbt automates the building and testing of data models to ensure quality before deployment.

2. Step 2: Identify the main goal in CI/CD context

   The goal is to catch errors early by automating tests and builds, not manual updates or UI creation.

3. Final Answer:

   To automate building and testing data models before deployment -> Option C

4. Quick Check:

   dbt automates testing and building = C [OK]

Hint: Remember: CI/CD means automate build and test steps [OK]

Common Mistakes:

• Thinking dbt creates user interfaces
• Assuming dbt is for manual updates
• Confusing dbt with raw data storage

2. Which of the following is the correct command to run dbt models in a CI/CD pipeline?

easy

A. dbt build

B. dbt run

C. dbt deploy

D. dbt execute

Solution

1. Step 1: Recall dbt commands for running models

   The standard command to run models is dbt run.

2. Step 2: Eliminate incorrect commands

   dbt build combines multiple steps including run and test, but to specifically run models use dbt run. dbt deploy and dbt execute do not exist in dbt CLI.

3. Final Answer:

   dbt run -> Option B

4. Quick Check:

   Run models with 'dbt run' = A [OK]

Hint: Use 'dbt run' to execute models in CI/CD [OK]

Common Mistakes:

• Using 'dbt build' which is invalid
• Confusing 'dbt deploy' with deployment
• Trying 'dbt execute' which is not a command

3. Given this snippet in a CI/CD pipeline script:

dbt deps
 dbt seed
 dbt run
 dbt test

What is the expected output after running dbt test?

medium

A. It runs tests on the built models and reports any failures

B. It installs dependencies for dbt packages

C. It seeds the database with initial data

D. It compiles SQL files without running tests

Solution

1. Step 1: Understand the sequence of commands

   dbt deps installs dependencies, dbt seed loads seed data, dbt run builds models, and dbt test runs tests on those models.

2. Step 2: Identify what dbt test does

   dbt test checks the models for errors or data quality issues and reports failures.

3. Final Answer:

   It runs tests on the built models and reports any failures -> Option A

4. Quick Check:

   'dbt test' runs model tests = A [OK]

Hint: Remember: 'dbt test' checks model correctness after build [OK]

Common Mistakes:

• Confusing 'dbt test' with dependency installation
• Thinking 'dbt test' seeds data
• Assuming 'dbt test' only compiles SQL

4. You see this error in your CI/CD pipeline logs:

ERROR: No profiles.yml found

What is the most likely cause?

medium

A. The dbt profile configuration file is missing or not accessible

B. The dbt models have syntax errors

C. The database connection is slow

D. The seed data is empty

Solution

1. Step 1: Analyze the error message

   The error clearly states that profiles.yml is not found, which is the configuration file dbt uses to connect to the database.

2. Step 2: Understand the impact of missing profiles.yml

   Without this file, dbt cannot connect to the database, causing the pipeline to fail before running models or tests.

3. Final Answer:

   The dbt profile configuration file is missing or not accessible -> Option A

4. Quick Check:

   'No profiles.yml found' means missing config = D [OK]

Hint: Check for profiles.yml file in CI environment [OK]

Common Mistakes:

• Assuming syntax errors cause this message
• Blaming slow database connection
• Thinking empty seed data causes this error

5. In a CI/CD pipeline, you want to ensure that dbt seeds data only once before running models and tests. Which pipeline step order is best?

hard

A. dbt run -> dbt seed -> dbt test -> dbt deps

B. dbt seed -> dbt test -> dbt deps -> dbt run

C. dbt test -> dbt seed -> dbt run -> dbt deps

D. dbt deps -> dbt seed -> dbt run -> dbt test

Solution

1. Step 1: Understand the correct order of dbt commands

   First, dbt deps installs dependencies, then dbt seed loads seed data once, followed by dbt run to build models, and finally dbt test to validate models.

2. Step 2: Evaluate the options for logical sequence

   dbt deps -> dbt seed -> dbt run -> dbt test follows the correct logical order ensuring seed data is loaded before models run and tests execute last.

3. Final Answer:

   dbt deps -> dbt seed -> dbt run -> dbt test -> Option D

4. Quick Check:

   Seed before run and test = B [OK]

Hint: Seed data before running models and tests [OK]

Common Mistakes:

• Running models before seeding data
• Testing before building models
• Installing dependencies after running models