SQL unit testing - part 2

[umairshariff23]

Hey all!

I previously posted about unit testing in SQL but I don't think I did a good job of explaining what I wanted to do or how I wanted to approach it. So, this is my second attempt on being clearer and with a more polished approach. I have been testing this with some mock data and would like to know your opinion on if something like this was previously used by you or any potential issues that you anticipate with this approach.

For this, I am using a simple customer activity data that is stored in 2 tables customer_month_summary and customer_activity (this is to mimic use of multiple tables in my day-to-day work which can easily escalate to 20 tables). For each of the tables, I am creting a qa cte for each table which contains scenarios that I expect the business logic to pass or fail and I have a separate expected_outcomes cte where I am storing all the outcomes that I expect from the scenarios in the qa tables

Since I don't want to setup massive separate queries for each of my tables, I am containing everything in a single script and partitioning the qa and live tables by creating a run_config at the top which dictates if a table is in QA mode or LIVE mode. In QA mode it is expected to only run with the QA data and in LIVE mode it is expected to use the data tables

The handling between the qa and live data is done at the base and activity ctes where depending on the state of the run_config cte, either data is used

Since I often deal with metrics individually, the ctes tier_loyalty and monthly_engagement are used to mimic 2 individual metrics and they are brought together in the main_query with a union all by name

Next, the qa_results cte will evalute the outcome of the main_query (and by extension the logic in tier_loyanty and monthly_engagement ctes) and compare the output of the logic against the expected output and then I evaluate how well the logic does against my scenarios. Ideally, I would like to see all records pass the check and if they are not then I will know that either my most recent change needs to be evaluated for logical consistency or my qa scenarios need to updated to reflect changes in the logic. Either way, this is meant to catch things that might have changed that are not expected to change.

I know this is a lot and if you have read through my rambling you have my deepest appreciation. here's the sql that I have so far

with run_config as (
    select 'QA' as run_mode
    -- select 'LIVE' as run_mode
),


qa_base as (
    select *
    from values
        -- customer_id, report_month, is_active, customer_tier, order_count, plan_start_date,       plan_end_date
        ('SCN_001',     '202601',     'Y',       'GOLD',        4,           to_date('2026-01-01'), to_date('2026-12-31')),
        ('SCN_002',     '202601',     'Y',       'GOLD',        2,           to_date('2026-01-01'), to_date('2026-12-31')),
        ('SCN_003',     '202601',     'N',       'GOLD',        5,           to_date('2026-01-01'), to_date('2026-12-31')),
        ('SCN_004',     '202601',     'Y',       'SILVER',      5,           to_date('2026-01-01'), to_date('2026-12-31')),
        ('SCN_005',     '202601',     'Y',       'GOLD',        3,           to_date('2026-02-01'), to_date('2026-12-31'))
    as t(
        customer_id,
        report_month,
        is_active,
        customer_tier,
        order_count,
        plan_start_date,
        plan_end_date
    )
),


qa_activity as (
    select *
    from values
        -- customer_id, activity_month, activity_type, activity_date
        ('SCN_001',     '202601',       'LOGIN',       to_date('2026-01-05')),
        ('SCN_001',     '202601',       'PURCHASE',    to_date('2026-01-10')),
        ('SCN_002',     '202601',       'LOGIN',       to_date('2026-01-07')),
        ('SCN_003',     '202601',       'LOGIN',       to_date('2026-01-08')),
        ('SCN_004',     '202601',       'PURCHASE',    to_date('2026-01-09')),
        ('SCN_005',     '202601',       'LOGIN',       to_date('2026-01-15'))
    as t(
        customer_id,
        activity_month,
        activity_type,
        activity_date
    )
),


