[CT-752] dbt 1.1 timeout_seconds behaviour change compared to dbt 1.0

[jeremyyeo]

Describe the bug

I'm not 100% sure if this is a bug or perhaps it was a bug in 1.0 but is now fixed with 1.1 (release notes indicate some significant changes to timeouts)

The timeout_seconds config didn't use to apply in 1.0 but now is in 1.1.

Steps To Reproduce

1. In your profile, set a timeout_seconds config on your default target, e.g.

# ~/.dbt/profiles.yml
profile_name:
  dev:
    type: bigquery
    timeout_seconds: 10
  ...
  target: dev
...

2. Add this model to your project (this model will take about 60 seconds - this must exceed your timeout_seconds config above):

-- models/my_long_model.sql

{{ config(materialized = 'table') }}

{% call set_sql_header(config) -%}
    DECLARE retry_count INT64;
    DECLARE DELAY_TIME DATETIME;
    DECLARE WAIT STRING;
    SET retry_count = 1;

    WHILE retry_count <= 60 DO
      SET retry_count = retry_count + 1;
      SET WAIT = 'TRUE';
      SET DELAY_TIME = DATETIME_ADD(CURRENT_DATETIME, INTERVAL 1 SECOND);
      WHILE WAIT = 'TRUE' DO
        IF (DELAY_TIME < CURRENT_DATETIME) THEN
        SET WAIT = 'FALSE';
        END IF;
      END WHILE;
    END WHILE;
{%- endcall %}

select 1 as val

3. Run with dbt-bigquery==1.0:

$ dbt run      
23:24:01  Running with dbt=1.0.7
23:24:02  Found 1 model, 0 tests, 0 snapshots, 0 analyses, 188 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics
23:24:02  
23:24:09  Concurrency: 1 threads (target='dev')
23:24:09  
23:24:09  1 of 1 START table model dbt_jyeo.my_long_model................................. [RUN]
23:25:19  1 of 1 OK created table model dbt_jyeo.my_long_model............................ [SCRIPT (0 processed) in 69.82s]
23:25:19  
23:25:19  Finished running 1 table model in 76.72s.
23:25:19  
23:25:19  Completed successfully
23:25:19  
23:25:19  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

4. Now run with dbt-bigquery==1.1:

$ dbt run      
23:26:18  Running with dbt=1.1.0
23:26:19  Found 1 model, 0 tests, 0 snapshots, 0 analyses, 191 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics
23:26:19  
23:26:25  Concurrency: 1 threads (target='dev')
23:26:25  
23:26:25  1 of 1 START table model dbt_jyeo.my_long_model ................................ [RUN]
23:26:40  1 of 1 ERROR creating table model dbt_jyeo.my_long_model ....................... [ERROR in 14.47s]
23:26:40  
23:26:40  Finished running 1 table model in 20.71s.
23:26:40  
23:26:40  Completed with 1 error and 0 warnings:
23:26:40  
23:26:40  Runtime Error in model my_long_model (models/my_long_model.sql)
23:26:40    Operation did not complete within the designated timeout.
23:26:40  
23:26:40  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Expected behavior

I guess this is expected? Seems like the config previously did nothing but now it IS doing something.

Screenshots and log output

System information

The output of dbt --version:

installed version: 1.0.7
   latest version: 1.1.1

Your version of dbt is out of date! You can find instructions for upgrading here:
https://docs.getdbt.com/docs/installation

Plugins:
  - bigquery: 1.0.0 - Update available!
  Your version of dbt-bigquery is out of date! You can find instructions for upgrading here:
  https://docs.getdbt.com/dbt-cli/install/overview

Core:
  - installed: 1.1.0
  - latest:    1.1.1 - Update available!

  Your version of dbt-core is out of date!
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Plugins:
  - bigquery:  1.1.0 - Up to date!

The operating system you're using:

macOS

The output of python --version:

Python 3.8.6

Additional context

My current guess is somewhere around here:

