Difficulty

Advanced

Reward

700 XP · 🧠 Feature Engineer

Prerequisites

PySpark · Delta Lake · Time-Windowing

Primary Stack

Databricks · PySpark · MLflow

Target

Snowflake · Delta Feature Store

Development Task

DEV-007

Customer ML Feature Engineering Pipeline

Design and implement a production-grade ML feature engineering pipeline that transforms trusted historical customer transactions into point-in-time accurate, reusable features with strict data leakage prevention, rolling time-window aggregations, and seamless Snowflake feature store publishing.

Project Brief

Understand data engineering for machine learning and point-in-time feature computation.

Business Scenario

The Data Science team at an omnichannel retail enterprise is developing customer lifetime value (LTV) and churn prediction models. To train reliable models and power real-time inference, data scientists require dozens of behavioral features computed across historical customer interactions.

Currently, individual data scientists write ad-hoc SQL and pandas scripts on their local laptops, leading to duplicated feature code, subtle calculation discrepancies, and severe **data leakage**—where future transaction data accidentally leaks into historical training observations.

Development Objective

Design and develop a production data engineering feature pipeline in Databricks and PySpark. The pipeline must ingest trusted Silver customer and transaction tables, calculate standardized behavioral feature sets (RFM metrics, category diversity, rolling 30/90/180-day spend), enforce point-in-time correctness without future leakage, generate reproducible training snapshots, support daily incremental feature refreshes, and publish curated feature tables to Snowflake.

Expected Outcome

Leakage-Free Features

Guarantee that features computed for prediction point T strictly observe data occurring prior to T.

Rolling Window Metrics

Compute rolling 30, 90, 180, and 365-day behavioral metrics (frequency, monetary, diversity).

Deterministic Reproducibility

Ensure re-executing historical feature generation yields bit-for-bit identical feature matrices.

Incremental Feature Refresh

Update daily active customer features incrementally without recomputing entire multi-year histories.

Delta Feature Store

Persist versioned feature sets in Delta Lake with metadata schema definitions.

Snowflake ML Serving Mart

Publish feature tables to Snowflake for both batch model training and downstream analytics.

Developer Focus

This is a **Data Engineering for ML** challenge, not model tuning. Your responsibility is to engineer scalable, deterministic, and leakage-free feature pipelines that serve both offline model training and online feature lookup.

Source System

Inspect the input Silver entity datasets: customer master profiles and line-item sales transactions.

Input Datasets Overview

The feature pipeline consumes from trusted Silver customer master records and validated line-item transaction tables.

Customer Master

Silver Delta (Keyed on customer_id)

Transaction Log

Silver Delta Sales Transactions

Historical Scope

3 Years of Historical Transactions

Target Entities

Customer Entity Level

Primary Key

customer_id + as_of_date (or feature_timestamp)

Serving Target

Snowflake ML_FEATURES & Delta Feature Store

Source Schema Entities

silver_customers*

DELTA TABLE

customer_id, registration_date, segment, state, country, is_active, status.

silver_transactions*

DELTA TABLE

transaction_id, customer_id, store_id, product_id, transaction_timestamp, quantity, unit_price, discount, net_amount.

silver_products*

DELTA TABLE

product_id, product_name, category, sub_category, cost_price, list_price.

Key Feature Categories to Derive

Recency (R)

Days since last purchase as of feature_timestamp; days between first and last purchase.

Frequency (F)

Total transaction count in 30d, 90d, 180d, and lifetime windows; average transactions per active month.

Monetary (M)

Total net spend, average order value (AOV), max single order spend, and total discount claimed in 30d/90d/180d.

Product & Category Diversity

Count of distinct product categories purchased, top purchased category by spend, return/cancellation ratio.

Trend & Velocity Indicators

Ratio of 30d spend vs 90d spend; velocity acceleration or deceleration signals.

Critical Engineering Rule

When computing features for observation date `T` (e.g. `2026-06-01`), **NO transaction with timestamp > `T`** may enter the calculation window. Even a 1-second future timestamp leak invalidates ML model safety.

Expected Architecture

Point-in-time feature generation architecture: observation spine, windowed joins, validation, and serving.

Feature Pipeline Flow

The architecture uses point-in-time observation spines (as-of date grids) to compute historical feature snapshots without data leakage, persisting versioned feature sets in Delta Lake and Snowflake.

Silver Trusted Sources

Databricks / Delta Lake

Cleansed customer profiles, transaction line items, and product category metadata.

↓

Observation Spine Generator

PySpark / SQL

