Databricks
Generative AI

Building a Customer Complaint Reply Generator with Databricks and Generative AI

A Databricks and Gemini pipeline that drafts a personal reply to every waiting complaint, checks each one against the house rules, and puts a person in front of every reply before it goes out.

Role: Independent case study
Year: 2026
Stack: Databricks · Unity Catalog · Delta Lake · Databricks Model Serving · Databricks Secrets · MLflow · Gemini · Python · pandas

Hearth & Oak is a fictional online furniture retailer from the Databricks Agentic AI Engineer Bootcamp. Forty-six unhappy customers were waiting for a reply, the oldest for nine days. The few who heard back all got the same template: no name, nothing about what broke, and an apology for “any inconvenience”. I built a pipeline on Databricks that drafts a personal reply to every complaint with Gemini, checks each draft against the company’s house rules, and queues it for a person to read before anything reaches a customer.

The problem

The care team is three people. A careful, personal reply takes about ten minutes, so the 46 waiting complaints were 460 minutes of writing, most of a working day for the whole team, with more arriving every day. To keep up, the team fell back on one template that fits every complaint and answers none of them. A customer whose sofa arrived with a split frame got the same words as one whose wardrobe door hangs crooked.

Writing faster with AI is only half the answer. A template is vague, but it is safe: it never names the wrong product or promises a refund nobody approved. The moment a reply becomes personal, it can be personally wrong, and a wrong personal reply does more damage than the template. So the brief had two halves: make the replies personal, and keep them correct.

The house voice was only ever a few words long: warm, short, sorry once, and only what the customer wrote. For the build, that became six rules every draft had to follow:

Use the customer’s first name.
Mention the product they bought.
Apologise once.
Stay under 80 words.
Never promise a refund or a date.
Say only what the customer wrote.

The approach

Solution architecture in six numbered stages. 01 Ingest: complaints.csv from the care inbox is uploaded to a managed table. 02 Govern: the complaints Delta table in Unity Catalog, alongside a google_ai secret scope holding model credentials. 03 Generate: a notebook that assembles a prompt from the house rules and each row, calls Gemini gemini-3.8-flash, collects the reply and word count, and pauses between calls. 04 Persist: a Delta table with one row per complaint. 05 Review: a checkpoint where the care team reads and approves. 06 Extend: rule checks, routing and priority, with refund requests going to the care lead. — The whole build: the complaint file goes in at the top, and nothing comes out the bottom without a person reading it.

Load and govern the complaints

The care inbox export, complaints.csv, was uploaded through the catalog’s file upload into a managed Delta table under Unity Catalog. Each row holds the customer’s name, the product, what they wrote, and how many days they have waited.

Databricks Catalog Explorer showing the complaints table in the databricksgenaibootcamp catalog, hearth_oak schema, with four columns: customer_name, product and feedback_text as strings, and days_waiting as bigint. — Four columns are all the model needs: who, what, what they said, and how long they have waited.

Keep the API key out of the notebook

Challenge 01

Call an outside model without putting its key in the code

Gemini sits outside Databricks and needs an API key. Pasting the key into a notebook means anyone who can open the notebook, or any copy of it, can use the key.

I stored the key once in a Databricks secret scope called google_ai, then set up a serving endpoint, gemini_free, that points at Google’s OpenAI-compatible address and reads its bearer token from {{secrets/google_ai/api_key}}. The notebook only ever names the endpoint. It never sees the key, so the code can be shared and the key cannot leak with it.

Python cell using the Databricks SDK WorkspaceClient to create a secret scope named google_ai and store a secret under the key api_key, with the key value hidden, then printing a confirmation message. — The key is written into the secret scope once. The value is hidden here, as it should be everywhere.

Databricks Edit serving endpoint page for gemini_free, using a Custom Provider at 100% traffic, with the model URL set to Google's generativelanguage OpenAI-compatible chat completions address and Bearer Token Authentication. — A serving endpoint wraps Gemini so the notebook talks to Databricks, not to Google directly.

Endpoint settings continued: the bearer token is a Secret Reference to secrets/google_ai/api_key, the model name is gemini-3.8-flash, and the task is Chat. — The token field holds a reference to the secret, not the secret itself.

Draft a reply for every complaint

The house rules went into the system prompt, so every call carries the same instructions. Each complaint goes in as the user message, built from that row’s name, product, waiting time and words.

Python cell importing get_deploy_client from mlflow.deployments and sleep from time, creating a Databricks deploy client, and defining a system prompt that tells the model to reply as a Hearth and Oak support representative in a professional, empathetic and natural tone, using the customer's name and product, exactly one apology, only what the customer wrote, within 80 words, and with no refunds, dates or assumptions. — The house voice written down for the first time, in a form the model reads before every reply.

Python loop over each complaint: builds a prompt with the customer name, product, days waiting and feedback text, sends system and user messages to the gemini_free endpoint with client.predict, extracts the reply text, appends the original details with the reply and its word count to a results list, then sleeps for 65 seconds. — One pass over every row. The word count is recorded with each reply so the length rule can be checked later.

Challenge 02

Stay inside the free tier's request limit

The endpoint runs on Gemini's free tier, which caps how many requests it accepts per minute. Sending 46 requests as fast as the loop can run would start failing part way through.

