Model Evaluation and Validation Questions

K-fold cross-validation: split the dataset into k equal folds, train the model k times each time using k-1 folds for training and the remaining fold for validation; average the performance across folds to estimate generalization. It reduces variance versus a single train/val split and makes efficient use of data.

Stratified k-fold: same procedure but each fold preserves the original class distribution (proportion of labels): the splits are stratified. This ensures each fold is representative for classification tasks, especially with class imbalance.

When prefer stratified k-fold: whenever class proportions are imbalanced or small classes must appear in every fold to get reliable estimates. Example: binary classification with 10,000 samples where the positive class has roughly 100 examples (1%). Using 5-fold regular k-fold with random splits, positives are not guaranteed to spread evenly across folds: a plausible random 5-way split might put 28, 24, 19, 15, and 14 positives in the five folds respectively, instead of an even 20 each, so fold 5's validation metrics are estimated from only 14 positive examples versus fold 1's 28, nearly doubling the variance of recall or AUC estimates across folds, and a particularly unlucky split at this rarity could leave some fold with close to zero positives, making that fold's precision or recall undefined or wildly noisy. Stratified 5-fold instead guarantees each fold gets almost exactly 20 positives (100/5), enabling stable, comparable metric estimates (precision/recall/AUC) across all five folds.

Drawback for time-series: both k-fold and stratified k-fold break temporal order and can leak future information into training. For time-series you should use time-aware validation (e.g., expanding window or time-series split) to preserve chronology.

Direct answer. Aggregate the predictions log with a single grouped query, filtering out unlabeled rows before computing the metrics, and add segment to the GROUP BY only when you need the breakdown, computing all segments in the same pass rather than re-scanning per segment.

The query.

SELECT
  DATE_TRUNC('day', event_time)          AS day,
  model_version,
  COUNT(*) FILTER (WHERE predicted_label = 1 AND ground_truth_label = 1) AS tp,
  COUNT(*) FILTER (WHERE predicted_label = 1 AND ground_truth_label = 0) AS fp,
  COUNT(*) FILTER (WHERE predicted_label = 0 AND ground_truth_label = 1) AS fn,
  ROUND(
    COUNT(*) FILTER (WHERE predicted_label = 1 AND ground_truth_label = 1)::numeric
    / NULLIF(COUNT(*) FILTER (WHERE predicted_label = 1), 0), 4
  ) AS precision,
  ROUND(
    COUNT(*) FILTER (WHERE predicted_label = 1 AND ground_truth_label = 1)::numeric
    / NULLIF(COUNT(*) FILTER (WHERE ground_truth_label = 1), 0), 4
  ) AS recall
FROM predictions
WHERE ground_truth_label IS NOT NULL
  AND event_time >= NOW() - INTERVAL '30 days'
GROUP BY 1, 2
ORDER BY 1, 2;

Structured elaboration. FILTER (WHERE ...) computes each conditional count in one pass over the same grouped rows, which is both more readable and cheaper than four separate CASE WHEN sums or four subqueries; NULLIF(..., 0) turns a would-be division-by-zero into a clean NULL rather than an error when a model_version/day pair has zero predicted-positive rows. Filtering ground_truth_label IS NOT NULL up front, before the aggregation, means Postgres never even considers unlabeled rows in the FILTER counts, so precision and recall are computed strictly over the labeled subset as required.

