Temporal & experiments

This tutorial shows how the same facts produce different correct answers when you change the time perspective.

You will report revenue by the segment each customer had at transaction time, then by their current segment. Next you will apply a knowledge-date cutoff to a late adjustment. The final section uses a tiny experiment to show why a point estimate needs uncertainty.

This advanced tutorial assumes entity bindings and EXTEND() from E-commerce . Run Data before every section after a notebook restart.

Meet the changing facts

The dataset contains three independent pieces:

Acme changes from smb to enterprise on February 15.
Beta remains smb through Q1 and becomes mid_market on April 1.
Four Q1 transactions include a −1000 adjustment that applies to February but was not recorded until April.

There are also four users in a small onboarding experiment.

FeatureQL

CREATE SCHEMA IF NOT EXISTS tutorial_te;
--
DROP TABLE IF EXISTS tutorial_te.experiment_events;
--
DROP TABLE IF EXISTS tutorial_te.experiment_assignments;
--
DROP TABLE IF EXISTS tutorial_te.transactions;
--
DROP TABLE IF EXISTS tutorial_te.customers_scd;
--
CREATE TABLE tutorial_te.customers_scd (
  id BIGINT,
  customer_id BIGINT,
  name VARCHAR,
  segment VARCHAR,
  effective_from DATE,
  effective_to DATE
);
--
INSERT INTO tutorial_te.customers_scd VALUES
  (1, 100, 'Acme', 'smb',        DATE '2023-01-01', DATE '2024-02-15'),
  (2, 100, 'Acme', 'enterprise', DATE '2024-02-15', NULL),
  (3, 200, 'Beta', 'smb',        DATE '2023-06-01', DATE '2024-04-01'),
  (4, 200, 'Beta', 'mid_market', DATE '2024-04-01', NULL);
--
CREATE TABLE tutorial_te.transactions (
  id BIGINT,
  customer_id BIGINT,
  amount BIGINT,
  tx_date DATE,
  recorded_at TIMESTAMP
);
--
INSERT INTO tutorial_te.transactions VALUES
  (1, 100,  5000, DATE '2024-01-15', TIMESTAMP '2024-01-15 10:00:00'),
  (2, 100,  8000, DATE '2024-03-01', TIMESTAMP '2024-03-01 09:00:00'),
  (3, 100, -1000, DATE '2024-02-28', TIMESTAMP '2024-04-02 15:00:00'),
  (4, 200,  3000, DATE '2024-02-20', TIMESTAMP '2024-02-20 11:00:00');
--
CREATE TABLE tutorial_te.experiment_assignments (
  user_id BIGINT,
  variant VARCHAR
);
--
INSERT INTO tutorial_te.experiment_assignments VALUES
  (1001, 'control'),
  (1002, 'treatment'),
  (1003, 'control'),
  (1004, 'treatment');
--
CREATE TABLE tutorial_te.experiment_events (
  user_id BIGINT,
  event_type VARCHAR
);
--
INSERT INTO tutorial_te.experiment_events VALUES
  (1001, 'activate'),
  (1002, 'activate'),
  (1004, 'activate');
--
SELECT CAST(COUNT() AS INTEGER) AS cnt FROM tutorial_te.transactions;

Result

Count BIGINT
4

Keep the two clocks separate

Before querying, distinguish two ideas:

Valid time asks when a fact applied in the business.
Recorded time asks when the system knew about it.

Segment history uses valid time. The late adjustment needs both clocks.

Use the segment valid at transaction time

Build each customer's segment history as an ordered array, then use SCD_AT_TIME() while enriching transactions. Every transaction receives the segment that was valid on its own tx_date.

FeatureQL

