How to connect Oracle to dbt

Why connect Oracle to dbt?

Running dbt on Oracle lets analytics engineers apply version-controlled transformations, tests, and documentation to long-standing Oracle data warehouses without expensive migrations.

Which adapter do I need?

Install the open-source dbt-oracle adapter—built and maintained by Oracle engineers—so dbt can issue SQL through the cx_Oracle driver.

How do I install dbt-oracle?

Use pip: pip install dbt-oracle==1.8.0. Ensure the Oracle Instant Client libraries are on the host’s LD_LIBRARY_PATH (Linux) or on Windows PATH.

Where do I put my Oracle credentials?

Create—or extend—~/.dbt/profiles.yml. Each profile contains one or more targets (dev, prod, ci) with connection parameters.

What does a minimal profiles.yml look like?

oracle_profile:
target: dev
outputs:
dev:
type: oracle
threads: 4
host: 10.10.0.15
port: 1521
user: analytics
pass: {{ env_var('DBT_ORACLE_PW') }}
service: ORCLPDB1
schema: ANALYTICS

How do I test the connection?

Run dbt debug --target dev --profile oracle_profile. A successful run returns "All checks passed!" and prints database version and role.

Can I set environment variables instead of plaintext?

Yes. Wrap any value with {{ env_var('VAR_NAME') }}. Store secrets in a vault or CI/CD manager, keeping profiles.yml in the repo.

How do I run a model on Oracle?

Place models under /models, then execute dbt run --models customers orders --target dev --profile oracle_profile. dbt compiles Jinja macros to Oracle-flavored SQL and issues them through the adapter.

Example transformation

-- models/customer_ltv.sql
select
c.id,
c.name,
sum(o.total_amount) as lifetime_value
from {{ source('oracle', 'Customers') }} c
left join {{ ref('orders_clean') }} o
on c.id = o.customer_id
group by c.id, c.name;

Best practices for Oracle + dbt

Use small thread counts (≤8) to avoid cursor exhaustion, leverage materialized_view materializations for fast incremental rebuilds, and pin adapter versions in requirements.txt.

How to deploy in CI/CD?

Set encrypted env vars (DBT_ORACLE_PW, ORACLE_HOST) in the pipeline. Run dbt deps, dbt seed, dbt test, then dbt run --target prod after passing tests.

Is Oracle Data Pump supported?

dbt itself does not handle data loading. Use Data Pump or GoldenGate upstream, then model those tables in dbt.