In this course, we’ll walk through installing dbt locally using a Python virtual environment. Then, we’ll provision a local PostgreSQL database and demonstrate how to connect dbt to it. This guide will help you setup dbt with a local PostgreSQL. If you want to setup dbt local PostgreSQL properly, we’ve covered it here. Finally, we’ll explore the structure of a dbt project, understanding the purpose of its folders and files.

Prerequisites

If you plan to follow along and practice, ensure you have the following are installed on your system:

Install dbt

In this section, we will install dbt locally using a Python virtual environment. We’ll then install the dbt-core dependency along with the postgres adapter, which allows dbt to connect to a PostgreSQL database.

Below, we create a virtual environment named dbt-env, activate it, and install both dependencies.

python -m venv dbt-env
source dbt-env/bin/activate
python -m pip install dbt-core==1.9.2 dbt-postgres==1.9.0

💡 If you’re using Windows, run the command: .dbt-envScriptsactivate to activate the virtual environment.

• Verify Installation

Once the installation is complete, run the following command to verify that DBT is installed correctly.

dbt --version

The command output will look like this:

Core:
  - installed: 1.9.2
  - latest:    1.9.2 - Up to date!

Plugins:
  - postgres: 1.9.0 - Up to date!

Setting up a dbt project

The first step when working with dbt is to initialize a project by providing its name to the init command. Run the following command to create a project named demo_project and start the process to setup dbt local postgresSQL.

 dbt init demo_project -s

💡 The -s option is used to skip providing PostgreSQL database information interactively. This will be covered in the next section, where we’ll explore how to connect dbt with PostgreSQL.

Explore the structure of a dbt project

In the previous step, we created demo_project, the project typically has the following structure:

demo_project/
│── Analyses/         
│── macros/           
│── models/            
│   ├── staging/      
│   ├── marts/        
│   ├── intermediate/ 
│── seeds/            
│── snapshots/        
│── tests/            
│── dbt_project.yml   
│── README.md

Key Files

At a minimum, a project requires the dbt_project.yml configuration file. Below are the files and folders created when initializing a project.

• models/: Contains SQL models that define transformations.
• analyses/:Contains SQL files that are not considered models. These files will be compiled but not executed.
• macros/: Stores reusable Jinja templates (similar to functions in programming languages).
• seeds/: Static CSV data that can be loaded into the warehouse.
• snapshots/: Tracks changes in data over time.
• tests/: SQL queries that you can write to test the models and resources in your project.
• dbt_project.yml: Configuration for models, tests, and execution settings.

Provision a PostgreSQL database

In this step, we will provision a PostgreSQL database that runs locally. The following command will start a PostgreSQL instance using Docker, making it accessible on a specified port which is essential for the setup of dbt local postgresSQL.

docker run --name dbt-postgres 
	-e POSTGRES_USER=dbt_user 
	-e POSTGRES_PASSWORD=dbt_pass 
	-e POSTGRES_DB=dbt_db 
	-p 5432:5432 
	-d postgres

Connect dbt to PostgreSQL and test the connection

To connect dbt to a PostgreSQL database, we’ll create a file named profiles.yml in the same location as dbt_project.yml. This file stores the connection details for your data platform. When you run dbt Core, it looks up the profile name in dbt_project.yml and then finds the corresponding profile in profiles.yml to establish the connection and complete the setup dbt local PostgreSQL process.

Inside the profiles.yml file, under the dev target, provide the PostgreSQL database details provisioned in the previous section, or use your own database credentials if you’re not using the one provisioned above.

demo_project:
  target: dev
  outputs:
    dev:
      type: postgres
      host: localhost
      user: dbt_user
      password: dbt_pass
      port: 5432
      dbname: test_dbt_db 
      schema: test_dbt_schema
      threads: 1

• Test the Connection

To test the connection between dbt and postgres database, run the command below:

dbt debug

If the connection is established successfully, you should see the following messages: [OK connection ok] and [All checks passed!]

Conclusion

In this tutorial, we learned how to set up dbt locally using a Python virtual environment. We explored the essential folders in the dbt project structure and concluded by connecting dbt to a PostgreSQL database. We hope you find this guide helpful for your setup dbt local postgresSQL journey. Now, it’s time to get hands-on with dbt’s capabilities.

Let’s move on to the next tutorial: Explore dbt models.