Hello all, are there any further details on the causes or fix for the issues with post hooks exceeding database limits (the fact that these limits get hit might show a problem for other DB's without the issue surfacing) - I can't see any open issue for this, is it expected behavior, a bug, user error and/or so on...

10:03:48  1 of 2 START hook: dwh.on-run-end.0 ............................................ [RUN]
10:03:48  1 of 2 OK hook: dwh.on-run-end.0 ............................................... [OK in 0.00s]
10:10:07  Database error while running on-run-end
10:10:07  
10:10:07  Finished running 1 seed, 2 hooks in 0 hours 6 minutes and 25.80 seconds (385.80s).
10:10:09  
10:10:09  Completed with 1 error and 0 warnings:
10:10:09  
10:10:09    on-run-end failed, error:
 001795 (42601): SQL compilation error: error line 2 at position 25
maximum number of expressions in a list exceeded, expected at most 16,384, got 20,842

No problem @Josh McCarter, glad i could help 😊

Hi @Ofek Weiss Quick question - this change helped on my local testing where the limit would otherwise occur but currently pushing to prod where our pipeline runs 8000ish tests (this is due to another change that formatted models resulting in re-builds and many test runs) the post hook is hanging (running but its not doing anything) - I'm not keen on removing the chunking as this does stop the snowflake limit but wondered if you might be able to advise on smaller or larger chunks for fixing the timeout. Cheers, Josh. Essentially hanging until the following - so I dont know if thats because its done what it wanted to do and intends to carry on (and cant because auth timeout) or if it's something else.

[0m16:14:32  Running 2 on-run-end hooks
[0m16:14:32  apply_meta_as_tags
[0m16:14:39  1 of 2 START hook: dwh.on-run-end.0 ............................................ [RUN]
[0m16:14:39  1 of 2 OK hook: dwh.on-run-end.0 ............................................... [[32mOK[0m in 0.00s]
[0m20:22:28  Database error while running on-run-end
[0m20:22:28  
[0m20:22:28  Finished running 8215 tests, 2 hooks in 4 hours 43 minutes and 40.61 seconds (17020.61s).
[0m20:22:32  
[0m20:22:32  [31mCompleted with 1 error and 9 warnings:[0m
[0m20:22:32  
[0m20:22:32    on-run-end failed, error:
 390114 (08001): Authentication token has expired.  The user must authenticate again.

I believe you can play around with any number lower than

16,384

(as this seems to be the limit of snowflake)

Hi @Ofek Weiss I'm currently locally running 10,000 tests with the post hook running afterwards and in the logs it states what it's trying to insert - should we expect that to be where the "chunking" is applied or are those inserts a separate thing. Chunk size is set to 5000 and here it's inserting 10223... 🤔 Vars are set with the others in the global dbt_project.yml vars section.

[0m14:39:42.941697 [debug] [MainThread]: Elementary: Inserting 10223 rows to table "DWH_PREPROD_DEC375"."ELEMENTARY"."ELEMENTARY_TEST_RESULTS"

vars:
  data_lake_db: data_lake
  snowflake_db: snowflake
  warehouse: default
  test: false
  incremental_test: false
  surrogate_key_treat_nulls_as_empty_strings: true
  use_common_masking_policy_schema_only: "True"
  common_masking_policy_schema: "SHARED"
  custom_materializations_map: '{ "sf_incremental": "table" }'
  common_tag_database: "{{ 'COMPLIANCE' if target.name == 'prod' else 'COMPLIANCE_PREPROD' }}"
  common_tag_schema: "TAGS"
  insert_rows_method: chunk
  dbt_artifacts_chunk_size: 5000

Based on your knowledge would this be something elementary should find easy or is there allot involved behind the scenes? (Because it's taking a very long time). In our prod env it's all running totally fine - but this is more something that can come up in our pipline - if there was a way to get the post hook not to run when calling the tests (in the pipeline) it would be ideal but I dont think thats a thing minus modifying the dbt_project.yml on the fly.

Hey @Josh McCarter, we actually had a bug that some insert queries did not consider the

dbt_artifacts_chunk_size

variable, this is resolved in version

0.14.1

of the dbt package

@Ofek Weiss I noticed the issue you mentioned and have updated the feature branch accordingly. However, I'm encountering resistance regarding the extended runtime of the post hook in our deployment pipeline when executing the tests. As a result, in the main branch, all post hook activities are currently suspended, effectively disabling elementary's test result collection. In this case, the post hook took an average of 3.017 seconds per test to capture and upload the artifacts. If we extrapolate this to 10,000 tests, the total runtime exceeds 8 hours, which can occur quite frequently with our setup (updating packages and other actions can trigger such extensive runs). My question is, is it realistic to expect the elementary post hook to handle 10,000 tests? (I also investigated memory usage, which Is improved by chunking and avoids hitting Snowflake's limits). There's also a bug in accurately capturing and recording the run time - the second post hook starts as soon as the first finished. Here's the relevant output:

15:15:53 60 of 60 PASS unique_int_tealium_audience_latest_unique_key .................... [PASS in 48.76s]
15:15:53
15:15:53 Running 2 on-run-end hooks
15:15:53 apply_meta_as_tags
15:15:53 1 of 2 START hook: dwh.on-run-end.0 ............................................ [RUN]
15:15:53 1 of 2 OK hook: dwh.on-run-end.0 ............................................... [OK in 0.00s]
15:18:54 2 of 2 START hook: elementary.on-run-end.0 ..................................... [RUN]
15:18:54 2 of 2 OK hook: elementary.on-run-end.0 ........................................ [OK in 0.00s]