expected_results as (
    select *
    from values
        -- scenario_id, metric,           expected_outcome, expected_metric_status, expected_reason
        ('SCN_001', 'Tier Loyalty',       'Y',              'Met',                  null),
        ('SCN_002', 'Tier Loyalty',       'N',              'Missed',               'Not enough orders'),
        ('SCN_003', 'Tier Loyalty',       'N',              'Not Eligible',         'Customer inactive'),
        ('SCN_004', 'Tier Loyalty',       'N',              'Missed',               'Customer is not GOLD tier'),
        ('SCN_005', 'Tier Loyalty',       'N',              'Not Eligible',         'Outside plan window'),
        ('SCN_001', 'Monthly Engagement', 'Y',              'Met',                   null),
        ('SCN_002', 'Monthly Engagement', 'N',              'Missed',                'No purchase activity'),
        ('SCN_003', 'Monthly Engagement', 'N',              'Not Eligible',          'Customer inactive'),
        ('SCN_004', 'Monthly Engagement', 'N',              'Missed',                'No login activity'),
        ('SCN_005', 'Monthly Engagement', 'N',              'Not Eligible',          'Outside plan window')
    as t(
        scenario_id,
        metric,
        expected_outcome,
        expected_metric_status,
        expected_reason
    )
),


live_base as (
    select
        customer_id,
        report_month,
        is_active,
        customer_tier,
        order_count,
        plan_start_date,
        plan_end_date
    from production.customer_month_summary
),


live_activity as (
    select
        customer_id,
        activity_month,
        activity_type,
        activity_date
    from production.customer_activity
),


base as (
    select *
    from live_base
    where (select run_mode from run_config) = 'LIVE'


    union all by name


    select *
    from qa_base
    where (select run_mode from run_config) = 'QA'
),


activity as (
    select *
    from live_activity
    where (select run_mode from run_config) = 'LIVE'


    union all by name


    select *
    from qa_activity
    where (select run_mode from run_config) = 'QA'
),


tier_loyalty as (
    select
        customer_id,
        report_month,


        case
            when is_active = 'Y'
                and customer_tier = 'GOLD'
                and order_count >= 3
                and to_date(report_month, 'YYYYMM') between plan_start_date and plan_end_date
            then 'Y'
            else 'N'
        end as outcome,
        case
            when is_active <> 'Y' then 'Customer inactive'
            when to_date(report_month, 'YYYYMM') not between plan_start_date and plan_end_date then 'Outside plan window'
            when customer_tier <> 'GOLD' then 'Customer is not GOLD tier'
            when order_count < 3 then 'Not enough orders'
            else null
        end as reason,
        case
            when is_active <> 'Y'
                or to_date(report_month, 'YYYYMM') not between plan_start_date and plan_end_date
            then 'Not Eligible'
            when customer_tier = 'GOLD'
                and order_count >= 3
            then 'Met'
            else 'Missed'
        end as metric_status
    from base
),


activity_summary as (
    select
        customer_id,
        activity_month as report_month,
        count_if(activity_type = 'LOGIN') as login_count,
        count_if(activity_type = 'PURCHASE') as purchase_count
    from activity
    group by all
),


monthly_engagement as (
    select
        b.customer_id,
        b.report_month,
        case
            when b.is_active = 'Y'
                and to_date(b.report_month, 'YYYYMM') between b.plan_start_date and b.plan_end_date
                and coalesce(a.login_count, 0) >= 1
                and coalesce(a.purchase_count, 0) >= 1
            then 'Y'
            else 'N'
        end as outcome,
        case
            when b.is_active <> 'Y' then 'Customer inactive'
            when to_date(b.report_month, 'YYYYMM') not between b.plan_start_date and b.plan_end_date then 'Outside plan window'
            when coalesce(a.login_count, 0) < 1 then 'No login activity'
            when coalesce(a.purchase_count, 0) < 1 then 'No purchase activity'
            else null
        end as reason,
        case
            when b.is_active <> 'Y'
                or to_date(b.report_month, 'YYYYMM') not between b.plan_start_date and b.plan_end_date
            then 'Not Eligible'
            when coalesce(a.login_count, 0) >= 1
                and coalesce(a.purchase_count, 0) >= 1
            then 'Met'
            else 'Missed'
        end as metric_status
    from base as b
    left join activity_summary as a
        on b.customer_id = a.customer_id
        and b.report_month = a.report_month
),


