Schema Management & DDL
Relevant source files
The following files were used as context for generating this wiki page:
Schema Management and Data Definition Language (DDL) operations in flext-target-oracle are responsible for translating Singer-standard JSON schemas into valid Oracle database structures. This system ensures that target tables, columns, and indexes are correctly provisioned before data loading begins, supporting both traditional relational "flattened" structures and modern JSON storage modes.
The core logic resides within the FlextTargetOracleLoader, which interacts with the flext-db-oracle API to execute DDL statements. The system handles table existence checks, column type mapping, audit column injection (SDC columns), and custom index creation based on project configuration.
Sources: src/flext_target_oracle/_utilities/loader.py:1-25, src/flext_target_oracle/_utilities/client.py:207-220
Schema Processing Flow
When a Singer SCHEMA message is received, the target coordinates a multi-step process to prepare the Oracle environment. This includes mapping JSON types to Oracle types, applying user-defined column mappings, and determining the appropriate storage strategy (JSON vs. Flattened).
The diagram illustrates the lifecycle of a schema message, from initial detection to the final caching of Oracle-specific column metadata.
Sources: src/flext_target_oracle/_utilities/loader.py:340-385, src/flext_target_oracle/_utilities/client.py:207-220
Data Type Mapping & Column Generation
The system converts Singer schema properties into m.DbOracle.Column objects. It utilizes the flext-db-oracle service to perform primary type mapping while allowing for local overrides such as ignored columns and renamed fields.
Storage Modes
The target supports three primary storage strategies defined in the configuration:
- Flattened: Standard relational approach where nested JSON objects are flattened into individual columns.
- JSON: Nested data is stored in a single
CLOBcolumn (defaulted toJSON_DATA). - Hybrid: A combination where specific keys are extracted to columns while the full record is preserved in a JSON blob.
Sources: src/flext_target_oracle/_utilities/loader.py:126-150, tests/integration/test_oracle.py:149-158
Standard Audit Columns
Every table created by the loader includes System Data Connector (SDC) columns for metadata tracking:
| Column Name | Oracle Data Type | Purpose |
|---|---|---|
_SDC_EXTRACTED_AT | TIMESTAMP | The time the record was extracted from the source. |
_SDC_LOADED_AT | TIMESTAMP | The time the record was inserted into Oracle (defaults to CURRENT_TIMESTAMP). |
Sources: src/flext_target_oracle/_utilities/loader.py:214-228
Column Ordering Rules
To ensure predictable table structures, the loader applies a prioritized ordering mechanism for columns. By default, columns are organized alphabetically within functional groups.
| Priority | Group | Description |
|---|---|---|
| 1 | primary_keys | Columns defined in Singer key_properties. |
| 2 | regular_columns | Standard data fields from the schema. |
| 3 | audit_columns | Timestamp fields ending in _AT (excluding SDC). |
| 4 | sdc_columns | Internal metadata columns prefixed with _SDC_. |
Sources: src/flext_target_oracle/_utilities/loader.py:230-270, tests/integration/test_oracle.py:204-215
Index Management
Beyond basic table creation, the target allows for the definition of custom indexes via configuration. These are applied immediately after the table is created.
Index names are truncated to 30 characters to comply with Oracle's standard identifier limits.
Sources: src/flext_target_oracle/_utilities/loader.py:408-435, tests/artifacts/oracle-init/01-create-test-schema.sql:103-112
DDL Implementation Details
The FlextTargetOracleLoader encapsulates the DDL logic, ensuring that no raw SQL is generated within the target itself, adhering to the project's design principles.
Key DDL Methods
ensure_table_exists: Coordinates the check-and-create logic for stream tables._loader_columns: Builds the cached metadata for a stream, including field renaming._create_custom_indexes: Iterates through thecustom_indexesconfiguration to apply performance optimizations._prepare_existing_table: Handles thetruncate_before_loadlogic if enabled in settings.
Sources: src/flext_target_oracle/_utilities/loader.py:84-110, src/flext_target_oracle/_utilities/loader.py:387-406
Configuration Example
The following YAML snippet demonstrates how DDL behavior is controlled via Meltano configuration:
settings:
- name: default_target_schema
kind: string
default: MELTANO_DATA
- name: load_method
kind: options
options:
- value: INSERT
- value: MERGE
- name: column_ordering
kind: string
default: alphabetical
Sources: examples/meltano_integration/meltano.yml:38-63
Error Handling in Schema Operations
DDL operations are wrapped in specific exception handlers to provide actionable feedback during failures.
- SchemaError: Raised when the Singer schema is invalid or cannot be mapped to Oracle types.
- OracleConnectionError: Raised if the loader cannot connect to the database to verify or create schemas.
- ValidationError: Raised if custom index definitions or column mappings violate Oracle identifier rules.
Sources: src/flext_target_oracle/_utilities/errors.py:126-150, src/flext_target_oracle/_utilities/loader.py:347-350
Schema Management in flext-target-oracle provides a robust bridge between dynamic Singer schemas and the rigid requirements of Oracle DDL. By centralizing logic in the FlextTargetOracleLoader and leveraging the flext-db-oracle API, the system maintains strict type safety and auditability while offering flexibility through JSON storage and custom indexing.