Skip to content

Features

Designer

  • SVG canvas — drag tables onto the canvas, drag arrows from source columns to target columns.
  • Multiple source tables → one target: add several SQL tables and drag a line between key columns to join them (INNER/LEFT). The tool generates the SELECT … FROM … JOIN query.
  • Per-column transforms: Trim, UpperCase, LowerCase, EmptyToNull.
  • Draft target: build a new target table schema directly on the canvas — drag source columns to the target side, then create the table in the database with one click.
  • C# and Python scripts in the transform step (after mappings, before load).
  • Post-load SQL runs on the target server after the load (UPDATE, MERGE, EXEC …).
  • Variables / parameterisation — declare {{name}} variables and reference them in the extract SQL, post-load SQL, target table name or a request body, so one pipeline is config-driven instead of hard-coded. Built-in macros {{run_date}}, {{run_timestamp}} and {{watermark}} are always available; {{watermark}} resolves to the last successful high-watermark value, which is how an incremental extract filters on it (WHERE ModifiedDate > '{{watermark}}'). An undeclared reference fails the run before any I/O.
  • Metadata-driven generation — use a templated pipeline (one with {{variables}}) as a stencil and stamp out one pipeline per row of a binding set: paste rows, or point at a control table (SELECT table_name AS table, schema_name AS schema FROM etl_config WHERE enabled = 1) and each row's columns become the variable values. Preview the result, then generate; the copies group together in the Workflows view. Build one load, run it for fifty tables.

Execution and observability

  • Real-time status via SignalR — per-step progress without polling.
  • Per-step timing (Extract, Transform, Script, Load, Post-load SQL).
  • Error handling — skipBadRows: bulk copy attempt, row-by-row fallback on failure.
  • Dead-letter quarantine — with quarantineBadRows, each row skipBadRows skips is written (with its load error, a UTC timestamp and the pipeline label) to a quarantine table that is created at run time on first use and kept across runs, so nothing is lost silently. Defaults to <target>_Quarantine; override with quarantineTable.
  • Data-quality expectations — per-column rules (NotNull, NotBlank, Regex, Range, InSet, MaxLength) checked on the mapped rows of the primary target before they load. A violated Fail rule aborts the run before the bad chunk loads (so a non-streaming load rolls back entirely); Warn rules just record their violation counts on the run and in notifications.
  • Transaction around DELETE / bulk load with TRUNCATE fallback.

Workflows and scheduling

  • The Workflows view lists all saved pipelines with cron schedule and enable/disable toggle.
  • The Quartz.NET scheduler runs inside the server — pipelines fire even when no browser is open.
  • Run history with per-step details in the Runs view.

Data Vault automation

The Data Vault view is a visual modeller: drag several source tables onto a canvas and design the whole vault at once.

  1. Connect and drag tables in — double-click source tables to drop them on the canvas. Each column is auto-classified as business key (primary key) or descriptor; click the role chip (BK/D/—) to change it.
  2. Draw relations — drag from a column to another table to create a link; the tool also auto-suggests links from matching key names. Each relation becomes a Lnk_ between the two hubs.
  3. Generate — for every table the tool builds:
    • stg_<Entity> — a stage table with the source columns plus persisted computed hash columns (hash key, hash diff, link hashes) and LoadDate/RecordSource
    • Hub_<Entity> with hash key, business key(s), LoadDate, RecordSource
    • Sat_<Entity> with HashDiff for descriptor CDC
    • Lnk_<Entity>_<Ref> for each drawn relation
    • a stage load (source → stage) plus one runnable Hodor pipeline per vault table
  4. Apply — creates the schemas and tables and saves the pipelines, which appear in the Workflows view (grouped per entity) ready to run or schedule.

Hashing happens in the stage layer (DV 2.0 "hash in stage"). Because the hub, satellite and link loads read from the stage table, they are insert-only, idempotent and independent of each other and of load order — they can run at any time and in parallel. Only the stage and the vault need to share a database; the source can live anywhere (a lake, CSV, REST API, another server), since only the stage load touches it.