FK-Resolved Dependencies

Overview	How It Works	Temporal Segmentation	Fan-In Aggregation
SQL Patterns	Fill Forward	Time Filters	Examples
Triggers & Performance	Unresolved FK Deps	Copy & Reconcile	UI Configuration
Back to Derived Streams

We're not aware of another platform that does this declaratively. InfluxDB, TimescaleDB, Apache Flink, KDB+, Snowflake, Databricks, and Palantir Foundry all support time-series storage and (in most cases) temporal joins. None of them auto-segment a derivation when an FK relationship changes — on each of those platforms, that requires custom pipeline code to detect change points, split time ranges, re-resolve targets, and stitch results back together. GroveStreams handles it as a single SQL expression on the variable definition.

Overview

Standard expression variables point directly at a specific stream (component + stream UID). FK-resolved dependencies replace that static pointer with a SQL statement that dynamically resolves which stream to use at derivation time.

This enables two powerful capabilities:

• Temporal relationship resolution — When an FK relationship changes over time (a meter moves from Customer A to Customer B), the engine automatically segments the derivation range and uses the correct target for each period.
• Fan-in aggregation — When a SQL resolves to multiple targets (all meters connected to a customer), the engine loads all target streams, aggregates them (SUM, AVG, MIN, MAX), and feeds a single result into the expression.

Both capabilities use the same Resolution SQL field on the variable definition. The derivation engine decides the behavior based on the SQL results and the Fan Aggregator setting.

How It Works

The Data Model

FK-resolved dependencies involve two distinct streams that serve different roles:

Stream	Role
Link stream	A stream on the source component whose value is the target component's primary key (component id). It defines the relationship — who is connected to whom. The derivation engine reads the link stream's history to detect when relationships changed over time. The link stream's type matches the target's PK type (STRING for string keys, LONG for numeric keys, etc.). This mirrors standard database FK behavior — a FK column holds the PK value of the referenced row. Example: A meter's customerId stream stores the customer component's id (e.g. "C123") this meter is currently assigned to. A salaries component's emp_no stream stores the employee's numeric PK (e.g. 10001 as LONG). Legacy UUID form: prior to 2026-04, link streams were required to be STRING and held the target's component UUID directly. That form still works in orgs where the target components' _component_id was auto-set to the UUID (the GS default when no explicit id is provided).
Value stream	A stream on the target (resolved) component that provides the actual data used in the expression. This is the stream whose data becomes the variable's value in the formula. Example: The customer's energy_rate stream — its values are what the expression engine sees when evaluating the rate variable.

The Resolution SQL bridges these two streams: it follows the link stream to find the target component, and specifies which value stream to load from that component.

The required data model pattern:

1. An entity (component) has a link stream whose value is the primary key (component id) of a related entity. The link stream's type matches the target's PK type: STRING for string keys, LONG for numeric keys. For example, a meter component has a customerId stream (STRING) storing the customer's id. A JDBC-imported salaries component has an emp_no stream (LONG) storing the numeric employee PK.
   
   Important: It is highly recommended that link streams be Regular (random) streams rather than interval streams. Regular streams only store data points when the value actually changes, making them ideal for relationship tracking. Interval streams can be used but are not recommended because they store a value per interval, which requires the engine to walk backward through the history in chunks to detect relationship changes.
2. The link stream's FK target template is configured (via REFERENCES targetTemplate in DDL, or the Stream Editor) to tell the system which template the linked entity belongs to.
3. The target entity (e.g., the customer) has one or more value streams (e.g., energy_rate, credit_limit) that the derivation expression needs. The value stream ID is specified in the Resolution SQL.

Resolution SQL Variables

Two variables are injected into the namespace of the resolution SQL before it executes. Pick whichever matches the shape of your link stream or join column:

