6 ms·

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 (

by RobinL 4y ago

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 * from 'in.parquet'), and the tests will run extremely fast One challenge if you're using Spark is that test can be frustratingly slow to run. One possible solution (that I use myself) is to run most tests using DuckDB, and only e.g. the overall test using Spark SQL. I've used the above strategy with PyTest, but I'm not sure conceptually it's particularly sensitive to the programming language/testrunner you use. Also I have no idea whether this is good practice - it's just something that seemed to work well for me. The approach with csvs can be nice because your customers can review these files for correctness (they may be the owners of the metric), without them needing to be coders. They just need to confirm in.csv should result in expected_out.csv. If it makes it more readable you can also inline the 'in' and 'expected_out' data e.g. as a list of dicts and pass into DuckDB as a pandas dataframe One gotya is SQL does not guarantee order so you need to somehow sort or otherwise ensure your tests are robust to this

jimmygrapes 4y ago
My ignorance of the topic (and experience with a mostly unrelated one) is showing, but all I could think of when you said CTE was "chronic traumatic encephalopathy". This made a lot more sense when you generalized answering the question as if it's a given that Python is necessary (I know that's not your intent, but that's how it comes off). Not much more to say, just observing, sorry if this is irrelevant commentary.
- jph00 4y ago
  CTE==Common Table Expression. It's not specific to any particular language. It's basically like a view, except you can define them the same place as you use them, and they can be recursive.
  - jimmygrapes 4y ago
    Thank you for the clarification, that actually makes sense in context! ... Although also now I'm a little. put off because you reminded me of working with DOS-based SAP interfaces and Oracle's Java-based attempts to make "interactive views" circa 2006 :/
- osigurdson 4y ago
  >> I could think of when you said CTE was "chronic traumatic encephalopathy" The title is "Ask HN: How do you test SQL"
- db48x 4y ago
  Sometimes using databases feels just like repeatedly slamming your head into your desk, so that fits.
  - collyw 4y ago
    I used to think that, but once you start thing about them in the right way, the relational model is pretty nice.
    - db48x 4y ago
      Agreed, but it doesn’t solve every problem. And then there are the purely operational issues; for example a few days ago autovacuum was never able to completely finish vacuuming one particular table, but then the problem mysteriously went away and now it’s fine. Wonderful.
shazzdeeds 4y ago
A few quick tips to help slow Spark tests. 1. Make sure you're using a shared session between the test suite. So that spin-up only has to occur once per suite and not per test. This has the drawback of not allowing dataframe name reuse across tests, but who cares. 2. If you have any kind of magic-number N in the SparkSQL or dataframe calls (e.g coalesce(N), repartition(N)) change N to be parameterized, and set it to 1 for the test. 3. Make sure the master doesn't have any more than 'local[2]' set. Or less depending on your workstation.
curiousllama 4y ago
> Try and write any complex SQL as a series of semantically meaningful CTEs I find this helps a heck of a lot with maintainability + debugging as well
- atwebb 4y ago
  It can wind up with more of a procedural thought though than set-based. Not always, just something to pay attention to and pushing filters early / explicitly. It is asking more of the optimizer a lot of the times and that's were a bunch of the cautions come in to play.
  - mwigdahl 4y ago
    Just tuned a query like this today. Many chained CTEs, joined together at each stage to do range checking logic within subgroups. There were so many self joins that the row estimates (and requested memory grant) were through the roof. I love CTEs, but the query semantics matter too.
