¶ Description
Filter Rows based on a set of "Reference" Keys.
¶ Parameters
Parameters:
- Key column in master table on 1st pin
- Key column in refrence table on 2nd pin
- Check uniqueness of primary key in the reference (slave)
- Truncate table on first failed row
¶ About
The FilterOnKey action filters rows from a master table (first input pin) based on the presence of matching key values in a reference table (second input pin). It behaves similarly to a MultiJoin, but is specifically optimized for filtering based on key existence.
¶ Functionality
- The reference table (second pin) is fully loaded into RAM at runtime.
- The master table (first pin) is scanned and filtered: only rows with matching keys in the reference table are retained.
- Keys must be unique in the reference (slave) table; otherwise, unexpected results may occur.
- Duplicate key detection is enabled by default but can be disabled if performance is a concern.
¶ Parameters
-
Key column in master table on 1st pin (0)
Select the column from the master table to match against. -
Key column in reference table on 2nd pin (1)
Select the column in the reference table that contains the keys to filter on. -
Check uniqueness of primary key in the reference table (slave)
Verifies that the reference table does not contain duplicate keys.
This is recommended to ensure correct results. Disable only if duplicates are guaranteed to be impossible. -
Truncate table on first failed row
If enabled, the output table is cleared immediately upon encountering the first row that causes a join/filter failure (e.g., no match found).
¶ Performance Considerations
- Since the reference table is loaded entirely into memory, large datasets may significantly increase RAM usage.
- Disabling key uniqueness checking can improve performance slightly but may risk incorrect filtering if duplicates exist.
¶ Data Quality Tip
If duplicate keys are found in the reference table, use the NaïveDeDuplicate action to clean the data before using FilterOnKey.
¶ Use Case
Use FilterOnKey when:
- You need to filter a large dataset by a list of allowed or valid keys.
- Performing a simplified, memory-efficient alternative to a join.
- Ensuring that only rows with validated foreign keys are passed through.
