Examples & Usage
Relevant source files
The following files were used as context for generating this wiki page:
Introduction
The flext-dbt-oracle module provides a modern dbt adapter implementation specifically optimized for Oracle Database environments. It integrates flext-core and flext-db-oracle to offer typed model metadata, deterministic staging model generation, and robust connection management. This system allows developers to execute standard dbt workflows—such as seed, run, and test—while leveraging Oracle-specific features like sequences, analytical functions, and specialized data types (CLOB, BLOB, JSON).
Sources: examples/README.md:10-12, rules.json:4-10, src/flext_dbt_oracle/models.py:12-16
Core Workflow Implementation
Model Generation and Staging
The ModelGenerator class provides a deterministic way to create staging models from source tables. This is a primary entry point for automating dbt model creation based on Oracle source metadata.
The generator creates DbtOracle.Model instances that include target table names, SQL bodies, and materialization strategies.
Sources: src/flext_dbt_oracle/models.py:53-73
Connection and Data Pipeline
The DbtOracle.Client within the utilities namespace manages the lifecycle of data extraction and pipeline execution. It provides methods for table discovery and connection testing.
Configuration and Settings
Usage of the adapter requires specific configuration parameters, often handled through FlextDbtOracleSettings. These settings map environmental variables to the internal connection logic.
Required Connection Parameters
| Parameter | Type | Description | Default / Source |
|---|---|---|---|
host | str | Oracle database host | ORACLE_HOST |
port | int | Connection port | ORACLE_PORT (1521) |
username | str | DB Username | ORACLE_USERNAME |
password | SecretStr | DB Password | ORACLE_PASSWORD |
service_name | str | Oracle Service Name | ORACLE_SERVICE_NAME (XEPDB1) |
schema | str | Target Schema | ORACLE_SCHEMA (HR) |
Sources: rules.json:115-122, tests/unit/_config_parts/connection.py:16-25
Data Type Mappings
The adapter provides explicit mappings between dbt logical types and Oracle physical types to ensure schema consistency.
| dbt Type | Oracle Type |
|---|---|
string | VARCHAR2(4000) |
text | CLOB |
integer | NUMBER(38,0) |
float | BINARY_DOUBLE |
boolean | NUMBER(1,0) |
timestamptz | TIMESTAMP WITH TIME ZONE |
Sources: rules.json:44-60
Usage Examples
Initializing the Adapter Client
To interact with the database, a client must be initialized with a settings object.
from flext_dbt_oracle.settings import FlextDbtOracleSettings
from flext_dbt_oracle.utilities import u
settings = FlextDbtOracleSettings(
oracle_host="localhost",
oracle_username="admin",
oracle_password="password",
oracle_service_name="ORCLPDB1"
)
client = u.DbtOracle.Client(settings=settings)
status = client.test_connection()
# Result: {"status": "connected", "host": "localhost", "database": "XEPDB1"}
Sources: src/flext_dbt_oracle/utilities.py:22-30, tests/unit/test_basic.py:19-24
Generating Staging Models
The ModelGenerator can be used to programmatically define staging models for a list of tables.
from flext_dbt_oracle.models import m
generator = m.create_generator()
tables = ["employees", "departments"]
staging_models = generator.generate_staging_models(source_tables=tables)
for model in staging_models:
print(f"Model: {model.name}, SQL: {model.sql_content}")
# Output Example: Model: stg_oracle_employees, SQL: select * from {{ source('oracle', 'employees') }}
Sources: src/flext_dbt_oracle/models.py:61-73, src/flext_dbt_oracle/models.py:183-188
Connection String Handling
The system automatically generates DSN strings based on whether a Service Name or SID is provided.
# Service Name example
settings = FlextDbtOracleSettings(..., oracle_service_name="XEPDB1")
# Result: oracle://user:***@host:1521/XEPDB1
# SID example
settings = FlextDbtOracleSettings(..., sid="XE")
# Result: oracle://user:***@host:1521:XE
Sources: src/flext_dbt_oracle/models.py:117-130, tests/unit/_config_parts/connection.py:35-42
Summary
The Examples & Usage of flext-dbt-oracle center around a typed, model-driven approach to Oracle data transformations. By utilizing the ModelGenerator for automation and the DbtOracle.Client for lifecycle management, developers can ensure that dbt models remain consistent with Oracle's specific SQL dialect and connectivity requirements. Governance and testing utilities further ensure that configurations, such as pool sizes and materialization strategies, are validated before runtime execution.
Sources: rules.json:20-40, src/flext_dbt_oracle/models.py:18-20, tests/unit/test_basic.py:32-35