ChadMoran 4y ago
> Try and write any complex SQL as a series of semantically meaningful CTEs I used this exact same method. Not only does it help me but those who come after trying to understand what's going on.
colanderman 4y ago
One caution with PostgreSQL, CTEs under some circumstances (and in all circumstances, prior to PostgreSQL 12) act as optimization barriers. Specify `NOT MATERIALIZED` before the CTE definition to ensure that they are optimized same as a sub-SELECT would be.
- nicoburns 4y ago
  This is a good point, although it’s worth noting that there are definitely cases where you want the sun table to be materialised (esp. when that table is small and referenced many times)
  - dragonwriter 4y ago
    Yeah, its definitely one of those things where it is worth reading the docs [0], and not just adopting an “always do it this way” rule of thumb. [0] https://www.postgresql.org/docs/current/queries-with.html#id-1.5.6.12.7 https://www.postgresql.org/docs/current/queries-with.html#id...
  - bolanyo 4y ago
    Is 'sun table' a particular concept here? A typo?
    - nicoburns 4y ago
      It's a typo. I was trying to write 'CTE table' and iOS "helpfully" autocorrected it.
- timwis 4y ago
  Just to flag this behaviour changes in v12: https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-allow-user-control-of-cte-materialization-and-change-the-default-behavior/ https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...
  - srcreigh 4y ago
    Not really. CTEs are still optimization barriers after v12 if they're referenced more than once. That blog post is confused, but the commit message in the post is clear enough.
devin 4y ago
> Try and write any complex SQL as a series of semantically meaningful CTEs. Could you or anyone else on the post provide an example?
- paulmd 4y ago
  let's say you are doing a paystub rollup - a department has multiple employees, an employee has multiple paystubs. if you are storing fully denormalized concrete data about the value of salary/medical/retirement both pre- and post-tax that was actually paid to each pay period (because this can vary!), then you can define a view that does salary-per-employee (taxable, untaxable, etc), and then a view that rolls up employees-per-department. And you can write unit tests for all of those. that's a super contrived example but basically once group aggregate or window functions and other complex sub-queries start coming into the picture it becomes highly desirable to write those as their own views. And you can write some simple unit tests for those views. there are tons of shitty weird sql quirks that come from nullity/etc and you can have very weird specific sum(mycol where condition) and other non-trivial sub-subquery logic, and it's simple to just write an expression that you think is true and validate that it works like you think, that all output groups (including empty/null groups etc) that you expect to be present or not present actually are/aren't, etc. I'm not personally advocating for writing those as CTEs specifically as a design goal in preference to views, personally I'd rather write views where possible. But recursive CTEs are the canonical approach for certain kinds of queries (particularly node/tree structures) and at minimum a CTE certainly is a "less powerful context" than a generalized WHERE EXISTS (select 1 from ... WHERE myVal = outerVal) or value-select subquery. it's desirable to have that isolation from the outer SQL query cursor imo (and depending on what you're asking, it may optimize to something different in terms of plan). Writing everything as a single query, where the sub-sub-query needs to be 100% sure not to depend on the outer-outer-cursor, is painful. What even is "DEEP_RANK()" in the context of this particular row/window? If you've got some bizarre (RANK(myId order by timestamp) or whatever, does it really work right? Etc. It's just a lot easier to conceptually write each "function" as a level with its own unit tests. Same as any other unit-testable function block, it's ideal if it's Obviously Correct and then you define compositions of Obvious Correctness with their own provable correctness. And if it's not Obviously Correct then you need the proof even more. Encapsulate whatever dumb shit you have to do to make it run correctly and quick into a subquery and just do a "inner join where outerQuery.myId = myView.myId". Hide the badness.