https://github.com/dbt-labs/dbt-bigquery/blame/main/dbt/adapters/bigquery/connections.py#L585-L590

Introduced in 1.1.

---

The main reason I looked into this was because an enterprise client has a singular job/model that needs to run for quite a long time (more than 3600 - the max config allowed in dbt Cloud). This means that prior to 1.0, this config did nothing, allowing them to run a really long model but come 1.1, the config is now respected making it so that the model can no longer complete successfully in dbt Cloud.

I think the fix is simply to increase the dbt Cloud max allowable for this config.

[nishantnathhoney]

👍 to this issue. We also have the same problem. This is a major bug! DBT Cloud can't be targeting Enterprise customers & breaking these kind of features. Brings severe trust issues with the product.

[jtcohen6]

@nishantnathhoney I'm so sorry you ran into this. Let me do my best to explain what happened here.

Previously, dbt-bigquery supported a timeout configuration, but it was unused by the most common types of queries. Exceptions where it was actually used: timeout for service account impersonation; copy jobs; dbt seed CSV loading.

That was confusing and undesirable behavior for many many users. They set a config thinking it took effect, and it was silently unused. So it was intended as a feature in v1.1 that timeout + retries are now configurable, and actually apply to most queries, one which we noted in our changelog, release notes, and docs upgrade guide.

There are a few different timeout configs now (docs), offering more granular control, all of which apply only to the execution of one single query (=model). (dbt Cloud also has job-level timeout set at 24 hrs.)

That said, we made sure (#159) to preserve backwards compatibility wherever possible. The default job_execution_timeout_seconds in v1.1 is None, i.e. unused = zero timeout, if not explicitly set. For the most common query types, that means no timeouts, just like before. (Exceptions: For those few query types where timeout_seconds had actually been respected in v1.0, we preserved a default of 300 seconds when job_execution_timeout_seconds is not set.) At the risk of some inconsistency in the codebase, the goal of those changes was absolute backwards compatibility with the default behaviors in v1.0, so that users wouldn't run into this issue in v1.1. Unfortunately, that wasn't the experience for users during the upgrade, if they upgraded with an existing setting for job_execution_timeout_seconds, without the ability to increase or remove it as needed.

@jeremyyeo Since the time you opened this issue, I understand that, in dbt Cloud, we've increased the acceptable bound of this configuration to be >1 hour. Let's also discuss with the relevant folks internally if there's a mechanism to mark this config as "optional" in dbt Cloud.

[jeremyyeo]

For those reading this now, you should be able to set the parameter up 6 hours¹ (21600 seconds) in dbt Cloud:

Footnotes

1. BigQuery's Query/multi-statement query execution-time limit. ↩

[leahwicz]

@jeremyyeo was there more that you wanted to discuss here or did you need more info? I'm going to close this out but please re-open if you want to keep the conversation going

[ruben-cit]

For those reading this now, you should be able to set the parameter up 6 hours1 (21600 seconds) in dbt Cloud:

Footnotes

1. BigQuery's Query/multi-statement query execution-time limit. ↩

Where exactly in the dbt Cloud settings are you seeing "Job Execution/Job Retries" settings? I can only find a "Run Timeout" setting.

[ruben-cit]

Figured it out. It's in the connection settings for the environment.

[ManishJaiswal007]

Figured it out. It's in the connection settings for the environment.

Im stuck on same issue can you share screentshot, It will be helpful.

[nishantnathhoney]

Account Settings -> Projects -> Click on the connection in Overview section

[Jpdsbarbosa]

Para aqueles que estão lendo isso agora, você deve ser capaz de definir o parâmetro para 6 horas 1 (21600 segundos) no dbt Cloud:

notas de rodapé

1. Limite de tempo de execução da consulta/consulta de várias instruções do BigQuery .↩

Onde exatamente nas configurações do dbt Cloud você está vendo as configurações de "Job Execution/Job Retries"? Só consigo encontrar uma configuração de "Tempo limite de execução".

This is acceptable to me! Thanks!