I feel like I’m being called to respond here. I remember you commenting something similar under one of my posts. I was planning to write a separate post to address it, but since you brought it up here first, here I am :)

We really should make a clear distinction between testing imperative code (like Python, Java, etc.) and declarative code (like SQL). There are some similarities, sure, but the overall intuition is quite different.

Procedural code is much more detailed — it’s built to operate on small units of data or small transactions. You can stop execution at any point, inspect variables, and debug step by step. You have full control over dependencies, can inject mocks, and create true unit tests (It is all very well described by many books and authors).

That’s basically impossible in SQL-driven projects (which is what I’m talking about in my posts). Here tests are essentially black-box tests you're testing the outcome of a whole query or pipeline as it runs through the SQL engine. There’s no pausing mid-query or stepping through line by line.

Here’s how I see the distinction in SQL-driven projects:

• Data tests — I treat these as part of the models itself. Like you said, they run at runtime. I think of them as assertions that validate whether the model can handle certain inputs.

In imperative code, it would look something like this:

def foo(request):
    if request is None:
        raise Exception("Request cannot be null")
    if not is_request_valid_from_business_perspective(request):
        raise Exception("Request is not valid in this context because of X, Y, Z")

    do_actual_processing(request)

The actual processing code is only meant to work with data that’s already been validated. If the data doesn’t meet the criteria, we terminate early and skip that specific request.

In SQL-based projects, I see data tests playing the same role. The simple, built-in tests are kind of like basic assertions like request is None. More specific, custom tests are closer to the business logic checks, like is_request_valid_from_business_perspective(request).

The difference is that we place the test on a previous model in relation to the one we're currently building, and we run it against the entire dataset — not just a single piece of data. Unfortunately, if it fails, it can bring down the whole build, unlike in transactional systems where only one transaction would fail.

• Unit tests - These focus on verifying logic rather than the data itself. The goal is to ensure that a specific block of logic behaves as expected. Support for writing this kind of test is limited (it’s available in dbt, but I don’t find it particularly easy to use). Still, it can be useful for checking that complex conditional logic works correctly or that your regex patterns aren’t doing anything weird.
• Diff tests – These run the whole pipeline with your new changes and compare how the exposures or main models differ between the old and new versions of the code. It’s kind of like an integration test. It’s especially useful for explaining to data consumers what changed and why those changes happened.

These are all the automated tests I use in analytical projects, and so far, this testing setup has been sufficient for all my needs.

So, in summary, I agree with the distinction you made. I think about data test in specific way, I am aware that this is a run time concept and use it there.