To set up dbt (Data Build Tool) for using Postgres locally, you need to:
- Install dbt on your local machine.
- Create a dbt project.
- Configure the dbt project to connect to your local Postgres database.
- Run dbt commands to test the setup.
Here are the detailed steps:
You can install dbt using pip:
pip install dbt-postgresThis will install both dbt-core and its plugin dbt-postgres:
$ dbt --version
installed version: 1.0.0
latest version: 1.0.0
Up to date!
Plugins:
- postgres: 1.0.0Navigate to the directory where you want to create your dbt project and run the following command:
dbt init my_dbt_projectReplace my_dbt_project with the name of your project.
Navigate to the project directory:
cd my_dbt_projectOpen the profiles.yml file located in the ~/.dbt directory (create it if it doesn't exist) and add the following configuration:
my_dbt_project:
target: dev
outputs:
dev:
type: postgres
host: localhost
user: postgres
password: postgres
port: 5433 # This should match the port mapping in your docker-compose file for the postgres_dbt container
dbname: nyc_taxi
schema: publicYou can now run dbt commands to test the setup. For example, to run the dbt debug command to test the connection:
dbt debugIf the connection is successful, you can start developing your dbt models. Here are some common dbt commands:
dbt run: Compile and run the models in your project.dbt test: Run tests on your data.dbt docs generate: Generate documentation for your project.dbt docs serve: Serve the documentation locally.
Create a simple dbt model to test the setup. Create a file named example_model.sql in the models directory of your dbt project with the following content:
-- models/example_model.sql
SELECT *
FROM {{ ref('my_table') }}Replace my_table with the name of a table in your Postgres database.
Run the dbt model:
dbt runThis will execute the SQL in example_model.sql and create a table/view in the Postgres database.
By following these steps, you will have dbt set up to work with your local Postgres database using Docker.
Where to install
profiles.yml?The
~/.dbt/profiles.ymlfile should be located in your user's home directory. On Windows, this would typically be:Replace
<YourUsername>with your actual Windows username. This file is used by dbt to store connection profiles for different projects.Here's how you can create the
profiles.ymlfile in the appropriate directory:C:\Users\<YourUsername>\..dbtif it doesn't already exist..dbtfolder, create a new file namedprofiles.yml.Here is the content you should put into
profiles.yml:Replace
<YourUsername>with your actual username. This configuration tells dbt how to connect to your local Postgres database running in the Docker container.Setting Up dbt in Your Project Directory
You mentioned that your project is located at
/c/workspace/de-zoomcamp/04_analytics_engineering/homework/taxi_rides_ny. Here's a step-by-step guide to set up dbt in your project directory:cd my_dbt_projectIf everything is set up correctly, dbt will confirm the connection to your Postgres database.
Example dbt Model
Create a simple dbt model to test the setup. Create a file named
example_model.sqlin themodelsdirectory of your dbt project with the following content:Replace
my_tablewith the name of a table in your Postgres database.Running the Model
Run the dbt model:
This will execute the SQL in
example_model.sqland create a table/view in the Postgres database.By following these steps, you will have dbt set up to work with your local Postgres database using Docker.