paulmd 4y ago
If your database supports it, unit tests are an absolutely ideal use-case for temporary tables or global temporary tables. A global temporary table can be defined with the same schema as the correct table and will "exist" for the purpose of view/CTE definitions, but any data inserted into the table will only ever be visible from that specific thread context. The rules depend but basically either it exists until the thread is closed, or until the session calls commit, but the semantics will (theoretically) be optimized for one thread and rapid inserts/clears. If you build your unit tests so that a thread will not commit until it's finished, or clear it before/after, it's pretty ideal for that. You can feed in data for that test and it won't be visible anywhere else. Potentially if you scripted your table creates you could add an additional rule that GLOBAL TEMPORARY TABLE gets added to any other tabledefs but only for unit tests. Or just update both in parallel. The other useful use-case I've found for this is that you can do an unlimited-size "WHERE myId IN (:1, :2 ...)" by doing a GTT expressed as "INNER JOIN myId = myGtt.myid", and this has the advantage of not thrashing the query planner for every literal sqltext resulting from different lengths of myList (this will fill up your query cache!). Since it's one literal string it always hits the same query plan cache. I am told this has problems in oracle's OCI when using PDBs, apparently GTTs will shit up the redo-log tablespace (WAL equivalent). But so do many many things, PDBs are apparently a much different and much less capable and incredibly immature implementation that seems essentially abandoned/not a focus from what I've been told. I was very surprised having originally written that code in regular Oracle and not having had problems but PDBs just aren't the same (right down to nulls being present in indexes!).
- n0n0n4t0r 4y ago
  Off topic incoming (sorry ) I used this trick (join temporaryFoo instead of where foo in ...) in production fifteen years ago, using MySQL. The gain was really astonishing. Several instructions can be optimized using joins on specialty craft tables (I know of LIMIT for instance). This is one of the worst drawbacks of orm everywhere: nobody even seems to think about those optimisations anymore.
  - paulmd 4y ago
    also, views can be defined as ORM objects/POJOs. They can be read-only, some views are "trivially-remappable" (if there is a 1:1 mapping from view columns to table columns) or even you can use INSTEAD OF INSERT/UPDATE triggers to take writes on that view and do something completely else with it. ;) I've used that to do dumb shit like lever a table schema into an ORM mapping that it wasn't really designed for, that I had to maintain fallback compatibility conditions onto the tables. views are really a db-level "interface" implementation that few people really exploit fully. Here is the definition, here is the implementation. And yes global temporary tables are such a cute cheat for unlimited-size query-specific data ;)
gpvos 4y ago
CTE = common table expression, i.e. a WITH clause (possibly recursive) before your SELECT (or other) statement. https://learnsql.com/blog/what-is-common-table-expression/ https://learnsql.com/blog/what-is-common-table-expression/
- stormdennis 4y ago
  Yes, I didn't know what it was so I had looked it up before I saw yours. The definition I got is below: A common table expression, or CTE, is a temporary named result set created from a simple SQL statement that can be used in subsequent SELECT, DELETE, INSERT, or UPDATE statements.
  - gpvos 4y ago
    I knew about WITH clauses, just didn't know the name.
  - xpil 4y ago
    > (...) in subsequent SELECT, DELETE, INSERT, or UPDATE statements The annoying part is that certain RDMBS engines (MySQL for instance) require you to write the INSERT keyword before any CTEs. So, your T-SQL or PGSQL query: `with cte1 as (...) insert into ... select ... from cte1;` becomes: `insert into ... with cte1 as (...) select ... from cte1;` I know the difference is minor but when you deal with many different DB engines, it is simply annoying.
marijnz0r 4y ago
I really like this answer because it tests CTEs in a modular way. One question I have is: how do you test a the CTE with an in.csv and out.csv without altering the original CTE? Currently I have a chain of multiple CTEs in a sequence, so how would I be able to take the middle CTE and "mock" the previous one without altering the CTE actually used in production? I prefer not to maintain a CTE for the live query and the same CTE adapted for tests.
mritchie712 4y ago
Doesn't that mean you have to copy all your data into duckdb? I'd imagine with even a modest data warehouse, loading the entire thing to duckdb would be unfeasible.
- RobinL 4y ago
  The tests are being run against small test datasets of 10s of rows rather than the real data that test whether the behaviour of transforms/joins etc. is as expected. You're right, this approach wouldn't be sensible if the tests have to use large production tables
  - mritchie712 4y ago
    How do you get the small datasets? Selecting a random sample from the source?
    - wnolens 4y ago
      The test itself would provide a test dataset. That way you can cook up the interesting cases and ensure your queries work on them.