@_component_uid	The UUID of the component being derived. Use this when the link stream on the source component holds target UUIDs (legacy form), or when joining against the target's _component_uid column directly. Example: SELECT customerUid, 'rate' FROM meter WHERE _component_uid = @_component_uid
@_component_id	The component id (human-readable PK) of the component being derived. Use this for standard DB-style FK resolution where the link stream holds the target's PK value (STRING or numeric). Example: SELECT _component_uid, 'kwh' FROM meter WHERE customerId = @_component_id — a fan-in from a customer to all meters pointing at that customer's id.

Resolution Flow

Single-Target Resolution (Standard FK Dep)

When the SQL resolves to exactly one target component, the engine loads the value stream from that component and uses its data as the variable's value in the expression. This is the most common case — a meter resolving to its one customer, a sensor to its one production line.

Multi-Target Resolution (Fan-In)

When the SQL resolves to multiple targets, the Fan Aggregator controls how they combine:

Fan Aggregator	Behavior
NONE	Validation error if more than one target resolves. This is the default — it enforces single-target resolution.
SUM	Sums all targets' values at each time point. Gaps (null values) are excluded from the sum.
AVG	Averages all targets' values at each time point. Only non-gap values participate. Average = SUM / count of non-gap values.
MIN	Takes the minimum value across all targets at each time point.
MAX	Takes the maximum value across all targets at each time point.
CARDINALITYCOUNT	Returns the total number of resolved target value streams. This is a constant value at every time point — it does not depend on whether targets have data or gaps. Use this as a denominator for coverage or per-unit calculations.
NONNULLCARDINALITYCOUNT	Returns the number of resolved target value streams that have non-null data at each time point (after fill-forward, if enabled). This varies per time point — targets with gaps contribute zero to the count. Use this for data quality and completeness calculations.

Temporal Segmentation

When an FK relationship changes over time, the derivation engine splits the derivation range into segments — one per stable relationship period — and derives each segment independently.

Single FK Dep

Consider a meter that was connected to Customer A from January through June, then moved to Customer B in July:

The engine:

1. Loads the customerUid link stream's history to detect relationship changes
2. Finds the change point at July
3. Creates two segments: Jan–Jun and Jul–Dec
4. Derives each segment using the correct customer's value stream data
5. Stitches the results into one continuous derived stream

Multiple FK Deps

When an expression has multiple FK variables (e.g., both a customer rate and a supplier margin), the engine intersects all variables' segment boundaries. It finds time ranges where all FK relationships are stable, then derives each unified segment with the correct targets for every variable.

Example: A meter with two FK deps — one to a customer that changes in April, one to a supplier that changes in July:

Period	Customer	Supplier	Segment
Jan – Mar	Customer A	Supplier 1	Segment 1
Apr – Jun	Customer B	Supplier 1	Segment 2
Jul – Dec	Customer B	Supplier 2	Segment 3

Multi-Hop Chains

FK resolution supports chains of any depth. For example, meter → customer → supplier. At each hop, the engine loads the link stream's history to detect when that relationship changed. Change points at any level in the chain create segment boundaries. The value stream is always loaded from the final resolved component in the chain.

Fan-In Aggregation

Fan-in is a reverse FK pattern. Instead of "which customer does this meter belong to?" (one-to-one), fan-in asks "which meters belong to this customer?" (one-to-many) and aggregates their data.

How Fan-In Works

The fan-in aggregation happens before the expression is evaluated. From the expression's perspective, a fan-in variable looks just like any other variable — it has one value per time point.

Fan-In with Fill Forward

Fill Forward is applied to each target stream individually before aggregation. This ensures that targets with sparse data (e.g., a meter that reports hourly while others report every 15 minutes) contribute their last known value to every time point, rather than creating gaps in the aggregation.

Fan-In and Gap Handling

At each time point, only non-gap (non-null) values participate in the aggregation:

• SUM — sums all non-null values
• AVG — sums non-null values and divides by the count of non-null values
• MIN / MAX — considers only non-null values
• If all targets are null at a time point, the aggregated result is null

Cardinality Counts

Two fan aggregator types return counts of resolved targets rather than aggregated values:

