Dynamic Schema Resolution

Relevant source files

The following files were used as context for generating this wiki page:

• src/flext_oracle_wms/discovery.py
• src/flext_oracle_wms/dynamic.py
• tests/unit/test_schema_dynamic.py
• tests/unit/oracle_wms_focused_discovery.py
• tests/unit/oracle_wms_optimized_discovery.py
• tests/unit/oracle_wms_complete_discovery.py
• tests/unit/complete_mock_pipeline.py

Dynamic Schema Resolution is a core feature of the flext-oracle-wms library designed to automatically discover, analyze, and transform Oracle WMS Cloud entity structures into standardized data formats, primarily the Singer protocol. This system eliminates the need for manual schema definitions by introspecting live Oracle WMS environments to determine field names, data types, and primary keys.

The process involves multi-stage discovery, starting from entity identification to deep data analysis for type inference. By leveraging real data samples, the system can distinguish between simple strings and complex formats like timestamps or URLs, ensuring high-fidelity data integration.

Sources: src/flext_oracle_wms/README.md, tests/unit/oracle_wms_complete_discovery.py:11-17

Architecture and Discovery Flow

The schema resolution architecture follows a sequence of discovery, analysis, and generation. It utilizes a FlextOracleWmsUtilitiesClient to interact with the Oracle WMS REST API (LGF API v10) to fetch both entity lists and sample records.

The system prioritizes entities with existing data to perform "deep inference." If an entity is empty, it falls back to basic structure discovery based on available field keys.
Sources: tests/unit/oracle_wms_focused_discovery.py:48-93, tests/unit/oracle_wms_optimized_discovery.py:64-98

Type Inference Engine

The core of dynamic resolution is the _oracle_to_singer_type logic (and its variants). It maps Python types and Oracle-specific field patterns to JSON Schema types used in the Singer protocol.

Logic for Type Mapping

The system uses a combination of value inspection and field name pattern matching:

1. Boolean: Identified via Python bool types.
2. Integer: Identified via Python int. Fields ending in _id or named id are explicitly categorized as Oracle ID fields.
3. Number: Identified via Python float. Used for quantities and weights.
4. String: The default type. Advanced logic checks for specific formats:
   • Date-Time: Matches patterns like _ts, timestamp, _at, or strings containing "T" and ":".
   • Date: Matches _date or _dt suffixes when the value lacks time components.
   • URI: Fields named url starting with "http".
5. Object/Array: Identified via Python dict or list for nested structures.

Sources: tests/unit/oracle_wms_focused_discovery.py:186-224, tests/unit/oracle_wms_optimized_discovery.py:207-251

Field Pattern Indicators

Indicator	Inferred Type	Description
_id, id	integer	Primary or Foreign Key
_ts, _at, timestamp	string (date-time)	Temporal markers
_code, _nbr, _num	string	Business identifiers/Codes
_qty, _weight, _vol	number	Numeric measurements

Sources: tests/unit/oracle_wms_focused_discovery.py:226-243, tests/unit/complete_mock_pipeline.py:195-212

Key Property Identification

Dynamic resolution automatically determines key_properties (Primary Keys) for entities to ensure idempotent loading in downstream targets.

Standard mappings include:

• Item: item_code, sku
• Order: order_nbr
• LPN: lpn_nbr
• Facility: facility_code

Sources: tests/unit/oracle_wms_focused_discovery.py:253-278, tests/unit/oracle_wms_optimized_discovery.py:277-302

Metadata and Schema Structure

The generated schema includes standard Oracle fields and injected FLEXT metadata for auditability (SDC - Singer Data Capture columns).

SDC Metadata Columns

Field	Type	Description
_sdc_extracted_at	date-time	Extraction timestamp
_sdc_entity	string	Source entity name
_sdc_record_hash	string	Deterministic hash for change detection
_sdc_sequence	integer	Record sequence in batch

Sources: tests/unit/oracle_wms_focused_discovery.py:173-178, tests/unit/complete_mock_pipeline.py:225-230

Example Schema Snippet

{
  "type": "object",
  "properties": {
    "item_code": { "type": ["string", "null"], "description": "Oracle WMS item code" },
    "weight_kg": { "type": ["number", "null"] },
    "create_ts": { "type": ["string", "null"], "format": "date-time" },
    "_sdc_extracted_at": { "type": "string", "format": "date-time" }
  },
  "key_properties": ["item_code"],
  "oracle_wms_entity": "item"
}

Sources: tests/unit/oracle_wms_focused_discovery.py:180-184, tests/unit/complete_mock_pipeline.py:108-115

Catalog Generation and Replication

Once schemas are resolved, they are aggregated into a Singer Catalog. This catalog defines how each stream should be replicated.

Replication Methods

• FULL_TABLE: Used for entities without clear modification timestamps.
• INCREMENTAL: Automatically selected if the entity contains mod_ts, create_ts, or updated_at fields. The mod_ts field is typically designated as the replication-key.

Sources: tests/unit/complete_mock_pipeline.py:247-268, tests/unit/oracle_wms_optimized_discovery.py:313-338

Summary

Dynamic Schema Resolution in the flext-oracle-wms project provides a robust, automated mechanism for translating complex Oracle WMS Cloud data structures into actionable integration schemas. By combining entity discovery, sample-based type inference, and intelligent key identification, the system ensures that data pipelines remain resilient to upstream changes in the Oracle WMS environment without requiring manual configuration updates.