MrPowers 4y ago
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 the test suite * Use fast DataFrame equality when possible. assertSmallDataFrameEquality is 4x faster than assertLargeDataFrameEquality. Some benchmarks here: https://github.com/MrPowers/spark-fast-tests#why-is-this-library-fast https://github.com/MrPowers/spark-fast-tests#why-is-this-lib... * Use column equality to test column functions. Don't compare DataFrames unless you're testing custom DataFrame transformations. See the spark-style-guide for definitions for these terms: https://github.com/MrPowers/spark-style-guide/blob/main/PYSPARK_STYLE_GUIDE.md https://github.com/MrPowers/spark-style-guide/blob/main/PYSP... Spark is an underrated tool for testing SQL. Spark makes it really easy to abstract SQL into unit testable chunks. Configuring your tests properly takes some knowledge, but you can make the tests run relatively quickly.
- RobinL 4y ago
  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!
  - MrPowers 4y ago
    Sure, this blog post explains the dependency injection design pattern with Spark: https://mrpowers.medium.com/dependency-injection-with-spark-8367b6956343 https://mrpowers.medium.com/dependency-injection-with-spark-... You can structure your code to read from paths when run in the production environment, but inject DataFrames you build in memory for your test suite. Spark is designed to read multiple files in parallel, so it's not optimized to read a single tiny file. That's why it's best to avoid I/O in Spark test suites whenever possible.
intrasight 4y ago
I'm a fan of CTEs too. Here's a pattern that I use in Sql Server for testing/debugging when using CTE pipelines. Same approach would work with "FOR JSON" declare @debug bit = 1; ;with cte1 as ( select @debug AS Debug1, ... ), cte2 as ( select @debug AS Debug2, ... from cte1 ), cte3 as ( select @debug AS Debug3, ... from cte2 ) select -- dump intermediate if debug ( select * from cte1 where Debug1=1 for xml raw ('row'), root ('cte1'), type ) ,( select * from cte2 where Debug2=1 for xml raw ('row'), root ('cte2'), type ) ,( select * from cte3 where Debug3=1 for xml raw ('row'), root ('cte2'), type ) -- final results ,( select ... from cte3 for xml raw ('row'), type ) for xml raw ('results'), type;
tensor 4y ago
Decomposing a large query into smaller subsets is the right approach, but I would strongly suggest doing it with VIEWs rather than CTEs. CTEs are a useful tool, but come with their own performance profiles and often using them too much will lead to slower queries.

by RobinL 4y ago

jimmygrapes 4y ago

My ignorance of the topic (and experience with a mostly unrelated one) is showing, but all I could think of when you said CTE was "chronic traumatic encephalopathy". This made a lot more sense when you generalized answering the question as if it's a given that Python is necessary (I know that's not your intent, but that's how it comes off). Not much more to say, just observing, sorry if this is irrelevant commentary.

jph00 4y ago
CTE==Common Table Expression. It's not specific to any particular language. It's basically like a view, except you can define them the same place as you use them, and they can be recursive.
- jimmygrapes 4y ago
  Thank you for the clarification, that actually makes sense in context! ... Although also now I'm a little. put off because you reminded me of working with DOS-based SAP interfaces and Oracle's Java-based attempts to make "interactive views" circa 2006 :/
osigurdson 4y ago
>> I could think of when you said CTE was "chronic traumatic encephalopathy" The title is "Ask HN: How do you test SQL"
db48x 4y ago
Sometimes using databases feels just like repeatedly slamming your head into your desk, so that fits.
- collyw 4y ago
  I used to think that, but once you start thing about them in the right way, the relational model is pretty nice.
  - db48x 4y ago
    Agreed, but it doesn’t solve every problem. And then there are the purely operational issues; for example a few days ago autovacuum was never able to completely finish vacuuming one particular table, but then the problem mysteriously went away and now it’s fine. Wonderful.

shazzdeeds 4y ago

