Oracle WMS DBT Strategy

Relevant source files

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

• README.md
• dbt_project.yml
• dbt_project_migrated.yml
• profiles.yml
• src/flext_dbt_oracle_wms/_utilities/client.py
• src/flext_dbt_oracle_wms/models.py
• src/flext_dbt_oracle_wms/_simple_api_workflow.py
• tests/data_quality/test_cross_model_consistency.sql

The Oracle WMS DBT Strategy defines the technical framework for transforming raw Oracle WMS data into business-ready analytical and operational models. The project utilizes flext-dbt-oracle-wms to orchestrate data flows, starting from API extraction through the flext-oracle-wms domain client and ending with DBT transformations managed by flext-meltano.

The strategy follows a layered architecture (Staging, Marts, Metrics) to ensure data quality, referential integrity, and performance optimization across Oracle WMS entities such as allocations, inventory, and shipments.

Sources: README.md, dbt_project.yml:25-70, src/flext_dbt_oracle_wms/_utilities/client.py:126-155

Layered Model Architecture

The project implements a structured transformation hierarchy to separate raw data ingestion from business logic consumption. This structure is defined in dbt_project.yml and utilizes standard materialization strategies.

Data Layers

Layer	Materialization	Schema	Purpose
Staging	view	wms_staging	Raw data cleaning and initial transformation.
Marts	table	wms_marts	Business-ready entities (Operational and Analytical).
Metrics	view	wms_metrics	Enterprise KPIs and dashboard-ready summaries.
Snapshots	timestamp	wms_snapshots	Change tracking for WMS entities over time.

Sources: dbt_project.yml:28-66, dbt_project_migrated.yml:32-48

Architectural Data Flow

The flow follows a standard pipeline from source extraction to analytical output.

The FlextDbtOracleWmsClient orchestrates this by triggering the run_full_oracle_wms_to_dbt_pipeline method, which handles discovery, extraction, and the final DBT transformation call.

Sources: src/flext_dbt_oracle_wms/_utilities/client.py:61-104, dbt_project.yml:28-60

Workflow Orchestration

The strategy leverages a Python-based facade to manage the lifecycle of a transformation run. The FlextDbtOracleWmsWorkflow class resolves entity names, manages tracking info, and determines if models should be dynamically generated.

Execution Sequence

The following sequence represents a typical "Oracle WMS to DBT" workflow:

Core WMS Entities and Configuration

The project identifies several critical WMS entities that form the basis of the transformation pipeline. These are configured via DBT variables to allow for environment-specific overrides.

Primary Entities

The standard WMS entities processed include:

• allocation
• inventory
• item
• order_hdr / order_dtl
• shipment
• task / wave

Sources: dbt_project.yml:84-95, dbt_project_migrated.yml:64-65

Environment Profiles

profiles.yml defines how DBT connects to various targets, supporting local development with DuckDB and production workloads with PostgreSQL.

# Simplified dev output example
dev:
  type: duckdb
  path: "{{ env_var('DBT_DUCKDB_PATH', './flext_oracle_wms_dev.duckdb') }}"
  schema: "{{ env_var('DBT_SCHEMA', 'wms_raw') }}"
  threads: 4

Sources: profiles.yml:8-16

Data Quality and Testing Strategy

The strategy mandates strict data quality gates, categorized into staging-level validations and cross-model consistency tests.

Quality Tests

• Staging Tests: Validate single-table constraints like non-null critical fields (e.g., company_code, facility_code) and business rule adherence (e.g., allocated_quantity > 0).
• Cross-Model Consistency: Ensures referential integrity between tables, such as verifying that all allocations reference valid order details and that inventory allocations do not exceed available quantities.

Sources: tests/staging/test_stg_wms__allocation_quality.sql:5-11, tests/data_quality/test_cross_model_consistency.sql:5-25

Test Logic Flow

Transformation Logic and Modeling

Transformations are handled by the FlextDbtOracleWmsTransformer and ModelGenerator classes. The ModelGenerator can programmatically create staging models based on Oracle source metadata.

Model Metadata Structure

The DbtModel model defines the attributes for WMS transformations:

• name: The DBT model identifier.
• materialization: Strategy (e.g., view or table).
• sql_content: The Jinja-SQL logic.
• wms_business_rules: Specific WMS rules attached to the model metadata.

Sources: src/flext_dbt_oracle_wms/models.py:98-127

Programmatic Model Generation

def generate_wms_staging_models(self, oracle_sources: t.StrSequence) -> p.Result[Sequence[DbtModel]]:
    models = [
        DbtModel(
            name=f"stg_wms_{source}",
            materialization="view",
            sql_content=f"select * from {{{{ source('oracle_wms', '{source}') }}}}"
        )
        for source in oracle_sources
    ]

Sources: src/flext_dbt_oracle_wms/models.py:133-156

Conclusion

The Oracle WMS DBT Strategy ensures a robust, scalable, and testable data environment. By combining dynamic model generation via ModelGenerator with structured SQL testing for referential integrity, the system provides high-confidence data for operational and analytical reporting. The migration to flext-meltano patterns further centralizes common configurations, reducing duplication and standardizing hook execution across the project.

Sources: dbt_project_migrated.yml:4-8, src/flext_dbt_oracle_wms/_simple_api_workflow.py:100-110