Two lakehouse reports off one Databricks build: a completion-risk list and a customer segment profile

Every work order that never completed in this dataset was never billed, so its revenue and direct_cost are both 0, exactly matching whether the job completed. Hand a classifier those two columns and it scores beautifully, because it is reading the answer, not predicting it. That failure has a name, leakage, and it is the single most common way a model demo lies.

This build produces two reports off the same Databricks tables. Report 1: the pre-visit completion-risk list, a small, legible model a dispatcher could work before the appointment window closes. Report 2: the customer segment profile, an unsupervised readout an account team could use to treat a recurring high-value account differently from a one-off price shopper.

Both run on the fictional field-service company from the medallion lakehouse post: work orders, technicians, revenue, and cost. Synthetic data. Seeded generator, no real company.

Report 1: the pre-visit completion-risk list

Given a booked work order, will it complete or fall through? A dispatcher works this list before the appointment window closes, so knowing which jobs are at risk buys time to follow up.

• The label is binary, from the silver status column: completed is 1, cancelled is 0. Orders still scheduled are dropped, because their outcome is not resolved yet, and training on an unresolved label is its own quiet bug.
• The scoring point is a pre-service checkpoint: after the confirmation reminder goes out, before the job is worked. Anything settled by that point is a fair feature; anything that only exists once the order resolves is not. The test is whether I could have known it before the outcome, not whether it existed at the moment of booking.

The feature I refused

• Every order that never completed was never billed, so its revenue and direct_cost are both 0, exactly matching the label.
• A model handed revenue scores a near-perfect AUC on this data, and that perfect score is the leak announcing itself, not the model being good.
• I left revenue, direct cost, and margin out of the model on purpose, because an order that was never completed was never billed, so those columns just re-encode the answer.
• What stays: service_category, technician_id, lead_time_days, prior_no_shows, and appointment_confirmed, all settled before the outcome.

What the numbers say

• 24 synthetic work orders, built so the signal is real but imperfect: 14 completed, 10 did not.
• Of the 15 confirmed orders, 13 completed (87%); of the 9 unconfirmed, only 1 did (11%), the strongest honest signal in the frame.

CompletedNot completed

• It is noisy on purpose: one order completed without a confirmation, and two were confirmed and still fell through. No single feature separates the classes cleanly, which is what a model should have to work with.

How I split, fit, and read it

• A stratified 70/30 train and validation split, seed 42: about 17 rows to train, 7 to validate. Stratifying keeps the completed-to-not ratio steady in both folds, which matters when the folds are this small.
• I reach for logistic regression, not because it is the strongest classifier, but because I can read it. One coefficient per feature, and the sign tells you the direction it pushes the prediction. If the signs come out the way the data was built, appointment_confirmed positive, lead_time_days and prior_no_shows negative, that is a sanity check that the model learned the mechanism I designed, not a result to report on its own. A small decision tree is an equally legible alternative, since you can print the splits directly.
• Tools I'd use: pandas and scikit-learn for a first pass at this size. At real scale, pyspark.ml's VectorAssembler and LogisticRegression train the same model distributed, directly over the Delta tables, same workflow, different engine.

How to read the metrics without overclaiming, with completed as the positive class:

• Precision: of the orders the model called completed, the share that truly completed.
• Recall: of the orders that truly completed, the share the model caught.
• AUC: ranking quality across every threshold. 0.5 is a coin flip, 1.0 is perfect separation, and on this data a 1.0 would be a leakage alarm, not a win.
• The confusion matrix lays predicted against actual:

	Predicted not completed	Predicted completed
Actually not completed	true negatives	false positives
Actually completed	false negatives	true positives

• Operationally, a dispatcher cares more about catching the bookings that will fall through, so a real deployment flips the positive class to "not completed" and optimizes recall on it, so the follow-up list misses few at-risk jobs. I keep completed as the positive class here to keep the example plain.
• On a 24-row validation fold, I do not quote a specific precision or AUC. One misclassified row swings the number too far for it to mean anything at this size, so the deliverable is the workflow and the confusion-matrix read, not a metric I would defend as this model's ceiling. On real data, the check is cross-validation, or a proper held-out period once there is enough of it.

Report 2: the customer segment profile

The other machine-learning question on this lakehouse has no label at all, and it fails in the opposite way. A classifier's classic sin is leakage; a segmentation's is reading structure into noise, or letting one unscaled column dominate the distance math and calling the result a "segment." There is no outcome to leak here, so leakage is not the risk. Scaling and careful interpretation are.

The task: group the customers by how they actually behave, so an account team treats a recurring high-value account differently from a one-off price shopper. The k-means mechanics themselves (k-means++, choosing k, why you standardize before clustering) I already cover in the sales-segmentation demo; this is the same method run where the data lives, on the lakehouse itself, with the result landed back where the rest of the stack can read it.