• CARDINALITYCOUNT — the total number of resolved target value streams. This is a constant at every time point. For example, if 5 meters resolve to a customer, CARDINALITYCOUNT = 5 at every interval.
• NONNULLCARDINALITYCOUNT — the number of resolved target value streams that have non-null data at each time point (after fill-forward, if enabled). This varies per interval — if 2 of the 5 meters have gaps at a given interval, NONNULLCARDINALITYCOUNT = 3 at that interval.

These are useful as denominators in expressions. For example, to calculate per-unit averages or data coverage percentages.

Example — Data Coverage Calculation:

A customer has 5 meters reporting kWh every hour, with a derived stream that calculates daily data coverage. The expression uses three fan-in variables, all resolving the same SQL but with different settings:

Variable	Cycle	Function	Fan Aggregator	Result
nongap	day	NONGAPCOUNT	SUM	Total non-gap hourly intervals across all meters (e.g., 108)
total	day	INTVLCOUNT	SUM	Total possible hourly intervals across all meters (5 × 24 = 120)
meter_count			CARDINALITYCOUNT	Number of meters (5)

Expression: nongap / total * 100 → 90% coverage
Or: nongap / (meter_count * 24) * 100 → same result, using cardinality as the denominator

Note that nongap and total use the existing rollup function field (NONGAPCOUNT, INTVLCOUNT) combined with a SUM fan aggregator to sum those counts across all targets. This is the same as using NONGAPCOUNT and INTVLCOUNT on non-FK variables — the fan aggregator simply controls how the N targets' values combine.

Fan-in vs. Aggregation Derivation: Fan-in aggregates streams as a variable inside an expression, so the aggregated result can be combined with other variables in a formula. Aggregation Derivation is a separate derivation type that aggregates streams directly into a result stream with no expression. Use fan-in when you need the aggregated value in a calculation. Use Aggregation Derivation when you just need the aggregate itself.

Resolution SQL Patterns

The Resolution SQL field accepts TEQ (Temporal Entity Query) SELECT statements. The SQL follows a nested subquery pattern where each SELECT level resolves one hop in the FK chain.

Standard Pattern (One-to-One)

Hops	SQL Pattern
1	SELECT linkStreamId, 'valueStreamId' FROM template WHERE _component_uid = @_component_uid linkStreamId = the link stream that resolves to the target component. 'valueStreamId' = the value stream on the target whose data is used in the expression.
2	SELECT linkStreamId2, 'valueStreamId' FROM template2 WHERE _component_uid = (SELECT linkStreamId1 FROM template1 WHERE _component_uid = @_component_uid) Each nested SELECT follows a link stream. The outermost SELECT specifies the value stream.
3+	Continue nesting. The innermost SELECT (containing @_component_uid) starts from the current component.

Fan-In Pattern (One-to-Many)

Fan-in uses the same SQL syntax, but the query naturally returns multiple rows. The key difference is that the FK relationship is reversed — the WHERE clause filters by a FK column instead of cuid:

Direction	SQL Pattern
Reverse 1-hop	SELECT _component_uid, 'targetStreamId' FROM childTemplate WHERE parentFkStreamId = @_component_uid This finds all components of childTemplate whose parentFkStreamId points to the current component, then reads targetStreamId on each.
Reverse through parent	SELECT _component_uid, 'targetStreamId' FROM childTemplate WHERE parentFkStreamId = (SELECT parentFkStreamId FROM currentTemplate WHERE _component_uid = @_component_uid) First resolves the current component's parent, then finds all children of that parent.

SQL pattern requirements: The first column of each SELECT is the link stream ID (or cuid for reverse lookups) — this is the relationship pointer the engine follows to find the target component. The second column of the outermost SELECT (as a string literal in single quotes) is the value stream ID — the stream on the resolved target component whose data will be used as the variable's value in the expression. JOINs and other SQL constructs are not supported in the resolution SQL — use nested subqueries exclusively.

