dbt for Machine Learning Features AI Prompt

Use dbt to build and manage ML feature tables for training and serving.

ML use case: {{use_case}} (e.g. churn prediction, recommendation, fraud detection)
Features needed: {{features}}
Downstream ML platform: {{platform}} (SageMaker, Vertex AI, Feature Store, custom)

1. Why dbt for ML features:
   - Features computed in the warehouse are reproducible, testable, and versioned
   - dbt tests catch feature drift before it reaches the model
   - Same feature definitions for training AND serving (no training-serving skew)
   - Feature history available via incremental models or snapshots

2. Feature table design:
   Each feature table has:
   - entity_id: the prediction target (customer_id, user_id, etc.)
   - feature_date: the date the feature was computed (for point-in-time correctness)
   - One column per feature

   Example: fct_customer_features_daily
   customer_id | feature_date | days_since_last_purchase | order_count_30d | avg_order_value_90d

3. Point-in-time correct features:
   For training: join features to labels using the feature_date <= label_date condition
   SELECT
     l.customer_id,
     l.churned_flag,
     f.days_since_last_purchase,
     f.order_count_30d
   FROM {{ ref('training_labels') }} l
   LEFT JOIN {{ ref('fct_customer_features_daily') }} f
     ON l.customer_id = f.customer_id
     AND f.feature_date = l.label_date

4. Feature tests for ML:
   - No future leakage: verify feature_date is always <= the observation date
   - No nulls in required features: all input features must be non-null
   - Reasonable ranges: order_count_30d between 0 and 1000
   - Stability: feature distribution should not shift dramatically week-over-week

5. Export to ML platform:
   Option A: Export from warehouse to S3/GCS as Parquet for batch training
   Option B: Connect dbt-generated tables directly to a feature store (Feast, Tecton)
   Option C: Use dbt Cloud job to trigger a downstream Python training pipeline on completion

Return: feature table schema, point-in-time join pattern, ML-specific tests, and export strategy.