"A sluice is a channel that controls the flow of water. Sluice is a toolkit that controls the flow of data. Except data doesn't flood your basement. Usually."

@caracal-lynx/sluice — a config-driven ETL toolkit for ERP data migrations, built by Caracal Lynx Limited.

📖 Full documentation: https://caracal-lynx.github.io/sluice/

Data quality is the hidden blocker for both migrations and AI adoption.

Sluice is a data migration and data quality tool that validates your data before it reaches its destination — not after. You describe the entire migration as a YAML file: where the data comes from, the quality rules it has to pass, how each field maps to the target. Sluice validates the source, transforms it, and loads only the clean records — the bad rows go to a rejection report so you can fix the source.

Clean data flows through.

🤔 What is this thing?

Sluice takes the pain out of ERP data migrations. You know the drill — a client has 20 years of customer records in a legacy SQL database, and they need them in a shiny new ERP system by Monday. The data is a mess, the field names are cryptic, and someone has helpfully stored postcodes in a column called ADDR5.

Sluice lets you describe the entire migration as a YAML pipeline config — where to get the data, what quality rules to enforce, how to transform the fields, and where to load the result. The engine is written once; every client engagement is just a folder of YAML files.

No UI. No server. No cloud dependency. Just the sluice CLI, TypeScript modules, and a strong cup of tea. ☕

✨ What it does

The data flows through four stages — like water through a sluice gate:

💾 Source(s)                🔍 Data Quality              ✨ Transform               🎯 Target
─────────────────    →     ─────────────────    →     ─────────────────    →     ─────────────────
MSSQL / CSV /              Validate rules              Map fields                 Business Central
XLSX / REST /              Reject bad rows             Apply lookups              IFS ERP
PostgreSQL                 Write DQ report             Cleanse values             BlueCherry ERP
                                                       Evaluate expressions       CSV / PostgreSQL
  (1..N sources)
        ↓
  🔀 Optional Merge
   coalesce, union,
   intersect, priority

Under the bonnet, all extracted data passes through a local DuckDB staging store before being transformed and loaded. Think of it as a staging area where data sits while it gets its act together before being presented to the target ERP. 🦆

Pipelines can be single-source (one YAML per entity, one source: block) or multi-source — 2+ sources merged on a key column using one of four built-in strategies before DQ and transform run. See Multi-Source Merge below.

🏗️ Architecture

Single-source pipeline

flowchart LR
    A[📄 Pipeline YAML] --> B[⚙️ Config Loader<br/>Zod validation<br/>ENV var resolution<br/>Composite rule expansion]
    B --> C[🔌 Source Adapter<br/>mssql / pg / csv<br/>xlsx / rest]
    C --> D[(🦆 DuckDB<br/>stg_raw)]
    D --> E[🔍 DQ Engine<br/>Rules validation<br/>Rejection report]
    E --> F[✨ Transform Engine<br/>Field mapping<br/>Lookup resolution<br/>Cleanse ops<br/>Custom plugins]
    F --> G[(🦆 DuckDB<br/>stg_transformed)]
    G --> H[🎯 Target Adapter<br/>bc / ifs / bluecherry<br/>csv / pg]
    H --> I[📦 Output<br/>CSV / REST / DB]
    E -->|❌ critical failures| J[🛑 Pipeline halted<br/>dq-summary.json<br/>rejected.csv]

Multi-source pipeline

flowchart LR
    A[📄 Pipeline YAML<br/>sources + merge] --> B[⚙️ Config Loader]
    B --> C1[🔌 Source 1]
    B --> C2[🔌 Source 2]
    B --> C3[🔌 Source N]
    C1 --> D1[(🦆 stg_raw_src1<br/>+ rename + per-source DQ)]
    C2 --> D2[(🦆 stg_raw_src2<br/>+ rename + per-source DQ)]
    C3 --> D3[(🦆 stg_raw_srcN<br/>+ rename + per-source DQ)]
    D1 --> M[🔀 MergeEngine<br/>coalesce / union<br/>intersect / priority-override]
    D2 --> M
    D3 --> M
    M --> G[(🦆 stg_merged<br/>+ stg_merge_conflicts.csv)]
    G --> E[🔍 Post-merge DQ]
    E --> F[✨ Transform → stg_transformed]
    F --> H[🎯 Target Adapter]
    H --> I[📦 Output]

