Apache Hop plugin

Data Hopper EDW

Model once. Generate loads and consumption layers.

Visual, model-driven Data Vault, Business Vault, and dimensional modeling for Apache Hop — with a shared Data Catalog, schema gates, content quality, and explainable source-to-target lineage.

release 0.16.0 Apache Hop 2.20.0-SNAPSHOT Java 21 .hsm · .hdv · .hbv · .hdm · .hem

The problem: hand-written vault pipelines drift from the model — hashing rules diverge, satellite CDC is copy-pasted, and hybrid teams (Hop + dbt + SQL) lack one metadata picture. The approach: the visual model is the contract; Hop generates DDL, insert-only loads, SCD2 consumption, and marts from that contract — from source systems through the warehouse.

Explore by topic

ARCH

Architecture

.hsm → catalog → DV → BV → DM. Resource definition group is the spine.

SRC

Data Catalog

DV_SOURCE contracts, COMPOSITE / JSON / PIPELINE feeds, versions.

HSM

Source modeler

.hsm tables, Free SQL, pipeline sources, Hop Server JDBC.

DV

Raw Data Vault

Hubs, links, satellites, hybrid modes, composite BKs, aliases.

BV

Business Vault

SCD2 (single & multi-sat), PIT, SQL tables, BV Update.

DM

Dimensional

Kimball dimensions, facts, bridges — Publish & Update.

VAL

Schema & harvest

Metadata harvest, CI/CD gates, blast radius, reports.

DQ

Data quality

Measure → gate, Phase 2 rules, pre/post load policies.

LIN

Lineage

Table/field reasons, OpenLineage / Marquez, reverse browser.

MAP

Roadmap

Plans and open GitHub issues grouped by theme.

Source model publishes feeds into the catalog. Update resource definition group publishes target layouts out. Harvest observes; it does not rewrite contracts.

Sources Data Vault Business Vault Dimensional Catalog

Build order

Configure EDW (catalog + shared configs)
Source model .hsm → Push to catalog
Generate / refine .hdv → Check model
Resource definition group (list the .hdv)
Update resource definition group (DDL + load + publish targets)
Add .hbv / .hdm to the same group and update again

Resource definition group

The project’s list of warehouse models plus the default catalog. Harvest, the schema gate, version tags, lineage browse, and the preferred load action all take a group — not a folder of catalog JSON.

Docs: docs/resource-definition-group.adoc, docs/getting-started-edw.adoc

Hybrid integration modes (per table)

Not every raw vault table must be Hop-loaded

Hop managed	Generate DDL and update pipelines (default).
External read-only	Table loaded elsewhere (dbt, SQL, other ETL); Hop documents it for BV and catalog.
Custom pipelines	You own `.hpl` loaders; Hop orchestrates them with generated ones.

Also on the map

Execution maps (.hem) — crawl workflows/models into lineage graphs for GUI and ops.
AI Help — optional LLM advisory with review-before-apply proposals.
Workflow actions — Data Vault / Business Vault / Dimensional Update, Validate resource definitions, DQ measure & gate.

Feature	Status	Detail
Data Catalog + `DV_SOURCE` (DB / COMPOSITE / JSON / PIPELINE)	Available	Catalog
Source modeler (`.hsm`): tables, FKs, queries, JSON, pipeline sources	Available	Source modeler
Free SQL over source models (Calcite) + Source model SQL transform	Available	Source modeler · #117
Hop Server JDBC (`jdbc:hop-hsm:`) + thin DBeaver client	Available	Source modeler
Metadata harvesting + schema gate `HARVEST_RUN`	Available	Schema & harvest · #112
Resource definition validation (issues, proposals, acknowledgements)	Available	Schema & harvest
Catalog version tags + schema impact simulation (CI/CD)	Available	Schema & harvest
Data quality measure + quality gate (Phase 2 rules, persist, alerts)	Available	Data quality
Data Vault modeler (`.hdv`) + Update action	Available	Raw DV
Composite hub BKs · hub aliases · integration modes	Available	Raw DV
Business Vault modeler (`.hbv`): SCD2, PIT, SQL views/tables	Available	Business Vault
Dimensional modeler (`.hdm`) + Publish / Update	Available	Dimensional
Source-to-target lineage, explainable DDL, reverse browser	Available	Lineage
Marquez / OpenLineage export	Available	Lineage · #101
Execution maps (`.hem`) · architecture export (Draw.io)	Available	Operations
AI Help · project Search Everywhere (Hop 2.19)	Available	Operations
Record Definition transforms · Date Dimension Generator	Available	Operations
BV naming rules engine	Planned	Roadmap · #41
Automated execution with dependency resolution	Planned	Roadmap · #42

