Staging Layer Operations
Relevant source files
The following files were used as context for generating this wiki page:
Staging Layer Operations within the flext-dbt-oracle-wms project represent the initial transformation phase where raw Oracle WMS data is prepared for enterprise analytics. This layer is responsible for taking data extracted from the Oracle WMS API and converting it into structured DBT models while enforcing strict data quality and business rule validations.
The staging layer is architected to utilize views as the default materialization, ensuring that transformations are applied dynamically on top of the raw source data. This phase serves as a critical gatekeeper, filtering out invalid records and ensuring that core entities like allocations, orders, and inventory meet enterprise standards before they propagate to the marts layer.
Sources: dbt_project.yml:32-35, src/flext_dbt_oracle_wms/_utilities/client.py:91-118
Architecture and Data Flow
The staging operation follows a structured pipeline managed by the FlextDbtOracleWmsClient. This pipeline orchestrates the discovery of WMS entities, extraction of raw records, validation against business requirements, and the final generation of DBT staging models.
Transformation Pipeline
The following diagram illustrates the sequence of operations from the initial API extraction to the creation of staging models.
The pipeline ensures that every record is validated against required_fields_per_entity before it is processed by the transformer into a DBT-compatible format.
Sources: src/flext_dbt_oracle_wms/_utilities/client.py:68-100, src/flext_dbt_oracle_wms/models.py:58-75
Staging Model Configuration
Staging models are logically separated within the DBT project structure. They are configured with specific schemas and tags to allow for targeted execution and monitoring.
| Configuration Item | Value | Description |
|---|---|---|
| Materialization | view | Default strategy for staging models to ensure data freshness. |
| Schema | wms_staging | The target database schema for staging objects. |
| Tags | ["staging", "oracle_wms"] | Labels used for selecting models during dbt runs. |
| Source Schema | wms_raw | The default schema where raw extracted data resides. |
Sources: dbt_project.yml:32-35, dbt_project.yml:51-52
Data Quality and Business Rules
Operations in the staging layer are heavily focused on maintaining data integrity through two types of automated checks: schema-level quality tests and logic-based business rules.
Critical Business Rule Validations
Staging operations enforce specific logic on the stg_wms__allocation model to prevent downstream errors.
- Quantity Checks: Allocated quantity cannot be negative, and packed quantity must not exceed allocated quantity.
- Temporal Integrity: Picked timestamps cannot be in the future, and modified timestamps must be greater than or equal to created timestamps.
- Business Date Range: Records must fall within a reasonable range, typically from the last 2 years to tomorrow.
Sources: tests/staging/test_stg_wms__allocation_business_rules.sql:5-30
Data Quality (DQ) Status
Models include a data_quality_status field (typically set to 'VALID') used to filter records during cross-model joins. This ensures that orphaned records—such as allocations without matching order details—are identified and excluded from reliable reporting.
Citations: tests/data_quality/test_cross_model_consistency.sql:5-18, tests/staging/test_stg_wms__allocation_quality.sql:5-10
Implementation Details
The staging layer is implemented using a combination of Python-based Pydantic models for initial validation and SQL-based DBT models for final materialization.
Model Generation Logic
The ModelGenerator class dynamically creates staging models based on available Oracle WMS sources. Each generated model defaults to a VIEW materialization and selects all fields from the corresponding source in the oracle_wms namespace.
Sources: src/flext_dbt_oracle_wms/models.py:157-175
Validation Logic
The FlextDbtOracleWmsClient utilizes the validate_oracle_wms_data method to enforce entity requirements before data reaches the database. If a record is missing required fields defined in settings, the entire extraction batch for that entity is marked as a failure.
Sources: src/flext_dbt_oracle_wms/_utilities/client.py:146-168
Summary
Staging Layer Operations serve as the foundational transformation tier for Oracle WMS data. By combining dynamic view materialization with rigorous Python-based validation and SQL business rule testing, the system ensures that only high-quality, consistent data is made available to the higher-level Marts and Metrics layers. This approach minimizes the risk of reporting errors caused by malformed API responses or inconsistent business logic in the source system.