I have a dbt project that is in redshift. I have a good sized test suite with built-in generic tests and unit tests giving decent coverage of my core models. One issue I ran into is that I couldn’t run a select * from my_core_model because I had some nested subqueries that aren’t supported in redshift (ERROR: This type of correlated subquery pattern is not supported due to internal error).
This broke one of our main analytics tables and wasn’t caught by tests because it was only one column (a revenue col that has a sum/subquery in a dependent table).
I’m wondering if there is a macro or way to just say “these are the tables I really care about, I just want to make sure a select * works on all of them” as a generic test. Since the tests in db need to return no rows, I’ve tried something like select * from my_core_model where pk_id is null but that query works since it optimizes it and doesn’t touch the revenue col. when I run select * from my_core_model where revenue is null it does fail, but then I would need to know all the cols in my models that specifically use subqueries.
I should mention as well that all of my tables are still default views.
I know the right answer is probably to use CTEs instead (I’ve since updated my current problem to use them and it went away) but it did make me think, as an absolute stopgap, it would be great to make sure a select * works on all my core tables.
Right now I just have a separate python script that does this for 4/5 tables after my dbt test, but I would love to do it in a more dbt-testy type of way. Just curious if anyone has run into this or has any thoughts
Thanks!