🧰 Tech Stack

| What | Package | Why | |------|---------|-----| | 🔤 Language | TypeScript 5.x strict | Because any is a cry for help | | 🟢 Runtime | Node.js 24 LTS | Active LTS until April 2028; OpenSSL 3.5; ESM-stable | | 📋 Config | js-yaml + zod | YAML in, typed objects out | | 🗄️ SQL Server | mssql | Because the legacy DB is always SQL Server | | 📊 Staging | @duckdb/node-api (embedded) | Promise-native, ABI-stable — no server, no npm rebuild after Node version bumps | | 📁 CSV | csv-parse + csv-stringify | Streaming, handles BOM, the works | | 📈 Excel | exceljs | Read-only — we're migrating away from it, after all | | 🌐 HTTP | axios + axios-retry | 3 retries, exponential backoff, rate limit respect | | 📅 Dates | dayjs | Because time zones are already somebody else's problem | | 🖥️ CLI | commander v12 | Clean commands, sane flags | | 📝 Logging | pino | Structured JSON logs — pretty in dev, parseable in CI | | 🧪 Testing | vitest | Not Jest. Never Jest. | | 🔒 Expressions | expr-eval | Safe expression parsing — no eval() here, thank you very much |

🧩 Extension model

Sluice's pipeline schema is fixed by design (readability, reviewability, predictable validation). Anything you can't express in the schema, you add via plugins. Three tiers, scaling from "no code, no install" to "publishable npm package":

| Tier | What it is | Where it lives | Best for | |---|---|---|---| | Tier 1 | YAML composite rules — bundle built-in DQ checks under a single ID | shared/rules.yaml in your project | Reusing common check combinations across pipelines without writing code | | Tier 2 | TypeScript file plugins — *.rule.ts / *.transform.ts / *.merge.ts | plugins/ next to your YAML | Custom logic for one project; rapid iteration | | Tier 3 | npm packages exporting register() | npmjs.com (public or private) | Distributing rules / adapters / strategies across teams or as paid products |

See PLUGINS.md for the full author's guide with worked examples for all three tiers.

🚀 Quick Start

A complete pipeline in 20 lines: read a CSV, validate emails, lowercase them, write the clean rows to a new CSV. The full file is checked into the repo at examples/hello-world.pipeline.yaml with sample data at examples/data/hello-world.csv.

pipeline:
  name: hello-world
  client: demo
  version: "1.0"
  entity: Customer

source:
  adapter: csv
  file: ./examples/data/hello-world.csv

dq:
  rules:
    - field: email
      checks:
        - { type: notNull, severity: critical }
        - { type: email,   severity: warning  }

transform:
  fields:
    - { from: name,    to: Name,    type: string, cleanse: trim }
    - { from: email,   to: Email,   type: string, cleanse: trim|lowercase }
    - { from: country, to: Country, type: string, default: GB }

target:
  adapter: csv
  output: ./output/hello-world-clean.csv

Run it end to end:

# 1. Install
npm install -g @caracal-lynx/sluice

# 2. Validate the config without touching any data
sluice check examples/hello-world.pipeline.yaml

# 3. Dry-run: extract + DQ + transform but don't write the target
sluice run examples/hello-world.pipeline.yaml --dry-run

# 4. Live run — writes ./output/hello-world-clean.csv +
#                        ./output/hello-world-rejected.csv (if any DQ failures)
sluice run examples/hello-world.pipeline.yaml

The sample data has one row with a malformed email — that's a warning, so the row is kept in the output but flagged in output/hello-world-rejected.csv. Open both CSVs side by side to see what passed and what got reported. Add an unknown@bad-style row (or strip an email entirely) to see how a critical failure halts the pipeline before any output is written.

Other CLI commands

