[HN Gopher] Ask HN: How do you test SQL?
___________________________________________________________________
Ask HN: How do you test SQL?
I've been looking for resources for our data team to apply best
practices for testing SQL pipelines (we use DBT) but have not found
anything. How do you test SQL pipelines? What patterns, tools and
best practices would you recommend? Any good reference material you
know of?
Author : pcarolan
Score : 88 points
Date : 2023-01-31 21:19 UTC (1 hours ago)
| pmarreck wrote:
| 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.
| edgyquant wrote:
| With ORMs you can get pretty close to this being unit testing
| for the DB though.
| AnEro wrote:
| With not complex data for sure, I think SQL testing is so
| much easier once you start having hierarchies and many to
| many relationships start piling up.
| teej wrote:
| 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.
| btilly wrote:
| I agree about the limitations of ORMs.
|
| However I have had great luck with using an ORM to load up
| the database and data, and then having a unit test that
| calls the function which does raw SQL in the middle. And
| now the raw database tests are integrated with the unit
| tests for the rest of the environment in a way that keeps
| them synchronized with the application code that also
| interacts with the same database.
|
| And, of course, the limits of ORMs do not change the fact
| that they have use cases.
| DaiPlusPlus wrote:
| There's ORMs and there's ORMs - at one end you have the
| (reprehensible) Active Record anti-pattern, at the other
| end you have EF Core extended with one's own build-time
| type generation - they're both "ORMs" to everyone
| involved, but they're totally incomparable.
|
| ...not to say they that EF Core doesn't have flaws (it
| does, and they're legion) but the ORMs of today are
| nothing like the ORMs of the 1990s... or even like 2010's
| NHibernate.
| DaiPlusPlus wrote:
| The problem with writing raw SQL (which I do personally
| prefer myself, too) is now you need to generate types
| and/or mappings for each distinct query's resultset schema
| - doing that by-hand is tedious and error-prone (or use
| untyped dict objects for every row, ew) - so what you
| really need is a project build-step that finds every query
| in your project and runs it against a prototype database
| instance in order to get schema result typing info, then
| generates the strong-types/mappings code for you before
| everything else gets compiled...
|
| ...and it works - but now you have possibly thousands of
| classes/structs that are all-so-similar but also subtly
| different - namely disjoint members (so they can't exist in
| an inheritance hierarchy, e.g. NewUser won't have a UserId
| value, result-types would be immutable, unless they need to
| be mutable, etc...). It's all such a huge pain. In a C#
| project of mine that does something like this, it means
| that every business-entity typically has at least ~5 actual
| class/struct/interface types associated with it: e.g.
| NewUser, IReadOnlyUser, IWritableUserValues, struct
| UserKey, MutableUser, UpdateUserValues, etc.
|
| ...surely there's a better way?
| jlund-molfese wrote:
| 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.
| rozularen wrote:
| we do something similar, we run some validation tests against
| the output regarding file size and line count but not the
| actual data
| gorgoiler wrote:
| Realistically, most of my tests are integration / end to end
| tests. They typically get written only when it comes to patch
| time, where you first want proof that the old system works before
| you tear it apart and rebuild it. I think that's probably the
| only SQL testing I've ever done and honestly, if they are fast
| enough, that kind of integration testing is all you will need
| too.
|
| As the meme say: _App worked before. App work afterwards. Can't
| explain that._
| drx wrote:
| If you're using dbt, dbt tests are a good start:
| https://docs.getdbt.com/docs/build/tests
|
| You can hook up dbt tests to your CI and Git(hub|lab) for data
| PRs.
|
| Depending on your needs, you can also look into data
| observability tools such as Datafold (paid) or re_data (free)
| geocrasher wrote:
| SQL pipelines can be tested pretty easily. If they look
| congested, use a snake (Python, if you like) to try to knock the
| CRUD out of them.
| sakopov wrote:
| .NET Shop using SQL Server here, but I think something similar to
| what we do can apply to any stack. We use TestContainers [1] to
| spin up a container with SQL Server engine running on it. Then
| use FluentMigrator [2] to provision tables and test data to run
| XUnit integration tests against. This has worked remarkably well.
|
| [1] https://dotnet.testcontainers.org/
|
| [2] https://fluentmigrator.github.io/
| gsvclass wrote:
| In GraphJin an automatic GraphQL to SQL compiler we use the
| gnomock library it startups a database instance (docker) then
| create the schema and tests data and finally our code connects to
| it and runs a series of tests. We run these across Mysql,
| Postgres and a few other DB's. Gnomock supports a wide range of
| them. Right now we don't take down the db for every test only
| between test runs but its fast enough that we could. This whole
| thing runs of a simple `go test -v .` command and we run it on
| every commit using a githook.
| https://github.com/dosco/graphjin/blob/master/tests/dbint_te...
| AnEro wrote:
| 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 'wrong' vs wrong, and it's
| easier to bring a non-technically inclined team member up to
| speed. Also, I think it's good to learn from the SaaS side of
| things and not bloat up or overengineer with infrastructure as
| data engineering is the latest development flavour. Keep it
| simple. Great expectations is a great tool however I think small
| teams should take really hard looks at their needs and see if a
| simple orchestration engine and SQL testing is enough. A
| centralized source for testing is great, however infrastructure
| isn't free even when it is you are paying for it with you and
| your teams time.
| hampelm wrote:
| 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
| AnEro wrote:
| 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...
| gxt wrote:
| Abstraction layer between the query you write and the one that
| gets executed. This way you can mock the schema, run the query on
| the mock to assert some condition x.
|
| A ref() concept like dbt's is sufficient. When testing, have ref
| output a different (test-x) name for all your references.
| purerandomness wrote:
| There's pgTAP for Postgres [1], the same approach probably is
| valid for other databases.
|
| Here's [2] a slide deck by David Wheeler giving an introduction
| into how it works.
|
| [1] https://pgtap.org/
|
| [2] https://www.slideshare.net/justatheory/unit-test-your-
| databa...
| dwohnitmok wrote:
| Given that you're using dbt, it comes with a testing framework
| out of the box: https://docs.getdbt.com/docs/build/tests
| samsquire wrote:
| 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.
| AnEro wrote:
| 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.
| javaunsafe2019 wrote:
| 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.
| kkleindev wrote:
| 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
| uticus wrote:
| Related - how is _any_ declarative language tested?
|
| Quick web search confirms suspicions, it is not easy
|
| https://www.metalevel.at/prolog/testing
| spamtarget wrote:
| SQL is not a declarative language. It is a functional language,
| and structured language on the top as extensions. HTML is a
| declarative language.
| winrid wrote:
| I try to do some kind of compile-time query checking. I really
| like sqlx with Rust, and other languages have some kind of
| equivalent (although maybe not as nice) like JOOQ. If you can
| store the queries in some kind of configuration, like SQL files,
| then this is easy no matter the language.
| epgui wrote:
| 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 %}
| ramenmeal wrote:
| We spin up a docker container running the DB technology we use,
| run our DB migration scripts on it, and then run integration
| tests against it. You get coverage of your migration scripts this
| way too.
| MBCook wrote:
| This is what we did at my last job. You can catch DB specific
| issues that a false implementation wouldn't show and make sure
| all your code paths work as expected.
|
| Every time new issues cropped up we would put new data in the
| test data designed to reproduce it. Every edge case we would
| run into.
|
| It provided so much confidence because it would catch and
| trigger so many edge cases that testing with mocks or by hand
| would miss.
| allanbreyes wrote:
| This is a great way to test for backwards-incompatible changes
| if your fleet is running canaries or instances on different
| versions backed by a singleton database. You apply the
| migrations, checkout the app from the old version, and then re-
| run your test suite. Any failures are a reasonably high signal
| that some backwards-incompatible migration was introduced.
| inetknght wrote:
| bonus points if you add test data for integration tests with
| sad paths too
| csteubs wrote:
| Absolutely this. I stood up a negative test suite for
| continuous DB queries late in 2020 and it's caught many
| potential show stopper integration issues since; about 45%
| more YoY than pre-suite.
|
| Took about a week of duplicating happy path tests,
| investigating gaps, and switching inputs/assertion types to
| get everything passing, but less than a week later we had our
| first verifiable test failure.
| uticus wrote:
| 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
| javaunsafe2019 wrote:
| You mean using interfaces and integration tests?
| uticus wrote:
| > using interfaces
|
| Kinda, but personally I describe as using LINQ queries. The
| dbcontext just isn't hooked up. It's a method that takes in
| an IQueryable<T> (there's the interface I suppose) and
| outputs a filtered IQueryable<T>. The unit test (see my next
| response) provides a test collection and expects a certain
| result.
|
| > and integration tests
|
| No, unit tests
___________________________________________________________________
(page generated 2023-01-31 23:00 UTC)