---
name: anofox-forecast-data-prep
description: >
  Data preparation for the anofox_forecast DuckDB extension —
  filling gaps, imputing nulls, dropping bad series, differencing,
  detrending, hierarchical key operations. Use when preparing raw time
  series for downstream forecasting or backtesting with `ts_forecast_by`
  / `ts_cv_folds_by`.
version: 2026.09.26
user-invocable: false
---

# Anofox Forecast — Data Preparation Cheat Sheet

**Extension:** `anofox_forecast` v2026.09.26 (Rust crate `anofox-forecast` v0.15.10) | **DuckDB:** v1.4.5 LTS / v1.5.4+ | **Dual naming:** `ts_*` and `anofox_fcst_ts_*` (identical)

Prep raw time series so downstream forecasting / CV has clean input: no gaps, no unwanted NULLs, no degenerate series, correct hierarchy.

## Critical gotcha — materialise between `_by` steps

`_by` table functions **do not chain in CTEs** — under parallel execution they silently return 0 rows. Always `CREATE TABLE` between pipeline steps.

```sql
-- BROKEN (silent 0 rows under parallelism):
WITH step1 AS (SELECT * FROM ts_fill_gaps_by('raw', id, ds, y, '1d', MAP{}))
SELECT * FROM ts_fill_nulls_const_by('step1', id, ds, y, 0.0);

-- CORRECT:
CREATE TABLE step1 AS SELECT * FROM ts_fill_gaps_by('raw', id, ds, y, '1d', MAP{});
CREATE TABLE step2 AS SELECT * FROM ts_fill_nulls_const_by('step1', id, ds, y, 0.0);
```

## Gap filling

### `ts_fill_gaps_by`
Insert missing date rows (NULL value) so every series has a complete regular grid.

```sql
ts_fill_gaps_by(source VARCHAR, group_col COLUMN, date_col COLUMN, value_col COLUMN,
                frequency VARCHAR) → TABLE
```

No params — pure frequency-driven grid completion. Inserted rows have NULL in `value_col`; use `ts_fill_nulls_*_by` next to impute.

Frequencies: `'1d'`, `'1h'`, `'30m'`, `'1w'`, `'1mo'`, `'1q'`, `'1y'` (Polars-style) or `'1 day'` (DuckDB INTERVAL) or raw int (days).