# Run DQ + transform; skip the load (faster than --dry-run for spec checks)
sluice validate customers.pipeline.yaml

# Profile source data — column stats, distinct counts, samples; no DQ
sluice profile customers.pipeline.yaml

# Inspect loaded plugins and merge strategies
sluice plugins
sluice merge list-strategies
sluice merge info coalesce

CLI flags

| Flag | What it does | |------|-------------| | --log-level debug\|info\|warn\|error | How chatty do you want the logs? | | --env <file> | Path to your .env file (default: ./.env) | | --output <dir> | Override the output directory | | --plugins <dir...> | Load additional plugin directories (alongside the pipeline plugins/ folder) | | --dry-run | Extract + DQ + transform, but don't write a single byte to the target |

When multiple plugin directories resolve to the same absolute path (for example, --plugins ./plugins), Sluice de-duplicates them before loading.

Exit codes

| Code | Meaning | |------|---------| | 0 | ✅ All good | | 1 | ❌ Pipeline error | | 2 | 🛑 Critical DQ violations halted the pipeline | | 3 | 📋 Config validation failed |

📄 Pipeline Config Format

Each migration entity gets its own YAML file. One entity, one file. Nice and tidy.

💡 One YAML file = one migrated entity
   (customers, items, vendors, styles, purchase orders, etc.)

A single-source pipeline has five sections:

pipeline:   { name, client, version, entity, description }
source:     { adapter, connection/file/endpoint, ... }
dq:         { rules, stopOnCritical, rejectionFile }
transform:  { lookups, fields }
target:     { adapter, output/baseUrl, ... }
run:        { mode, batchSize, logLevel, dryRun, ... }  # all optional

A multi-source pipeline swaps source: for sources: + merge::

pipeline:   { ... }
sources:    [ { id, priority, adapter, ..., rename? }, ... ]   # 2+ entries
merge:      { key, strategy, onUnmatched, fieldStrategies, conflictLog, incrementalSource? }
dq:         { ... }                 # rules can be scoped via sourceId
transform:  { ... }
target:     { ... }
run:        { ... }

PipelineSchema requires either source: (single) or both sources: + merge: (multi) — never both. The CLI auto-routes based on which shape the YAML has, so there's no flag to remember.

📥 Source Adapters