A few quick tips to help slow Spark tests. 1. Make sure you're using a shared session between the test suite. So that spin-up only has to occur once per suite and not per test. This has the drawback of not allowing dataframe name reuse across tests, but who cares. 2. If you have any kind of magic-number N in the SparkSQL or dataframe calls (e.g coalesce(N), repartition(N)) change N to be parameterized, and set it to 1 for the test. 3. Make sure the master doesn't have any more than 'local[2]' set. Or less depending on your workstation.

curiousllama 4y ago

> Try and write any complex SQL as a series of semantically meaningful CTEs I find this helps a heck of a lot with maintainability + debugging as well

atwebb 4y ago
It can wind up with more of a procedural thought though than set-based. Not always, just something to pay attention to and pushing filters early / explicitly. It is asking more of the optimizer a lot of the times and that's were a bunch of the cautions come in to play.
- mwigdahl 4y ago
  Just tuned a query like this today. Many chained CTEs, joined together at each stage to do range checking logic within subgroups. There were so many self joins that the row estimates (and requested memory grant) were through the roof. I love CTEs, but the query semantics matter too.

ChadMoran 4y ago

> Try and write any complex SQL as a series of semantically meaningful CTEs I used this exact same method. Not only does it help me but those who come after trying to understand what's going on.

colanderman 4y ago

One caution with PostgreSQL, CTEs under some circumstances (and in all circumstances, prior to PostgreSQL 12) act as optimization barriers. Specify `NOT MATERIALIZED` before the CTE definition to ensure that they are optimized same as a sub-SELECT would be.

nicoburns 4y ago
This is a good point, although it’s worth noting that there are definitely cases where you want the sun table to be materialised (esp. when that table is small and referenced many times)
- dragonwriter 4y ago
  Yeah, its definitely one of those things where it is worth reading the docs [0], and not just adopting an “always do it this way” rule of thumb. [0] https://www.postgresql.org/docs/current/queries-with.html#id-1.5.6.12.7 https://www.postgresql.org/docs/current/queries-with.html#id...
- bolanyo 4y ago
  Is 'sun table' a particular concept here? A typo?
  - nicoburns 4y ago
    It's a typo. I was trying to write 'CTE table' and iOS "helpfully" autocorrected it.
timwis 4y ago
Just to flag this behaviour changes in v12: https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-allow-user-control-of-cte-materialization-and-change-the-default-behavior/ https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...
- srcreigh 4y ago
  Not really. CTEs are still optimization barriers after v12 if they're referenced more than once. That blog post is confused, but the commit message in the post is clear enough.

devin 4y ago

> Try and write any complex SQL as a series of semantically meaningful CTEs. Could you or anyone else on the post provide an example?

paulmd 4y ago
let's say you are doing a paystub rollup - a department has multiple employees, an employee has multiple paystubs. if you are storing fully denormalized concrete data about the value of salary/medical/retirement both pre- and post-tax that was actually paid to each pay period (because this can vary!), then you can define a view that does salary-per-employee (taxable, untaxable, etc), and then a view that rolls up employees-per-department. And you can write unit tests for all of those. that's a super contrived example but basically once group aggregate or window functions and other complex sub-queries start coming into the picture it becomes highly desirable to write those as their own views. And you can write some simple unit tests for those views. there are tons of shitty weird sql quirks that come from nullity/etc and you can have very weird specific sum(mycol where condition) and other non-trivial sub-subquery logic, and it's simple to just write an expression that you think is true and validate that it works like you think, that all output groups (including empty/null groups etc) that you expect to be present or not present actually are/aren't, etc. I'm not personally advocating for writing those as CTEs specifically as a design goal in preference to views, personally I'd rather write views where possible. But recursive CTEs are the canonical approach for certain kinds of queries (particularly node/tree structures) and at minimum a CTE certainly is a "less powerful context" than a generalized WHERE EXISTS (select 1 from ... WHERE myVal = outerVal) or value-select subquery. it's desirable to have that isolation from the outer SQL query cursor imo (and depending on what you're asking, it may optimize to something different in terms of plan). Writing everything as a single query, where the sub-sub-query needs to be 100% sure not to depend on the outer-outer-cursor, is painful. What even is "DEEP_RANK()" in the context of this particular row/window? If you've got some bizarre (RANK(myId order by timestamp) or whatever, does it really work right? Etc. It's just a lot easier to conceptually write each "function" as a level with its own unit tests. Same as any other unit-testable function block, it's ideal if it's Obviously Correct and then you define compositions of Obvious Correctness with their own provable correctness. And if it's not Obviously Correct then you need the proof even more. Encapsulate whatever dumb shit you have to do to make it run correctly and quick into a subquery and just do a "inner join where outerQuery.myId = myView.myId". Hide the badness.