**Month-end rule (v0.10.0, D-09):** monthly/quarterly/yearly gap fill preserves
calendar alignment — a month-end series stays month-end (`2024-01-31` → inserted
`2024-02-29` → `2024-03-31`, not the 1st), a 1st-of-month series stays on the 1st, and
any other day-of-month keeps its own day (clamped to the target month's length). Same
rule `ts_fill_forward_by` uses when forward-filling with a calendar frequency.

```sql
CREATE TABLE gaps_filled AS
SELECT * FROM ts_fill_gaps_by('raw', product_id, ds, y, '1d');

-- Month-end series: the inserted row lands on 2024-02-29, not 2024-02-01
CREATE TABLE month_end_raw AS
SELECT * FROM (VALUES
    (1, TIMESTAMP '2024-01-31', 10.0),
    (1, TIMESTAMP '2024-03-31', 30.0)
) t(product_id, ds, y);

CREATE TABLE monthly_filled AS
SELECT * FROM ts_fill_gaps_by('month_end_raw', product_id, ds, y, '1mo');
```

## NULL imputation

Table macros. Signatures:

| Function | Signature |
|---|---|
| `ts_fill_nulls_forward_by(source, group_col, date_col, value_col)` | Last observation carried forward |
| `ts_fill_nulls_backward_by(source, group_col, date_col, value_col)` | Next observation carried backward |
| `ts_fill_nulls_mean_by(source, group_col, date_col, value_col)` | Per-group mean, computed in Rust (NaN counts as missing too; all-missing group stays NULL) |
| `ts_fill_nulls_const_by(source, group_col, date_col, value_col, fill_value)` | Constant (e.g. 0.0 for retail counts) |
| `ts_fill_forward_by(...)` | Streaming per-group forward fill; also `ts_fill_forward_operator` for windowed variants |
| `ts_mark_unknown_by` | Marks values matching a sentinel as unknown |
| `ts_fill_unknown_by` | Fills previously-marked unknowns |

```sql
-- Retail: zero-fill absent-day rows produced by ts_fill_gaps_by
CREATE TABLE nulls_filled AS
SELECT * FROM ts_fill_nulls_const_by('gaps_filled', product_id, ds, y, 0.0);
```

## Series filtering — drop degenerate cases

| Function | Drops when |
|---|---|
| `ts_drop_constant_by` | Rust `is_constant`: all values equal, or **fewer than two non-NULL values (incl. all-NULL groups)** |
| `ts_drop_short_by(source, group_col, min_length)` | Series length < min |
| `ts_drop_gappy_by(source, group_col, value_col, max_gap_ratio)` | Gap ratio exceeds threshold (0.1 = 10 %) |
| `ts_drop_zeros_by` | All zero values |
| `ts_drop_leading_zeros_by` | Trim leading zeros (product not yet launched) |
| `ts_drop_trailing_zeros_by` | Trim trailing zeros (product discontinued) |
| `ts_drop_edge_zeros_by` | Trim both leading and trailing zeros |

```sql
CREATE TABLE clean AS
SELECT * FROM ts_drop_short_by('nulls_filled', product_id, 24);   -- ≥ 2 years monthly
```

## Transforms

- **`ts_diff_by(source, group_col, date_col, value_col, diff_order INTEGER, seasonal_lag := 1)`**
  — d-th order differencing in Rust (numpy `diff(n=diff_order)`); `diff_order=2` is the true
  second difference `y[t] - 2*y[t-1] + y[t-2]`. Pass `seasonal_lag := k` for the old lag-k
  reading `y[t] - y[t-k]` (breaking change from pre-Phase-12: `diff_order` used to mean lag-k).
  One row per input row; first `diff_order * seasonal_lag` rows per group are NULL; any
  difference touching a NULL/NaN input is NULL (never an error).
- **`ts_detrend_by(source, group_col, date_col, value_col, method)`** — removes a fitted trend
  per group. `method`: `'linear'`; `'quadratic'` (alias `'polynomial'`, `'poly'`, degree 2);
  `'cubic'` (degree 3); `'diff'`/`'diff2'`; `'loess'` (alias `'lowess'`, `O(n^2)`); `'auto'`
  (default, AIC-selects among the above). An unrecognised method name is a SQL error naming
  every valid choice, not a silent fallback.
- **`ts_validate_timestamps_by`** — check for out-of-order / duplicate timestamps.

```sql
CREATE TABLE stationary AS
SELECT * FROM ts_diff_by('clean', product_id, ds, y, 1);   -- first-order difference (same as lag-1)
```

## Hierarchical key operations

Retail and IoT panels usually have composite keys (region × store × item). These macros manipulate them:

| Function | Purpose |
|---|---|
| `ts_combine_keys` | Concatenate multiple ID columns into one, `sep`-delimited |
| `ts_split_keys` | Reverse — split back into components |
| `ts_aggregate_hierarchy` | Sum-aggregate up a hierarchy level (region+store+item → region+store) |
| `ts_validate_separator` | Verify separator string isn't present in the source columns |

```sql
-- Combine two IDs for grouping
CREATE TABLE with_combined AS
SELECT * FROM ts_combine_keys('raw', region_id, store_id, ds, y, MAP{'sep': '|'});

-- Aggregate to store level
CREATE TABLE store_level AS
SELECT * FROM ts_aggregate_hierarchy('raw', region_id, store_id, item_id, ds, y, 2);
```

## Canonical prep pipeline

```sql
-- 1. Regularise the time grid
CREATE OR REPLACE TABLE p1 AS
SELECT * FROM ts_fill_gaps_by('raw', product_id, ds, y, '1d');

-- 2. Impute (retail → zero; continuous → forward)
CREATE OR REPLACE TABLE p2 AS
SELECT * FROM ts_fill_nulls_const_by('p1', product_id, ds, y, 0.0);

-- 3. Drop what can't be forecast
CREATE OR REPLACE TABLE p3 AS
SELECT * FROM ts_drop_short_by('p2', product_id, 30);

CREATE OR REPLACE TABLE clean AS
SELECT * FROM ts_drop_constant_by('p3', product_id, y);
```

## STRUCT vs MAP params

Both work everywhere for functions that accept a trailing params argument. STRUCT is
recommended — keeps numeric params typed. Note `ts_fill_gaps_by` itself is a **5-argument**
macro `(source, group_col, date_col, value_col, frequency)` — it takes no `params`/`tolerance`
argument at all; passing a 6th argument raises a Binder Error:

```sql
-- Correct 5-arg call (no params argument exists for this macro)
SELECT * FROM ts_fill_gaps_by('clean', product_id, ds, y, '1d');

-- Functions that DO take a trailing params argument accept either form:
-- STRUCT (recommended)
SELECT * FROM ts_forecast_by('clean', product_id, ds, y, 'AutoETS', 7, '1d', {seasonal_period: 7});
-- MAP (all strings)
SELECT * FROM ts_forecast_by('clean', product_id, ds, y, 'AutoETS', 7, '1d', MAP{'seasonal_period': '7'});
```

Empty params: `MAP{}` or `{}`.

## Common patterns

- **Kaggle-style split**: prep on train + test together, then split at the cutoff. Prevents inconsistent gap-fill / drop decisions between splits.
- **CV-ready input**: `ts_cv_folds_by` requires the input to already be gap-filled and NULL-imputed. Always run the prep pipeline first.
- **JSON extension required** for a subset of prep-related functions (period detection auto-loading, etc.). Enable `SET autoinstall_known_extensions=1; SET autoload_known_extensions=1;` in your session.

See also: `anofox-forecast-eda` (quality check before prep), `anofox-forecast-detection` (fill gaps before detecting periods), `anofox-forecast-backtest` (cv_folds requires clean input), `anofox-forecast-models` (forecast requires clean input).

Reference docs:
- `docs/api/04-data-preparation.md`
- `docs/guides/01-getting-started.md`