| Adapter | Use when... | |---------|-------------| | mssql | The legacy system is SQL Server (it's always SQL Server) | | pg | The legacy system is PostgreSQL (you lucky thing) | | csv | Someone emailed you a CSV export at 11pm the night before go-live | | xlsx | Same as above but Excel, complete with merged cells and mystery formatting | | rest | The source system has an API! Progress! |

🎯 Target Adapters

| Adapter | Loads to... | |---------|-------------| | bc | Microsoft Dynamics 365 Business Central (via OData REST + OAuth2) | | ifs | IFS ERP (via fixed-format CSV import — no header, specific column order) | | bluecherry | BlueCherry ERP / CGS (CSV import, US-format dates, headers required) | | csv | Generic CSV — for anything else or for manual inspection | | pg | PostgreSQL — useful for intermediate staging or custom targets |

🔍 Data Quality Rules

Nine built-in rule types, configurable per field:

dq:
  stopOnCritical: true
  rules:
    - field: CUST_CODE
      checks:
        - { type: notNull,       severity: critical }  # 💥 stops the pipeline
        - { type: unique,        severity: critical }
        - { type: pattern,       value: "^[A-Z0-9]{3,10}$", severity: warning }

    - field: EMAIL
      checks:
        - { type: email,         severity: warning }   # ⚠️  flagged but not rejected

    - field: POST_CODE
      checks:
        - { type: ukPostcode,    severity: warning }   # 🇬🇧 all UK formats

| Rule | What it checks | |------|---------------| | notNull | Not null, not empty, not just whitespace | | unique | No duplicates across the whole dataset | | pattern | ECMAScript regex | | email | RFC 5322-ish email validation | | ukPostcode | All current UK postcode formats | | maxLength | String length cap | | min / max | Numeric range | | allowedValues | Enum-style allowed value list |

Severity levels: critical (row rejected, pipeline can halt) · warning (flagged in report, row kept) · info (summary only)

✨ Transform: Field Mapping Types

| Type | What it does | |------|-------------| | string | Cast + optional cleanse ops + optional truncation | | number | Integer coercion (NaN = error) | | decimal | Fixed-precision decimal stored as string | | boolean | '1','true','yes','y','t' → true. Everything else → false | | date | Parse source date, output in target format | | lookup | Resolve via a CSV or SQL lookup table | | concat | Join multiple source fields with a separator | | constant | Emit a fixed value (e.g. CustomerGroup: DOMESTIC) | | expression | Evaluate an expression against the source row | | custom | Delegate to a TransformPlugin via customOp (Phase 2) |

🧹 Cleanse Operations

Pipe-chain them: cleanse: trim|titleCase|normaliseUnicode

| Op | Before | After | |----|--------|-------| | trim | " hello " | "hello" | | uppercase | "hello" | "HELLO" | | lowercase | "HELLO" | "hello" | | titleCase | "john smith" | "John Smith" | | stripNonAlpha | "AB-12!" | "AB" | | stripNonNumeric | "AB-12!" | "12" | | padStart:6:0 | "42" | "000042" | | nullIfEmpty | "" | null | | normaliseUnicode | "café" | "cafe" | | normaliseQuotes | "it's" | "it's" |

📁 Repository Structure

sluice/
├── src/
│   ├── cli.ts                  ← CLI entry point (commander)
│   ├── runner.ts               ← PipelineRunner — single-source orchestration
│   ├── multi-source-runner.ts  ← MultiSourcePipelineRunner (Phase 3)
│   ├── config/                 ← Zod schema, YAML loader, ENV var + composite expansion
│   ├── adapters/
│   │   ├── source/             ← mssql, pg, csv, xlsx, rest
│   │   └── target/             ← bc, ifs, bluecherry, csv, pg
│   ├── staging/                ← DuckDB wrapper (stg_raw → stg_merged → stg_transformed)
│   ├── dq/                     ← DQ engine, rules, rejection reporter
│   ├── transform/              ← Transform engine, lookup resolver, cleanse ops
│   ├── merge/                  ← MergeEngine, SQL builder, 4 built-in strategies
│   ├── plugins/                ← Rule/Transform/Merge registries + file & npm loaders
│   └── utils/                  ← logger (pino), errors, env helpers
├── tests/
│   ├── fixtures/               ← sample pipeline YAMLs, CSV/rules data, plugin files
│   ├── unit/                   ← unit tests (all I/O mocked)
│   └── integration/            ← real DuckDB :memory: + CSV fixtures
└── clients/                    ← 🙈 gitignored — each client has their own repo
    ├── acme-corp/                ← Acme Corp pipelines
    └── style-co/                  ← Style Co pipelines

⚙️ Environment Variables

Connection strings and credentials live in .env (never in YAML files, never in Git).

# .env
SOURCE_MSSQL=mssql://user:[email protected]/LegacyDB
BC_BASE_URL=https://api.businesscentral.dynamics.com/v2.0
BC_TENANT_ID=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
BC_CLIENT_ID=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
BC_CLIENT_SECRET=your-secret-here
BC_COMPANY=Example Company Ltd

Reference them in YAML with ${ENV_VAR} — resolved at runtime, never stored in config:

source:
  adapter: mssql
  connection: ${SOURCE_MSSQL}

🧩 Phase 2: Extension System

Phase 2 adds a three-tier plugin system so you can extend Sluice without touching the core engine.

Tier 1 — Composite Rules (YAML) 📋

Name a bundle of checks in a shared rules file and reference them like built-ins:

# shared/rules.yaml
rules:
  - id: style-coStyleNo
    checks:
      - { type: notNull,   severity: critical }
      - { type: pattern,   value: "^[A-Z]{2}[0-9]{4}$", severity: critical }
      - { type: maxLength, value: 6, severity: critical }

# In your pipeline:
dq:
  rulesFile: ../../shared/rules.yaml
  rules:
    - field: STYLE_NO
      checks:
        - { type: style-coStyleNo }   # expands to the three checks above ✨

Tier 2 — Plugin Files (TypeScript) 🔌

Drop a *.rule.ts, *.transform.ts, or *.merge.ts file into a plugins/ folder next to your pipeline YAMLs. Auto-discovered at startup:

// plugins/ukVatNumber.rule.ts
export const rule: RulePlugin = {
  id: 'ukVatNumber',
  validate(value, config, rowIndex, field) {
    const valid = /^GB([0-9]{9}|[0-9]{12}|(GD|HA)[0-9]{3})$/.test(String(value));
    return valid ? null : { field, rowIndex, value, rule: 'ukVatNumber',
      severity: config.severity, message: 'Invalid UK VAT number' };
  }
};

Tier 3 — npm Packages 📦

When plugins are useful across multiple clients, promote them to scoped npm packages and declare them in sluice.config.yaml:

# sluice.config.yaml
plugins:
  - package: "@caracal-lynx/etl-rules-uk"
  - package: "@caracal-lynx/etl-rules-fashion"
  - package: "@caracal-lynx/etl-transform-ifs"

All three tiers use the same registry interfaces and are invoked identically by the engines. The engine doesn't know or care which tier a rule came from. 🤷

List Loaded Plugins

sluice plugins

# Include extra plugin directories outside the pipeline folder
sluice plugins --plugins ./shared/plugins ./team/plugins

Output:

📋 Data Quality Rules:
  • ukVatNumber
  • bcAccountCode
  • iso8601Date

🔄 Transform Operations:
  • slugGenerator
  • normalizeCompanyName
  • fixedDecimal

🔀 Merge Strategies:
  • coalesce
  • priority-override
  • union
  • intersect

Getting Started with Plugins

Detailed guide: PLUGINS.md

• Create a custom DQ rule
• Create a custom transform operation
• Create a custom merge strategy
• Package plugins as npm packages
• Test and debug plugins
• Real-world examples

🔀 Multi-Source Merge

Phase 3 lets a single pipeline extract from 2+ sources and merge them on a key column before DQ and transform. Useful when the master record for an entity is scattered across systems — master data in SQL Server, pricing enrichment in an Excel sheet, product descriptions in a REST API, and so on.

Built-in merge strategies

| Strategy | Behaviour | When to use | |---|---|---| | coalesce | First non-null value wins (priority-ordered; whitespace treated as blank) | Enriching a primary source with fallback data from lower-priority sources | | priority-override | Highest-priority source wins, even if null or blank | Strict priority — the trusted source is the trusted source, full stop | | union | All rows from all sources, deduplicated by key | Combining independent datasets (e.g. multi-warehouse inventory) | | intersect | Only rows present in all sources | Reconciliation / "find the records that agree" |

Custom strategies can be dropped in as *.merge.ts plugins or shipped as npm packages — same three-tier model as DQ rules and transforms.

A minimal multi-source pipeline

pipeline:
  name: style-co-products-merged
  client: style-co
  version: "1.0"
  entity: Style

sources:
  - id: sql-server              # staging table: stg_raw_sql-server
    priority: 1                 # lower = higher precedence
    adapter: mssql
    connection: ${SOURCE_2_MSSQL}
    query: "SELECT STYLE_NO, STYLE_DESC, COST_PRICE FROM dbo.Styles WHERE Active = 1"

  - id: excel
    priority: 2
    adapter: xlsx
    file: ./data/product-data.xlsx
    sheet: "Products"
    rename:                     # applied in-place after extract, before DQ
      Style Number: STYLE_NO
      Description: STYLE_DESC
      Fibre: FIBRE_CONTENT

merge:
  key: STYLE_NO                 # single column or array for composite keys
  strategy: coalesce
  onUnmatched: include          # include | exclude | warn | error
  fieldStrategies:              # per-field overrides
    - { field: FIBRE_CONTENT, source: excel }          # pin to one source
    - { field: COST_PRICE,    strategy: priority-override }
  conflictLog: ./output/style-co-products-conflicts.csv   # optional CSV of field disagreements

dq:
  stopOnCritical: true
  rules:
    - field: STYLE_NO           # 🎯 pre-merge: scoped to one source
      sourceId: sql-server
      checks: [ { type: notNull, severity: critical }, { type: unique, severity: critical } ]
    - field: STYLE_DESC         # 🎯 post-merge: runs against stg_merged
      checks: [ { type: notNull, severity: critical } ]

transform: { ... }
target:    { ... }

Pre-merge rules (sourceId: …) run against each source's staging table before merging and generate per-source rejection CSVs (suffixed -{sourceId}). Post-merge rules (no sourceId) run once against stg_merged.

Incremental multi-source

merge:
  incrementalSource: sql-server   # must match a source id; required in incremental mode
run:
  mode: incremental
  incrementalField: UPDATED_AT

Only the named source is filtered by timestamp; other sources run full each time. The state file gains a per-source sources block tracking each source's last run time.

Inspect merge strategies

sluice merge list-strategies        # ids + descriptions for all registered strategies
sluice merge info coalesce          # details for one strategy

A full working example lives at tests/fixtures/style-co-products-merged.pipeline.yaml.

🧪 Testing

npm test           # run tests once
npm run test:watch # watch mode (great for TDD)
npm run test:cov   # with coverage report

• Unit tests mock all I/O with vi.mock — no live databases required
• Integration tests use real DuckDB (:memory:) with CSV fixtures
• Target: 80% line coverage across src/dq/ and src/transform/
• CI runs on ubuntu-latest via GitHub Actions

🏗️ Development

npm run build      # tsc compile
npm run dev        # tsx watch src/cli.ts (live reload)
npm run lint       # eslint
npm run format     # prettier

# Pretty logs in dev:
npm run dev -- run customers.pipeline.yaml | npx pino-pretty

Note: Uses tsx, not ts-node. Path aliases work correctly on Windows without extra configuration. 🪟

🚫 Things Sluice Is Not

• ❌ A web application or dashboard (there's no UI — this is a good thing)
• ❌ A streaming / real-time ingestion platform
• ❌ A data warehouse
• ❌ A multi-tenant SaaS product
• ❌ An excuse to use eval() anywhere

🏢 Sluice + Caracal Lynx Professional Services

The Sluice core CLI is open-source and free to use. Caracal Lynx offers additional paid services built on top of it:

| Service | What it is | |---|---| | Enrichment Service | Async API lookups (EU VAT, UK VAT, trade tariff) — fills gaps in source data | | Application Adapters | Pre-built ERP adapters (IFS, Business Central, BlueCherry) | | Domain Rule Packages | UK compliance rules, fashion/retail data standards | | Client-Specific Plugins | Bespoke plugins tailored to your source system and data model | | Sluice MCP Server 🚧 | AI-assisted migration using Claude — agentic pipeline authoring, live schema inspection, automatic DQ iteration. Coming soon — Phase 9. | | Migration Delivery | Full end-to-end data migration, delivered by Caracal Lynx |

📧 [email protected] 🌐 caracallynx.com

🤝 Community

• 🐛 Report a bug or request a feature
• 💬 Ask a question or share a use case
• 🤲 Contributing guide
• 🤝 Code of Conduct

🔐 Security

Found a vulnerability? Please do not open a public issue. See SECURITY.md for the disclosure process — [email protected], 48-hour acknowledgement, 90-day disclosure SLA.

⚖️ Licence

Sluice is licensed under the Elastic Licence 2.0. See docs/licensing-faq.md for a plain-English explainer of what you can and can't do with it. Short version: use it freely for your own data migrations; don't resell it as a hosted service or strip the licence headers.

🏷️ About

Built and maintained by Caracal Lynx Limited (SC826823) — Gretna, Scotland.

npm package:  @caracal-lynx/sluice
owner:        Caracal Lynx Limited (SC826823)
author:       Michael Scott
maintainers:  Michael Scott, Carolyn Scott, Andrew Scott, Duncan Scott

Clean data flows through. 💧