DEFINE
    transactions := ENTITY(),
    tx_id := INPUT(BIGINT#transactions),
    tx_source := EXTERNAL_COLUMNS(
        tx_id BIGINT#transactions BIND TO tx_id,
        customer_id BIGINT,
        amount BIGINT,
        tx_date DATE
        FROM SQL(SELECT id AS tx_id, customer_id, amount, tx_date FROM tutorial_te.transactions)
    ),
    tx_customer_id := tx_source[customer_id],
    tx_amount := tx_source[amount],
    tx_date := tx_source[tx_date],
    customers := ENTITY(),
    customer_id := INPUT(BIGINT#customers),
    tx_timestamp := INPUT(TIMESTAMP),
    scd_source := EXTERNAL_COLUMNS(
        customer_id BIGINT#customers BIND TO customer_id,
        segment_scd ARRAY(ROW(time_update TIMESTAMP, value VARCHAR))
        FROM SQL(
            SELECT customer_id,
                ARRAY_AGG(ROW(effective_from::TIMESTAMP, segment) ORDER BY effective_from) AS segment_scd
            FROM tutorial_te.customers_scd GROUP BY (customer_id)
        )
    ),
    seg_scd := scd_source[segment_scd],
    seg_at_time := SCD_AT_TIME(seg_scd, tx_timestamp)[value],
    segment := EXTEND(
        ROW(tx_customer_id AS cid, tx_date::TIMESTAMP AS ts)
        WITH seg_at_time AS seg
        VIA cid,
        ts BIND TO customer_id,
        tx_timestamp
    )[seg]
RETURN
    segment,
    revenue := SUM(tx_amount) GROUP BY (segment)
FOR
    tx_id := BIND_COLUMNS(
        id
        FROM TABLE(tutorial_te.transactions)
    )
ORDER BY segment
;

Result

SEGMENT VARCHAR	REVENUE BIGINT
enterprise	7000
smb	8000

Acme's January 5000 remains smb. Its March 8000 and February −1000 adjustment belong to enterprise. Beta's February 3000 remains smb.

The point-in-time totals are therefore smb 8000 and enterprise 7000.

Restate Q1 using current segments

Now deliberately ask a different question: attribute all Q1 revenue to each customer's current segment. Select the open-ended SCD row instead of looking up the segment at each transaction date.

FeatureQL

DEFINE
    transactions := ENTITY(),
    tx_id := INPUT(BIGINT#transactions),
    tx_source := EXTERNAL_COLUMNS(
        tx_id BIGINT#transactions BIND TO tx_id,
        customer_id BIGINT,
        amount BIGINT,
        tx_date DATE
        FROM SQL(SELECT id AS tx_id, customer_id, amount, tx_date FROM tutorial_te.transactions)
    ),
    tx_customer_id := tx_source[customer_id],
    tx_amount := tx_source[amount],
    customers := ENTITY(),
    customer_id := INPUT(BIGINT#customers),
    current_seg_source := EXTERNAL_COLUMNS(
        customer_id BIGINT#customers BIND TO customer_id,
        current_segment VARCHAR
        FROM SQL(
            SELECT customer_id, segment AS current_segment
            FROM tutorial_te.customers_scd
            WHERE effective_to IS NULL
        )
    ),
    current_segment := current_seg_source[current_segment],
    segment := EXTEND(
        ROW(tx_customer_id AS cid)
        WITH current_segment AS seg
        VIA cid BIND TO customer_id
    )[seg]
RETURN
    segment,
    revenue := SUM(tx_amount) GROUP BY (segment)
FOR
    tx_id := BIND_COLUMNS(
        id
        FROM TABLE(tutorial_te.transactions)
    )
ORDER BY segment
;

Result

SEGMENT VARCHAR	REVENUE BIGINT
enterprise	12000
mid_market	3000

Acme's January revenue moves with it to enterprise, while all of Beta's revenue moves to mid_market. The result becomes enterprise 12000 and mid_market 3000.

Neither answer is universally correct. The first explains the business as it was; the second restates history using today's classification.

Reproduce what was known at a reporting cutoff

The late adjustment applies to February, but it was recorded in April. To reproduce an earlier report, filter on both:

tx_date to keep facts that apply to Q1; and
recorded_at to keep only facts known by the reporting cutoff.

First calculate Q1 revenue using knowledge available on March 31:

FeatureQL

DEFINE
    tx_id := INPUT(BIGINT),
    tx_source := EXTERNAL_COLUMNS(
        tx_id BIGINT BIND TO tx_id,
        amount BIGINT,
        tx_date DATE,
        recorded_at TIMESTAMP
        FROM SQL(SELECT id AS tx_id, amount, tx_date, recorded_at FROM tutorial_te.transactions)
    ),
    tx_amount := tx_source[amount],
    tx_date := tx_source[tx_date],
    tx_recorded_at := tx_source[recorded_at],
    is_q1 := tx_date >= DATE '2024-01-01' AND tx_date < DATE '2024-04-01',
    known_as_of := tx_recorded_at <= TIMESTAMP '2024-03-31 23:59:59'
RETURN
    total_revenue_as_of_mar31 := SUM(tx_amount)
    FILTER (WHERE is_q1 AND known_as_of)
FOR
    tx_id := BIND_COLUMNS(
        id
        FROM TABLE(tutorial_te.transactions)
    )
;

Result

TOTAL_REVENUE_AS_OF_MAR31 BIGINT
16000

The total is 16000 because the adjustment had not yet been recorded.

Move the knowledge cutoff to April 30:

FeatureQL

DEFINE
    tx_id := INPUT(BIGINT),
    tx_source := EXTERNAL_COLUMNS(
        tx_id BIGINT BIND TO tx_id,
        amount BIGINT,
        tx_date DATE,
        recorded_at TIMESTAMP
        FROM SQL(SELECT id AS tx_id, amount, tx_date, recorded_at FROM tutorial_te.transactions)
    ),
    tx_amount := tx_source[amount],
    tx_date := tx_source[tx_date],
    tx_recorded_at := tx_source[recorded_at],
    is_q1 := tx_date >= DATE '2024-01-01' AND tx_date < DATE '2024-04-01',
    known_as_of := tx_recorded_at <= TIMESTAMP '2024-04-30 23:59:59'
RETURN
    total_revenue_as_of_apr30 := SUM(tx_amount)
    FILTER (WHERE is_q1 AND known_as_of)
FOR
    tx_id := BIND_COLUMNS(
        id
        FROM TABLE(tutorial_te.transactions)
    )
;

Result

TOTAL_REVENUE_AS_OF_APR30 BIGINT
15000

The total becomes 15000. The transactions' business dates did not change; the −1000 entered the known record.

Put uncertainty beside an experiment result

The final dataset has only two users in control and two in treatment. Calculate activation rate and a normal-approximation 95% confidence interval for each arm:

FeatureQL

DEFINE
    exp_users := ENTITY(),
    user_id := INPUT(BIGINT#exp_users),
    assign_source := EXTERNAL_COLUMNS(
        user_id BIGINT#exp_users BIND TO user_id,
        variant VARCHAR
        FROM TABLE(tutorial_te.experiment_assignments)
    ),
    event_source := EXTERNAL_COLUMNS(
        user_id BIGINT#exp_users BIND TO user_id,
        event_type VARCHAR
        FROM TABLE(tutorial_te.experiment_events)
    ),
    variant := assign_source[variant],
    did_activate := user_id.RELATED(
        COUNT_IF(event_source[event_type] = 'activate')
        GROUP BY (event_source[user_id])
    )
    > 0,
    assigned := COUNT() GROUP BY (variant),
    activated := COUNT_IF(did_activate) GROUP BY (variant),
    p := activated::DOUBLE / assigned::DOUBLE,
    se := SQRT(p * (1e0 - p) / assigned::DOUBLE),
    activation_rate := ROUND(p, 4),
    ci_low := ROUND(GREATEST(0e0, p - 1.96e0 * se), 4),
    ci_high := ROUND(LEAST(1e0, p + 1.96e0 * se), 4)
RETURN
    variant,
    assigned,
    activated,
    activation_rate,
    ci_low,
    ci_high
FOR
    user_id := BIND_COLUMNS(
        user_id
        FROM TABLE(tutorial_te.experiment_assignments)
    )
ORDER BY variant
;

Result

VARIANT VARCHAR	ASSIGNED BIGINT	ACTIVATED BIGINT	ACTIVATION_RATE DOUBLE	CI_LOW DOUBLE	CI_HIGH DOUBLE
control	2	1	0.5	0	1
treatment	2	2	1	1	1

Control activates at 50%, with an interval spanning nearly the entire probability range. Treatment activates at 100%, producing a degenerate interval at 1 under this simple approximation.

The point estimates look far apart, but two users per arm provide almost no evidence. The useful output is not just the rate; it is the rate beside a warning about precision.

What's next

You have separated historical classification, current classification, business time, and knowledge time, then kept uncertainty attached to an experimental estimate.

Continue with Graphs & referrals for recursive structures, or revisit SaaS metrics for a simpler as-of measure without SCD2 history.

Last update at: 2026/10/13 08:32:21

On this page

Meet the changing facts Keep the two clocks separate Use the segment valid at transaction time Restate Q1 using current segments Reproduce what was known at a reporting cutoff Put uncertainty beside an experiment result What's next