Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ask HN: How do you test SQL?

news.ycombinator.com

Post reply on HN
Print view View original

11–20 of 322 posts

Re: Ask HN: How do you test SQL?

#11

Post by samsquire »

From an SQL database implementation perspective, in my toy Python barebones SQL database that barely supports inner joins (https://github.com/samsquire/hash-db) I tested by testing on postgresql and seeing if my query with two joins produces the same results.

I ought to produce unit tests that prove that tuples from each join operation produces the correct dataset. I've only ever tested with 3 join operations in one query.

For a user perspective, I guess you could write some tooling that loads example data into a database and does an incremental join with each part of the join statement added.

Re: Ask HN: How do you test SQL?

#12

Post by rozularen »

jlund-molfese wrote: post #3

In SQL-heavy ETL pipelines, I normally don't test the SQL queries by themselves, but do black box & performance testing to verify that the output of a certain batch job matches what I expect (automated acceptance testing). This is easier if you have the same input every time the tests run, like a frozen database image, because then you can basically have snapshot tests.

we do something similar, we run some validation tests against the output regarding file size and line count but not the actual data

Re: Ask HN: How do you test SQL?

#13

Post by javaunsafe2019 »

I always did that with integration tests. Put some data in the db. Use the repository aka your sql and validate the results.

Most of the times there is a layer around your sql (a repository, a bash script or whatever) that you can use for integration testing.

Re: Ask HN: How do you test SQL?

#14

Post by AnEro »

hampelm wrote: post #9
AnEro wrote: post #5

Gitlabs has their guide up, I love it and use it all the time. I've been doing data engineering in a small team for about 4 years, helping hospitals with data and hopefully making it easier to understand. Something that is overlooked or undervalued in my opinion, have stark distinctions for separating out technical and business logic tests. It makes it easier communicating what's happening in the event something is '…

Could you link to the specific guide you're referring to? I see a couple on quick search -- perhaps this one? https://docs.gitlab.com/ee/development/database_review.html

Enjoy, it's honestly the best resource I've seen on data teams that is open. https://about.gitlab.com/handbook/business-technology/data-t...

Re: Ask HN: How do you test SQL?

#15

Post by kkleindev »

The teams I've been working on have resorted to data tests instead of code tests. That means that the data produced by your code is tested against a certain set of expectations - in stark contrast to code being tested _before_ its execution.

We've written our own tool to compare different data sources against each other. This allows, for example, to test for invariants (or expected variations) between and after a transformation.

The tool is open source: https://github.com/QuantCo/datajudge

We've also written a blog post trying to illustrate a use case: https://tech.quantco.com/2022/06/20/datajudge.html

Re: Ask HN: How do you test SQL?

#16

Post by teej »

edgyquant wrote: post #6
pmarreck wrote: post #2

Excellent question. Not sure why it's getting no traction. For my own use-cases, I usually test this at the application level and not the DB level. This is admittedly not unit-testing my SQL (or stored procs or triggers) but integration-testing it.

With ORMs you can get pretty close to this being unit testing for the DB though.

I haven't seen an ORM that handles analytical queries well. I'd rather write raw SQL than use SQLAlchemy for complex queries with multiple joins, aggregations, and window functions.

Re: Ask HN: How do you test SQL?

#17

Post by epgui »

We use this and take an example-based tests approach for any non-trivial DBT models: https://github.com/EqualExperts/dbt-unit-testing

More trivial example:

    {%
        call dbt_unit_testing.test(
            'REDACTED',
            'Should replace nullish values with NULL'
        )
    %}
        {% call dbt_unit_testing.mock_source('REDACTED', 'REDACTED', opts) %}

            "id" | "industry"
            1    | 'A'
            2    | 'B'
            3    | ''
            4    | 'Other'
            5    | 'C'
            6    | NULL

        {% endcall %}

        {% call dbt_unit_testing.expect(opts) %}

            "history_id" | "REDACTED"
            1            | 'A'
            2            | 'B'
            3            | NULL
            4            | NULL
            5            | 'C'
            6            | NULL

        {% endcall %}
    {% endcall %}

Re: Ask HN: How do you test SQL?

#19

Post by uticus »

My favorite interview question. No, I mean when I'm being interviewed. The sheepish grins let me know I'm not alone.

Best ideas IMO (no particular order):

- make SQL dumber, move logic that needs testing out of SQL

- use an ORM that allows composing, disconnect composition & test (ie EF for .NET groups, test the LINQ for correct filtering etc, instead of testing for expected data from a db) (I see this has already been recommended elsewhere)

* edited formatting

Re: Ask HN: How do you test SQL?

#20

Post by AnEro »

samsquire wrote: post #11

From an SQL database implementation perspective, in my toy Python barebones SQL database that barely supports inner joins ( https://github.com/samsquire/hash-db ) I tested by testing on postgresql and seeing if my query with two joins produces the same results. I ought to produce unit tests that prove that tuples from each join operation produces the correct dataset. I've only ever tested with 3 join operations in on…

Personally I like using dimensions from each set and seeing if the business logic side lines up. So measure sales month over month with 2 different sources and/or have the full join count important fields on matches what makes it in and out of the combination as a score board.
Post reply on HN