Marts Layer (KPIs)
Relevant source files
The following files were used as context for generating this wiki page:
The Marts Layer (KPIs) within the flext-dbt-oracle-wms project represents the final stage of the data transformation pipeline, providing business-ready data models and enterprise-grade Key Performance Indicators (KPIs). This layer transforms raw staging data into structured formats suitable for operational reporting and analytical dashboards, focusing on warehouse efficiency, allocation accuracy, and inventory health.
The layer is architecturally segmented into three primary sub-modules: Operational Marts, Analytical Marts, and Metrics. These components ensure that data is not only consistent across the enterprise but also optimized for high-performance querying in various environments ranging from local development to production.
Sources: dbt_project.yml:32-55, README.md:10-12
Architecture and Data Flow
The Marts layer sits at the top of a 5-layer architecture, consuming data primarily from the Staging layer. The data flow follows a progression from raw WMS API extraction to refined business metrics.
The flow represents the transition from granular transactional records to aggregated enterprise metrics used for decision-making.
Sources: dbt_project_migrated.yml:1-12, tests/data_quality/test_cross_model_consistency.sql:50-70
Operational and Analytical Segmentation
The project distinguishes between real-time operational needs and long-term analytical reporting through specific model configurations:
| Sub-Module | Materialization | Purpose | Key Tags |
|---|---|---|---|
| Operational | Table | Real-time warehouse operations tracking | operational, real_time |
| Analytical | Table | Historical trend analysis and reporting | analytical, reporting |
| Metrics | View | High-level enterprise KPIs and dashboards | metrics, kpi, dashboard |
Sources: dbt_project.yml:41-55
Key KPI Definitions and Logic
The system tracks several critical KPIs primarily focused on the allocation lifecycle. These are validated through rigorous data quality tests to ensure accuracy.
Allocation and Efficiency Metrics
The core of the operational summary is found in the opr_wms__allocation_summary model. Key metrics include:
- Pack Efficiency Percent: Measures the efficiency of the packing process. Validated to remain between 0% and 100%.
- Average Pick Time (Hours): Tracks the time taken for picking operations. Validated to be non-negative and under 24 hours.
- Outstanding Quantity: Represents the remaining quantity to be processed. Validated to never be negative.
- Daily Total Allocations: A summary metric that must match the sum of status-level records for a given facility and date.
Sources: tests/marts/test_opr_wms__allocation_summary_kpis.sql:5-45
Cross-Model Consistency
To maintain integrity, the Marts layer enforces consistency between transactional staging records and analytical summaries.
The diagram illustrates the referential and aggregation requirements between staging and mart models.
Sources: tests/data_quality/test_cross_model_consistency.sql:5-65
Configuration and Deployment
The Marts layer is configured within the dbt_project.yml and its migrated counterpart to use specific schemas and materialization strategies based on the environment.
Schema Strategy
Marts are typically materialized as tables in the wms_marts schema, while metrics are materialized as views in the wms_metrics schema. This separation allows for performant access to pre-calculated tables while keeping KPI logic flexible in views.
Sources: dbt_project.yml:38-55, dbt_project_migrated.yml:45-48
Environment Profiles
The profiles.yml defines how these marts are deployed across different targets:
- Dev/CI: Uses DuckDB for fast local execution and testing.
- Staging/Prod: Uses PostgreSQL or Snowflake with higher thread counts (8-16) to handle enterprise data volumes.
Sources: profiles.yml:10-48
Data Quality Gates
The Marts layer includes specialized tests to ensure that business logic remains sound during transformation:
- Status Summation: The sum of
status_percentage_of_dailyfor all records in a facility on a given date must equal 100%. - Operational vs. KPI Match: Totals in the
met_wms__kpi_dashboardmust align with the daily totals inopr_wms__allocation_summarywithin a 0.01 tolerance. - Referential Integrity: All allocations in the marts must correspond to valid order details in the staging layer.
Sources: tests/marts/test_opr_wms__allocation_summary_kpis.sql:13-20, tests/data_quality/test_cross_model_consistency.sql:50-70
Conclusion
The Marts Layer (KPIs) serves as the definitive source of truth for Oracle WMS data within the FLEXT ecosystem. By segregating operational and analytical concerns and applying strict data quality validations on efficiency and allocation metrics, the system ensures that warehouse managers and business analysts have access to reliable, high-performance data models.