Advanced Query Filtering & Ordering

Relevant source files

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

• examples/configs/test_config_filters_advanced.json
• examples/configs/test_config_filters_complex.json
• examples/configs/test_config_ordering.json
• src/flext_tap_oracle_wms/utilities.py
• src/flext_tap_oracle_wms/streams.py
• src/flext_tap_oracle_wms/settings.py
• meltano.yml
• tests/integration/test_functional.py

The Advanced Query Filtering & Ordering system in the FLEXT Tap Oracle WMS module provides a flexible mechanism to refine data extraction requests sent to the Oracle WMS API. This system allows developers and users to define complex criteria for selecting records and specifying the sort order of the resulting data sets. By leveraging structured configuration options, the tap can generate precise API request parameters that optimize data transfer and ensure only relevant records are processed.

This module supports both global filters applicable across all entities and entity-specific overrides for granular control. The filtering logic handles various data types, including timestamps for incremental replication and specific field values for targeted data sets.

Architecture and Data Flow

The filtering and ordering logic is integrated into the FlextTapOracleWmsStream class, which handles the construction of request parameters during the data extraction process. Configuration is managed via Pydantic models in settings.py, which are populated from JSON configuration files or environment variables.

Request Parameter Construction

When a stream prepares to fetch records, it invokes internal methods to build operation keywords (kwargs). This includes calculating pagination offsets, applying replication keys, and injecting filter/ordering strings derived from the configuration.

The diagram shows how configuration and state flow into the stream's parameter builder to produce the final API request.
Sources: src/flext_tap_oracle_wms/streams.py:165-185, src/flext_tap_oracle_wms/settings.py

Configuration Elements

Filtering and ordering are primarily controlled through three configuration keys defined in the project settings.

Configuration Key	Type	Description
global_filters	Object	Filters applied to every stream unless overridden.
entity_filters	Object	Entity-specific filter definitions.
entity_ordering	Object	Entity-specific sort order definitions.

Sources: meltano.yml:148-158, src/flext_tap_oracle_wms/settings.py

Filtering Logic

Filters support standard comparison operators. For incremental streams, the system automatically appends timestamp filters (e.g., >= or <) based on the stream's replication key and current context.

• Timestamp Filtering: Used to extract records modified after a specific last_updated_ts.
• Complex Filters: Multiple conditions can be combined, such as filtering for specific statuses or facility codes.

{
  "entity_filters": {
    "inventory": "item_id__name:ITEM001",
    "orders": "status:shipped"
  }
}

Sources: examples/configs/test_config_filters_complex.json, tests/integration/test_functional.py:230-245

Ordering Logic

Ordering is specified per entity and dictates the sequence of records returned by the Oracle WMS API. This is critical for consistent pagination and incremental syncs.

This sequence illustrates the injection of ordering parameters into the HTTP request flow.
Sources: src/flext_tap_oracle_wms/streams.py:165-185, examples/configs/test_config_ordering.json

Implementation Details

Parameter Mapping

The FlextTapOracleWmsStream utilizes the configuration to populate the filter and ordering keys in the request parameters.

• Filter Application: If a replication_key_value exists in the context, it is formatted as a comparison filter (e.g., last_updated_ts__gte:2024-01-01T00:00:00Z).
• Pagination constraints: The limit parameter is restricted to a maximum of 1250 as required by the Oracle WMS API.

Sources: src/flext_tap_oracle_wms/streams.py:170-180, tests/integration/test_functional.py:205-215

Utilities and Validation

The system includes utilities for validating business rules related to filters. For example, it ensures that entities are not simultaneously included and excluded, and that date ranges for filters are logically sound (start_date <= end_date).

Sources: src/flext_tap_oracle_wms/utilities.py, src/flext_tap_oracle_wms/settings.py:120-140

Conclusion

The Advanced Query Filtering & Ordering module is a core component that enables efficient and targeted data extraction. By providing structured configuration for entity_filters and entity_ordering, the system allows the tap to handle large datasets by offloading selection and sorting logic to the Oracle WMS API. This minimizes local processing and network overhead, ensuring high-performance integration within the FLEXT ecosystem.