dbt Oracle Adapter
Relevant source files
The following files were used as context for generating this wiki page:
The dbt Oracle Adapter is a modern dbt integration for Oracle Database built on the FLEXT framework. It provides the necessary logic, metadata normalization, and connection management to execute dbt models (tables, views, incremental, and snapshots) against Oracle environments. The adapter is designed to be "modern dbt only," supporting dbt-core version 1.8.0 or higher and leveraging the v12 adapter API.
Sources: README.md:1-10, rules.json:1-15
Architecture and Components
The adapter architecture is structured around typed settings, domain models, and utility facades. It inherits core database logic from flext-db-oracle and dbt-specific service patterns from flext-meltano.
Core Logic and Integration
The adapter enforces several architectural constraints:
- No Direct Database Access: Operations must use the mandatory connection service and custom connection manager.
- Modernization: It utilizes structured logging, metrics collection, and query execution modernization.
- PostgreSQL Exclusion: The adapter explicitly forbids dependencies on PostgreSQL-related libraries like
psycopg2.
Sources: rules.json:16-25, rules.json:145-155
Data Flow Overview
The following diagram illustrates the flow from runtime settings to the generation of dbt-compatible Oracle connection profiles and model metadata.
Configuration and Settings
Configuration is managed via FlextDbtOracleSettings, which utilizes environment variables with the prefix FLEXT_DBT_ORACLE_.
Connection Parameters
The adapter supports both Service Name and SID-based connections using the tcp or tcps protocols.
| Parameter | Type | Default | Description |
|---|---|---|---|
oracle_host | str | localhost | Oracle database host |
oracle_port | int | 1521 | Oracle database port |
oracle_username | str | oracle | Database username |
oracle_service_name | str | XEPDB1 | Oracle service name |
sid | str | None | None | Optional Oracle SID |
protocol | Literal['tcp', 'tcps'] | tcp | Connection protocol |
Sources: src/flext_dbt_oracle/settings.py:27-55, rules.json:115-125
Performance and Pooling
The adapter includes built-in support for connection pooling and query performance tuning.
| Setting | Type | Default | Description |
|---|---|---|---|
pool_min_size | int | 1 | Minimum pool size |
pool_max_size | int | 10 | Maximum pool size |
query_timeout | int | 300 | Query timeout in seconds |
fetch_size | int | 1000 | Batch size for fetching rows |
Sources: src/flext_dbt_oracle/settings.py:86-110, rules.json:96-105
Data Type Mappings
The adapter provides a comprehensive mapping between dbt types and Oracle-specific data types to ensure schema consistency.
| dbt Type | Oracle Target Type |
|---|---|
string | VARCHAR2(4000) |
text | CLOB |
integer | NUMBER(38,0) |
float | BINARY_DOUBLE |
boolean | NUMBER(1,0) |
timestamp | TIMESTAMP |
timestamptz | TIMESTAMP WITH TIME ZONE |
json | JSON |
Sources: rules.json:52-82
Model Generation and Metadata
The adapter includes a ModelGenerator to programmatically create dbt model metadata, specifically for staging layers.
Model Metadata Structure
The FlextDbtOracleModels.DbtOracle.Model class captures essential dbt metadata:
- name: The dbt model name.
- materialization: Strategy such as
table,view,incremental, orsnapshot. - sql_content: The raw SQL body of the model.
- dependencies: Upstream model references.
Sources: src/flext_dbt_oracle/models.py:23-54
Relation Handling
The OracleTableAdapter handles the normalization of schema and table names into fully qualified relation names (e.g., SCHEMA.TABLE).
SQL Features and Macros
The adapter supports advanced Oracle SQL features and implements required dbt macros.
Supported SQL Features
- Window Functions: Supported for complex analytical queries.
- CTE Support: Includes support for recursive Common Table Expressions.
- Merge Statements: Used for
incrementalmaterialization strategies. - Pagination: Implemented via
rownumpatterns.
Sources: rules.json:83-95
Required Macros
The following dbt macros are implemented to support core adapter functionality:
get_columns_in_relationlist_relations_without_cachingmake_temp_relationoracle_create_sequenceoracle_analyze_table
Sources: rules.json:130-143
Summary
The dbt Oracle Adapter serves as a specialized integration layer that bridges dbt-core with Oracle Database environments. By leveraging the FLEXT framework's typed settings and model namespaces, it provides a robust, validation-heavy approach to database transformation workflows, ensuring that connection management and SQL dialect specifics (like NLS settings and Oracle-specific data types) are handled natively.
Sources: src/flext_dbt_oracle/base.py:15-45, rules.json:1-10