​

Connect dbt to Firebolt using the PostgreSQL protocol

• dbt Cloud using the PostgreSQL adapter
• dbt Core using the PostgreSQL adapter with mutual TLS (mTLS)

Note Firebolt also provides a native dbt adapter, but it is currently limited to Firebolt Core only. This guide focuses on the PostgreSQL protocol, which works for both Cloud and Core.

​

Overview

• Firebolt is exposed as a PostgreSQL-compatible endpoint
• Authentication is done using service accounts
• Account and engine are provided via the username field
  • Username use a triple identifier format: <account>:<engine>:<service_account_id>

​

Prerequisites

1. dbt Cloud or dbt Core – Depending on your choice, have access to either a dbt Cloud account or a local installation of dbt Core.
2. Firebolt account – You need an active Firebolt account. If you do not have one, you can sign up for one.
3. Firebolt database and table – You must have access to a Firebolt database that contains a table with data ready for transformation. If you don’t have access, you can create a database and then load data into it.
4. Firebolt service account – You must have access to an active Firebolt service account, which facilitates programmatic access to Firebolt, its ID and secret.
5. Firebolt user – You must have a user that is associated with your service account. The user should have USAGE permission to query your database, and OPERATE permission to start and stop an engine if it is not already started.

​

Connect dbt Cloud to Firebolt

​

Step 1: Create a PostgreSQL connection

1. Go to Settings → Connections
2. Click New connection
3. Select PostgreSQL

• Fill in the required fields:
  • Connection name: Any descriptive name (for example: Firebolt PostgreSQL)
  • Server hostname: pg.<region>.app.firebolt.io
    • Replace <region> with your Firebolt account region (for example: us-east-1)
  • Port: 5432
• Expand Optional settings and set:
  • Database name: <database_name>
    • Replace the placeholders with your account’s information:
• Click Save.

​

Step 2: Create a dbt Cloud project

1. Go to Projects
2. Click + New project
3. Enter a Project name
4. Continue to Configure your development environment

​

Configure development credentials

• Connection: Select the PostgreSQL connection created earlier
• Username: <account_name>:<engine_name>:<service_account_client_id>
• Password: <service_account_client_secret>
  • Replace the placeholders with your service account’s information:
    • <account_name>: Your Firebolt account name
    • <engine_name>: Your Firebolt engine name
    • <service_account_client_id>: Client ID of your service account
    • <service_account_client_secret>: Client secret of your service account

​

Allowlisting dbt Cloud IP Addresses

​

Get your dbt Cloud IP address

1. While creating or editing the PostgreSQL connection in dbt Cloud, scroll to the Settings section
2. Locate the message indicating the IP addresses dbt Cloud will connect from.
3. Copy all IP addresses listed in that message

​

Request allowlisting

• Your name and email address
• Your organization name
• Name of the tool you want to connect (dbt Cloud in this case)
• The dbt Cloud IPs addresses to allowlist

​

Connect dbt Core to Firebolt (mTLS)

• PostgreSQL adapter
• Service account credentials
• Mutual TLS (mTLS)
• Full server certificate verification (verify-full)

​

Step 1: Generate certificates (client cert + Let’s Encrypt root)

• Generates a client private key and certificate
• Downloads the Let’s Encrypt root CA used by Firebolt servers
• Derives a public key to attach to the Firebolt service account

#!/bin/bash
set -euo pipefail

CLIENT_CN="${CLIENT_CN:-firebolt-dbt}"
DAYS_VALID="${DAYS_VALID:-730}"
OUT_DIR="${OUT_DIR:-./out}"

LE_ROOT_URL="https://letsencrypt.org/certs/isrgrootx1.pem.txt"

mkdir -p "$OUT_DIR"

echo "==> Client CN        : $CLIENT_CN"
echo "==> Validity (days)  : $DAYS_VALID"
echo "==> Output directory : $OUT_DIR"

# ---- 1) Server root CA (verify Firebolt server cert) ----
echo "==> Downloading Let's Encrypt root (ISRG Root X1)"
curl -fsSL "$LE_ROOT_URL" -o "$OUT_DIR/isrgrootx1.pem"

# Sanity check
openssl x509 -in "$OUT_DIR/isrgrootx1.pem" -noout -subject >/dev/null

# ---- 2) Client private key ----
echo "==> Generating client private key"
openssl genrsa -out "$OUT_DIR/fb-client.key" 2048

# ---- 3) CSR ----
echo "==> Creating CSR"
openssl req -new \
  -key "$OUT_DIR/fb-client.key" \
  -out "$OUT_DIR/fb-client.csr" \
  -subj "/CN=$CLIENT_CN"