paulmd 4y ago

If your database supports it, unit tests are an absolutely ideal use-case for temporary tables or global temporary tables. A global temporary table can be defined with the same schema as the correct table and will "exist" for the purpose of view/CTE definitions, but any data inserted into the table will only ever be visible from that specific thread context. The rules depend but basically either it exists until the thread is closed, or until the session calls commit, but the semantics will (theoretically) be optimized for one thread and rapid inserts/clears. If you build your unit tests so that a thread will not commit until it's finished, or clear it before/after, it's pretty ideal for that. You can feed in data for that test and it won't be visible anywhere else. Potentially if you scripted your table creates you could add an additional rule that GLOBAL TEMPORARY TABLE gets added to any other tabledefs but only for unit tests. Or just update both in parallel. The other useful use-case I've found for this is that you can do an unlimited-size "WHERE myId IN (:1, :2 ...)" by doing a GTT expressed as "INNER JOIN myId = myGtt.myid", and this has the advantage of not thrashing the query planner for every literal sqltext resulting from different lengths of myList (this will fill up your query cache!). Since it's one literal string it always hits the same query plan cache. I am told this has problems in oracle's OCI when using PDBs, apparently GTTs will shit up the redo-log tablespace (WAL equivalent). But so do many many things, PDBs are apparently a much different and much less capable and incredibly immature implementation that seems essentially abandoned/not a focus from what I've been told. I was very surprised having originally written that code in regular Oracle and not having had problems but PDBs just aren't the same (right down to nulls being present in indexes!).

n0n0n4t0r 4y ago
Off topic incoming (sorry ) I used this trick (join temporaryFoo instead of where foo in ...) in production fifteen years ago, using MySQL. The gain was really astonishing. Several instructions can be optimized using joins on specialty craft tables (I know of LIMIT for instance). This is one of the worst drawbacks of orm everywhere: nobody even seems to think about those optimisations anymore.
- paulmd 4y ago
  also, views can be defined as ORM objects/POJOs. They can be read-only, some views are "trivially-remappable" (if there is a 1:1 mapping from view columns to table columns) or even you can use INSTEAD OF INSERT/UPDATE triggers to take writes on that view and do something completely else with it. ;) I've used that to do dumb shit like lever a table schema into an ORM mapping that it wasn't really designed for, that I had to maintain fallback compatibility conditions onto the tables. views are really a db-level "interface" implementation that few people really exploit fully. Here is the definition, here is the implementation. And yes global temporary tables are such a cute cheat for unlimited-size query-specific data ;)

gpvos 4y ago

CTE = common table expression, i.e. a WITH clause (possibly recursive) before your SELECT (or other) statement. https://learnsql.com/blog/what-is-common-table-expression/ https://learnsql.com/blog/what-is-common-table-expression/

stormdennis 4y ago
Yes, I didn't know what it was so I had looked it up before I saw yours. The definition I got is below: A common table expression, or CTE, is a temporary named result set created from a simple SQL statement that can be used in subsequent SELECT, DELETE, INSERT, or UPDATE statements.
- gpvos 4y ago
  I knew about WITH clauses, just didn't know the name.