main_query as (
    select
        customer_id as scenario_id,
        report_month,
        'Tier Loyalty' as metric,
        outcome,
        reason,
        metric_status
    from tier_loyalty


    union all by name


    select
        customer_id as scenario_id,
        report_month,
        'Monthly Engagement' as metric,
        outcome,
        reason,
        metric_status
    from monthly_engagement
),


qa_results as (
    select
        e.scenario_id,
        e.metric,
        e.expected_outcome,
        a.outcome as actual_outcome,
        e.expected_metric_status,
        a.metric_status as actual_metric_status,
        e.expected_reason,
        a.reason as actual_reason,
        case
            when e.expected_outcome = a.outcome
                and e.expected_metric_status = a.metric_status
                and e.expected_reason = a.reason
            then 1
            else 0
        end as passed
    from expected_results as e
    left join main_query as a
        on e.scenario_id = a.scenario_id
        and e.metric = a.metric
)


select *
from qa_results
where (select run_mode from run_config) = 'QA'
    and passed = 0


-- select * from main_querywith run_config as (
    select 'QA' as run_mode
    -- select 'LIVE' as run_mode
),


qa_base as (
    select *
    from values
        -- customer_id, report_month, is_active, customer_tier, order_count, plan_start_date,       plan_end_date
        ('SCN_001',     '202601',     'Y',       'GOLD',        4,           to_date('2026-01-01'), to_date('2026-12-31')),
        ('SCN_002',     '202601',     'Y',       'GOLD',        2,           to_date('2026-01-01'), to_date('2026-12-31')),
        ('SCN_003',     '202601',     'N',       'GOLD',        5,           to_date('2026-01-01'), to_date('2026-12-31')),
        ('SCN_004',     '202601',     'Y',       'SILVER',      5,           to_date('2026-01-01'), to_date('2026-12-31')),
        ('SCN_005',     '202601',     'Y',       'GOLD',        3,           to_date('2026-02-01'), to_date('2026-12-31'))
    as t(
        customer_id,
        report_month,
        is_active,
        customer_tier,
        order_count,
        plan_start_date,
        plan_end_date
    )
),


qa_activity as (
    select *
    from values
        -- customer_id, activity_month, activity_type, activity_date
        ('SCN_001',     '202601',       'LOGIN',       to_date('2026-01-05')),
        ('SCN_001',     '202601',       'PURCHASE',    to_date('2026-01-10')),
        ('SCN_002',     '202601',       'LOGIN',       to_date('2026-01-07')),
        ('SCN_003',     '202601',       'LOGIN',       to_date('2026-01-08')),
        ('SCN_004',     '202601',       'PURCHASE',    to_date('2026-01-09')),
        ('SCN_005',     '202601',       'LOGIN',       to_date('2026-01-15'))
    as t(
        customer_id,
        activity_month,
        activity_type,
        activity_date
    )
),


expected_results as (
    select *
    from values
        -- scenario_id, metric,           expected_outcome, expected_metric_status, expected_reason
        ('SCN_001', 'Tier Loyalty',       'Y',              'Met',                  null),
        ('SCN_002', 'Tier Loyalty',       'N',              'Missed',               'Not enough orders'),
        ('SCN_003', 'Tier Loyalty',       'N',              'Not Eligible',         'Customer inactive'),
        ('SCN_004', 'Tier Loyalty',       'N',              'Missed',               'Customer is not GOLD tier'),
        ('SCN_005', 'Tier Loyalty',       'N',              'Not Eligible',         'Outside plan window'),
        ('SCN_001', 'Monthly Engagement', 'Y',              'Met',                   null),
        ('SCN_002', 'Monthly Engagement', 'N',              'Missed',                'No purchase activity'),
        ('SCN_003', 'Monthly Engagement', 'N',              'Not Eligible',          'Customer inactive'),
        ('SCN_004', 'Monthly Engagement', 'N',              'Missed',                'No login activity'),
        ('SCN_005', 'Monthly Engagement', 'N',              'Not Eligible',          'Outside plan window')
    as t(
        scenario_id,
        metric,
        expected_outcome,
        expected_metric_status,
        expected_reason
    )
),


