anofox-forecast-data-preplisted

# Anofox Forecast — Data Preparation Cheat Sheet **Extension:** `anofox_forecast` v0.15.3 | **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). ```sql CREATE TABLE gaps_filled AS SELECT * FROM ts_fill_ga