Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 36 ยท Projects and Practice

Project: A Governed Sales Mart with a Churn Model

Combine governance and ML: design a Unity Catalog sales mart with grants, masks and row filters, train a churn model, register it, and score customers in batch.

In the first project you built the data flow. This one adds the questions a real organisation asks next: who may see what, how do we use the data for a prediction, and how do we ship it safely? You will plan a sales mart in Unity Catalog and attach a churn model to it.

Databricks cannot run on this site. Product code is read-only; the runnable blocks are plain Python (numpy only) that perform the same steps on a tiny dataset so you can check the reasoning.

The scenario

A subscription shop wants (a) a gold sales mart for analysts across three regions, (b) a monthly list of customers likely to cancel, for the retention team. Constraints: email addresses are personal data, regional managers see only their region, and the model's training data must be traceable.

Step 1: design the catalog

Object Purpose Access
prod.silver.customers, prod.silver.orders Clean inputs data-engineers, ETL service principal
prod.gold.sales_mart Orders joined to customers analysts (masked), region managers (filtered)
prod.ml.customer_features Features per customer ml-engineers
prod.ml.churn_model Registered model ETL service principal: EXECUTE
prod.ml.churn_scores Monthly predictions retention team: SELECT
SQL
GRANT USE CATALOG ON CATALOG prod TO `analysts`, `regional-managers`, `retention`;
GRANT USE SCHEMA, SELECT ON SCHEMA prod.gold TO `analysts`, `regional-managers`;
GRANT USE SCHEMA ON SCHEMA prod.ml TO `retention`;
GRANT SELECT ON TABLE prod.ml.churn_scores TO `retention`;

Step 2: protect personal data

SQL
CREATE FUNCTION prod.gold.region_filter(region STRING) RETURNS BOOLEAN
RETURN is_account_group_member('global-sales')
    OR (is_account_group_member('managers-eu') AND region = 'EU')
    OR (is_account_group_member('managers-us') AND region = 'US');

ALTER TABLE prod.gold.sales_mart SET ROW FILTER prod.gold.region_filter ON (region);

CREATE FUNCTION prod.gold.mask_email(email STRING) RETURNS STRING
RETURN CASE WHEN is_account_group_member('pii-readers') THEN email
            ELSE concat(left(email, 1), '***@', split_part(email, '@', 2)) END;

ALTER TABLE prod.gold.sales_mart ALTER COLUMN email SET MASK prod.gold.mask_email;
ALTER TABLE prod.gold.sales_mart ALTER COLUMN email SET TAGS ('pii' = 'email');

Details in Lineage, Row Filters and Column Masks.

Step 3: features

A customer is likely to churn if they order less often and spend less than before. Compute simple features:

Python
features = (spark.table("prod.silver.orders")
    .groupBy("customer_id")
    .agg(F.count("*").alias("orders_90d"),
         F.avg("amount").alias("avg_amount"),
         F.datediff(F.current_date(), F.max("order_ts").cast("date")).alias("days_since_last")))
features.write.mode("overwrite").saveAsTable("prod.ml.customer_features")

Step 4: train, track and register

The runnable stand-in below builds a deterministic dataset and fits a logistic regression with plain numpy gradient descent, so you can see tracking, a metric and a promotion rule together.

Python Stand-in: train a churn model and decide on promotion

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice the baseline: a model must beat the naive "nobody churns" guess before you register it. On Databricks you would log each fit with mlflow.log_metric, register the best version as prod.ml.churn_model, and set the champion alias (see The Model Registry).

Step 5: score in batch and publish

Python
predict = mlflow.pyfunc.spark_udf(spark, "models:/prod.ml.churn_model@champion", result_type="double")
scores = (spark.table("prod.ml.customer_features")
          .withColumn("churn_risk", predict(F.struct("orders_90d", "avg_amount", "days_since_last")))
          .withColumn("scored_at", F.current_timestamp()))
scores.write.mode("overwrite").saveAsTable("prod.ml.churn_scores")
Python Stand-in: ranking customers for the retention team

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Step 6: operate it

  1. Job: tasks build_features -> score -> publish, monthly cron, run as a service principal.
  2. Monitoring: write predictions plus later outcomes to a table, compute accuracy by month, and alert when it falls or when input distributions drift.
  3. Governance review: lineage should show churn_scores connected back to silver.orders; the model's grants should be EXECUTE only for the scoring principal.
  4. Cost: scoring runs on serverless or a job cluster for minutes per month; no always-on endpoint is needed (see Feature Engineering and Model Serving).

Extensions

  • Add a point-in-time feature table so training uses features as of the cancellation date.
  • Serve the model on an endpoint for a support-agent screen.
  • Add a fairness check: compare risk scores between regions.
  • Bundle everything and deploy with CI.

Check your understanding

Quick check ยท 3 questions

of 3 answered You scored / 3 ยท perfect!

  1. 1. Why compare a new model with a baseline such as 'nobody churns'?

    A model that cannot beat a naive rule is not useful.
  2. 2. How do regional managers see only their rows without separate tables?

    A row filter evaluates group membership for every query.
  3. 3. Which privilege lets the scoring job use the registered model?

    Models are called with EXECUTE.

Summary

  • Design the catalog by audience: who reads what, who writes what.
  • Row filters, masks and tags protect personal data in the same table everyone queries.
  • Train, compare to a baseline, register, alias, and score in batch.
  • Monitor quality and lineage after launch.

Next: Databricks Practice Questions