Generates entity observation grid (customer_id × observation_date) for point-in-time historical alignment.

↓

Point-in-Time Windowing

PySpark / Range Joins

Join transactions strictly on transaction_timestamp <= observation_date to eliminate future data leakage.

↓

Feature Aggregation Engine

PySpark Transformations

Compute Recency, Frequency, Monetary, Diversity, and Velocity features across 30d/90d/180d windows.

↓

Feature Validation & Null Imputation

Great Expectations / PySpark

Validate distributions, assert absence of future timestamps, and apply deterministic null imputation.

↓

Delta Feature Store & Snowflake

Delta Lake / Snowflake

Host versioned offline feature tables and synchronize current feature vectors for model serving.

Architecture Expectations

Strict Point-in-Time Correctness

Features calculated for any historical timestamp must strictly mirror what was known at that exact moment in time.

Deterministic Null Handling

Customers with zero transactions in a 30-day window must have spend=0.0 and count=0, rather than propagating NULLs.

Incremental Refresh Support

Daily production runs must calculate as-of-today features without having to backfill 3 years of history.

Feature Store Metadata & Versioning

All feature definitions, descriptions, and data types must be recorded in feature catalog documentation.

Architecture Note

Separate your feature engineering pipeline into two modes: **Historical Backfill** (generating multi-timestamp training spines) and **Daily Production Refresh** (computing latest feature vectors for live model inference).

Development Requirements

Implement feature calculation modules, point-in-time joins, leakage prevention, and serving tables.

Developer Responsibilities

The implementation must address the following 15 engineering requirements across feature engineering, leakage testing, data validation, and deployment.

Source Preparation & Customer Cohorts

Prepare trusted customer master and transaction datasets.

Required Checklist

✓Filter active customers and join registration date metadata.

✓Cast transaction timestamps to UTC TIMESTAMP and monetary columns to DECIMAL.

✓Filter out test accounts and non-financial records.

Expected Outcome

Clean, trusted baseline inputs for feature engineering.

Observation Spine Generation

Create point-in-time observation grids for historical feature calculation.

Required Checklist

✓Generate periodic snapshot dates (e.g. 1st of every month or weekly spines).

✓Cross-join active customer_ids with observation_dates where observation_date >= registration_date.

Expected Outcome

Structured (customer_id, as_of_date) observation grid.

Point-in-Time Join & Leakage Prevention

Prevent future transaction data from leaking into past features.

Required Checklist

✓Enforce join condition: transaction_timestamp <= as_of_date.

✓Ensure window bounds strictly calculate looking backwards from as_of_date (e.g. as_of_date - INTERVAL 30 DAYS).

✓Assert maximum transaction_timestamp in feature group is <= as_of_date.

Expected Outcome

Guaranteed leakage-free historical feature matrix.

Recency (R) Feature Calculations

Derive customer purchase recency and tenure metrics.

Required Checklist

✓Calculate days_since_last_purchase = datediff(as_of_date, max(transaction_date)).

✓Calculate customer_age_days = datediff(as_of_date, registration_date).

✓Calculate days_between_first_last_purchase.

Expected Outcome

Temporal customer recency features.

Frequency (F) Rolling Window Metrics

Derive transaction volume across multiple time horizons.

Required Checklist

✓Compute count_orders_30d, count_orders_90d, count_orders_180d, count_orders_lifetime.

✓Compute average_orders_per_month across tenure.

Expected Outcome

Multi-horizon transaction frequency features.

Monetary (M) Rolling Spend Metrics

Derive financial value and order size statistics.

Required Checklist

✓Compute sum_net_spend_30d, sum_net_spend_90d, sum_net_spend_180d, sum_net_spend_lifetime.

✓Compute avg_order_value_30d and avg_order_value_lifetime.

✓Compute max_order_spend_lifetime and sum_discount_amount_90d.

Expected Outcome

Robust monetary and spending capacity features.

Product Category Diversity Metrics

Capture customer category preferences and shopping breadth.

Required Checklist

✓Compute count_distinct_categories_purchased in 90d window.

✓Identify preferred_category (highest spend category in 180d).

✓Calculate ratio of private_label vs national_brand spend.

Expected Outcome

Categorical preference and diversity features.

Velocity & Spending Acceleration Indicators

Derive momentum features capturing changes in customer behavior.

Required Checklist

✓Compute spend_ratio_30d_vs_90d = (sum_spend_30d / (sum_spend_90d / 3.0)).

✓Compute frequency_trend_30d_vs_90d.

✓Handle division by zero gracefully using nullif or conditional logic.