- xpil 4y ago
  > (...) in subsequent SELECT, DELETE, INSERT, or UPDATE statements The annoying part is that certain RDMBS engines (MySQL for instance) require you to write the INSERT keyword before any CTEs. So, your T-SQL or PGSQL query: `with cte1 as (...) insert into ... select ... from cte1;` becomes: `insert into ... with cte1 as (...) select ... from cte1;` I know the difference is minor but when you deal with many different DB engines, it is simply annoying.

marijnz0r 4y ago

I really like this answer because it tests CTEs in a modular way. One question I have is: how do you test a the CTE with an in.csv and out.csv without altering the original CTE? Currently I have a chain of multiple CTEs in a sequence, so how would I be able to take the middle CTE and "mock" the previous one without altering the CTE actually used in production? I prefer not to maintain a CTE for the live query and the same CTE adapted for tests.

mritchie712 4y ago

Doesn't that mean you have to copy all your data into duckdb? I'd imagine with even a modest data warehouse, loading the entire thing to duckdb would be unfeasible.

RobinL 4y ago
The tests are being run against small test datasets of 10s of rows rather than the real data that test whether the behaviour of transforms/joins etc. is as expected. You're right, this approach wouldn't be sensible if the tests have to use large production tables
- mritchie712 4y ago
  How do you get the small datasets? Selecting a random sample from the source?
  - wnolens 4y ago
    The test itself would provide a test dataset. That way you can cook up the interesting cases and ensure your queries work on them.

MrPowers 4y ago

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 the test suite * Use fast DataFrame equality when possible. assertSmallDataFrameEquality is 4x faster than assertLargeDataFrameEquality. Some benchmarks here: https://github.com/MrPowers/spark-fast-tests#why-is-this-library-fast https://github.com/MrPowers/spark-fast-tests#why-is-this-lib... * Use column equality to test column functions. Don't compare DataFrames unless you're testing custom DataFrame transformations. See the spark-style-guide for definitions for these terms: https://github.com/MrPowers/spark-style-guide/blob/main/PYSPARK_STYLE_GUIDE.md https://github.com/MrPowers/spark-style-guide/blob/main/PYSP... Spark is an underrated tool for testing SQL. Spark makes it really easy to abstract SQL into unit testable chunks. Configuring your tests properly takes some knowledge, but you can make the tests run relatively quickly.

RobinL 4y ago
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!
- MrPowers 4y ago
  Sure, this blog post explains the dependency injection design pattern with Spark: https://mrpowers.medium.com/dependency-injection-with-spark-8367b6956343 https://mrpowers.medium.com/dependency-injection-with-spark-... You can structure your code to read from paths when run in the production environment, but inject DataFrames you build in memory for your test suite. Spark is designed to read multiple files in parallel, so it's not optimized to read a single tiny file. That's why it's best to avoid I/O in Spark test suites whenever possible.

intrasight 4y ago

I'm a fan of CTEs too. Here's a pattern that I use in Sql Server for testing/debugging when using CTE pipelines. Same approach would work with "FOR JSON" declare @debug bit = 1; ;with cte1 as ( select @debug AS Debug1, ... ), cte2 as ( select @debug AS Debug2, ... from cte1 ), cte3 as ( select @debug AS Debug3, ... from cte2 ) select -- dump intermediate if debug ( select * from cte1 where Debug1=1 for xml raw ('row'), root ('cte1'), type ) ,( select * from cte2 where Debug2=1 for xml raw ('row'), root ('cte2'), type ) ,( select * from cte3 where Debug3=1 for xml raw ('row'), root ('cte2'), type ) -- final results ,( select ... from cte3 for xml raw ('row'), type ) for xml raw ('results'), type;

tensor 4y ago

Decomposing a large query into smaller subsets is the right approach, but I would strongly suggest doing it with VIEWs rather than CTEs. CTEs are a useful tool, but come with their own performance profiles and often using them too much will lead to slower queries.