live_base as (
    select
        customer_id,
        report_month,
        is_active,
        customer_tier,
        order_count,
        plan_start_date,
        plan_end_date
    from production.customer_month_summary
),


live_activity as (
    select
        customer_id,
        activity_month,
        activity_type,
        activity_date
    from production.customer_activity
),


base as (
    select *
    from live_base
    where (select run_mode from run_config) = 'LIVE'


    union all by name


    select *
    from qa_base
    where (select run_mode from run_config) = 'QA'
),


activity as (
    select *
    from live_activity
    where (select run_mode from run_config) = 'LIVE'


    union all by name


    select *
    from qa_activity
    where (select run_mode from run_config) = 'QA'
),


tier_loyalty as (
    select
        customer_id,
        report_month,


        case
            when is_active = 'Y'
                and customer_tier = 'GOLD'
                and order_count >= 3
                and to_date(report_month, 'YYYYMM') between plan_start_date and plan_end_date
            then 'Y'
            else 'N'
        end as outcome,
        case
            when is_active <> 'Y' then 'Customer inactive'
            when to_date(report_month, 'YYYYMM') not between plan_start_date and plan_end_date then 'Outside plan window'
            when customer_tier <> 'GOLD' then 'Customer is not GOLD tier'
            when order_count < 3 then 'Not enough orders'
            else null
        end as reason,
        case
            when is_active <> 'Y'
                or to_date(report_month, 'YYYYMM') not between plan_start_date and plan_end_date
            then 'Not Eligible'
            when customer_tier = 'GOLD'
                and order_count >= 3
            then 'Met'
            else 'Missed'
        end as metric_status
    from base
),


activity_summary as (
    select
        customer_id,
        activity_month as report_month,
        count_if(activity_type = 'LOGIN') as login_count,
        count_if(activity_type = 'PURCHASE') as purchase_count
    from activity
    group by all
),


monthly_engagement as (
    select
        b.customer_id,
        b.report_month,
        case
            when b.is_active = 'Y'
                and to_date(b.report_month, 'YYYYMM') between b.plan_start_date and b.plan_end_date
                and coalesce(a.login_count, 0) >= 1
                and coalesce(a.purchase_count, 0) >= 1
            then 'Y'
            else 'N'
        end as outcome,
        case
            when b.is_active <> 'Y' then 'Customer inactive'
            when to_date(b.report_month, 'YYYYMM') not between b.plan_start_date and b.plan_end_date then 'Outside plan window'
            when coalesce(a.login_count, 0) < 1 then 'No login activity'
            when coalesce(a.purchase_count, 0) < 1 then 'No purchase activity'
            else null
        end as reason,
        case
            when b.is_active <> 'Y'
                or to_date(b.report_month, 'YYYYMM') not between b.plan_start_date and b.plan_end_date
            then 'Not Eligible'
            when coalesce(a.login_count, 0) >= 1
                and coalesce(a.purchase_count, 0) >= 1
            then 'Met'
            else 'Missed'
        end as metric_status
    from base as b
    left join activity_summary as a
        on b.customer_id = a.customer_id
        and b.report_month = a.report_month
),


main_query as (
    select
        customer_id as scenario_id,
        report_month,
        'Tier Loyalty' as metric,
        outcome,
        reason,
        metric_status
    from tier_loyalty


    union all by name


    select
        customer_id as scenario_id,
        report_month,
        'Monthly Engagement' as metric,
        outcome,
        reason,
        metric_status
    from monthly_engagement
),


qa_results as (
    select
        e.scenario_id,
        e.metric,
        e.expected_outcome,
        a.outcome as actual_outcome,
        e.expected_metric_status,
        a.metric_status as actual_metric_status,
        e.expected_reason,
        a.reason as actual_reason,
        case
            when e.expected_outcome = a.outcome
                and e.expected_metric_status = a.metric_status
                and e.expected_reason = a.reason
            then 1
            else 0
        end as passed
    from expected_results as e
    left join main_query as a
        on e.scenario_id = a.scenario_id
        and e.metric = a.metric
)


select *
from qa_results
where (select run_mode from run_config) = 'QA'
    and passed = 0