Expected Outcome

Leading indicators of customer churn and acceleration.

Deterministic Null Imputation & Encoding

Ensure feature vectors are directly consumable by ML models.

Required Checklist

✓Impute 0 for counts and spend features when customer had no transactions in window.

✓Set default days_since_last_purchase = customer_age_days if no transactions exist.

✓One-hot or frequency encode categorical attributes (segment, top_category).

Expected Outcome

Clean numerical feature matrix free of unhandled NULLs.

Incremental Daily Feature Refresh

Compute current feature vectors efficiently for daily production runs.

Required Checklist

✓Implement daily incremental pipeline where as_of_date = current_date().

✓Execute MERGE into Delta and Snowflake feature tables on customer_id.

✓Avoid scanning entire historical transaction files by reading 180-day partition window.

Expected Outcome

Fast, cost-effective daily feature updates.

Delta Feature Store & MLflow Integration

Persist feature tables with versioning and lineage tracking.

Required Checklist

✓Write to Delta table CUSTOMER_FEATURE_STORE partitioned by as_of_date.

✓Log feature schema metadata and lineage parameters in MLflow.

Expected Outcome

Governed, reproducible feature store.

Snowflake ML Serving Table Publishing

Publish curated feature tables to Snowflake.

Required Checklist

✓Publish offline training table ML_CUSTOMER_FEATURES_TRAINING to Snowflake.

✓Publish online inference table ML_CUSTOMER_FEATURES_CURRENT to Snowflake.

✓Ensure primary key (customer_id, as_of_date) uniqueness.

Expected Outcome

Synchronized Snowflake feature serving layer.

Data Quality & Distribution Assertions

Validate feature statistical distributions.

Required Checklist

✓Assert non-negative values for all spend, count, and recency features.

✓Assert spend_30d <= spend_90d <= spend_180d for every row.

✓Flag extreme outlier features exceeding 99.9th percentile thresholds.

Expected Outcome

Guaranteed statistical integrity across feature sets.

Feature Lineage & Data Dictionary

Publish complete feature definitions and formulas.

Required Checklist

✓Document exact mathematical formulas, window definitions, and null imputation rules for all features.

Expected Outcome

Comprehensive feature catalog for data scientists.

Configuration Externalization

Externalize window sizes and serving paths across environments.

Required Checklist

✓Maintain YAML config specifying window lengths (30, 90, 180), storage paths, and Snowflake schemas.

Expected Outcome

Configuration-driven feature pipeline deployments.

Implementation Rationale

Structuring feature engineering logic into modular transformer classes allows data scientists to register new features without rewriting core windowing and join logic.

Testing Requirements

Demonstrate point-in-time correctness, data leakage prevention, null imputation, and incremental refreshes.

Required Test Scenarios

Validate the feature pipeline against the following 11 test scenarios:

Point-in-Time Data Leakage Test

Scenario

Inject a large transaction on 2026-07-15 and compute features for as_of_date = 2026-07-01.

Expected Result

The 2026-07-15 transaction has ZERO impact on the 2026-07-01 feature row; sum_net_spend_30d excludes it.

Window Monotonicity Assertion

Scenario

Verify spend_30d <= spend_90d <= spend_180d <= spend_lifetime for every customer.

Expected Result

0 rows violate monotonicity; 100% of rows satisfy spending window containment.

Zero-Transaction Customer Null Handling

Scenario

Compute features for a newly registered customer who has made zero purchases.

Expected Result

Features return count_orders = 0, sum_spend = 0.00, and days_since_last_purchase = customer_age_days without NULLs.

Recency Calculation Accuracy

Scenario

Customer purchase occurs exactly 14 days prior to as_of_date.

Expected Result

days_since_last_purchase evaluates to exactly 14.

Category Diversity Calculation

Scenario

Customer buys items across 3 distinct categories in the 90-day window.

Expected Result

count_distinct_categories_purchased_90d equals 3.

Velocity Ratio Division by Zero

Scenario

Customer has zero spend in 90d and zero spend in 30d.

Expected Result

spend_ratio_30d_vs_90d safely evaluates to 0.0 or 1.0 without runtime divide-by-zero crash.

Historical Feature Reproducibility

Scenario

Re-run historical feature pipeline for past observation dates twice.

Expected Result

Output feature matrix matches bit-for-bit with 0.00 variance.

Daily Incremental Refresh Test

Scenario

Execute daily feature refresh for today's date.

Expected Result

Feature store updates active customer records via MERGE in under 5 minutes.

Snowflake Serving Table Integrity

