dbt-oracle Adapter

1.10.0 · active · verified Fri Apr 17

dbt-oracle is a dbt (data build tool) adapter that enables dbt to connect to and transform data within Oracle Autonomous Database. It allows users to leverage dbt's powerful data transformation capabilities on their Oracle data warehouses. The library is actively maintained, with frequent patch releases within minor versions following dbt-core's release cadence. The current version is 1.10.0.

Common errors

```
PackageNotFoundError: dbt-oracle is not installed for dbt-core==X.Y.Z
```
cause The installed `dbt-oracle` version is not compatible with your `dbt-core` version, or `dbt-oracle` is not installed in the same environment as `dbt-core`.

fix Check `dbt-oracle`'s GitHub releases or documentation for compatible `dbt-core` versions. Reinstall `dbt-oracle` and `dbt-core` in a clean virtual environment, ensuring their versions align. Example: `pip install dbt-core==1.10.x dbt-oracle==1.9.4`.
```
ModuleNotFoundError: No module named 'oracledb'
```
cause The `oracledb` Python driver, a required dependency for `dbt-oracle`, is not installed or not accessible in your Python environment.

fix Explicitly install the `oracledb` driver: `pip install oracledb`. If it's installed, ensure your `PATH` and `LD_LIBRARY_PATH` (or `DYLD_LIBRARY_PATH` on macOS) correctly point to your Oracle Client libraries if not using a thin client.
```
ORA-XXXXX: connection error (e.g., ORA-12154, ORA-01017, ORA-28000)
```
cause The dbt profile's connection parameters are incorrect (user, password, host, port, SID/service_name, TNS alias), or there are underlying network/database authentication issues.

fix Verify all connection details in your `profiles.yml` are accurate. Test connectivity using an external tool like SQL*Plus, SQL Developer, or `tnsping` with the same credentials. Ensure the Oracle database is accessible from the dbt machine.
```
RuntimeError: Your Python version X.Y is not supported by dbt-oracle.
```
cause You are trying to run `dbt-oracle` with an unsupported Python version (e.g., Python 3.8 or older).

fix Upgrade your Python environment to version 3.9 or newer. Create a new virtual environment with a supported Python version and reinstall `dbt-oracle`.

Warnings

breaking dbt-oracle versions are tightly coupled with dbt-core versions. Upgrading dbt-core without ensuring compatibility with your dbt-oracle version will likely result in dependency conflicts or runtime errors.
Fix: Always check the dbt-oracle GitHub releases or documentation for the `dbt-core` version it supports before upgrading either. For example, v1.9.4 requires `dbt-core` 1.10.
breaking Support for Python 3.8 was dropped in version 1.8.4. Attempting to use dbt-oracle with Python 3.8 or older will fail.
Fix: Ensure your Python environment is version 3.9 or higher (current PyPI requires >=3.9).
gotcha Frequent upgrades to the underlying `oracledb` Python driver can introduce new requirements or minor behavioral changes. Ensure your environment has the necessary Oracle Client libraries if using features like TNS or Wallets.
Fix: Refer to the `oracledb` documentation for its specific system requirements. Reinstall `oracledb` if you encounter issues after `dbt-oracle` upgrades its driver version.
gotcha Snapshot behavior was significantly updated in v1.9.1 (stronger SHA-256 hash function) and v1.8.4 (`dbt_valid_to` column precision). Existing snapshots might behave differently or require regeneration after upgrading.
Fix: Carefully review snapshot models and consider a full refresh or testing in a non-production environment after upgrading to these versions. Monitor for changes in hash collisions or validity period calculations.

Install

pip install dbt-oracle Install dbt-oracle

Quickstart

To use dbt-oracle, you configure your Oracle connection details in a `profiles.yml` file, typically located in `~/.dbt/profiles.yml`. This configuration defines how dbt connects to your Oracle database. After setting up the profile, you can initialize a dbt project and run your models using standard dbt CLI commands like `dbt run`.

import os

# Configure dbt profile in profiles.yml (e.g., ~/.dbt/profiles.yml)
# Replace with your actual database details and environment variables
profiles_yml_content = f"""
my_oracle_project:
  target: dev
  outputs:
    dev:
      type: oracle
      user: "{os.environ.get('DBT_ORACLE_USER', 'your_user')}"
      password: "{os.environ.get('DBT_ORACLE_PASSWORD', 'your_password')}"
      host: "{os.environ.get('DBT_ORACLE_HOST', 'localhost')}"
      port: {os.environ.get('DBT_ORACLE_PORT', '1521')}
      sid: "{os.environ.get('DBT_ORACLE_SID', '')}" # or service_name
      service_name: "{os.environ.get('DBT_ORACLE_SERVICE_NAME', 'your_service_name')}"
      schema: "{os.environ.get('DBT_ORACLE_SCHEMA', 'your_schema')}"
      threads: 4
      # Optional: tns_name, wallet_location, connect_string
"""

# Example of how you would set up your dbt project and run it via command line
# This code snippet only shows the profile configuration setup conceptually.
# Actual dbt operations are typically run from the terminal.
print("dbt-oracle profile configuration ready. Next, run dbt commands:")
print("1. Initialize a dbt project: `dbt init <project_name>`")
print("2. Test connection: `dbt debug`")
print("3. Run transformations: `dbt run`")

view raw JSON →