Fan Aggregator required: If a Resolution SQL returns more than one target and the Fan Aggregator is set to NONE, the engine throws a validation error. You must set Fan Aggregator to SUM, AVG, MIN, MAX, CARDINALITYCOUNT, or NONNULLCARDINALITYCOUNT for queries that can resolve to multiple targets.

Fill Forward with FK Deps

Fill Forward is enabled by default for FK-resolved variables. This is almost always what you want, because FK targets typically have data at different cadences than sibling streams.

Example: A meter's kwh stream reports every 15 minutes. The customer's energy_rate changes once a quarter. Without Fill Forward, the rate would only contribute a value at the four timestamps per year when it changes. With Fill Forward, the rate is carried forward to every 15-minute interval.

Fill Forward and Fan-In

For fan-in variables, Fill Forward is applied to each target stream individually before aggregation. This is important when targets report at different cadences:

Without Fill Forward, the SUM at intervals 2 and 4 would only include Meter B's values (10 and 26 respectively), producing an inconsistent total that drops whenever a meter has a gap.

Fill Forward with Rollup Cycles

Fill Forward works with variables that have a rollup cycle and function. The fill-forward seed is the rolled-up value from the prior interval, not the raw last sample. For example, if a variable uses MAX over an hourly cycle, the seed is the MAX from the previous hour.

Restrictions

Fill Forward is not available for:

• Point stream variables — point streams have no time dimension

See Fill Forward on the main Derived Streams page for full details.

Limits and Constraints

The following limits protect derivation performance and prevent misconfigured streams from consuming excessive resources:

Limit	Value	Description
FK relationship changes per link stream	500	Maximum number of distinct relationship changes in a single link stream's history. Each change creates a temporal segment that loads and derives independently. Link streams are intended for infrequent changes (e.g., a meter reassigned to a different customer a few times per year). If this limit is exceeded, derivation throws an error suggesting a Regular (random) stream be used.
Variables per expression	15	Maximum number of dependent variables in a single derivation expression. This includes both static and FK-resolved variables. Configurable via MAX_DERIVED_INTVL_DEPENDENTS.
Intervals in memory	550,000	Maximum number of intervals that can be held in memory during a single derivation. For fan-in, this applies to the total across all resolved targets (N targets × intervals per target). Configurable via MAX_DERIVATION_INTVLS.

Interval streams as link streams: When an interval stream is used as a link stream, the engine walks backward through the history in chunks of 10,000 intervals to detect relationship changes, rather than loading the entire history. This prevents memory issues but is slower than using a Regular (random) stream, which only stores data points when the value actually changes. The 500-change limit applies to both stream types.

Throttling and scheduling for FK deps: FK-resolved dependencies — especially fan-in aggregations — can be expensive because they load multiple value streams and aggregate them at each time point. If derivation repeatedly takes longer than 30 seconds or throws errors, automatic derivation is throttled (paused) after 3 consecutive issues. Set up a Derivation Schedule on the template to run these streams at fixed intervals instead of on every data arrival. Scheduled streams continue to derive even when throttled. See the Throttling and Scheduling section for details.

Time Filters

Stream time filters are fully respected by FK-resolved dependencies. When a dependent variable has a time filter configured, the filter is applied to the loaded data before the variable enters the expression.

For fan-in variables, the time filter applies to each target stream individually. Filtered-out intervals are treated as gaps (null). If Fill Forward is enabled, the fill-forward logic respects the filter — only non-filtered values are carried forward.

Configuration Examples

Each example below shows the variable grid (as configured in the derivation panel), the expression, and the Resolution SQL for each FK variable. The variable grid columns shown are those relevant to FK deps; hidden columns (Reconcile Lock, etc.) are omitted.

Two ways to configure FK deps:

UI: In the stream editor's derivation panel, show the hidden columns (Resolution SQL, Fan Aggregator, Fill Forward) by right-clicking a column header. Add a variable, enter a name, set the Resolution SQL, and optionally set the Fan Aggregator. The Component and Stream columns will show "(resolved by SQL)" for FK variables.

