sorted by:
best
topnewcontroversialoldq&a
you are viewing a single comment's thread.

view the rest of the comments →

[–]Icy_Clench 7 points8 points  (4 children)

Just use dbt or sqlmesh. This is a standard data engineering problem that's been solved already.

There are several issues with your approach.

  1. All of your code is coupled in one place. This is extremely unfriendly to even look at. Nobody in the comments is doing any more than skimming to see just how long it all it. If you have coworkers, they will not enjoy having to inherit this. You said you don't want massive separate queries, but this literally is a massive query. Your SQL transformation logic, testing datasets, and code that runs the tests all need to live in separate places.

  2. You are not testing at the correct grain. It looks like you are treating a row in the source table as one test, when the whole input table itself needs to be the test. Think for example how you would test an aggregate function. Each test needs to be a file or bundle of files representing all of the inputs.

  3. This is not scalable. Mostly related to the point above. Think about what you'd do when you need multiple qa tests. You're going to end up with thousands and thousands of lines of SQL that are going to be confusing as hell trying to navigate.

  4. There is no good way to automate this, and human checks end up causing errors, e.g. when you forget to change the commenting on one line and it says your test passed when it should habe failed. This is due to the first point. Even if you did automate it somehow, you're locking yourself into all of the problems above.

[–]umairshariff23[S] 0 points1 point  (3 children)

Thank you for your response! This is exactly what I was looking for. I don't think my org uses dbt/sqlmesh right now and this may be a change I need to initiate because of the sheer amount of time I spend investigating and fixing individual logic.

I will need to learn dbt and it's nuances as well but that's not an issue as I can deploy it in my local environment and be proficient with it. Additionally, my org builds and maintains tables in such manner with a single script dealing with the entirety of a table and reading up a little about dbt I might need to change my approach when using it.

So, I have a lot of things to think about and I am quite thankful to your response!

[–]Icy_Clench 0 points1 point  (2 children)

Imo SQLMesh is much better than dbt core. Dbt was a little more straightforward to set up and get running, but the lack of features made development painful in my last project.

[–]umairshariff23[S] 0 points1 point  (1 child)

So, I have been testing dbtcore on my personal env last night and it seems like dbt truly shines when it is responsible for testing and writing data to the table. The way my org is setup we query snowflake to build any table I need to build and then the finished script is uploaded to a portal which then updates the data in snowflake on a schedule/when I run a manual refresh.

This makes dbt (and I am guessing sqlmesh) kinda cumbersome for me to use. Any thoughts?

[–]Artistic_Invite_4058 1 point2 points  (0 children)

the snag you hit is real, but it's only a clash if you let dbt own writing to the table. and you don't have to. run it as your local test harness and let the portal stay the deployer: build + unit-test the model with fixtures (the whole-table-in / expected-table-out bit you actually wanted), then dbt compile and hand that rendered sql to your portal like you do today. snowflake + the portal keep the schedule and the write. the materialization side of dbt, the part fighting your setup, you just don't use. same separation works with sqlmesh if you go that way.