Shipped in 0.16.0: a dimensional model service exposes a .hdm as a masked jdbc:hop-hsm: schema with query audit; lineage cards; crosstab selections and page format; the architecture poster adds Hop JDBC clients.

Shipped in 0.15.0: search inside model files; the Data Vault AI advisor includes catalog sources and can apply a suggested model; refreshed documentation screenshots, including modeling a vault from catalog sources.

Shipped in 0.14.0: EDW Journey opens on the architecture poster; row previews use Hop's Show rows dialog; the dimensional AI advisor no longer freezes Hop; Windows 11 guide for the retail example on WSL2.

Shipped in 0.13.0: drawn EDW icons and a factless-fact icon; training modules 0–8; hidden source cards; SQL Server relationship profile; EDW component explorer.

Shipped in 0.12.0: operational analytics from the OPS database; source-model browse and stress/force layout; lookup indexes on load keys; Type 1 skip-unchanged loads; fact dimension-join copies; catalog connection and data-type mapping editors; target-table preview from the named configuration; quieter DDL logging.

Shipped in 0.11.0: Hop 2.20.0-SNAPSHOT; source masking; semantic layer (.hsl) and fact crosstabs; split generated Data Vault files; synthetic data; AdventureWorks sample; bus matrix; Database Workbench previews; presentations, Performance and Gantt charts.

Shipped in 0.10.0: rebrand to Data Hopper EDW (org.hopper:hopper-edw); generate project documentation; BV SCD2 calculations / SQL Expression, hub business keys, hash-key partitions; Jinja/dbt import; target type mappings; dialog Help HTML.

Shipped in 0.9.0: shared model configuration and Configure EDW setup; generate a Data Vault from a source model; Hop Lineage View (.hlv); first-time architecture / getting-started EDW docs and plugin HTML.

Shipped in 0.8.0: source modeler Free SQL (Calcite pushdown + residual pipelines), Source model SQL transform, Hop Server jdbc:hop-hsm: + thin client for DBeaver; pipeline sources on .hsm; metadata harvesting phases 1–4; richer retail CRM / ASN fixtures.

0.5.0–0.7.0 highlights: OpenLineage / Marquez; optional PK/FK DDL; source-to-target lineage; composite hub business keys; hub aliases; architecture Draw.io export; data quality Phase 2; Hop 2.19 search.

What lives in the catalog

DV_SOURCE — logical feeds with record-source indicator, optional group for partial loads, and physical layout: database table, COMPOSITE (multi-table query), JSON, or PIPELINE.
Namespace pattern: hop/{project}/sources (e.g. hop/retail-example/sources).
FILE catalog storage (retail: work/edw-catalog/) opened in the Hop Data Catalog perspective.
Hubs, links, and satellites reference source names, not raw connection strings.
Go to origin opens the .hsm card for published composite / JSON / pipeline feeds.

Published namespaces

hop/{project}/models/{model}/ — DV table layouts
hop/{project}/business-vault/{model}/ — BV targets
hop/{project}/dimensional/{model}/ — mart tables
hop/{project}/lineage/{dv|bv|dm}/{model}/ — lineage siblings (derived, not edited)
hop/{project}/operations/ — load metrics, harvest, quality ops stubs

Catalog versions & transforms

Tag catalog version freezes source contracts (e.g. v1.0.0).
Baselines for the CI/CD schema gate (live source, working vs tag, version vs version, harvest run).
Resource definition group is the spine: model lists + default catalog; harvest, schema gate, versions, and Update RDG all take a group.
Pipeline transforms: Record Definition Input / Output / Data Input.

Catalog + model = shared contract between ingestion, vault, and consumption teams — including hybrid warehouses where some tables are loaded outside Hop.

Docs: docs/architecture.adoc, docs/data-catalog.adoc, docs/resource-definition-group.adoc, docs/datavault-source.adoc

Tables & FKs

Import database tables with PK/FK discovery, crow’s-foot relationships, and live schema checks.

Source queries

Join builder + projection, or Free SQL (Calcite) with explain plan, preview, and publish as COMPOSITE.

JSON & pipelines

Flatten parent JSON fields; declare .hpl + output transform + fields; publish as JSON / PIPELINE.