DDL: In TEQ DDL, use the VIA clause in DEPENDS ON. The engine generates the Resolution SQL from the chain syntax. See the TEQ DDL reference.

Example 1: Energy Billing (Interval Stream, Single FK Dep)

Scenario: A utility company has meters and customers. Each meter has a customerUid link stream pointing to its assigned customer. The customer has an energy_rate stream (price per kWh) that changes periodically. A derived stream on each meter calculates the billing amount: kwh * rate.

Templates:

• meter — streams: kwh (interval, hourly), customerUid (link to customer), billed (derived)
• customer — streams: energy_rate (interval or rdm)

Variable Grid:

Name	Component	Stream	Cycle	Function	Resolution SQL	Fan Agg	Fill Fwd
kwh	(this component)	kwh
rate	(resolved by SQL)	(resolved by SQL)			SELECT customerUid, 'energy_rate' FROM meter WHERE _component_uid = @_component_uid	NONE	Yes

TEQ DDL equivalent:

CREATE TABLE meter (
    kwh DOUBLE INTERVAL BASE_CYCLE 'hour',
    customerUid STRING REFERENCES customer,
    billed DOUBLE DERIVED AS 'kwh * rate'
        DEPENDS ON (kwh FROM kwh, rate VIA customerUid.customer.energy_rate FILL_FORWARD)
)

What happens at derivation:

1. The engine reads the customerUid stream on this meter
2. If the value changed during the derivation range (meter moved to a different customer), the range is split into segments
3. For each segment, the customer's energy_rate is loaded and fill-forwarded
4. The expression kwh * rate is evaluated at each interval using the correct rate

Example 2: Regulatory Compliance Threshold (RDM Stream, Multi-Hop FK)

Scenario: A pharmaceutical company tracks temperature in cold-storage rooms. Each room is assigned to a facility, and each facility is assigned to a regulatory region. The regulatory region has a max_temp_threshold that changes when regulations are updated. A derived stream on each room calculates whether the room exceeds its regional threshold.

Templates:

• cold_room — streams: temperature (rdm, samples at irregular intervals), facilityUid (link), temp_exceedance (derived)
• facility — streams: regionUid (link)
• region — streams: max_temp_threshold (rdm)

Variable Grid:

Name	Component	Stream	Resolution SQL	Fan Agg	Fill Fwd
temp	(this component)	temperature
threshold	(resolved by SQL)	(resolved by SQL)	SELECT regionUid, 'max_temp_threshold' FROM facility WHERE _component_uid = (SELECT facilityUid FROM cold_room WHERE _component_uid = @_component_uid)	NONE	Yes

TEQ DDL equivalent:

CREATE TABLE cold_room (
    temperature DOUBLE,
    facilityUid STRING REFERENCES facility,
    temp_exceedance DOUBLE DERIVED AS 'if(temp > threshold, temp - threshold, 0)'
        DEPENDS ON (
            temp FROM temperature,
            threshold VIA facilityUid.facility.regionUid.region.max_temp_threshold FILL_FORWARD
        )
        ALLOW_NULLS
)

Why this works with RDM streams: The temperature stream reports at irregular intervals (whenever the sensor samples). The max_temp_threshold stream changes only when regulations update (perhaps once a year). Fill Forward carries the threshold forward to every temperature sample timestamp. If the room is reassigned to a different facility (or the facility to a different region), the engine segments the derivation automatically.

Example 3: Customer Total Consumption (Interval Stream, Single Fan-In)

Scenario: A utility customer has multiple meters. The customer needs a total_kwh derived stream that sums the kwh values from all connected meters. When meters are added or removed, the total automatically adjusts.

Templates:

• meter — streams: kwh (interval, hourly), customerUid (link to customer)
• customer — streams: total_kwh (derived, hourly)

