How can you use prompt engineering to improve data quality checks within a Databricks notebook using LLMs?

Use the language model to write and explain the checks, and to judge free text that rules cannot, but let ordinary SQL rules decide pass or fail for anything a rule can express. On Databricks that means: rules as expectations or constraints, and ai_query or the AI functions, with carefully written prompts, for the fuzzy cases.

1. Generate rules, then review them

Give the model the table's schema, a sample of rows and what the data means, and ask for checks as SQL:

Here is the schema of main.sales.orders and 20 sample rows. Propose data quality rules as SQL boolean expressions, one per line, with a name and a reason: required fields, valid ranges, allowed values, and relationships between columns. Use only these columns.

A good answer gives rules such as order_total >= 0 or ship_date >= order_date. An engineer reads each one and puts the accepted ones in pipeline expectations or CHECK constraints, so they run every time, deterministically and for free.

2. Judge what rules cannot

Some checks need reading: is a product description in English, does a free-text address look complete, does a support note contain personal data? ai_query runs a prompt over each row:

SELECT order_id,
       ai_query(
         'my-chat-endpoint',
         CONCAT('Is this a complete postal address with street, city and postcode? ',
                'Answer only with JSON. Address: ', shipping_address),
         responseFormat => 'STRUCT<complete:BOOLEAN, missing:STRING>',
         failOnError => false
       ) AS check
FROM main.sales.orders
WHERE order_date = current_date();

Prompt engineering matters here:

• Constrain the output with responseFormat, so the answer is a typed field you can filter on, not prose.
• Give clear criteria and, where useful, a few labelled examples of good and bad rows.
• Use failOnError => false so one failed call returns an error message instead of failing the whole query.

Task-specific functions cover common cases without a custom prompt: ai_classify for categories, ai_mask to hide personal data, ai_analyze_sentiment, ai_extract for fields.

3. Explain failures to people

Summarising the day's expectation metrics, for example "drop rate of valid_email rose from 0.1% to 4% after 09:00", is a task a model does well, as long as it is fed the actual numbers from the pipeline's event log rather than asked to guess.

Limits to state in an interview

• A model can be wrong and is not deterministic; never let it be the only check on data that must be right.
• Every call costs money and time, so run it on new or changed rows, not the whole table each day.
• Measure it: label a few hundred rows by hand and compare the model's verdicts before relying on them.