[CT-1535] Investigate performance of functional tests

[gshank]

It takes a long time to run our functional/integration tests now. Investigate to identify if there are particular long-running tests which are slowing things down, or if it's the nature of our testing framework. Right now there is a large delay in getting the results of our functional test runs which delays the processing of pull requests.

Identify steps that could be taken to improve the test speed. One option is to split up the tests into subsets so that they can be run in parallel. We could possibly limit the testing of multiple versions of Python to a once-daily build.

[gshank]

I am seeing a bunch of my functional test runs canceled when they reach 45 minutes.

[nitinbhojwani]

Another area of performance improvement:

Dbt invokes a single query per test e.g. if a null check is configured for 3 fields in a model, it simply invokes 3 queries against the target data store.

I think it's suboptimal and there should be a way to group them in a single query.

[jtcohen6]

Relabeling this from tests to ci/cd, since this is about our integration/functional tests (pytest) that run in CI for dbt-core, rather than the built-in feature for data testing (dbt test).

@nitinbhojwani For what you're asking, you might be interested in:

• This older issue, thinking through how multiple tests could be combined into one query: [Feature] Run all tests in one query #4346
• Work that Sung et al have been thinking through around redefining tests as constraints, on data platforms that support it ([CT-1355] Add column type constraints as dbt native configs #6076 + related issues)

[max-sixty]

At risk of commenting from the peanut gallery, a couple of things that might be helpful:

• pytest has a --durations option which will show the longest running tests
• There's also a -n pytest option for running in parallel (although GH runners are fairly small)
• Something to consider is running some set of slow tests either after merging or when specifically requested with a label. That way, they don't get in the way of most small PRs. If a break does get mistakenly get through, it's always easy to revert. Here's how we do that in PRQL: https://github.com/prql/prql/blob/0.2.11/.github/workflows/pull-request.yaml#L58-L69

[jtcohen6]

Two next steps here:

• Parallelize our tests: [CT-1579] Split integration tests into groups  #6354
• Sanity check on parsing performance: [CT-2141] [Spike] Sanity check on full parse performance #7005. Could also do a proof of concept for providing & parsing all project source ""files"" out of memory—rather than writing & reading from disk, as our testing framework currently does. Related to some of the initial experimentation in CT 1808 diff based partial parsing #6873.

Going to close this issue for now, given there are a few threads to pull on being tracked in other places.