I paused 65 seconds between calls. That makes a full run of 46 complaints take about 50 minutes, which is slow but never fails, and it is still far quicker than the 460 minutes of writing it replaces. With a paid quota the pause could be much shorter, but for one backlog a slow run that finishes beat a fast one that drops replies.

The replies, with each complaint beside them, were saved to a Delta table so they outlive the notebook session and the care team can read them side by side.

Python cell converting the results list into a Spark DataFrame and writing it in overwrite mode to databricksgenaibootcamp.hearth_oak.complaints_response. — Saved to Unity Catalog next to the complaints it answers.

The response table showing two rows. Priya Sharma, sofa, 9 days waiting, 63 words: a reply opening Hello Priya Sharma, saying sorry that her sofa frame is split down the middle right out of the box. Tom Reid, wardrobe, 7 days waiting, 66 words: a reply opening Hello Tom, saying sorry that the door on his wardrobe hangs crooked. — Compare these with 'Dear valued customer': each one names the person, the product and what actually went wrong.

Check every draft against the rules

A person still reads every reply, but if reading takes as long as writing, nothing has been gained. So the notebook checks the rules a computer can check reliably, and tells the reviewer exactly which one a draft broke.

Challenge 03

Make human review fast instead of skipping it

Sending drafts straight to customers would be fastest, and it would remove the one safeguard against a fluent reply that is wrong. Some rule breaks are also easy to miss by eye: a second apology tucked into the last line, or a reply that runs past 80 words but still looks short on screen.

I wrote four automatic checks: the reply uses the customer’s first name, stays under 80 words, mentions no refund, payback, compensation or credit, and apologises exactly once. A draft that passes all four is marked DRAFT READY. One that fails is marked NEEDS FIX, with the failed rules listed beside it, so the reviewer knows where to look. The judgement calls, like whether a reply says only what the customer wrote, stay with the person reading it.

Python cell converting the response table to pandas, lowercasing the reply and the customer's first name, then adding four true or false columns: uses_name, under_80, no_refund, which looks for refund, payback, money-back, compensation, credit or reimbursement, and sorry_once, which counts sorry, apologize, forgive, mistake and pardon and expects exactly one. — Four rules, each a single line, each producing a yes or no per reply.

Python cell setting rule_status to DRAFT READY when every rule passes and NEEDS FIX otherwise, building a to_fix column listing the failed rules, displaying the full queue and the replies that need fixing, and printing how many drafts pass all rules. — Every draft gets a status, and a failed one says which rule it broke.

Notebook output: 45 of 46 drafts pass all rules. — 45 of 46 drafts met every automatic rule on the first run.

Table of replies needing a fix with one row: Ravi Patel, a reply beginning Hello Ravi Patel, I am so sorry for the frustration caused, with under_80 in the to_fix column. — The one failure: Ravi Patel's reply ran over 80 words, and the table says so.

Route each reply to the right person

The brief listed routing and priority as optional extensions. I built both, because a queue of 46 drafts is only useful if the reviewer knows which ones need someone more senior and which ones to read first.

Challenge 04

Give every reply exactly one place to go

Some customers ask for their money back, and only the care lead can approve a refund. Others have already chased once and been ignored. A reply can also fail a rule and come from one of those customers, so the order of the checks decides where it lands.

The routing looks at what the customer wrote, not at the reply. If they mention a refund, or say they have contacted the shop before or had no reply, the draft goes to the care lead. The checks run in a fixed order and the first match wins: a draft that breaks a rule goes back for a rewrite first, because there is no point sending a broken reply to anyone, and everything else goes to normal review as DRAFT READY.

Python cell flagging complaints that ask for a refund or mention contacting the shop before, then a route function returning NEEDS FIX if any rule failed, CARE LEAD if the customer asked for a refund or had contacted the shop before, and DRAFT READY otherwise, applied to every row and counted. — One status per reply, decided in order: fix it, escalate it, or review it.

Status counts: DRAFT READY 42, CARE LEAD 3, NEEDS FIX 1. — 42 drafts ready for normal review, 3 for the care lead, 1 to rewrite.

Finally, I grouped the queue by how long each customer had waited, so the reviewer starts with the people who have waited longest.

Python helper function grouping days waiting into 0-1 day, 2-4 days and 5+ days, then a group by on the waiting group counting customers and listing their names, displayed as a summary table. — A simple banding, so the oldest complaints are impossible to miss.

Summary table: 0-1 day, 25 customers; 2-4 days, 18 customers; 5+ days, 3 customers: Priya Sharma, Tom Reid and Aisha Khan. — Three customers had waited five days or more. Their drafts are the first ones the team should read.

Limits and trade-offs

The keyword checks are simple. They catch a missing name or a second “sorry”, but they cannot tell whether a reply invented a detail. That is why a person still reads every draft.
Some rules stay with the reviewer. Mentioning the right product and saying only what the customer wrote need judgement, so they are not scored automatically.
Speed against oversight. Sending every draft automatically would be faster, and it would remove the checkpoint the whole design depends on. I kept the checkpoint.

What this shows

The model was the easy part. What made the replies usable was everything around it: the complaints in a governed table, the key kept in a secret scope, the house rules in the prompt, every draft checked and routed, and a person reading each one before it is sent. The team goes from writing 46 replies to reviewing 46 drafts, with the three that need the care lead and the one that needs a rewrite already marked.