Variable Grid (on the customer's total_kwh stream):

Name	Component	Stream	Resolution SQL	Fan Agg	Fill Fwd
meter_kwh	(resolved by SQL)	(resolved by SQL)	SELECT _component_uid, 'kwh' FROM meter WHERE customerUid = @_component_uid	SUM	Yes

How the Resolution SQL works: The SQL asks "find all meter components whose customerUid stream points to the current customer (@_component_uid), then read each meter's kwh stream." If 5 meters are connected, the engine loads all 5 kwh streams, fill-forwards each, sums them at each hourly interval, and puts the single summed value into the meter_kwh variable.

The expression is just meter_kwh — a pass-through. The fan-in aggregation happens before the expression is evaluated. You could also combine the fan-in result with other variables: meter_kwh * rate_adjustment.

Example 4: Portfolio Risk Score (RDM Stream, Multiple Fan-Ins)

Scenario: A financial services company manages portfolios of trading instruments. Each portfolio tracks a risk score derived from the maximum volatility and average return across all instruments in the portfolio. Instruments are assigned to portfolios via a link stream, and assignments can change (instruments rebalanced between portfolios).

Templates:

• instrument — streams: portfolioUid (link), volatility (rdm), daily_return (rdm)
• portfolio — streams: risk_score (derived)

Variable Grid (on the portfolio's risk_score stream):

Name	Component	Stream	Resolution SQL	Fan Agg	Fill Fwd
max_vol	(resolved by SQL)	(resolved by SQL)	SELECT _component_uid, 'volatility' FROM instrument WHERE portfolioUid = @_component_uid	MAX	Yes
avg_ret	(resolved by SQL)	(resolved by SQL)	SELECT _component_uid, 'daily_return' FROM instrument WHERE portfolioUid = @_component_uid	AVG	Yes

What happens:

• The max_vol variable resolves to all instruments in this portfolio, loads each instrument's volatility stream, and takes the MAX across all instruments at each time point.
• The avg_ret variable resolves to the same instruments but loads their daily_return streams and computes the AVG.
• Both aggregated values enter the expression as single variables. The expression computes the risk ratio.
• If an instrument is moved to a different portfolio (its portfolioUid changes), the next resolution cycle detects the change and adjusts which instruments are included.

Example 5: Fleet Efficiency with Supplier Benchmark (Mixed FK Deps)

Scenario: A logistics company manages a fleet of vehicles. Each vehicle tracks fuel consumption and is assigned to a depot. The depot is assigned to a fuel supplier that publishes a benchmark efficiency rate. A derived stream on each vehicle computes its efficiency relative to the supplier's benchmark.

Templates:

• vehicle — streams: fuel_consumption (interval, daily), distance (interval, daily), depotUid (link), efficiency_vs_benchmark (derived)
• depot — streams: supplierUid (link)
• fuel_supplier — streams: benchmark_rate (rdm, changes quarterly)

Variable Grid:

Name	Component	Stream	Resolution SQL	Fan Agg	Fill Fwd
fuel	(this component)	fuel_consumption
dist	(this component)	distance
benchmark	(resolved by SQL)	(resolved by SQL)	SELECT supplierUid, 'benchmark_rate' FROM depot WHERE _component_uid = (SELECT depotUid FROM vehicle WHERE _component_uid = @_component_uid)	NONE	Yes

This computes the vehicle's actual km/liter efficiency as a percentage of the supplier's benchmark. If the vehicle transfers to a different depot (or the depot switches suppliers), the derivation automatically segments and uses the correct benchmark for each period.

Example 6: Building Zone Average Temperature (Interval Stream, Fan-In with Rollup)

Scenario: A smart building has temperature sensors assigned to zones. Each zone needs an average temperature derived from all sensors in that zone. The sensors report every minute, but the zone average should be computed at a 15-minute cycle.

Templates:

• temp_sensor — streams: temperature (interval, 1-minute), zoneUid (link)
• building_zone — streams: avg_temperature (derived, 15-minute base cycle)

Variable Grid (on the zone's avg_temperature stream):

Name	Component	Stream	Cycle	Function	Resolution SQL	Fan Agg	Fill Fwd
temps	(resolved by SQL)	(resolved by SQL)	15min	AVG	SELECT _component_uid, 'temperature' FROM temp_sensor WHERE zoneUid = @_component_uid	AVG	Yes

What happens step by step:

1. Resolution SQL finds all sensors in this zone (e.g., 8 sensors)
2. Each sensor's 1-minute temperature data is loaded
3. The Cycle/Function settings (15min / AVG) roll up each sensor's 1-minute data to 15-minute averages
4. The Fan Aggregator (AVG) averages all 8 sensors' rolled-up values at each 15-minute interval
5. The result is a single average temperature per 15-minute interval

Two levels of AVG: The Cycle/Function AVG rolls up each sensor from 1-minute to 15-minute. The Fan Aggregator AVG then averages across all sensors. These are independent operations. You could also use Cycle/Function = MAX with Fan Aggregator = AVG to get the average of each sensor's 15-minute peak temperature.

Example 7: Department Salary Budget (Interval Stream, Fan-In + Direct Dep)

Scenario: An HR system tracks employees and departments. Each employee has a monthly salary stream. Each department needs a derived stream showing total payroll as a percentage of the department's annual budget.

Templates:

• employee — streams: monthly_salary (interval, monthly), departmentUid (link)
• department — streams: annual_budget (rdm, updated annually), budget_utilization (derived, monthly)

Variable Grid (on the department's budget_utilization stream):

Name	Component	Stream	Resolution SQL	Fan Agg	Fill Fwd
payroll	(resolved by SQL)	(resolved by SQL)	SELECT _component_uid, 'monthly_salary' FROM employee WHERE departmentUid = @_component_uid	SUM	Yes
budget	(this component)	annual_budget			Yes

This expression sums all employee salaries in the department (fan-in SUM), divides by the monthly budget (annual budget / 12), and converts to a percentage. If an employee transfers to another department, they automatically drop out of the original department's calculation and into the new one.

Derivation Triggers and Performance

Background Derivation Path

Derived streams with FK-resolved dependencies are handled by the background derivation job (not inline with data arrival). The job runs every 1–3 minutes and handles:

1. FK resolution (~every 10 minutes): Re-resolves each FK dependency's SQL, updates materialized precedent links, and stores the resolved source stream UIDs
2. Derivation (on data arrival at resolved source): Once precedent links exist, data arrival at the source stream triggers derivation through the standard dirty-flag mechanism

Relationship Change Propagation

When a relationship changes (e.g., a meter's customerUid is updated to point to a different customer):

• The next FK resolution cycle (within ~10 minutes) detects the change
• The old precedent link is removed; a new one is created on the new source stream
• Subsequent data arrivals trigger derivation from the new source

Historical relationship changes: If you backdate a relationship change (append a value to the link stream with a historical timestamp), retroactive re-derivation is not immediate. The next precedent reconciliation cycle (within ~10 minutes) walks the full FK history, migrates stale precedent links, and flags the dependent stream for re-derivation from the earliest affected timestamp. The following derivation cycle then re-derives the historical range automatically. If you need an immediate refresh, use the Re-derive Stream action to trigger it without waiting for the reconciliation cycle.

Performance Characteristics

• FK history loading is fast — FK streams (link streams) typically have very few data points. A meter doesn't change customers often, so loading the full history of a link stream is inexpensive.
• Fan-in scales with target count — Loading N target streams is proportional to N. For large fan-in (hundreds of targets), consider using a Derivation Schedule rather than triggering on every data arrival.
• Temporal segmentation is additive — If an FK relationship has K change points, the engine derives K+1 segments. Each segment is independent, so the cost is roughly K+1 times a single derivation.

Unresolved FK Dependencies

Not every FK relationship is guaranteed to resolve at derivation time. In many real-world scenarios, the link stream may be empty or point to a component that doesn't have the expected value stream:

• A pump that hasn't been connected to a tank yet
• An employee who hasn't been assigned a manager
• A meter installed before customer assignment is complete
• A vehicle temporarily unassigned from any route

Behavior

When an FK dependency cannot be resolved (no segments are produced by the Resolution SQL), the derivation engine does not abort. Instead, the unresolved variable is set to null and the expression proceeds with all other variables populated normally.

How the null is handled depends on the Allow Nulls setting on the derived stream:

Allow Nulls	Behavior When FK Dep is Unresolved
Enabled (recommended)	The variable is null in the expression. The expression evaluator produces a gap (null output) for any interval where the null variable participates in the calculation. Other variables in the expression are still evaluated normally. completedDate advances — the derivation is not stuck.
Disabled	The null causes the entire expression to produce a gap for those intervals. The result is the same (null output), but the intent is different — Allow Nulls = Disabled indicates that you expect all variables to be present and want explicit gap tracking when they are not.

Tip: Enable Allow Nulls on derived streams with FK dependencies that may be intermittently unresolved. This ensures completedDate advances even when a relationship hasn't been established yet. Once the FK relationship is set (e.g., a pump is connected to a tank), subsequent derivations will resolve normally and produce values.

Mixed Resolved and Unresolved

An expression can have a mix of FK dependencies where some resolve and others do not. Each dependency is resolved independently: If rate and consumption resolve but tankLevel does not (the pump isn't connected to a tank), the expression evaluates as: The resolved variables (rate, consumption) load data normally. Only the unresolved variable (tankLevel) is null.

If you need the expression to produce a value even when some FK deps are unresolved, use conditional logic or restructure the expression to isolate the optional dependency into its own derived stream.

Note: When all FK dependencies are unresolved (no static or FK dep resolves), the derivation still runs. All variables are null, the expression produces null output, and completedDate advances. This prevents the stream from becoming permanently stuck waiting for a relationship that may not exist yet.

Copy, Folder Copy, and Reconcile

FK-resolved dependencies are portable by design. Because Resolution SQL uses @_component_uid (the current component's UID), it resolves dynamically at derivation time.

Component Copy

When you copy a component with FK-resolved variables, the copy works correctly with no manual intervention. The Resolution SQL resolves relative to the new component's @_component_uid, so it finds the new component's own FK relationships.

Folder Copy

FK-resolved variables are unaffected by folder copy. They resolve dynamically and do not store UIDs for external targets.

Template Reconcile

Variables with Resolution SQL are automatically safe during reconcile. The template defines the SQL pattern, but each component resolves its own targets based on its own FK stream data. There is nothing component-specific to overwrite.

See Expression Derivation and Change for full details.

UI Configuration Reference

The derivation panel's variable grid has several columns relevant to FK deps. Some are hidden by default; right-click a column header to show them.

Column	Default	Description
Resolution SQL	Hidden	The TEQ SELECT statement that resolves the target stream(s) at derivation time. When set, the Component and Stream columns show "(resolved by SQL)" and are read-only. Click the cell to open a text editor for the SQL.
Fan Aggregator	Hidden	Aggregation function for multi-target (fan-in) resolution. NONE (default) requires exactly one target. SUM, AVG, MIN, MAX aggregate across multiple targets. Only applicable when Resolution SQL is set.
Fill Forward	Hidden	Carries the variable's last known value forward to timestamps where other variables have data. Enabled by default for FK-resolved variables. Not applicable to point streams.
Cycle	Visible	Optional rollup cycle for the resolved target stream. When set, the target stream's data is rolled up to this cycle before entering the expression. For fan-in, each target stream is rolled up individually before aggregation.
Function	Visible	Rollup aggregation method (SUM, AVG, MAX, MIN, LAST, FIRST, TWA). Used when Cycle is set to a non-base cycle. For fan-in, this rollup is applied to each target individually — it is separate from the Fan Aggregator which operates across targets.

Don't confuse Cycle/Function with Fan Aggregator.

Cycle + Function = vertical aggregation (rolling up one stream's data from a finer cycle to a coarser cycle). Applied per-stream, before fan-in.

Fan Aggregator = horizontal aggregation (combining N streams' values at the same time point into one). Applied after rollups, across all resolved target streams.