Scenario

Query Snowflake ML_CUSTOMER_FEATURES_CURRENT for duplicate customer_ids.

Expected Result

Primary key uniqueness holds with 0 duplicate customer records.

Negative Value & Outlier Check

Scenario

Run distribution assertions across all numerical features.

Expected Result

0 negative spend or count values detected; outliers above threshold are flagged.

Reconciliation vs Silver Source

Scenario

Compare sum(sum_net_spend_lifetime) across all customers in feature store against sum(net_amount) in Silver.

Expected Result

Total feature lifetime revenue equals Silver source transactions exactly.

Testing Principle

Data leakage is the most dangerous bug in ML engineering because models trained on leaked data show 99% accuracy in testing but fail catastrophically in production. The point-in-time leakage test is non-negotiable.

Acceptance Criteria

Verify that the ML feature pipeline fulfills all production Definition of Done criteria.

Definition of Done

The implementation is complete when all 10 criteria are satisfied:

Zero Future Data Leakage Proven

Point-in-time tests verify that no transaction data occurring after observation timestamp enters features.

RFM & Diversity Features Calculated

Recency, Frequency, Monetary, Diversity, and Velocity features are calculated across 30d/90d/180d windows.

Deterministic Imputation Enforced

Feature matrices contain zero unexpected NULL values; zero-activity customers default cleanly.

Window Monotonicity Holds

All spend and transaction count features satisfy nested window containment.

Historical Reproducibility Verified

Re-running past feature generation runs yields identical feature datasets.

Incremental Daily Refresh Working

Daily production refresh executes efficiently without full historical recomputation.

Delta Feature Store & Snowflake Deployed

Feature tables are populated in Delta Lake and synchronized to Snowflake ML schemas.

All 11 Test Scenarios Pass

Complete test suite passes with leakage validation outputs and reconciliation logs.

Data Dictionary Published

Mathematical formulas and descriptions for every feature are documented in markdown.

Modular & Maintainable Codebase

Feature logic is organized into clean transformer classes free of hard-coded credentials.

Acceptance Rule

Sign-off requires execution test logs explicitly demonstrating that injecting future events does not alter historical feature outputs.

Developer Deliverables

Submit all feature engineering code, leakage tests, schema catalogs, and validation evidence.

Required Deliverables

The submission must include the following 10 artifacts:

Observation Spine Generator

PySpark module generating point-in-time snapshot grids for training datasets.

Feature Transformer Modules

Modular PySpark classes computing Recency, Frequency, Monetary, Diversity, and Velocity features.

Leakage-Free Point-in-Time Join Logic

Range join implementation enforcing transaction_timestamp <= as_of_date.

Null Imputation & Encoding Pipeline

Transformation step replacing missing activity with default zeroes and encoding categorical flags.

Daily Incremental Refresh Pipeline

Job updating latest customer feature vectors in Delta Lake and Snowflake via MERGE.

Snowflake ML Tables DDL & Loader

SQL DDL and loader scripts staging and publishing feature tables to Snowflake.

Point-in-Time Data Leakage Test Suite

Automated tests asserting zero future data leakage under synthetic out-of-order event injection.

Automated Test & Validation Suite

Complete test suite covering all 11 required verification scenarios.

Feature Data Dictionary

Documentation containing names, definitions, formulas, and data types for all engineered features.

Configuration & Architecture Guide

YAML configuration files and README explaining point-in-time join design and scaling strategy.

Submission Principle

Ensure your test suite can be run on synthetic test data to demonstrate leakage prevention without external DB dependencies.

Engineering Constraints

Adhere to strict operational and architectural boundaries for ML data engineering.

Required Boundaries

The solution must strictly comply with the following 10 constraints:

Zero Future Data Leakage

Never include events with timestamp > as_of_date in historical training feature calculations.

Deterministic Feature Computation

Feature formulas must be strictly deterministic; avoid non-deterministic random seeds or volatile datetime calls.

Decoupled from Model Training

Feature engineering code must prepare data for storage and serving; do not embed model training code in this pipeline.

Controlled Memory & Shuffle

Point-in-time joins across large transaction files must be partitioned to prevent Spark out-of-memory errors.

No Hard-Coded Credentials

All storage paths, secret scopes, and database credentials must be externalized in configuration.

Explicit Null Handling

Never leave unhandled NULLs in numeric feature columns that could break downstream ML algorithms.

Idempotent Serving Updates

Re-running daily feature refreshes must not produce duplicate customer rows in Snowflake.

Feature Lineage Traceability

