ETL Professional Features

The ETL family covers the pipeline node types. This page documents the professional-grade capabilities behind them: reference-data lookups (exact and fuzzy), slowly changing dimensions, change data capture, aggregation and reshaping, the expression engine, data profiling and per-node run metrics.

Lookups
Slowly Changing Dimensions
Change Data Capture
Aggregate, Pivot and Unpivot
Expression Engine
Data Profiling
Pipeline Metrics

Lookups

ETLLookup — Reference Data Enrichment

Joins the incoming stream against a reference source (table, query or in-memory set) and returns selected columns. It renders as a double-cylinder shape (main data + reference data).

Property	Type	Description
`CacheMode`	`CacheMode`	`None`, `Partial` or `Full` — how much of the reference set is cached in memory.
`MatchColumns`	`string`	Columns used as the join key.
`ReturnColumns`	`string`	Columns to add to the output row.
`FailOnNoMatch`	`bool`	When true, an unmatched row fails the pipeline instead of passing through.
`OutputSchema`	`string`	Declared schema of the enriched output, used for downstream validation.

ETLFuzzyLookupNode — Approximate Matching

Matches when keys differ slightly (typos, formatting, transliteration). Similarity is computed with one of four algorithms and gated by a threshold:

Property	Type	Description
`Algorithm`	`FuzzyAlgorithm`	`Levenshtein` (default), `Soundex`, `Metaphone` or `JaroWinkler`.
`Threshold`	`double`	Minimum similarity in `[0, 1]`; default `0.8`.
`MatchColumn`	`string`	Incoming column to match.
`ReferenceColumn`	`string`	Reference column to compare against.

Slowly Changing Dimensions

ETLScd implements the three classic dimension strategies:

Property	Type	Description
`Type`	`ScdType`	`Type1` (overwrite), `Type2` (historical rows with effective dates), `Type3` (previous-value columns).
`BusinessKeys`	`string`	Natural keys that identify a dimension member.
`ChangeTrackingColumns`	`string`	Columns whose changes create a new version.
`EffectiveFromColumn` / `EffectiveToColumn`	`string`	Validity window columns used by Type 2.
`CurrentFlagColumn`	`string`	Current-row indicator column.
`TableName`	`string`	Target dimension table.

Change Data Capture

ETLCdcNode extracts changed rows since the last run:

Property	Type	Description
`Method`	`CdcMethod`	`Timestamp` (default), `Version`, `FullRefresh` or `LogBased`.
`TrackingColumn`	`string`	Column used by timestamp/version detection; default `ModifiedAt`.
`ChangeIndicator`	`string`	Output column carrying the operation (insert/update/delete); default `__CDC_Operation`.

Aggregate, Pivot and Unpivot

Node	Purpose
`ETLAggregate`	Group-by with aggregate functions (sum, count, min, max, average).
`ETLPivot`	Rows to columns using a pivot key and value column.
`ETLUnpivot`	Columns to rows (normalization).
`ETLSort`	Ordering by one or more columns, ascending or descending.
`ETLDerivedColumn`	Adds computed columns from expressions (see below).
`ETLFilter`	Row predicate using the expression engine.
`ETLConditionalSplit`	Routes rows to different outputs by predicate.
`ETLJoin` / `ETLMerge` / `ETLMulticast`	Relational join, union-style merge, and fan-out to multiple consumers.
`ETLSurrogateKeyNode`	Generates surrogate keys for dimension loading.
`ETLRowCount`	Counts rows and exposes the value downstream.
`ETLScript`	Custom scripted transform for cases the built-in nodes do not cover.

Expression Engine

ExpressionEngine evaluates SQL-like expressions over a row dictionary. It is the engine behind derived columns, filters and conditional splits, and it is usable directly:

var engine = new ExpressionEngine();

var row = new Dictionary<string, object>
{
    ["Amount"] = 125.50m,
    ["Country"] = "NL",
    ["Discount"] = 0.15
};

// Scalar result
object total = engine.Evaluate("Amount * (1 - Discount)", row);
Console.WriteLine(total);   // 106.675

// Boolean predicate (for filters and splits)
bool highValue = engine.EvaluateBoolean("Amount > 100 AND Country = 'NL'", row);

Invalid expressions raise ExpressionException with the failing token position, which the host surfaces in the expression tester dialog. See Automation Data Sources for how expressions integrate with datasource-backed nodes.

Data Profiling

DataProfiler produces per-column statistics you can inspect before designing a pipeline (or attach to a run for data-quality reporting):

Member	Type	Description
`Name`	`string`	Column name.
`Count` / `NullCount` / `DistinctCount`	`int`	Row count, nulls and distinct values.
`Min` / `Max` / `Average` / `Sum`	`object` / `double?`	Numeric and date statistics when applicable.
`MinLength` / `MaxLength`	`int?`	String length range.
`SampleValues`	`List<object>`	Representative values for display.
`IsNumeric` / `IsDateTime`	`bool`	Inferred type flags.
`NullPercentage(rowCount)`	`double`	Null share as a percentage.

Pipeline Metrics

Each node run can record NodeRunMetrics, collected in a PipelineMetrics instance for the whole run:

var metrics = new PipelineMetrics();

// Recorded per node by the pipeline runner
metrics.Nodes.Add(new NodeRunMetrics
{
    NodeName = "LookupCustomers",
    RowsIn = 12000,
    RowsOut = 11840,
    Elapsed = TimeSpan.FromMilliseconds(184),
    Status = "Completed"
});

foreach (var node in metrics.Nodes)
    Console.WriteLine($"{node.NodeName}: {node.RowsIn} in / {node.RowsOut} out, " +
                      $"{node.RowsPerSecond:0} rows/s ({node.Status})");

RowsPerSecond is derived from RowsOut and Elapsed. Metrics pair with the workflow engine's run results to give end-to-end throughput reporting; see Workflow Engine.

Table of Contents