PostgreSQL Data Vault 2.0 Engine

A reference Data Vault 2.0 implementation built with PostgreSQL, dbt, AutomateDV, and Python-friendly workflows. The project loads order data from a CSV seed, creates a Raw Vault, builds a Business Vault point-in-time table, and exposes a customer-focused information mart.

What this project demonstrates

• Data Vault 2.0 modeling: hubs, links, satellites, hash keys, hashdiffs, load dates, and record sources.
• PostgreSQL storage: local PostgreSQL 15 is provided through Docker Compose.
• dbt transformations: SQL models and AutomateDV macros manage repeatable, lineage-aware builds.
• Business Vault logic: a PIT model provides the latest satellite state for each snapshot date.
• Analytics-ready output: fct_customer_daily_summary presents daily customer order status.
• Python integration point: Python can own ingestion, file/API extraction, orchestration, and operational checks before handing prepared data to dbt.

Architecture

flowchart TD
bronze["Bronze Layer: Landing\nseeds/raw_orders.csv"]

silver_stage["Silver Layer: Staging\nmodels/stage/stg_raw_orders\nHash keys, hashdiffs, load metadata"]

silver_raw["Silver Layer: Raw Vault\nHub: hub_customer\nLink: link_customer_order\nSatellite: sat_order_details"]

silver_business["Silver Layer: Business Vault\ndim_snapshot_dates\nPIT: pit_customer"]

gold["Gold Layer: Information Mart\nfct_customer_daily_summary\nCustomer daily order status"]

bronze --> silver_stage
silver_stage --> silver_raw
silver_raw --> silver_business
silver_business --> gold

classDef bronzeLayer fill:#7a4e24,stroke:#d9a066,color:#ffffff,stroke-width:2px
classDef silverLayer fill:#24566b,stroke:#67c7e8,color:#ffffff,stroke-width:2px
classDef goldLayer fill:#8a6a17,stroke:#f1ca62,color:#ffffff,stroke-width:2px

class bronze bronzeLayer
class silver_stage,silver_raw,silver_business silverLayer
class gold goldLayer

Loading

The source seed contains customer, order, amount, and order-date data. The staging model derives LOAD_DATE and RECORD_SOURCE, then AutomateDV generates the hash keys and order-amount hashdiff used by the vault models. In this project, the Bronze, Silver, and Gold names describe logical data layers; the physical database is PostgreSQL.

Technology stack

Technology	Role
PostgreSQL 15	Data warehouse database for local development
dbt	Model execution, dependency management, lineage, and testing
AutomateDV 0.10.1	Data Vault 2.0 macro library
Python	Recommended ingestion and orchestration layer
Docker Compose	Reproducible local PostgreSQL environment

Project layout

models/                  dbt models
models/stage/             Source preparation and hash generation
models/raw_vault/hubs/    Business keys, such as customers
models/raw_vault/links/   Relationships, such as customer orders
models/raw_vault/sats/    Descriptive and historized attributes
models/business_vault/    PIT and snapshot date models
models/info_marts/        Consumer-facing analytical models
seeds/raw_orders.csv      Example source data
macros/                   Project-specific dbt macros
tests/                    dbt tests
snapshots/                dbt snapshot definitions

Prerequisites

• Docker Desktop with Docker Compose
• Python 3.10 or newer
• A Python environment with dbt-postgres installed
• dbt Core compatible with the installed adapter

Create and activate a virtual environment, then install the adapter:

python -m venv .venv
.\.venv\Scripts\Activate.ps1
python -m pip install --upgrade pip
python -m pip install dbt-postgres

Local setup

1. Start PostgreSQL with docker compose up -d postgres.
2. Update the placeholder password in docker-compose.yml, then use the same values in your dbt profile. A local profile at %USERPROFILE%\.dbt\profiles.yml can look like this:

The profile must use type: postgres, host: localhost, port: 5432, user: dbt_user, dbname: data_vault_db, and a schema such as public. Set password to the same local value configured for the container and use threads: 4 for local development.

1. Install the project package dependency and verify the connection with dbt deps and dbt debug.
2. Load the example source and build the project with dbt seed, dbt run, and dbt test.

For a clean reload of the seed, use dbt seed --full-refresh. To rebuild only a layer, use a selector such as dbt run --select stage, dbt run --select raw_vault, or dbt run --select info_marts.

dbt model behavior

The project configuration materializes:

• Staging models as views.
• Raw Vault models as incremental tables.
• The PIT and snapshot date models as tables.
• The customer daily summary as a table in the info_mart schema.

AutomateDV is configured with readable string hash formatting through the hex_formatting project variable. The staging metadata currently uses order_date as the load timestamp and !RAW_ORDERS_CSV as the record source.

Python workflow

Python is intended to sit upstream of dbt. A production ingestion script or service can:

1. Extract order data from files, APIs, or operational systems.
2. Validate required columns and data types.
3. Write a normalized landing file or load a PostgreSQL staging table.
4. Invoke dbt commands such as dbt seed, dbt run, and dbt test through an orchestrator.
5. Capture dbt logs and fail the pipeline when quality checks fail.

The repository currently provides the dbt and PostgreSQL implementation; it does not yet contain a Python package or ingestion module.

Useful commands

# Start and stop the database
docker compose up -d postgres
docker compose down

# Inspect and build dbt artifacts
dbt ls
dbt compile
dbt docs generate
dbt docs serve

# Run selected models and their dependencies
dbt run --select +fct_customer_daily_summary

# Remove generated dbt dependencies and artifacts
dbt clean

Data Vault 2.0 conventions in this project

• Hubs store stable business keys, such as customer_id.
• Links store relationships between hubs, such as a customer and an order.
• Satellites store descriptive attributes and their history, using a hashdiff to detect changes.
• Hash keys provide deterministic primary and foreign keys across the vault.
• Load dates and record sources preserve lineage and auditability.
• PIT structures simplify point-in-time queries over historized satellite data.

Next steps

• Add dbt schema YAML files with column descriptions and relationship tests.
• Add a Python ingestion package and an orchestrated run entry point.
• Parameterize the snapshot date range instead of keeping it fixed to August 2026.
• Add CI checks for dbt debug, dbt compile, dbt run, and dbt test.

Resources

• dbt documentation
• AutomateDV documentation
• Data Vault 2.0 overview
• PostgreSQL documentation