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.