How to Integrate MySQL with dbt in PostgreSQL

Why use dbt with MySQL?

dbt turns raw MySQL tables into reliable analytics models with version control, testing, and documentation—all without moving data to another warehouse.

What do I need before starting?

Install Python 3.8+, pip install dbt-mysql, and create a MySQL user with SELECT, CREATE, and INSERT privileges on the target database.

How do I configure profiles.yml?

Store connection details under ~/.dbt/profiles.yml. Set type: mysql, host, port, user, password, schema, and threads.

Sample profiles.yml

my_mysql_project:
target: dev
outputs:
dev:
type: mysql
server: 127.0.0.1
port: 3306
user: analytics
password: {{ env_var('MYSQL_PW') }}
schema: ecommerce
threads: 4


How do I run dbt commands?

Use dbt run to materialize models, dbt test for data quality, and dbt build to execute both in one step.

How can I model Customers and Orders?

Create a SQL file in models/ that joins operational tables, then reference it with ref() in downstream models.

models/customers_with_orders.sql

select
c.id as customer_id,
c.name,
c.email,
o.id as order_id,
o.order_date,
o.total_amount
from {{ source('ecommerce', 'Customers') }} c
left join {{ source('ecommerce', 'Orders') }} o
on o.customer_id = c.id


How do I add tests?

In schema.yml, declare unique and not_null tests on customer_id to catch duplicates and blanks.

What are best practices?

• Use incremental models with unique_key to avoid full reloads.
• Add indexes in post-hook SQL for faster joins.
• Pin adapter versions in requirements.txt.

Common mistakes and fixes

Wrong adapter type: Setting type: postgres instead of mysql breaks authentication. Fix by updating the profile.

No unique_key: Incremental models rerun the entire table without it. Add unique_key: id to the model config.

Next steps

Schedule dbt build in CI/CD, publish docs with dbt docs serve, and monitor query performance.