Examples & Real-World Usage
Relevant source files
The following files were used as context for generating this wiki page:
The flext-dbt-oracle-wms package provides a structured framework for transforming Oracle Warehouse Management System (WMS) data using dbt. It bridges the gap between raw API data extraction and business-ready analytical models by orchestrating workflows that include discovery, extraction, validation, and dbt transformation.
Real-world usage of this system involves two primary interfaces: a Python Simple API (Facade) for programmatic integration and a Command Line Interface (CLI) for operational tasks. These interfaces interact with a core client that manages the lifecycle of WMS data processing.
Sources: README.md:1-15, src/flext_dbt_oracle_wms/_utilities/client.py:18-20
End-to-End Pipeline Execution
The primary use case is the execution of a full data pipeline. This process automates the transition of data from the Oracle WMS API through to dbt models. The system uses a FlextDbtOracleWmsClient to orchestrate these steps sequentially.
Pipeline Data Flow
The following diagram illustrates the sequence of operations within a standard pipeline run:
The pipeline ensures that data is only passed to the transformation layer if it passes validation against configured required fields.
Sources: src/flext_dbt_oracle_wms/_utilities/client.py:61-100, tests/unit/test_simple_api.py:126-140
CLI Operational Usage
The CLI service (FlextDbtOracleWmsCliService) allows operators to trigger specific parts of the workflow or the entire pipeline manually.
| Command | Argument | Description | Default Behavior |
|---|---|---|---|
discover | None | Lists available entities from the Oracle WMS instance. | N/A |
extract | entity_name | Extracts raw data for a specific entity and prints/saves it. | Defaults to inventory if no entity provided. |
pipeline | None | Runs the full end-to-end pipeline (Discover -> Extract -> Transform). | Runs all enabled WMS entities. |
The CLI returns exit code 0 on success and 1 on failure, allowing for integration into shell scripts or cron jobs.
Sources: tests/unit/test_cli.py:100-145, src/flext_dbt_oracle_wms/cli.py
Programmatic Integration (Simple API)
Developers can use the FlextDbtOracleWms facade for high-level operations without managing the underlying Meltano or dbt complexities directly.
Inventory Workflow Example
A common real-world scenario is extracting inventory data to generate processing recommendations.
from flext_dbt_oracle_wms.simple_api import FlextDbtOracleWms
from flext_dbt_oracle_wms.settings import FlextDbtOracleWmsSettings
settings = FlextDbtOracleWmsSettings()
service = FlextDbtOracleWms(settings=settings)
# Execute specific workflow
result = service.run_oracle_wms_to_dbt_workflow(
inventory_items=["item-001", "item-002"],
run_transformations=True
)
if result.success:
print(f"Pipeline processed {result.value['total_records']} records")
Sources: tests/unit/test_simple_api.py:126-150, src/flext_dbt_oracle_wms/utilities.py:33-50
Data Quality and Consistency Auditing
The system includes SQL-based tests designed to run within dbt to ensure real-world business logic is maintained across different WMS entities.
Cross-Model Validation
The project defines tests to catch common WMS data discrepancies:
- Orphaned Allocations: Ensuring all records in
stg_wms__allocationmap back to a validstg_wms__order_dtl. - Over-Allocation: Verifying that
allocated_quantitydoes not exceedavailable_quantityin the inventory models. - KPI Alignment: Comparing high-level dashboard totals in
met_wms__kpi_dashboardagainst granular operational summaries inopr_wms__allocation_summary.
Configuration and Environments
Usage varies across environments (Dev, Staging, Prod) as defined in profiles.yml. The project supports multiple database targets for different stages of the data lifecycle.
| Environment | Database Type | Primary Purpose |
|---|---|---|
| dev | DuckDB | Local development and fast iterative testing. |
| staging | PostgreSQL | Integration testing with shared data sets. |
| prod | PostgreSQL/Snowflake | Production-grade analytical reporting. |
| ci | DuckDB (Memory) | Ephemeral testing during CI/CD pipelines. |
Sources: profiles.yml:10-48, dbt_project.yml:15-40
Conclusion
The flext-dbt-oracle-wms package provides a robust mechanism for managing Oracle WMS data transformations. By leveraging a centralized client for orchestration and offering both CLI and programmatic interfaces, it supports diverse operational needs from automated nightly pipelines to manual data exploration and rigorous quality auditing.