Free SQL & data virtualisation (#117)

Apache Calcite parses SQL against the source model schema (tables, named queries, JSON, pipeline feeds).
Full pushdown — single Table Input when all tables are DATABASE on one connection.
Residual path — Sort + Merge Join, Filter, Group By, Calculator, MetaInject / JSON subgraphs when mixed.
Source model SQL pipeline transform for runtime free SQL against a .hsm.
Source model service metadata maps a public name to a server-side .hsm path.
Hop Server servlet /hop/sourceModelData + thin client jar hop-hsm-jdbc for DBeaver: jdbc:hop-hsm://{username}:{password}@{host}:{port}/{database} ({database} = service name = JDBC schema).

Publish & load

Queries → catalog COMPOSITE
JSON → catalog JSON (parent + JsonInput loads)
Pipelines → catalog PIPELINE (MetaInject + record source)
DV Update injects generated graphs with hub sort/distinct when needed

Retail sample

retail-example/models/source-tables-crm.hsm
Multi-table customer query, shipment JSON, ASN pipeline feed
Service metadata: metadata/source-model-service/crm.json

Source modeler with query dialog — Source modeler — retail CRM tables and multi-table query SQL

Docs: docs/source-modeler-overview.adoc, hop-hsm-jdbc/README.md, plan docs/plans/source-model-sql-virtualization-plan.md

Hubs

Core business entities with multi-source business keys; composite BKs (multi-field → one vault column).

Links

Relationships between hubs; transactional links with dependent child keys; hub aliases (same hub twice).

Satellites

Descriptive history — hashdiff or load-end-date patterns; multi-active via driving keys; JSON/pipeline sources.

Operations on the model

Edit model→ Import sources→ Check model→ Generate DDL→ Data Vault Update

Data Vault Update action

Validates (optionally), generates DDL, stages update pipelines
Runs loads in parallel with a shared load timestamp
Supports record source groups for partial model updates
Fail-if-DDL and explainable structure changes (via lineage)

DV 2.0 options

Hashing: MD5 / SHA1 / SHA256 / SHA512
HEX, String, or Binary hash keys
Trim + casing, delimiter, null placeholder
Unknown / invalid sentinel records
Standard column naming (load TS, record source)

Retail 360 Data Vault model in Hop GUI — Retail 360 Data Vault model

Docs: docs/datavault-plugin.adoc, docs/dv-hub.adoc, docs/dv-link.adoc, docs/dv-satellite.adoc, docs/dv-integration-modes.adoc

Table types

SCD Type 2

valid_from / valid_to (configurable names)
Open-ended current rows
Single-satellite or multi-satellite merge (e.g. Customer 360)
Incremental load support with separate DV/BV databases

PIT & SQL

PIT helpers for point-in-time joins across satellites
SQL business tables — view or table materialization
ref() / source helpers; BV→BV canvas references
Catalog model registry for short lookups

Link .hbv → .hdv→ Define SCD2 / PIT / SQL→ Business Vault Update→ Optional catalog publish

Customer 360 Business Vault model — Customer 360 — multi-satellite SCD2 Business Vault

Docs: docs/business-vault-overview.adoc, docs/business-vault-scd2.adoc, docs/business-vault-pit.adoc, docs/business-vault-sql-view.adoc

Dimensions

Conformed, role-playing, junk, range dimensions.

Facts

Transactional, snapshot, and aggregate facts.

Bridges

Many-to-many relationship structures.

Date dimension

Date Dimension Generator transform for calendar attributes.

Dimensional Publish

Draft a .hdm model from a .hdv vault as a starting point for the mart design.

Dimensional Update

Load the mart from the model — same validation-before-execution philosophy as DV/BV updates.

Dimensional model fact orders — Dimensional model — fact table example

Docs: docs/dimensional-modeler-overview.adoc, docs/dimensional-update-action.adoc, docs/date-dimension-generator.adoc

Check model

Same engine in GUI toolbar and update actions
Structural rules + optional live source type checking
Stops broken references before pipeline generation

Resource definition validation

Feed-level checks with remediation proposals
Acknowledgements for accepted exceptions
Downstream impact Source→DV→BV→DM
Design-time options: baseline + axes before each run

Metadata harvesting (phases 1–4)

Harvest source metadata action — connection-batched live discovery, OPS history (schema_harvest_*)
Does not rewrite the catalog; observation is separate from load
Schema gate mode HARVEST_RUN reuses a harvest without rediscovery
History browser, PK/FK drift, apply FKs → catalog / generate .hsm, model-check prefers harvest for types

Validate resource definitions — CI/CD schema gate

Compare modes: live source · working vs tag · version vs version · HARVEST_RUN
Failure severity: FAIL_ON_BLOCKING, FAIL_ON_WARNINGS, WARN_ONLY
Reports: Markdown and/or HTML with blast-radius tables (e.g. under work/reports/)
Place the action before DV/BV/DM update actions in workflows

Catalog-safe length remediation

Working catalog field length is the contract — length expand does not rewrite the catalog
Expands DV satellite, BV SCD2 maps, and DM SQL-sourced columns (multi-table SQL + workflow)
Retail sample: workflows/schema-remediation/accept-address_line1/ (address_line1 → 75 on sat / BV / d_customer)

Validate sources options dialog — Validate sources — baseline and check axes (design time)

Source validation results dialog — Results with CRITICAL BLOCKED, expected vs actual, remediation entry points

Validate resource definitions action dialog — Validate resource definitions — CI/CD schema gate

Docs: docs/resource-definition-validation.adoc, docs/metadata-harvesting.adoc

Measure Data Quality

Run rules against catalog subjects; produce a report; optionally persist history. Does not fail the workflow on findings (only infrastructure errors).

Evaluate Quality Gate

Apply policy to a report; optional alert sinks. Can fail the workflow (FAIL_ON_BLOCKING / FAIL_ON_WARNINGS) or alert only.

Typical patterns

Measure (sources)→ Gate FAIL_ON_BLOCKING→ Vault Update→ Measure (targets)→ Gate ALERT_ONLY

Rule library (Phase 1 + 2)

Phase 1: min/max row count, NOT NULL, allowed values, range, not-empty string.

Phase 2: null ratio max, min/max distinct, regex, min/max length, SQL assertion (trusted authors; single SELECT).

Schema validation (resource definitions) and content quality are separate stacks — both can protect loads and observe warehouses without conflating contract drift with bad data.

Docs: docs/data-quality.adoc

Capabilities

Table + field lineage for DV, BV, and DM with structured reason codes
Lineage tab on hub, link, satellite, BV SCD2, and dimensional table dialogs
Explainable DDL — every structure change is explained via lineage edges (not a boolean only)
Catalog publish — sibling records under hop/{project}/lineage/…
Drift gate — Validate resource definitions compares models to catalog lineage baselines
Reverse browser — from source field to all DV/BV/DM consumers (Resource definition group → Browse lineage…)
OpenLineage / Marquez export (0.5.0) — workflow action Export data lineage: folder and/or HTTP POST, column lineage, physical location facets, dimension-alias symlinks

Selected reason codes

Code	Meaning
`USER_EXPLICIT_MAPPING`	Explicit source field mapping (e.g. hub business key)
`DEFAULT_SAME_AS_SOURCE`	Target column uses the same name as the source
`STANDARD_COLUMN`	Config-driven technical column (load TS, record source, …)
`HASH_FROM_BUSINESS_KEYS`	Hash key derived from business keys
`LINK_HUB_KEY_MAPPING`	Link maps source columns to participating hub keys
`BV_SCD2_FIELD_MAP`	Explicit Business Vault SCD2 field mapping
`DM_ROLE_MAPPING`	Dimensional role, measure, or staging mapping

Satellite dialog Lineage tab — Lineage tab — satellite field contributions and reasons

Docs: docs/source-to-target-lineage.adoc, docs/openlineage-export.adoc · CHANGELOG 0.4.0–0.16.0

Execution maps (.hem)

Crawl a root workflow or model
Persist a graph of workflows, models, generated pipelines, source datasets
Open in Hop GUI for execution and lineage-oriented views

Runtime operations

Docker runners: scripts/run-hop.sh, run-postgres.sh
Parallel pipeline orchestration
Record source groups for partial loads
Catalog-backed load-run metrics; workflow load overview; per-table duration bars

Pipeline transforms & tooling

Record Definition Input / Output / Data Input — catalog definitions and field layouts as rows
Source model SQL — free SQL against a .hsm at runtime
Date Dimension Generator — populate standard calendar dimension attributes
Architecture export — Draw.io SOLUTION / inventory / DV·BV·DM diagrams
Search Everywhere — Hop 2.19 project search for models and plugin metadata
hop svg — export pipelines, workflows, and models to SVG

AI Help (optional)

LLM advisory on Data Vault models, pipelines, and workflows — scenarios, context inclusions, review-before-apply proposals.

Suggest hubs, links, satellites from catalog sources
Layout and type mapping proposals
Performance tuning scenario using load-run metrics (parallel copies, preload cache)

Configure under Hop GUI → Configuration → AI Assistant. Human modelers remain in control.

Execution map in Hop GUI — Execution map — retail six-month simulation

Docs: docs/operations.adoc, docs/execution-maps.adoc, docs/ai-advisory.md, docs/performance-tuning.md

retail-example

Learning path — CSV → CRM → DV → BV → DM, initial and monthly updates.

Register as Hop project retail-example
Source model source-tables-crm.hsm (queries, JSON, pipeline feeds)
workflows/run-retail-initial.hwf then monthly run-retail-update.hwf
Quality gates pre/post load; optional harvest + schema remediation samples
Primary onboarding: docs/getting-started-retail.adoc

integration-tests

CI / regression — Customer 360, multi-active, external read-only, golden datasets.

Register as Hop project hopper-edw
PostgreSQL, MySQL, SingleStore, SQL Server runners
./run-tests.sh or multi-DB Docker matrix

Featured fixtures

vault1 — classic hub / link / satellite
customer-phone — multi-active satellite
customer-360 — four satellites merged into one BV SCD2 table
customer-360-external — same BV with external read-only DV satellites
retail CRM HSM — Free SQL, shipment JSON, ASN pipeline, source-model-service crm

Video tutorials

Recently shipped

#117 · Available

Free SQL / source-model virtualisation

Calcite free SQL, Source model SQL transform, Hop Server servlet + thin hop-hsm-jdbc client for DBeaver.

#112 · Available

Metadata harvesting

Harvest action, OPS history, HARVEST_RUN gate mode, history browser, FK apply / HSM generate.

#116 · Available

Pipeline sources on source modeler

Declare .hpl + output fields; catalog PIPELINE; MetaInject loads with record source.

#101 · Available

Marquez / OpenLineage export

Export data lineage action (folder + HTTP), physical location facets, dimension-alias symlinks.

#103–#106 · Available

Hub aliases · Draw.io export · Search · RDG update

Same hub twice on a link; architecture export; Hop 2.19 project search; Update resource definition group action.

Named plans Planned

plan · #41

BV naming rules engine

Configurable naming conventions for Business Vault tables and columns.

plan

Coach panel · AI M2 · metrics extensions

In-modeler coaching UI, deeper AI assistant milestones, load-run metrics evolution.

Platform & automation

#42 · important

Automated execution with dependency resolution

Hand over DV/BV/DM models; resolve update order, parallel tracks, per resource group, skip already-updated windows.

#80

Automated DDL regression testing / DDL guard

Compare generated DDL to deployed schema; halt on destructive changes until human approval.

#82

Downstream impact dry-run on field changes

When updating catalog source fields, simulate Source→DV→BV→DM consequences before apply.

Lineage & observability

#79

Visual lineage with live metrics

Overlay runtime telemetry on hubs/satellites (e.g. “42 min today vs 2 min average”).

#78

Time-travel auditing & as-of validation

Automated as-of reconciliation: sample historical source vs vault PIT query and diff.

Modeling enhancements

#86

Date dimension enhancements

Custom SQL attributes, relative attributes (YTD, M−1, …) driven by a business reference date.

#72

dbt-style macros in SQL Business Vault tables

Register macros in Hop metadata; path toward dbt-core interoperability.

#77

Ghost records & early-arriving facts

Built-in orphan prevention, quarantine / error satellite, auto placeholder hubs.

UX, docs & follow-ups

#83

Validation issues UX

Clearer path from found issues to details/proposals (beyond double-click).

#25

Automatic model documentation generator

Generate table docs, descriptions, and source-to-target mappings from models.

#92 · Available

PK / constraints in generated DDL

Shipped in 0.5.0: optional primary and foreign keys on DV/BV/DM model DDL (default off).

Note: Lineage (#97), OpenLineage (#101), optional PK/FK DDL (#92), Free SQL (#117), harvesting (#112), and pipeline sources (#116) are available in current mainline builds. Remaining open issues focus on automation (#42), macros (#72), live metrics (#79), and similar enhancements.

Model the warehouse. Let Hop generate the mechanics.

Source systems from .hsm · Raw vault from .hdv · Business vault from .hbv · Dimensional marts from .hdm · Catalog, harvest, validation, quality · Free SQL / JDBC over sources · Lineage that explains every mapping.

Who benefits

Architects	Governed hashing, hybrid warehouses, schema gates, harvest history, lineage drift detection.
Modelers	Visual design from source to mart, Free SQL, generated pipelines, BV without hand-written SCD2 SQL.
Operations	Workflow batches, CI/CD gates, quality pre/post load, execution maps, parallel orchestration.
Analytics / BI	SCD2 timelines, dimensional marts; optional DBeaver access via `jdbc:hop-hsm:` to source models.

Get started

Install plugin 0.16.0 into Apache Hop 2.20.0-SNAPSHOT
Walk retail-example/ with docs/getting-started-retail.adoc
Deep fixtures: integration-tests/
Feature list: docs/feature-overview.adoc