docs/p2_feature_engineering.md

# P2 Feature & Target Engineering Tools

** Aditya · Sprint: June 1–12 · Status: shipped (registry 18 → 22 tools in this snapshot)**

Four tools that close the gap between "I have a SQL result" and "I can
train an honest model on it": `derive_feature`, `define_target`,
`time_split`, `handle_imbalance`. Specs follow `docs/v1_tools.md` §5;
this doc is the implementation reference.

The canonical predictive chain is now:

```
run_sql(label="loans")
  → derive_feature(df_label="loans", name="payment_burden",
                   expression="payments / amount")
  → define_target(df_label="loans", target_column="y_default",
                  positive_definition="status = 'B'",
                  scope_filter="status IN ('A','B')",
                  entity_column="loan_id")
  → time_split(df_label="loans", time_col="granted_date",
               cutoff="2017-06-01")
  → handle_imbalance(df_label="train")          # defaults target from define_target
  → train_tabular_model(df_label="train", target_column="y_default", ...)
  → predict / evaluate_predictions on "test"
```

---

## 1. `derive_feature`

`lexsi_ds/agent/tools/derive_feature.py`

Adds ONE column to a cached `run_sql` DataFrame from a scalar
expression — a ratio, date diff, or CASE bucket — without a SQL
round-trip. Mutates `sql_result:<df_label>` in place (v1_tools §5.1
contract).

| Arg | Type | Default | Notes |
|---|---|---|---|
| `df_label` | `str` | — | cached frame to mutate |
| `name` | `str` | — | new column; identifier-validated |
| `expression` | `str` | — | scalar SQL (duckdb) or `df.eval` (pandas) |
| `engine` | `"duckdb" \| "pandas"` | `"duckdb"` | duckdb is binder-validated |
| `overwrite` | `bool` | `False` | guard against clobbering source columns |

Failure modes surfaced as `ok=False` with a recoverable observation:
`unknown_label` (lists cached labels), binder errors (lists available
columns), `column_exists`, `forbidden_expression` (statement-level SQL
keywords blocked), `non_rowwise_expression` (aggregates rejected —
those belong in `run_sql`).

## 2. `define_target`

`lexsi_ds/agent/tools/define_target.py`

Materializes the prediction target as an explicit column and records
the **recipe** — task type, positive definition, scope filter, entity
column — under `last_target_definition`. Fixes the v0 failure mode of
training on a raw multi-code column when the question was binary.

| Arg | Type | Default | Notes |
|---|---|---|---|
| `df_label` | `str` | — | |
| `target_column` | `str` | — | created if `positive_definition` given, else validated |
| `task_type` | `"classification" \| "regression"` | `"classification"` | |
| `positive_definition` | `str \| None` | `None` | SQL boolean (cls) / numeric expr (reg) |
| `scope_filter` | `str \| None` | `None` | rows kept for training; rest dropped |
| `entity_column` | `str \| None` | `None` | recorded so training excludes it |

Validation: ≥2 classes for classification (else `single_class_target`),
≤20 classes (else `high_cardinality_target` — likely a regression
target), numeric dtype for regression, NULL-target rows flagged.

## 3. `time_split`

`lexsi_ds/agent/tools/time_split.py`

Temporal train/test split: train = strictly before the cutoff, test =
at/after. Two modes — explicit `cutoff` (ISO date) or `test_fraction`
(chronologically-last fraction; the derived cutoff is reported).
VARCHAR date columns are coerced; >20% parse failures →
`bad_time_column`. A cutoff outside the data range → `degenerate_split`
with the actual range in the observation so the planner can recover.
The observation states the no-overlap invariant explicitly ("every
train row precedes every test row") so the summariser can cite it.

Cache: `sql_result:<train_label>`, `sql_result:<test_label>`,
`last_split = {kind:"time", time_col, cutoff, train_label, test_label,
n_train, n_test, source_label}`. Sides under 50 rows are warned per the
spec.

## 4. `handle_imbalance`

`lexsi_ds/agent/tools/handle_imbalance.py`

Class-balance report + **ranked strategy proposal — it never resamples
itself** (the planner/user decides; silent data surgery is not
shippable in an enterprise deployment). Defaults its target from
`last_target_definition` so it chains naturally after `define_target`.

Severity bands on the imbalance ratio (majority/minority): balanced
(<1.5), mild (<3), moderate (<10), severe (≥10). Strategy ranking is
data-aware: class weights always first; undersampling only when the
majority can spare rows (≥2k); SMOTE/oversampling when the minority is
tiny (<200); **Lexsi synthetic augmentation offered only when
`ctx.org` is attached** (`train_synthetic_model` →
`generate_synthetic_data_points` on the platform), so offline runs stay
clean; metric guidance (AUROC/PR-AUC over accuracy) always appended for
moderate/severe. Continuous targets (>20 distinct values) are rejected
with `non_categorical_target` and a pointer at the regression path.

Cache: `last_imbalance_report`.

---

## 5. Feature lineage (enterprise upgrade, this iteration)

`lexsi_ds/agent/tools/_lineage.py`

All four tools append a structured record to
`ctx.cache["feature_lineage"]` (ordinal, tool, UTC timestamp,
operation details). This gives every trained model a reproducible
"how the training frame was built" recipe — the model-risk-management
audit artifact regulated customers ask for, and the data source for the
planned `export_report` (P3) audit section. Append-only, run-scoped,
zero hot-path cost.

## 6. New planner rules

Rules **20–23** added to `PLANNER_SYSTEM` (`lexsi_ds/agent/prompts.py`),
one per tool, additive over 1–19:

- **20** derive features with `derive_feature`, don't re-run SQL
- **21** time-split temporal data before training — never random-split it
- **22** `define_target` whenever the label must be constructed
- **23** `handle_imbalance` before classification training; cite AUROC/PR-AUC when moderate/severe

> Merge note: Bhavish's June-10 branch also claims rules 20–22 for his
> P1 tools (`detect_data_quality_issues`, `suggest_join_paths`,
> `sample_rows`). Merges shoud include second renumbers — the rules are
> independent prose blocks, so it's a mechanical shift.

## 7. Benchmark coverage

`bench/pkdd/p2_feature_engineering.yaml` — 10 items:

- happy + error path per tool (8), trajectory-scored: expected/forbidden
  tools, `ok` flag, error code, observation substrings, cache keys
  written;
- the **leakage pair** (2): the same default-risk question random-split
  vs time-split. Passes when the time-split run trains strictly
  pre-cutoff and reports an AUROC **lower** than the leaky random-split
  AUROC — demonstrating the leak rather than asserting a fixed number.
  This validates demo step 3.

## 8. Tests

`tests/test_p2_feature_engineering.py` — 25 hermetic tests (in-memory
DuckDB, no PKDD file, no SDK, no LLM), reusing the offline-synthetic
fixture family from `conftest.py` plus a local `temporal_loan_df`
fixture with a time-drifting label. Covers every happy/error path
above, the cache contracts, the no-temporal-overlap invariant, lineage
ordering, and registry wiring.