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

261–270 of 322 posts

Re: Ask HN: How do you test SQL?

#261

Post by crabbone »

ramenmeal wrote: post #18

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.

This may work for something simple, but a typical database cluster setup will be impossible / impractical to try to emulate in containers because you'd need to configure a lot of things not normally available inside containers (s.a. how storage is attached, how memory is allocated).

Since OP mentioned DBT (kind of weird, hopefully, it's at least DBT2, since DBT is very old), they mean to test the productivity of the system rather than correctness of some queries (typical tests that deal with workloads similar to DBT2 are, eg. pgbench). Running pgbench over a database setup in a container will tell you nothing / might just confuse you, if you don't understand the difference between how database is meant to be setup and what happens in container.

Re: Ask HN: How do you test SQL?

#263

Post by RobinL »

MrPowers wrote: post #256
RobinL wrote: post #43

Try and write any complex SQL as a series of semantically meaningful CTEs. Test each part of the CTE pipeline with an in.parquet and an expected_out.parquet (or in.csv and out.csv if you have simple datatypes, so it works better with git). And similarly test larger parts of the pipeline with 'in' and 'expected_out' files. If you use DuckDB to run the tests, you can reference those files as if they were tables (select…

Spark makes it easy to wrap SQL in functions that are easy to test. I am the author of the popular Scala Spark (spark-fast-tests) and PySpark (chispa) testing libraries. Some additional tips to speed up Spark tests (can speed up tests between 70-90%): * reuse the same Spark session throughout the test suite * Set shuffle partitions to 2 (instead of default which is 200) * Use dependency injection to avoid disk I/O in…

That's super useful, thanks. Could you expand on the 'Use dependency injection to avoid disk I/O in the test suite' point please - I'm not sure I understand what it means but it sounds interesting!

Re: Ask HN: How do you test SQL?

#264

Post by crabbone »

Can you please expand on what you mean by DBT?

DBT, specifically, DBT-2 is a suit of tests designed to benchmark a database system. These tests aren't interested in, eg. correctness of an application that is using the database. They are meant to be testing the system as a whole by modeling some sort of an "average business" and defining some sort of an "average business operation" and estimating how many of such operations can a particular deployment of a system perform.

Such tests are rarely of any interest to application developer, and are more geared towards DBAs who execute such tests to estimate the efficiency of a system they deploy or to estimate the amount of hardware necessary to support a business.

MySQL DBT2 suit: https://dev.mysql.com/downloads/benchmarks.html

PostgreSQL DBT2 suit: https://wiki.postgresql.org/wiki/DBT-2

Those tools are typically modeled on TPC-B... And, it would require a separate discussion to describe why these tests are obsolete and why there isn't really any replacement.

----

However, from the rest of your question it seems that you may use DBT acronym in some other way... So, what exactly are you testing? Are you interested in performance? A benchmark? Schema correctness? Are you perhaps trying to simply test the application that is using a SQL database and you want to avoid dealing with the database setup as much as possible?

Re: Ask HN: How do you test SQL?

#265

Post by newrotik »

urthor wrote: post #138
ahakanbaba wrote: post #125

The hard part about testing SQL is decoupling from infrastructure and big data sources. We use DuckDB, and pandas dataframes mock data sources to unit test SQL. Python testing frameworks (or simple assert statements) can be used to compare inputs and outputs. When the tests pass, we can change from DuckDB to Spark. This helps decouple testing Spark pipelines from the SparkSession and infrastructure, which saves a lot…

fwiw, you can share a spark session between unit tests. Even persist a spark session throughout the day so your tests run against a hot session. Straight TDD with spark is perfectly fine if you know what you're doing. I'm not saying it's easy or there's an easy guide somewhere, but it's possible. If you're using Pyspark via the API, it's likely an incredibly important part of your process.

Would you be able to elaborate on your approach to TDD with spark sessions? You can persist them, which is only useful if you are doing multiple tests in a run.

But I find myself running one given test, making some code changes, and then wanting to run it again, over and over. Instantiating a local spark session takes several seconds every iteration. Enough for me to often want to "alt tab" into something else instead of waiting. It's very disruptive.

I did not know about Fugue but will definitely give it a try. Looks almost too good to be true.

Re: Ask HN: How do you test SQL?

#266

Post by vitorbaptistaa »

crabbone wrote: post #264

Can you please expand on what you mean by DBT? DBT, specifically, DBT-2 is a suit of tests designed to benchmark a database system . These tests aren't interested in, eg. correctness of an application that is using the database. They are meant to be testing the system as a whole by modeling some sort of an "average business" and defining some sort of an "average business operation" and estimating how many of such ope…

Not the OP, but I think he/she meant https://www.getdbt.com

Re: Ask HN: How do you test SQL?

#267

Post by Vaslo »

crabbone wrote: post #264

Can you please expand on what you mean by DBT? DBT, specifically, DBT-2 is a suit of tests designed to benchmark a database system . These tests aren't interested in, eg. correctness of an application that is using the database. They are meant to be testing the system as a whole by modeling some sort of an "average business" and defining some sort of an "average business operation" and estimating how many of such ope…

I think you are talking about two different DBTs. OP is referring to this one I believe:

https://www.getdbt.com/

Re: Ask HN: How do you test SQL?

#268

Post by crabbone »

crabbone wrote: post #264

Can you please expand on what you mean by DBT? DBT, specifically, DBT-2 is a suit of tests designed to benchmark a database system . These tests aren't interested in, eg. correctness of an application that is using the database. They are meant to be testing the system as a whole by modeling some sort of an "average business" and defining some sort of an "average business operation" and estimating how many of such ope…

Oh, someone else below suggested that DBT in OP may be this: https://docs.getdbt.com/ .

Well... I don't know anything about this tool, but from cursory reading, this is the correctness kind of testing that is meant to examine whether the way you defined the schema actually leads to the results you expect in the data. Well... it's not an interesting kind of testing, at least not for me. So, I don't know much about it. Also, while, obviously, I'm not familiar with this newer tool, I wouldn't trust much someone who reused the name of an existing tool to do something completely unrelated in the same domain. It just feels like these people don't have a very good familiarity with the subject because they caused this confusion.

Re: Ask HN: How do you test SQL?

#269

Post by crabbone »

Vaslo wrote: post #267
crabbone wrote: post #264

Can you please expand on what you mean by DBT? DBT, specifically, DBT-2 is a suit of tests designed to benchmark a database system . These tests aren't interested in, eg. correctness of an application that is using the database. They are meant to be testing the system as a whole by modeling some sort of an "average business" and defining some sort of an "average business operation" and estimating how many of such ope…

I think you are talking about two different DBTs. OP is referring to this one I believe: https://www.getdbt.com/

Yeah, I went to read more comments and figured as much. I don't know much about this tool, but since it is certainly newer than original DBT, and chosen to use the same name, while operating in the same domain... I wouldn't trust these people. I have a feeling they simply didn't know the older tool existed, and I generally don't want to trust people who don't know stuff about what seems like their primary business.

Re: Ask HN: How do you test SQL?

#270

Post by crabbone »

vitorbaptistaa wrote: post #266
crabbone wrote: post #264

Can you please expand on what you mean by DBT? DBT, specifically, DBT-2 is a suit of tests designed to benchmark a database system . These tests aren't interested in, eg. correctness of an application that is using the database. They are meant to be testing the system as a whole by modeling some sort of an "average business" and defining some sort of an "average business operation" and estimating how many of such ope…

Not the OP, but I think he/she meant https://www.getdbt.com

Yeah, thanks for pointing this out. The kind of testing this tool does isn't up my line of work as a system / storage automation person, so, I wouldn't immediately know about it. Reusing the name of an older tool in the area with not so many tools for a completely different purpose doesn't instill a lot of trust though.
Post reply on HN