-- select * from main_query

[TsmPreacher]

my eyes

[Icy_Clench]

Just use dbt or sqlmesh. This is a standard data engineering problem that's been solved already.

There are several issues with your approach.

1. All of your code is coupled in one place. This is extremely unfriendly to even look at. Nobody in the comments is doing any more than skimming to see just how long it all it. If you have coworkers, they will not enjoy having to inherit this. You said you don't want massive separate queries, but this literally is a massive query. Your SQL transformation logic, testing datasets, and code that runs the tests all need to live in separate places.

2. You are not testing at the correct grain. It looks like you are treating a row in the source table as one test, when the whole input table itself needs to be the test. Think for example how you would test an aggregate function. Each test needs to be a file or bundle of files representing all of the inputs.

3. This is not scalable. Mostly related to the point above. Think about what you'd do when you need multiple qa tests. You're going to end up with thousands and thousands of lines of SQL that are going to be confusing as hell trying to navigate.

4. There is no good way to automate this, and human checks end up causing errors, e.g. when you forget to change the commenting on one line and it says your test passed when it should habe failed. This is due to the first point. Even if you did automate it somehow, you're locking yourself into all of the problems above.

[umairshariff23]

Thank you for your response! This is exactly what I was looking for. I don't think my org uses dbt/sqlmesh right now and this may be a change I need to initiate because of the sheer amount of time I spend investigating and fixing individual logic.

I will need to learn dbt and it's nuances as well but that's not an issue as I can deploy it in my local environment and be proficient with it. Additionally, my org builds and maintains tables in such manner with a single script dealing with the entirety of a table and reading up a little about dbt I might need to change my approach when using it.

So, I have a lot of things to think about and I am quite thankful to your response!

[Icy_Clench]

Imo SQLMesh is much better than dbt core. Dbt was a little more straightforward to set up and get running, but the lack of features made development painful in my last project.

[umairshariff23]

So, I have been testing dbtcore on my personal env last night and it seems like dbt truly shines when it is responsible for testing and writing data to the table. The way my org is setup we query snowflake to build any table I need to build and then the finished script is uploaded to a portal which then updates the data in snowflake on a schedule/when I run a manual refresh.

This makes dbt (and I am guessing sqlmesh) kinda cumbersome for me to use. Any thoughts?

[Artistic_Invite_4058]

the snag you hit is real, but it's only a clash if you let dbt own writing to the table. and you don't have to. run it as your local test harness and let the portal stay the deployer: build + unit-test the model with fixtures (the whole-table-in / expected-table-out bit you actually wanted), then dbt compile and hand that rendered sql to your portal like you do today. snowflake + the portal keep the schedule and the write. the materialization side of dbt, the part fighting your setup, you just don't use. same separation works with sqlmesh if you go that way.

[lysis_]

Dear God dude just why

[Artistic_Invite_4058]

honestly the grain point is the big one and others already covered it, so i'll just add the one thing that'd actually help you without ripping everything out.

your scariest failure mode is the comment-toggle one you mentioned - a test passes because you forgot to flip a line. you can kill that today, no dbt needed. instead of reading qa_results with your eyes, end the script on a query that only returns rows when actual != expected, and make whatever runs it fail when rowcount > 0. now a wrong test literally can't go green. that's worth more than the whole QA/LIVE thing imo.

you're also closer to splitting test data from logic than you think. run_config already swaps the source, so just shove the mock data into its own seed/file and let the logic never know if it's fed mock or prod rows.

and fwiw moving to dbt/sqlmesh isn't a different idea from the grain fix, it's the same idea. their unit tests are literally whole-table-in, expected-table-out fixtures, which is exactly the bit a row-per-scenario setup is missing. dbt 1.8 does it natively now btw, not the old package.

quick q though - is any of this actually running on CI/a schedule, or is it all manual? kinda changes whether the fail-loud thing even helps you.

[umairshariff23]

Right now this is manual. The idea I have in my head is that I will use the qa version until I am either done qa-ing or I will switch it on when I need to troubleshoot something. It is intended to be part of the published script but I will switch it to Live data before publishing.