DBT Configuration & Dependencies

Relevant source files

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

• dbt_project.yml
• dbt_project_migrated.yml
• profiles.yml
• packages.yml
• pyproject.toml
• src/flext_dbt_oracle_wms/_utilities/client.py

Introduction

The flext-dbt-oracle-wms project implements a structured DBT (Data Build Tool) environment designed to transform raw Oracle WMS API data into business-ready analytical models. It serves as an integration layer that connects WMS data extracted via Singer taps to a downstream data warehouse using a multi-layered architecture. The system is designed to handle enterprise-level warehouse management entities such as allocations, inventory, and shipments.

Sources: README.md:9-11, dbt_project.yml:1-10

This configuration establishes a standardized transformation pipeline that leverages flext-meltano patterns to reduce duplication and ensure consistency across the FLEXT ecosystem. It manages everything from connection profiles for multiple environments (Dev, Staging, Prod, CI) to complex inter-package dependencies and SQL-based data quality gates.

Sources: dbt_project_migrated.yml:1-8, profiles.yml:6-48

Project Architecture & Layers

The project follows a modular 5-layer architecture for data transformation. Each layer is configured with specific materialization strategies and target schemas to optimize for both performance and data accessibility.

Transformation Layers

The models are organized into specific functional directories, as defined in the project configuration:

Layer	Materialization	Schema	Description
Staging	view	wms_staging	Raw data cleanup and initial transformation.
Marts	table	wms_marts	Business-ready entities (Operational and Analytical).
Metrics	view	wms_metrics	KPI and dashboard-ready reporting values.
Snapshots	timestamp	wms_snapshots	Type-2 Slowly Changing Dimensions (SCD) for history.
Seeds	table	wms_reference	Static reference data and CSV-based lookups.

Sources: dbt_project.yml:25-58, dbt_project.yml:85-91, dbt_project_migrated.yml:33-41

Data Flow Diagram

The following diagram illustrates the flow of data from the raw Oracle WMS source through the DBT transformation layers defined in the project configuration.

Profiles & Environment Configuration

Connectivity is managed through profiles.yml, which supports various database backends including DuckDB (for local development and CI) and PostgreSQL (for staging and production).

Environment Definitions

• dev: Uses DuckDB for fast local iterations. Path defaults to ./flext_oracle_wms_dev.duckdb.
• staging: Uses PostgreSQL for shared testing. Configurable via DBT_POSTGRES_* environment variables.
• prod: Production PostgreSQL environment with higher thread counts (16) and shorter connection timeouts (10s).
• ci: In-memory DuckDB instance for automated testing pipelines.

Sources: profiles.yml:8-48

External Dependencies & Packages

The project manages its software and DBT macro dependencies through pyproject.toml and packages.yml.

Python Dependencies

The core transformation logic relies on the following internal and external libraries:

• flext-core: Base framework utilities.
• flext-meltano: Orchestration runner for DBT and Meltano.
• flext-oracle-wms: Domain-specific models and WMS API clients.
• click: Command-line interface support.
• pydantic: Data validation and settings management.

Sources: pyproject.toml:25-30

DBT Packages

The project imports several standardized macro libraries to handle common SQL tasks and data quality:

• dbt-labs/dbt_utils: Standard SQL utility macros.
• godatadriven/dbt_date: Temporal data handling for WMS timestamps.
• metaplane/dbt_expectations: Advanced data quality testing.
• dbt-labs/audit_helper: Utilities for auditing schema changes.

Sources: packages.yml:3-16

Configuration Variables & Hooks

Standard variables and lifecycle hooks are defined to control the behavior of the transformation runs.

Key Configuration Variables

Variable	Default / Source	Purpose
oracle_wms_schema	wms_raw	Target schema for raw data ingestion.
business_start_date	2024-01-01	Filters data for processing windows.
freshness_warn_after	12 hours	Warning threshold for source data staleness.
enable_incremental	true	Performance optimization for large WMS tables.
wms_entities	List of 10+	Defines entities like allocation, inventory, shipment.

Sources: dbt_project.yml:71-105

Execution Hooks

The project implements "Enterprise Hooks" to ensure auditability and data quality during every execution cycle.

Data Quality & Testing

Data quality is enforced through two mechanisms: schema-level tests and cross-model consistency SQL scripts.

Test Configuration

Tests are configured to store failures in a dedicated wms_test_failures schema. This allows for retrospective analysis of data quality issues without polluting production tables.
Sources: dbt_project.yml:64-67

Consistency Validation

Custom SQL tests (e.g., test_cross_model_consistency.sql) validate complex business logic:

• Orphaned Allocations: Ensures every allocation record maps back to a valid order detail.
• Over-allocation: Ensures allocated_quantity does not exceed available_quantity in inventory.
• KPI Mismatches: Validates that analytical dashboard totals match operational summaries.

Sources: tests/data_quality/test_cross_model_consistency.sql:7-78

Conclusion

The DBT Configuration & Dependencies within this project provide a robust, scalable framework for Oracle WMS data transformation. By utilizing a multi-layered architecture, environment-specific profiles, and a suite of standardized DBT packages, the system ensures that raw warehouse data is reliably converted into high-quality analytical models while maintaining strict audit trails and data quality standards.