Home
Relevant source files
The following files were used as context for generating this wiki page:
The flext-dbt-oracle-wms project provides a specialized framework for Oracle Warehouse Management System (WMS) data transformation using DBT. It serves as an integration layer that orchestrates the flow of data from Oracle WMS APIs into structured data models suitable for analytical and operational use cases. The system utilizes flext-meltano for pipeline execution and flext-oracle-wms for direct domain interactions.
Sources: README.md:1-10, dbt_project.yml:30-40, src/flext_dbt_oracle_wms/_utilities/client.py:19-30
System Architecture
The architecture follows a multi-layered approach to handle data from extraction to final business-ready marts. It is built upon the flext ecosystem, inheriting patterns from flext-core and flext-meltano.
High-Level Workflow
The core workflow involves discovering entities in Oracle WMS, extracting data via API clients, validating the records against business rules, and finally applying DBT transformations.
The workflow ensures data integrity by performing validation before the transformation phase begins.
Sources: src/flext_dbt_oracle_wms/_utilities/client.py:53-85, src/flext_dbt_oracle_wms/_simple_api_workflow.py:27-40
Core Components
| Component | Responsibility |
|---|---|
| Simple API Facade | Provides a unified entry point for running workflows and validating connections. |
| FlextDbtOracleWmsClient | Orchestrates WMS extraction and DBT execution via Meltano. |
| DBT Project | Defines the SQL models, staging layers, and materialization strategies. |
| Service Helpers | Handles workflow tracking, logging, and performance recommendations. |
Sources: src/flext_dbt_oracle_wms/_simple_api_workflow.py:16-25, src/flext_dbt_oracle_wms/_utilities/client.py:22-35, src/flext_dbt_oracle_wms/utilities.py:46-55
Data Modeling and Transformation
The transformation logic is organized into a five-layer architecture (migrated version) or a standard enterprise structure.
DBT Layering
The project categorizes models into specific schemas and materialization types:
- Staging (
wms_staging): Raw data cleanup, usually materialized as views. - Marts (
wms_marts): Business-ready data, including operational and analytical models. - Metrics (
wms_metrics): KPI and dashboard-focused views. - Snapshots (
wms_snapshots): Change tracking using timestamp-based strategies.
Sources: dbt_project.yml:34-75, dbt_project_migrated.yml:25-45
Entity Relationships
The project validates cross-model consistency for critical WMS entities.
Pipeline Orchestration
The FlextDbtOracleWmsClient manages the interaction between the Oracle WMS domain client and the DBT runner.
Execution Sequence
The full pipeline execution follows a synchronous sequence:
Configuration and Environments
The system uses profiles.yml and environment variables to manage different targets.
| Target | Database Type | Primary Use |
|---|---|---|
dev | DuckDB | Local development and fast iterations. |
staging | PostgreSQL | Shared environment for validation. |
prod | PostgreSQL/Snowflake | Production workloads. |
ci | DuckDB (:memory:) | Automated testing in CI/CD pipelines. |
Sources: profiles.yml:9-45
Key Variables
DBT variables configured in dbt_project.yml control behavior such as:
wms_entities: List of entities to process (allocation, inventory, orders, etc.).freshness_warn_after: Thresholds for data staleness.enable_incremental_models: Performance toggle for large datasets.
Sources: dbt_project.yml:79-115
Quality Gates and Validation
The project implements several layers of data quality checks:
- Business Rule Validation: Checked during the extraction phase via the
FlextDbtOracleWmsClient. - Schema Tests: Enforced during the DBT run (e.g.,
store_failures: true). - Cross-Model Consistency: SQL tests that ensure referential integrity between allocations, inventory, and orders.
Sources: src/flext_dbt_oracle_wms/_utilities/client.py:151-170, tests/data_quality/test_cross_model_consistency.sql:1-50, tests/staging/test_stg_wms__allocation_business_rules.sql:1-15
Conclusion
The flext-dbt-oracle-wms project provides a robust, typed, and governed environment for transforming complex Oracle WMS data. By leveraging the flext ecosystem and a structured DBT architecture, it ensures that warehouse data is consistently validated, modeled, and ready for enterprise reporting and operational analysis.