Singer Data Flattening

Relevant source files

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

• src/flext_oracle_wms/flattening.py
• tests/unit/oracle_wms_complete_discovery.py
• tests/unit/complete_mock_pipeline.py
• tests/unit/oracle_wms_focused_discovery.py
• tests/unit/oracle_wms_optimized_discovery.py
• src/README.md
• src/flext_oracle_wms/README.md

Singer Data Flattening is a core architectural component of the flext-oracle-wms library designed to transform complex, nested Oracle WMS Cloud JSON structures into flat relational formats compatible with the Singer protocol. This ensures that data extracted from Oracle WMS can be seamlessly integrated into modern data pipelines using tools like Meltano.

The system utilizes dynamic schema discovery and type mapping to handle the extensive variety of Oracle WMS entities (over 320 entities), providing automated schema generation that supports incremental and full-table replication methods.

Sources: src/README.md, src/flext_oracle_wms/README.md, tests/unit/complete_mock_pipeline.py:126-150

Architecture and Data Flow

The flattening process is integrated into the discovery and extraction pipeline. It operates by analyzing sample data from Oracle WMS entities and mapping Python/Oracle types to Singer-compliant JSON Schema types.

Schema Generation and Type Mapping

The flattening engine dynamically generates Singer schemas by inspecting fields and their corresponding values. This involves mapping internal Oracle types to standardized Singer types (string, integer, number, boolean, object, array).

Type Mapping Strategy

The system uses a strategy pattern to infer types based on both field name suffixes and actual value inspection.

Python/Oracle Type	Singer Type	Indicators/Logic
int	["integer", "null"]	Field name ends in id or _id.
float	["number", "null"]	Field name ends in _qty, _weight_kg, _volume_liters.
str (ISO8601)	["string", "null"]	Contains "T" and ":" with timezone offsets. Format: date-time.
str (Date)	["string", "null"]	Contains two "-" without "T". Format: date.
dict	["object", "null"]	Nested structures representing sub-entities.

Sources: tests/unit/complete_mock_pipeline.py:302-350, tests/unit/oracle_wms_complete_discovery.py:408-445

Implementation Example: Type Inference

def _map_to_singer_type(self, python_type, sample_value, field_name):
    if isinstance(sample_value, bool):
        return {"type": ["boolean", "null"]}
    if isinstance(sample_value, int):
        return {"type": ["integer", "null"]}
    if isinstance(sample_value, str):
        if self._is_datetime_field(field_name, sample_value):
            return {"type": ["string", "null"], "format": "date-time"}
        return {"type": ["string", "null"]}
    return {"type": ["string", "null"]}

Sources: tests/unit/oracle_wms_complete_discovery.py:408-428

Metadata Enrichment

To support Singer protocol requirements, the flattening process enriches records with specific _sdc (Singer Data Capture) metadata fields. These fields are essential for tracking and auditing data as it moves through the pipeline.

Metadata Field	Type	Description
_sdc_extracted_at	date-time	The UTC timestamp when the record was fetched from Oracle WMS.
_sdc_entity	string	The original name of the Oracle WMS entity (e.g., inventory).
_sdc_sequence	integer	A sequence number for ordering records within a batch.
_sdc_record_hash	string	A unique hash used for deduplication and change tracking.

Sources: tests/unit/complete_mock_pipeline.py:365-375, tests/unit/oracle_wms_optimized_discovery.py:228-245

Key Components

Discovery Clients

Different discovery modes utilize flattening to prepare schemas for varied use cases:

• Complete Discovery: Performs 100% automatic discovery of all 22+ API categories without fallbacks.
• Focused Discovery: Prioritizes high-value entities (e.g., item, inventory, order_hdr) to generate schemas quickly.
• Optimized Discovery: Uses parallel batch processing to analyze entity structures at scale.

Sources: tests/unit/oracle_wms_complete_discovery.py:15-30, tests/unit/oracle_wms_focused_discovery.py:31-60, tests/unit/oracle_wms_optimized_discovery.py:25-55

Primary Functions

• generate_singer_schemas_with_flattening(): Orchestrates the generation of schemas based on Oracle metadata. Sources: tests/unit/oracle_wms_complete_discovery.py:377-385
• _determine_key_properties(): Identifies primary keys (e.g., id, code, order_nbr) for Singer stream catalogs. Sources: tests/unit/oracle_wms_optimized_discovery.py:326-350
• _create_singer_catalog(): Wraps flattened schemas into a Singer-compliant catalog file containing stream metadata and replication methods. Sources: tests/unit/oracle_wms_focused_discovery.py:321-352

Summary

Singer Data Flattening provides the translation layer between Oracle WMS Cloud's hierarchical data model and the relational expectations of downstream data warehouses. By automating type inference and metadata enrichment, it enables high-fidelity data synchronization with minimal manual configuration.