Worked example. Say model_version v3 on a given day has 5 rows with ground truth: 3 true positives, 1 false positive, 1 false negative (and any number of true negatives, which don't enter precision or recall). Precision = 3/(3+1) = 0.75, recall = 3/(3+1) = 0.75 in this small case; the query above reproduces exactly this arithmetic per (day, model_version) group because FILTER counts are computed over the full partition before the division.

Extending to per-segment metrics without re-scanning. Add segment as a second grouping key in the same query rather than running the whole query once per segment value:

GROUP BY DATE_TRUNC('day', event_time), model_version, segment

This still costs one sequential (or index) scan of the 30-day window; Postgres computes all segment buckets in the same aggregation pass because GROUP BY fans a single scan out into multiple output groups, it does not rerun the scan per group. If segment is not already a column on predictions, join it from a small dimension table on user_id before aggregating, and add a composite index on (model_version, event_time) (or a BRIN index on event_time for a large, time-ordered table) so the 30-day filter can use an index range scan instead of a full table scan. For a table with hundreds of millions of rows, also consider a nightly pre-aggregated rollup table keyed by (day, model_version, segment) that the dashboard reads from, so the expensive full-precision scan runs once per day rather than on every dashboard refresh.

Trade-offs and pitfalls. ROUND(...::numeric, 4) avoids the classic Postgres pitfall of integer division truncating to 0 when both counts are small integers; casting to numeric (or ::float) before dividing is required. Running one query per segment value instead of grouping by segment directly is the anti-pattern to avoid: it turns an O(1)-scan aggregation into an O(number of segments)-scan one.

Precision, recall, specificity, F1, and accuracy (binary classification): definitions:

• Precision = TP / (TP + FP). Of predicted frauds, fraction that are actual fraud. Measures false-alarm rate.
• Recall (a.k.a. sensitivity) = TP / (TP + FN). Of actual frauds, fraction we detect. Measures missed-fraud rate.
• Specificity = TN / (TN + FP). Of actual non-fraud transactions, the fraction correctly left alone. Prioritize it when a false positive on a legitimate customer is expensive: e.g. a system that auto-freezes flagged accounts, where wrongly freezing a real customer's account triggers a costly support escalation and risks losing that customer, so specificity (not just recall on the fraud side) becomes the number the fraud-ops team watches.
• F1 = 2 * (precision * recall) / (precision + recall). Harmonic mean balancing precision and recall; useful when classes are imbalanced.
• Accuracy = (TP + TN) / (TP + FP + TN + FN). Overall fraction correct across both classes. Prioritize it when classes are roughly balanced and the two error types cost about the same, e.g. an internal audit-sampling task where a false positive and a false negative both just cost one analyst-hour to re-check, unlike this heavily imbalanced fraud case where accuracy is the misleading headline number.

Business priority for fraud detection:

• If false negatives (missed fraud) are much more costly than false positives, prioritize recall. Catching fraud reduces direct monetary loss; higher recall may raise false positives (lower precision), which increases investigation cost but is acceptable if business tolerates it.

Example confusion matrix (actual vs predicted):

• True Fraud (positive): 100 cases
• Non-Fraud (negative): 9,900 cases
  Scenario A (conservative threshold → high recall):
• TP=95, FN=5, FP=800, TN=9,100
  Precision = 95/(95+800)=10.6%; Recall=95/100=95%; Specificity = TN/(TN+FP) = 9,100/9,900 ≈ 91.9%; Accuracy = (TP+TN)/Total = (95+9,100)/10,000 = 9,195/10,000 ≈ 92.0%
  Scenario B (strict threshold → high precision):
• TP=60, FN=40, FP=100, TN=9,800
  Precision = 60/(60+100)=37.5%; Recall=60%; Specificity = TN/(TN+FP) = 9,800/9,900 ≈ 99.0%; Accuracy = (TP+TN)/Total = (60+9,800)/10,000 = 9,860/10,000 = 98.6%
  Notice specificity and accuracy stay high (>90%) in BOTH scenarios here: with only 100 actual frauds against 9,900 actual non-frauds, the negative class dominates the denominator for both metrics, so neither one moves much even as precision swings from 10.6% to 37.5% and recall swings from 95% to 60%. That is exactly why specificity and accuracy are the wrong metrics to lead with on this imbalanced a problem, and why the scenario above prioritizes recall/precision instead.

Trade-offs and thresholding:

• Lowering decision threshold increases predicted positives → raises recall, lowers precision.
• Raising threshold decreases predicted positives → raises precision, lowers recall.
• Use precision-recall curve and choose threshold to meet a target recall (business SLA), possibly optimizing a cost function: Cost = C_FN * FN + C_FP * FP.
• Monitor production metrics (precision at required recall, alert volume) and consider using ranked outputs + manual review for top-K risky transactions to balance operational load.

Additional points:

• F1 useful if you want a single balanced metric, but when costs are asymmetric use recall or a weighted metric (Fβ with β>1 emphasizes recall).

Direct answer. Fitting a scaler, imputer, or encoder on the FULL dataset before splitting lets statistics from the validation fold (its mean, its variance, its category frequencies) leak into the numbers the model trains on; wrapping every preprocessing step and the model together in one Pipeline, then cross-validating the pipeline as a single unit, is what forces every transform to be fit on training folds only.

The failure mode, concretely. StandardScaler().fit(X) on the whole dataset computes a mean and standard deviation that already "know about" the validation rows; every fold's "training" data has then been centered and scaled using information from rows it will later be evaluated against, which inflates validation metrics by an amount that depends on how much the fold's distribution differs from the full dataset's.

Code (executed and verified: cross_val_score refits the scaler inside every fold automatically).

from sklearn.pipeline import Pipeline
from sklearn.preprocessing import StandardScaler
from sklearn.linear_model import LogisticRegression
from sklearn.model_selection import cross_val_score

pipe = Pipeline([
    ("scale", StandardScaler()),
    ("clf", LogisticRegression()),
])
scores = cross_val_score(pipe, X, y, cv=5, scoring="accuracy")

Run on 300 synthetic rows, this produced per-fold accuracies of [0.833, 0.750, 0.933, 0.867, 0.850]: the point isn't the specific numbers, it's that cross_val_score calls .fit() on the WHOLE pipeline (scaler included) inside each fold, so StandardScaler only ever sees that fold's training rows, never the validation rows, automatically.

Structured elaboration: the API features that enforce this. Pipeline makes .fit(), .transform(), and .predict() atomic across every step, so calling .fit() on the pipeline as a whole (which is what cross_val_score, GridSearchCV, and cross_val_predict all do internally) refits every preprocessing step from scratch on only the data handed to that call. The API failure mode to watch for is calling scaler.fit_transform(X) yourself BEFORE handing data to cross-validation, since at that point the leak has already happened outside the CV loop's visibility, and no amount of correct Pipeline usage downstream fixes it.

Trade-offs and pitfalls. Pipeline costs you the convenience of caching an expensive shared transform (say, a slow embedding lookup) across folds, since it's designed to recompute per fold on purpose; for a genuinely expensive, purely-input-derived transform (not fit on the target), Pipeline's memory parameter can cache the fitted step, but be careful this doesn't quietly re-introduce a data-dependent fit that should have been fold-local.

Overfitting: a model captures noise or idiosyncrasies in the training data and performs much better on training than on unseen data. Underfitting: a model is too simple to capture the underlying signal and performs poorly on both training and validation.

Learning curves: plot model performance (e.g., accuracy or RMSE) on training and validation sets versus training set size.

• Overfitting signature: training performance is high, validation performance is much lower; gap persists as data grows. Validation may improve slowly with more data. Worked example (train/validation accuracy at increasing training-set size n): n=100: train=99%, val=58%; n=500: train=98%, val=61%; n=1000: train=97%, val=62%; n=2000: train=97%, val=63%. The training curve sits near-flat and high the whole way, the validation curve creeps up only slowly, and the gap between them (roughly 35-40 points) barely narrows even as n quadruples: that persistent, wide, flat gap is exactly the "training error stays low while validation error stays high and roughly flat" pattern the question describes.
• Underfitting signature: both training and validation performance are poor and close to each other; adding more data doesn't help much. Worked example: n=100: train=64%, val=61%; n=500: train=66%, val=64%; n=1000: train=67%, val=65%; n=2000: train=67%, val=66%. Both curves sit low from the start, stay within a couple of points of each other throughout, and neither one climbs meaningfully as n grows: the model has hit a capacity ceiling that more data can't fix, unlike the overfitting case where the gap (not the level) was the problem.

Practical BI pipeline changes

• If I detect overfitting: add regularization or simplify the model used in automated reports (e.g., switch from a high-cardinality decision tree to a regularized logistic regression or apply L1 feature selection). Also enforce cross-validation in model training and reduce dimensionality (aggregate categorical levels, drop redundant features) before publishing predictions to dashboards. Applied to the worked example above, adding L2 regularization and dropping redundant features would be expected to pull training accuracy down from the high-90s toward the mid-80s while lifting validation accuracy from the low 60s toward the low-to-mid 70s, narrowing the gap rather than closing it in one step.
• If I detect underfitting: enrich features and increase model capacity: add derived features (time-based aggregations, interaction terms), loosen regularization, or use a more flexible model (e.g., gradient boosted trees) in the modeling stage. Re-run validation and update ETL to include the new features so dashboards reflect improved predictions. Applied to the worked underfitting example above, swapping in gradient boosted trees and adding derived features would be expected to lift both curves together, e.g. training accuracy from ~67% toward the mid-80s and validation from ~66% toward the high-70s, since the ceiling here was capacity, not overfitting.

These changes balance predictive accuracy with interpretability and operational constraints typical in BI deliverables.