Every feature must have clear provenance back to the underlying Silver transaction column.

Scalable for Billions of Rows

Design windowing logic using PySpark DataFrame APIs rather than single-node pandas transformations.

Production Code Quality

Code must be modularized into reusable classes/functions with proper type annotations and docstrings.

Constraint Notice

Any code that uses `current_date()` inside historical training backfill queries will result in immediate rejection due to data leakage violation.

Suggested Project Structure

Recommended repository layout for production ML feature engineering.

Recommended Project Layout

Structure your repository to separate spine generation, feature transformers, point-in-time joins, tests, and documentation:

DEV-007-ml-feature-pipeline/
│
├── README.md
│
├── config/
│   ├── dev.yaml
│   └── prod.yaml
│
├── src/
│   ├── spine/
│   │   └── observation_spine.py    # (customer_id, as_of_date) grid builder
│   ├── transformers/
│   │   ├── recency_features.py     # Days since last purchase & tenure
│   │   ├── frequency_features.py   # Rolling 30d/90d/180d order counts
│   │   ├── monetary_features.py    # Rolling spend, AOV & discounts
│   │   ├── diversity_features.py   # Distinct categories & preferred brand
│   │   └── velocity_features.py    # 30d vs 90d acceleration metrics
│   ├── pipeline/
│   │   ├── point_in_time_join.py   # Leakage-free range join engine
│   │   ├── null_imputer.py         # Deterministic default values
│   │   ├── backfill_job.py         # Multi-timestamp historical generator
│   │   └── daily_refresh_job.py    # Incremental as-of-today refresh
│   └── sinks/
│       ├── delta_feature_store.py  # Delta Lake feature store writer
│       └── snowflake_publisher.py  # Snowflake staging & merge loader
│
├── tests/
│   ├── test_leakage_prevention.py  # Future-timestamp injection test
│   ├── test_window_monotonicity.py
│   ├── test_recency_calculation.py
│   ├── test_null_imputation.py
│   └── test_daily_incremental.py
│
└── docs/
    ├── feature_dictionary.md       # Full formulas & schema metadata
    └── point_in_time_design.md     # Mathematical proof of leakage prevention

Module Responsibilities

src/spine/

Builds observation date grids for entities, establishing historical prediction timestamps.

src/transformers/

Modular feature calculation classes computing RFM, diversity, and velocity metrics.

src/pipeline/

Enforces point-in-time join constraints, applies null imputation, and manages backfill/daily jobs.

src/sinks/

Persists versioned feature tables to Delta Lake and Snowflake ML schemas.

tests/

Test suite verifying leakage prevention, window containment, reproducibility, and null defaults.

docs/

Feature data dictionary containing mathematical formulas, descriptions, and lineage.

Design Rationale

Decoupling individual feature transformers from the point-in-time join engine ensures new business features can be added and unit-tested in isolation without re-engineering the complex time-window join machinery.

Submission Checklist

Final engineering quality checklist before submitting DEV-007.

Final Review Checklist

Verify every checklist item before submitting your feature engineering pipeline:

✓

Observation Spine Generation Working

Periodic snapshot grids are constructed correctly without missing active customer cohorts.

✓

Point-in-Time Joins Enforced

All transaction joins strictly obey transaction_timestamp <= as_of_date.

✓

Zero Data Leakage Validated

Automated tests prove that future transaction injection does not alter historical feature rows.

✓

RFM & Diversity Features Implemented

Recency, Frequency, Monetary, Diversity, and Velocity features are computed accurately.

✓

Deterministic Null Imputation Verified

Zero-activity customers receive default zeroes and tenure days without unhandled NULLs.

✓

Window Monotonicity Holds

All spend and count features satisfy 30d <= 90d <= 180d <= lifetime containment.

✓

Incremental Daily Refresh Tested

Daily production job refreshes as-of-today features via MERGE without full historical scans.

✓

Delta Feature Store & Snowflake Sync Active

Feature tables are stored in Delta Lake and synchronized to Snowflake ML serving schemas.

✓

All 11 Test Scenarios Pass

Complete test suite passes with leakage verification outputs and reconciliation evidence.

✓

Feature Data Dictionary Published

docs/feature_dictionary.md contains exact formulas, descriptions, and data types for all features.

✓

Code Quality & Modularity

Code is structured into clean modular transformers free of hard-coded credentials or volatile dates.

Ready for Review

Submit DEV-007 only after the point-in-time joins, RFM feature transformers, leakage tests, Snowflake sync, incremental refresh, and data dictionary have been thoroughly validated.