Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.junction.com/llms.txt

Use this file to discover all available pages before exploring further.

​
Overview

Use the ALIGN clause to fill empty time buckets in your aggregated output with values carried over from neighbouring buckets. Without it, a GROUP BY over sparse data - for example a once-a-week weigh-in grouped daily - produces only the rows that have real observations, with no rows for the days in between. With ALIGN you get one row per bucket in the time range, with empty buckets filled according to your chosen carry operator. All ALIGN behaviour is post-aggregation: Junction Sense first runs your GROUP BY and aggregates, then materialises the missing buckets and fills them. The ALIGN clause supports three carry operators:
  • "carry_forward" - fill empty buckets with the most recent prior non-empty value (last-observation-carried-forward).
  • "carry_backward" - fill empty buckets with the next subsequent non-empty value.
  • "carry_nearest" - fill empty buckets with the value from the nearest non-empty bucket (past or future, whichever is closer; ties prefer the past value).
Each operator takes a duration argument (max_age for forward/backward, span for nearest) that caps how far the carry will reach. Buckets beyond the cap remain as explicit nulls in the output.

​
Operators

​
"carry_forward" - last-observation-carried-forward

For each empty bucket, carries the most recent prior non-empty bucket’s aggregate forward in time. max_age caps how far back the carry searches; if no prior non-empty bucket exists within max_age, the bucket stays as an explicit null. Use carry_forward for causal or streaming use cases where only past data should influence the current value - for example a daily weight series built from sparse weigh-ins, or HRV tracking that should hold the last known reading across days the device wasn’t worn.

Input arguments

ArgumentRemarks
max_ageOptional Period - caps look-back. Omit to carry indefinitely.

​
"carry_backward" - carry-backward

Symmetric counterpart to carry_forward. For each empty bucket, carries the nearest subsequent non-empty bucket’s aggregate backward in time. max_age caps how far forward the carry searches. Useful for retrospective analysis - for example applying a measurement taken on day N back to days N-1, N-2, etc., up to the cap.

Input arguments

ArgumentRemarks
max_ageOptional Period - caps look-forward. Omit to carry indefinitely.

​
"carry_nearest" - carry from nearest bucket

For each empty bucket, borrows the value from the nearest non-empty bucket in either direction - past or future, whichever is closer. span caps the search in both directions. On a tie (equidistant past and future non-empty buckets), the past value is preferred. Use carry_nearest when direction doesn’t matter and minimising carry distance is the priority - for example filling a daily weight composite from the closest measurement within a few days, in either direction.

Input arguments

ArgumentRemarks
spanOptional Period - caps search in both directions. Omit to carry indefinitely.

​
Operator comparison

OperatorDirectionTie behaviour
"carry_forward"Backward only- (single direction)
"carry_backward"Forward only- (single direction)
"carry_nearest"Both - picks closestPrefer past on tie

​
Behaviour

​
Bucket materialisation

Every ALIGN operator materialises empty datetime buckets in the output. Even if the carry cap is reached and a bucket can’t be filled, the row still appears in the result with a null value. Downstream consumers see a contiguous time series rather than a sparse one.

​
Spine extension beyond observed data

The spine extends beyond the observed data range by the carry window so sparse data - like one weigh-in per week - produces a properly-carried series:
  • carry_forward(max_age=X) extends the spine forward by X past the latest observation.
  • carry_backward(max_age=X) extends the spine backward by X before the earliest observation.
  • carry_nearest(span=X) extends on both sides by X.
Forward extension is clamped at the current time - the spine never includes buckets dated past “now”. If a recent observation plus X would land in the future, the spine ends at the bucket containing the current moment. Backward extension is not capped. When max_age / span is omitted (uncapped carry), no extension is applied - the spine falls back to the observed range. For dense-data queries that’s fine; for the very-sparse case specify a cap to opt in to boundary materialisation.

​
Column-level carry

Each output column is carried independently. A bucket that has a null aggregate for column A but a real value for column B will have A filled from the nearest eligible source while B retains its real value. This is the intended behaviour - it avoids introducing nulls when a value is available within the carry window, regardless of other columns.

​
Per-partition carry

When group_by contains keys beyond the Date Truncate expression (for example Source.col("source_provider")), each unique combination of those keys is treated as an independent time series. Values do not carry across partition boundaries - Oura’s last reading will never fill an Apple Health bucket, even if the Apple Health partition has an empty bucket within max_age. Every group_by column must be projected in select (via group_key("*") or each group_key(i)); otherwise the query is rejected.

​
Validation

When you create a Continuous Query or submit a synchronous Query with ALIGN, the following must hold:
  1. group_by must contain at least one Date Truncate expression. carry_forward, carry_backward, and carry_nearest all operate over the truncated datetime axis.
  2. max_age / span must be greater than or equal to the group_by Date Truncate period. A carry window smaller than the bucket size could never reach an adjacent bucket and is rejected rather than silently no-op’d.
  3. Every group_by column must be projected in select - use group_key("*") for the common case.
  4. group_by cannot mix a Date Truncate expression with a Date Part expression (such as day-of-month). Date Part values are derived from the same date column as the bucket and treating them as independent partition keys would produce impossible combinations like (February, day-of-month=30).

​
Honest null is the default

Omitting .align() preserves the existing behaviour - sparse GROUP BY output, no extra rows for missing buckets. Reach for ALIGN only when you actively want carry behaviour.

​
Examples

​
Carry weight forward up to 14 days

Fill in the days between weigh-ins so a daily trend chart has a usable value every day, capped at 14 days of carry to avoid stale data. 
import vitalx.aggregation as va

va.select(
    va.group_key("*"),
    va.Body.col("weight_kilogram").mean(),
).group_by(
    va.date_trunc(va.Body.index(), 1, "day"),
).align("carry_forward", max_age=va.period(14, "day"))

​
Carry HRV forward on days the wearable was not worn

import vitalx.aggregation as va

va.select(
    va.group_key("*"),
    va.Sleep.col("hrv_mean_rmssd").mean(),
).group_by(
    va.date_trunc(va.Sleep.index(), 1, "day"),
).align("carry_forward", max_age=va.period(7, "day"))

​
Fill weight from the nearest measurement within 3 days

Use carry_nearest when direction doesn’t matter and minimising carry distance is the priority. 
import vitalx.aggregation as va

va.select(
    va.group_key("*"),
    va.Body.col("weight_kilogram").mean(),
).group_by(
    va.date_trunc(va.Body.index(), 1, "day"),
).align("carry_nearest", span=va.period(3, "day"))

​
Per-provider weight trend with independent carry

Group by source_provider so each provider gets its own series. The carry stays within each partition - Oura’s value never bleeds into Apple Health’s series. 
import vitalx.aggregation as va

va.select(
    va.group_key("*"),
    va.Body.col("weight_kilogram").mean(),
).group_by(
    va.date_trunc(va.Body.index(), 1, "day"),
    va.Source.col("source_provider"),
).align("carry_forward", max_age=va.period(14, "day"))

​
Blood pressure: infrequently measured, carry up to 3 months

For lab-grade measurements that are sampled infrequently but stay relevant for weeks or months between readings. 
import vitalx.aggregation as va

va.select(
    va.group_key("*"),
    va.Timeseries.col("blood_pressure").field("systolic").mean(),
).group_by(
    va.date_trunc(va.Timeseries.index(), 1, "day"),
).align("carry_forward", max_age=va.period(3, "month"))