The Five Columns ML Platform Wants
dbt hands your code to a remote runtime as an opaque string and gets a relation back. It never reads your Python.
Chapter 20 declared what a number means. This chapter asks the same question about a transformation: when does it belong in SQL, and when does reaching for Python actually win?
ML Platform has asked for a churn feature table — one row per listener, five columns, computed off Chapter 9's fct_listens. The word "feature engineering" makes people reach for pandas. Commit to a language on all five columns below before the widget reveals anything.
A dbt Python model is not "dbt running Python." It is dbt generating a stored procedure or a job submission whose body is your function, wrapping it in a machine-written shim, and materializing the returned DataFrame through the ordinary SQL materialization path. Everything that feels missing — Jinja, --empty, unit tests, views, column-level lineage — is missing for one reason: dbt hands your code to a remote runtime as an opaque string and gets a relation back. It never reads your Python.
Most of what gets called "ML feature engineering" is plain SQL. Counts, ratios, recency, windowed aggregates, entropy, OLS slope, streaks, MAD z-scores and — the one that catches everyone — exponential decay weights are all set-based: EXP, LN, POWER, REGR_SLOPE and MEDIAN exist in Snowflake, BigQuery and Databricks alike. The irreducible thing is iterating a numerical optimisation to a convergence criterion over a group, which is an argmax, and SQL has neither an argmax nor a convergence predicate. Get this split wrong and you pay for it in every guarantee Chapter 7 through Chapter 14 spent thirteen chapters building.
- dbt Python model
- A .py file in models/ defining exactly one function named model(dbt, session) that returns a DataFrame. dbt materializes the returned frame as a table (or an incremental table) and nothing else.
- The irreducible case
- A numerical optimisation iterated to convergence over a group — an argmax with a stopping rule. Every other feature in this chapter is a set-based aggregate a warehouse already knows how to plan and parallelise.
- p_silent_14d
- The probability a listener goes 14 days without a listen, from a two-parameter Weibull fit over their inter-listen gaps. The only column here that genuinely needs Python — and the only one that returns NULL on Wavelength's shipped data.
- Half-life decay weight
- A listen aged d days contributes 2^(-d/7) with a 7-day half-life. It sounds like numpy. It is POWER(0.5, d / 7.0) inside a SUM.
| Column | Verdict | Because |
|---|---|---|
| listens_28d | SQL | count(*) |
| active_days_28d | SQL | count(distinct listen_date) |
| completion_rate_28d | SQL | avg(case when is_completed then 1 else 0 end) |
| recency_weighted_listens_hl7 | SQL | sum(power(0.5, datediff('day', listen_date, asof) / 7.0)) — the flagship trap |
| p_silent_14d | Python | scipy.stats.weibull_min.fit(x, floc=0) — an argmax with a convergence test |
Four of five stay in SQL, which is the same verdict Chapter 1’s fit wizard already grades as a pass: "mostly SQL, with a little Python for one or two steps." This chapter demonstrates it with code rather than re-running the wizard.
Hearing "exponential decay weights" and reaching for a Python model without checking whether the warehouse has POWER. You copy every qualifying row of a fact table into one process to compute a sum(). The answer is identical to the digit — 4.133 / 3.030 / 2.421 either way — and you have traded --empty, unit tests, column-level lineage and the optimizer for it. Section 2 runs both versions side by side.
Trusting a feature column because its arithmetic is simple. u_4's completion_rate_28d reads 0.400 and leans on ls_507, the row carrying Chapter 9's drift bug (ep_1008's duration lands in fct_listens as 1,980 ms instead of 1,980,000). Here the 90% threshold happens to clear under both durations, so is_completed is true either way — the right number for the wrong reason. Move one notch to avg(pct_completed) and u_4 reads 18,743.5 where the truth is 75.3. A feature table inherits every upstream defect silently, and a churn model would learn from the poisoned column without a single test going red.
Before writing a Python model, write the SQL you think is impossible. Half the time it compiles.
Say out loud which single operation in the feature is not expressible as a set operation. If you cannot name one, the whole node is SQL.
Check the warehouse function list before the pandas docs — POWER, EXP, LN, MEDIAN and REGR_SLOPE cover more "ML feature engineering" than most people expect.
A dbt Python model returns a DataFrame — Snowpark, PySpark or pandas, depending on your warehouse — and dbt materializes it as a table. What a DataFrame is, why nothing happens until something forces execution, and what a shuffle costs are the Apache Spark KB’s subject; pandas itself belongs to the Python KB. Everything in this chapter is about the seam: the contract dbt asks your function to honour, and what that seam costs you in the dbt guarantees you already have.
DataFrames and Datasets — Apache Spark KBPoint-in-time correctness, as-of joins, label leakage, training/serving skew and what a feature store is for are argued in full there and are not re-argued here. This chapter assumes the feature definition is already correct and asks only where the code that computes it should live.
ML feature and feature-store modeling — Data Modeling KBThe obvious second parent for a churn feature set is dim_subscription — tenure, plan, price. It is not used, and the reason is arithmetic rather than taste: fct_listens keys the person as user_id (u_4, u_7, u_9) and dim_subscription keys them as listener_id (L-9001 through L-9006), with no crosswalk anywhere in this project. The join returns zero rows. Chapter 7 set the precedent when it refused to build a test across the sh_11-versus-101 boundary rather than re-key either side; this chapter does the same, and everything dim_subscription would have contributed is trivial SQL anyway.
The weak answer is "yes, dbt supports Python models." The strong answer starts by splitting the feature set: name the columns that are set-based aggregates and stay in SQL, then name the single operation that is not — usually an iterative fit, an argmax, or a convergence criterion. Then close with the part interviewers rarely hear: the Python node should ref the narrowest pre-aggregated relation you can build, because everything you push down into SQL is work the optimizer does in parallel instead of work one process does alone.
Four of five "ML features" are SQL; the fifth is one function call over four rows. Push everything expressible down into SQL, and let the Python node ref the narrowest pre-aggregated relation it possibly can.