The Need: Regression Testing That Works

If you've ever worked with data, you know about:

• A bad join breaks a dashboard.
• Duplicated data appears.
• A client calls and asks, "These numbers can’t be right.”

When you dig in, what do you find? Business rules buried in spreadsheets, requirements out of sync with code, and testing practices reduced to spot-checks. It's not because people don't care—testing SQL logic has always been manual, fragile, and tedious.

The tech world promises that AI will liberate us from drudge work, though no one seems quite sure what we'll do with all that extra time. Last night, I decided to put this promise to the test. Could I create a prompt that turns a few facts into a comprehensive test strategy? The results were promising: a proof-of-concept in 15 minutes, and a working version in 30. Then I wondered (or for those of you who remember, I had a V8 moment)—why not ask AI to design the perfect prompt?

Prompt (from my AI)

You are a senior enterprise IT architect with deep expertise in database development, quality engineering, and automated testing.

Given a database schema and a set of business rules, generate T-SQL queries suitable for inclusion in an automated post-refresh test suite. Each query must follow this principle:

• Pass condition: Returns zero rows when all data conforms to the rules.
• Fail condition: Returns only the rows that violate the business rule, with enough context to support root-cause analysis.

Your response should include:

1. A brief test strategy outlining your approach.
2. A list of test cases, each expressed as a clear and efficient T-SQL query.
3. A completeness review to identify any potential gaps in the provided rules or suggest additional validations based on best practices.

How To Use It

Prepare your inputs. Copy in the relevant schema (table names, fields, relationships) and your business rules—even if they’re incomplete.

Paste the schema and rules into the prompt and run it through your AI.

Review the output. You’ll get a test strategy, one or more T-SQL queries, and often a few suggestions for rules you may have missed.

An Example

My AI generated the example below to validate that the prompt works. What do you think?

📦 SCENARIO: SaaS SUBSCRIPTION BILLING SYSTEM

This system manages customers, plans, subscriptions, and invoices for a SaaS product.

🗃️ TABLES

[Customer]

• [CustomerID] (PK)
• [Email]
• [SignupDate]
• [Status] (Active, Suspended, Cancelled)

[Plan]

• [PlanID] (PK)
• [PlanName]
• [MonthlyRate]

[Subscription]

• [SubscriptionID] (PK)
• [CustomerID] (FK to [Customer])
• [PlanID] (FK to [Plan])
• [StartDate]
• [EndDate] (nullable)
• [Status] (Active, Cancelled, Paused)

[Invoice]

• [InvoiceID] (PK)
• [CustomerID] (FK to [Customer])
• [InvoiceDate]
• [Amount]
• [IsPaid] (bit)

📜 BUSINESS RULES

1. Every active customer must have at least one active subscription.
2. A customer should not have overlapping active subscriptions.
3. Invoice amount must match the monthly rate of the subscribed plan (if one subscription only).
4. Invoices must not exist for customers marked as Cancelled.
5. No subscriptions may exist with a StartDate later than their EndDate.
6. All emails must be unique and valid (contain ‘@’).

✅ TEST STRATEGY

We will write T-SQL tests that check each rule. These are to be run after each refresh or ETL cycle. Each test returns:

• Zero rows if the data passes.
• Rows with violations if issues are found.

These queries are meant to integrate into CI/CD-style data quality pipelines.

🧪 TEST CASES

🧪 1. Active customers must have an active subscription

SELECT c.CustomerID, c.Email
FROM [Customer] c
LEFT JOIN [Subscription] s
    ON c.CustomerID = s.CustomerID
    AND s.Status = 'Active'
WHERE c.Status = 'Active'
  AND s.CustomerID IS NULL;

🧪 2. No overlapping active subscriptions per customer

SELECT s1.CustomerID, s1.SubscriptionID, s2.SubscriptionID, s1.StartDate, s1.EndDate, s2.StartDate, s2.EndDate
FROM [Subscription] s1
JOIN [Subscription] s2
  ON s1.CustomerID = s2.CustomerID
 AND s1.SubscriptionID <> s2.SubscriptionID
 AND s1.Status = 'Active'
 AND s2.Status = 'Active'
 AND (
     s1.StartDate BETWEEN s2.StartDate AND ISNULL(s2.EndDate, '9999-12-31') OR
     s2.StartDate BETWEEN s1.StartDate AND ISNULL(s1.EndDate, '9999-12-31')
 );

🧪 3. Invoice amount matches the plan rate (if one active sub only)

WITH SingleSub AS (
  SELECT s.CustomerID, s.PlanID
  FROM [Subscription] s
  WHERE s.Status = 'Active'
  GROUP BY s.CustomerID, s.PlanID
  HAVING COUNT(*) = 1
)
SELECT i.InvoiceID, i.CustomerID, i.Amount, p.MonthlyRate
FROM [Invoice] i
JOIN SingleSub ss ON i.CustomerID = ss.CustomerID
JOIN [Plan] p ON ss.PlanID = p.PlanID
WHERE i.Amount <> p.MonthlyRate;

🧪 4. No invoices for cancelled customers

SELECT i.InvoiceID, i.CustomerID
FROM [Invoice] i
JOIN [Customer] c ON i.CustomerID = c.CustomerID
WHERE c.Status = 'Cancelled';

🧪 5. Subscriptions with invalid start/end dates

SELECT SubscriptionID, StartDate, EndDate
FROM [Subscription]
WHERE EndDate IS NOT NULL AND StartDate > EndDate;

🧪 6. Email must be unique and valid

-- Invalid email format
SELECT CustomerID, Email
FROM [Customer]
WHERE Email NOT LIKE '%@%';

-- Non-unique emails
SELECT Email, COUNT(*) AS Count
FROM [Customer]
GROUP BY Email
HAVING COUNT(*) > 1;

🔍 COMPLETENESS REVIEW

🧠 OPTIONAL FUTURE TESTS

• Ensure Paused or Cancelled subscriptions are not billed.
• Test for gaps in coverage when StartDate and EndDate are consecutive.
• Billing proration logic (if implemented).
• Subscription changes (plan upgrades/downgrades) timeline integrity.

Let me know if you’d like a script pack version for CI pipelines or if you want to add churn analysis, MRR validation, or other SaaS metrics to the test suite.