How to Connect ParadeDB to dbt in PostgreSQL

Galaxy Glossary

How do I connect ParadeDB to dbt?

Configure dbt’s Postgres adapter so models run directly against a ParadeDB instance.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Table of Contents

Why use dbt with ParadeDB?

dbt automates SQL transformations, while ParadeDB adds vector search and full-text power on top of PostgreSQL. Wiring them together lets analytics and ML teams ship feature-rich models through a single workflow.

How do I define a ParadeDB target in `profiles.yml`?

Point dbt’s built-in postgres adapter at your ParadeDB host. Supply hostname, port, database, user, and schema exactly as you would any Postgres database.

Full `profiles.yml` example

paradedb:
target: dev
outputs:
dev:
type: postgres # ParadeDB speaks the Postgres protocol
host: localhost # or parade.company.com
port: 5432
user: parade_user
password: env_var(DBT_DB_PASSWORD)
dbname: parade_db # default database
schema: analytics # where dbt will create models
threads: 4 # parallelism
keepalives_idle: 0 # optional network tuning

How do I test the connection?

Run dbt debug --target dev. dbt opens a session, checks permissions, and confirms ParadeDB extensions such as pgvector are accessible.

How do I run transformations on ParadeDB?

Add SQL models under models/, then execute dbt run. dbt compiles each model to Postgres-compatible SQL and ships it to ParadeDB.

Sample model: `models/customer_ltv.sql`

select
c.id as customer_id,
c.name,
sum(o.total_amount) as lifetime_value
from {{ ref('Customers') }} c
join {{ ref('Orders') }} o on o.customer_id = c.id
group by 1,2;

Best practices for ParadeDB + dbt

Enable needed extensions (CREATE EXTENSION pgvector;) in a migration before dbt runs. Pin dbt to a Postgres version supported by your ParadeDB build. Use incremental models for large vector tables to avoid full reloads.

Common mistakes and fixes

Missing extension: dbt fails if a model uses vector types but pgvector is not installed. Create it once per database.
Wrong adapter: Setting type: paradedb throws an error—use type: postgres instead because ParadeDB speaks the Postgres wire protocol.

Why How to Connect ParadeDB to dbt in PostgreSQL is important

How to Connect ParadeDB to dbt in PostgreSQL Example Usage


-- dbt model that uses ParadeDB’s vector similarity to recommend products

select
  oi.order_id,
  p.id           as product_id,
  p.name,
  p.price,
  p.embedding <-> cust_pref.embedding as distance
from  oi
join    p on p.id = oi.product_id,
     lateral (
       select embedding
       from customer_preferences
       where customer_id = (
         select customer_id from Orders where id = oi.order_id
       )
     ) cust_pref
order by distance asc
limit 10;

How to Connect ParadeDB to dbt in PostgreSQL Syntax


profiles.yml syntax

paradedb:
  target: <target_name>
  outputs:
    <target_name>:
      type: postgres         # required; ParadeDB reuses Postgres adapter
      host: <hostname | ip>  # e.g., parade.internal
      port: <integer>        # default 5432
      user: <db_user>
      password: <db_password or env_var>
      dbname: <database>
      schema: <schema>
      threads: <int>         # parallel model builds
      keepalives_idle: <int> # optional
      search_path: <schema_list>

Environment variables can replace any value using dbt’s <code>env_var(…)</code> helper.

Common Mistakes

Using a non-superuser role to install extensions; ParadeDB needs CREATE EXTENSION privileges. Fix by granting the role temporary superuser or creating extensions in advance.
Forgetting to set the dbt target schema, causing models to be created in <code>public</code>. Always include <code>schema:</code> in the profile or set <code>search_path</code>.

Frequently Asked Questions (FAQs)

Does dbt need a special adapter for ParadeDB?

No. Use the built-in Postgres adapter because ParadeDB is protocol-compatible.

Can I use incremental models with vector columns?

Yes. Declare an incremental model and ensure your unique_key covers the vector column if updates are expected.

How do I manage environments?

Create multiple outputs under one profile—dev, staging, prod—each pointing to a different ParadeDB instance. Switch with --target.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.