• Features are per-customer aggregates rolled up in Spark, directly against the silver work-orders table: how often a customer books, their average ticket, their gross margin, and how often they cancel, the field-service analog of RFM. Source is the per-order table, not the monthly gold aggregate, because gold is already rolled up by month and cannot be re-aggregated per customer.
• Standardize before clustering: unscaled, average ticket (hundreds of dollars) would drown out cancel rate (0 to 1) in the distance math, and every "segment" would really just be a revenue band. This is the unsupervised analog of the leakage check, the failure to refuse on purpose.
• k-means with k=4 and a fixed seed (42, same reason as the classifier's split: a rerun reproduces the same segments).

Recurring high-valueSteady corePrice-sensitiveAt-risk / churning

What the numbers say

Segment	Customers
Recurring high-value	12
Steady core	14
Price-sensitive	12
At-risk / churning	11
Total	49

• I do not attach a dollar figure to any segment, or call one "the profitable one." That would be inventing an outcome on data I made up. The only numbers I report are checkable properties of the artifact: how many segments (4), and the silhouette score on this set, a coherence measure, not a business result.
• Tools I'd use: pyspark.ml (VectorAssembler, StandardScaler, KMeans in one Pipeline), so the clustering runs on Spark, on the Delta tables, not a browser tab or a throwaway notebook.

Landing it where the business can read it

• The step that makes this a lakehouse exercise, not a notebook toy: write the segment labels back to a governed Delta table, fieldservice.gold.customer_segments.
• A Power BI semantic model can join that table to the fact table and slice every existing measure by segment, off one shared definition of what each segment is. That is why the model runs where the data lives: the answer lands somewhere the dashboard, the SQL, and the next model can all read it.

Where MLflow fits

• Wrapping a fit in an MLflow run logs the parameters, the metrics, and the trained model as artifacts, and each run shows up in the Experiments tab for comparison later.
• For the completion model: log the model type, the feature list, and the test-split fraction as params; precision, recall, and AUC as metrics; the fitted model as an artifact.
• For the segmentation: log k, the silhouette score, and the standardization choice as params and metrics; the fitted pipeline and the labeled gold.customer_segments table as artifacts.
• The payoff is not the logging call, it is what it buys: three months from now I can open a run and see exactly which features, which split, and which model produced a given number, so "why did version two score differently" is a diff of two logged runs, not a guess.
• Tools I'd use: MLflow, built into Databricks. mlflow.sklearn.autolog() before the fit captures most of this automatically.

Why these reports matter to the business

• The completion-risk list gives a dispatcher a follow-up target before the appointment window closes, instead of finding out about a no-show after the truck is already on the schedule.
• The segment profile lets an account team treat a recurring high-value account differently from a one-off price shopper, without anyone eyeballing the order history by hand.
• Both stay legible: the classifier's coefficients can be read and checked against the mechanism they were built to find, and the segments trace to four checkable aggregates, not a black box a manager has to take on faith.
• Both are reproducible: a fixed seed and a fixed split mean the same run rebuilds the same way tomorrow, and MLflow turns "why did this change" into a diff instead of a guess.

My process on the job

• Frame a resolvable label at a real scoring checkpoint, and drop rows whose outcome is not decided yet.
• Refuse any feature that only exists once the outcome does, even when it would flatter the metric.
• Split with a fixed seed, stratified when the classes are imbalanced.
• Reach for the most legible model that fits the task first, logistic regression or a shallow decision tree; a bigger model is a second step, not the default.
• Evaluate on the metric the operational decision actually needs (recall on the at-risk class, for a real dispatcher list), plus a confusion matrix, and skip quoting a headline number a small validation fold cannot support.
• For an unsupervised question, standardize before trusting any distance math, and do not attach a business outcome to a cluster the data cannot back.
• Land the result in a governed table the rest of the stack can read, not a notebook output.
• Track every run in MLflow: params, metrics, and the artifact, so a changed number is a diff, not a guess.

Key takeaways

• Leakage is the most common way a model demo lies. Here revenue, direct_cost, and margin were left out on purpose, because a not-completed order was never billed, so those columns just re-encode the label.
• A fair feature is one you could have known before the outcome happened. I score each order at a pre-service checkpoint and keep only the attributes settled by then.
• I reached for logistic regression because I can read it. The coefficient signs let me check whether the model learned the mechanism I designed, worth more than a marginal accuracy bump on a piece like this.
• On a 24-row validation fold, I do not quote a specific AUC or precision. One misclassified row swings the number too far, so the deliverable is the workflow and the confusion matrix, not a metric.
• Standardizing before the distance math is the segmentation's analog of the leakage check, and landing the segments in a governed Delta table means the dashboard, the SQL, and the next model all read one definition.

Related posts

Other walkthroughs built on the same fictional field-service company:

• The monthly gross-margin bridge report, where the silver and gold tables these features read from get built.
• Tracing gross margin from raw record to dashboard, following one order through every layer.
• Six enterprise SQL patterns for dedup, idempotent loads, and a Type 2 dimension.
• The Power BI star schema and DAX measure dictionary that the segment labels can slice.
• Putting the Power BI project under version control, so a measure edit gets a diff, a review, and an audit trail.