howdy! we maintain a very large dbt project pointe...
# community-support
i
howdy! we maintain a very large dbt project pointed at Google BigQuery and are working on pulling elementary into our stack ... we merged it yesterday afternoon, and this morning started to see the following exception on all of our elementary post-hooks:
Copy code
on-run-end failed, error: Resources exceeded during query execution: Not enough resources for query planning - too many subqueries or query is too complex.
have other folks seen this before? some cursory Googling is telling me that it's a complexity quota in BigQuery that we've hit
j
Hey Ian! What elementary version are you on? This should be fixed in the latest version.
i
hmm, I'm on
0.16.1
but according to PyPI that is the latest version
j
Sorry not of the python package, but of the dbt package (what you have defined in your
packages.yml
. You should be on
0.16.2
to get this PR: https://github.com/elementary-data/dbt-data-reliability/pull/765
i
AH thank you, let me see
amazing, that built I'll take a run at it. thank you Joost!
j
Great! Let me know if it didn't work 🙂
i
bummer, no dice! for what it's worth we maintain a very large dbt project (> 4,000 models) so I'm wondering if this is going to be a persistent issue or if we need to fine tune which models trigger the post-hooks and which ones don't
j
This is on me 🫠 I forgot to tell you to set the following vars in your `dbt_project.yml`:
Copy code
vars:
  dbt_artifacts_chunk_size: 1000
  query_max_size: 1000000
4000+ models 😎
Let me know if it works!
i
hi Joost - unfortunately this still didn't work for us I'm seeing
on-run-end failed, error: The query is too large. The maximum standard SQL query length is 1024.00K characters, including comments and white space characters
here's what our config looks like now
we dropped the chunk size down to 500, which seems to have worked but we're now seeing
Query error: Transaction is aborted due to concurrent update against table tmc-data-warehouse:dbt_elementary.dbt_models
... do y'all have guidance / docs for enabling a backoff or something? most of our ELT pipelines run concurrently so this exception makes sense to me
j
Great that the
chunk_size
worked! Is there any way of the pipelines consecutively?
Once it has done a succesful run it shouldn't be such a heavy query the next time I believe
I would have to confirm this though
i
but the size of the query doesn't factor into concurrent transactions right? it is not feasible for us to run these pipelines linearly on a daily basis so this seems like a problem
j
No it doesn't - we would have to dive into it a bit more. My hope is that the query is small enough that the chance of updating the same table at the same time is small enough that it wouldn't give any issues
i
gotcha, because the transaction would start and end quickly enough for this not to be a routine issue
j
That is my optimistic hope haha
yes
i
makes sense to me, let me know if there's anything you'd like us to try on our end
j
So if we can run it manually consecutelively and let the initial queries finish
then we can try to do you (airflow?) scheduling and see if anything fails
i
we use an orchestration platform called Civis to execute our pipelines, I'm assuming the
models
post hook differs depending on the select statement?
basically we're running ~50 ELT pipelines with different select statements in the dbt command (depending on the data source), so the elementary post hook is running ~50 times daily
j
And did you already have a succesful day of all the runs?
Because it does do caching so the updates should be relatively small
i
no we just merged the PR to reduce the chunk size last night, then this morning many of dbt jobs failed
j
Ok will also ask our tech lead if he has any ideas. Let me know if you have any updates!
i
thanks Joost
I've gotten our chunk size down to
100
and am still seeing "the query is too large"
presumably reducing the chunk size means that the inserts into the BigQuery tables will be more numerous but shorter in length - is that the wrong assumption to make?
I've played around with the chunk size quite a bit and am still unable to run these models in production - if you have any advice that would be really helpful
j
Just to double check, you are on dbt package version 0.16.2 right?
i
ack great call, our package metadata was cached at 0.15.2
let me see if that fixes it
j
I'm keeping my fingers crossed
Hey Ian, any luck? 🙂
i
early returns look good! I'm running it on bigger and bigger pseudo-production runs to make sure it holds up but so far so good