# ---- 4) Self-sign client cert with clientAuth extensions ----
cat > "$OUT_DIR/client.ext" <<'EOF'
basicConstraints=CA:FALSE
keyUsage=digitalSignature,keyEncipherment
extendedKeyUsage=clientAuth
subjectKeyIdentifier=hash
authorityKeyIdentifier=keyid,issuer
EOF

echo "==> Creating self-signed client certificate (clientAuth)"
openssl x509 -req \
  -in "$OUT_DIR/fb-client.csr" \
  -signkey "$OUT_DIR/fb-client.key" \
  -out "$OUT_DIR/fb-client.crt" \
  -days "$DAYS_VALID" \
  -sha256 \
  -extfile "$OUT_DIR/client.ext"

rm -f "$OUT_DIR/fb-client.csr" "$OUT_DIR/client.ext"

# ---- 5) Public key for Firebolt service account ----
echo "==> Deriving public key for Firebolt service account"
openssl pkey \
  -in "$OUT_DIR/fb-client.key" \
  -pubout \
  -out "$OUT_DIR/fb-public.pem"

echo ""
echo "✅ Done. Generated files:"
echo ""
echo "dbt Core / libpq SSL:"
echo "  sslrootcert : $OUT_DIR/isrgrootx1.pem"
echo "  sslcert     : $OUT_DIR/fb-client.crt"
echo "  sslkey      : $OUT_DIR/fb-client.key"
echo ""
echo "Firebolt service account:"
echo "  Public key  : $OUT_DIR/fb-public.pem"

​

Step 2: Configure the Firebolt service account

ALTER SERVICE ACCOUNT "<service_account_name>"
SET PUBLIC_KEY = '-----BEGIN PUBLIC KEY-----
<contents of fb-public.pem>
-----END PUBLIC KEY-----';

​

Step 3: Configure profiles.yml

firebolt_mtls:
  target: dev
  outputs:
    dev:
      type: postgres
      host: pg.<region>.app.firebolt.io
      port: 5432

      # Firebolt service account credentials (triple identifier)
      user:  <account_name>:<engine_name>:<service_account_client_id>
      password: <service_account_client_secret>

      dbname: <database_name>
      schema: public
      threads: 1

      # --- SSL / mTLS ---
      sslmode: verify-full
      sslrootcert: <path>/out/isrgrootx1.pem
      sslcert: <path>/out/fb-client.crt
      sslkey: <path>/out/fb-client.key

• <region>: Your Firebolt region (for example: us-east-1)
• <service_account_client_id>: Client ID of your service account
• <service_account_client_secret>: Client secret of your service account
• <account_name>: Your Firebolt account name
• <database_name>: Your Firebolt database name
• <engine_name>: Your Firebolt engine name
• <path>: Path to the directory where the certificates were generated

​

Troubleshooting, known limitations and workarounds

​

Dependent views and model rebuild failures

• dbt run
• dbt build
• dbt run --full-refresh

• references to dependent views
• failures to ALTER TABLE or RENAME objects
• messages indicating the object does not exist, even though dbt expects it to

​

Why this happens

1. Create a temporary relation (__dbt_tmp)
2. Rename the existing relation to a backup (__dbt_backup)
3. Rename the temporary relation to the target name

• dbt maintains an internal relation cache
• Executing raw SQL does not update dbt’s cache
• dbt may still think the view exists and attempt to rename it
• This leads to flaky behavior (for example: every second run fails)

​

Recommended workaround (safe and stable)

​

1. Define a helper macro

{% macro firebolt_drop_view_if_exists(schema_name, view_name) %}
  {% if execute %}
    {% set rel = adapter.get_relation(
        database=this.database,
        schema=schema_name,
        identifier=view_name
    ) %}
    {% if rel is not none %}
      {% do adapter.drop_relation(rel) %}
    {% endif %}
  {% endif %}
{% endmacro %}

• Checks whether the view exists
• Drops it using dbt’s adapter API
• Keeps dbt’s internal state consistent

​

1. Call the macro from a model pre-hook

{{
  config(
    pre_hook=[
      "{{ firebolt_drop_view_if_exists('public', 'dependent_view_name') }}"
    ]
  )
}}

models:
  - name: orders
    pre_hook:
      - "{{ firebolt_drop_view_if_exists('public', 'dependent_view_name') }}"

​

Note and limitations

• Do not use raw DROP VIEW statements in hooks
• Avoid recreating views manually inside hooks, as this can introduce cycles
• The drop must run on the upstream table model, not on the view itself

​

Additional resources

• dbt documentation home
• Introduction to dbt
• Installing dbt Core
• Connecting dbt to PostgreSQL
• Configuring profiles.yml
• Building models in dbt
• dbt materializations
• Hooks in dbt (pre-hook and post-hook)