[Bug] `bigquery_use_batch_source_freshness` does not work

[jeremyyeo]

Is this a new bug?

• I believe this is a new bug
• I have searched the existing issues, and I could not find an existing issue for this bug

Which packages are affected?

• dbt-adapters
• dbt-tests-adapter
• dbt-athena
• dbt-athena-community
• dbt-bigquery
• dbt-postgres
• dbt-redshift
• dbt-snowflake
• dbt-spark

Current Behavior

Batch source freshness checking on BQ does not work as documented and errors.

Expected Behavior

No errors.

Steps To Reproduce

1. Create some sources:

create or replace table dbt_jyeo.customers as (
  select 1 id, 'alice' as first_name, current_timestamp() as updated_at
);

create or replace table dbt_jyeo.orders as (
  select 1 id, 100 as order_count, current_timestamp() as updated_at
);

2. Setup dbt project:

# dbt_project.yml
name: dbt_basic_5
profile: all
version: "1.0.0"

models:
  dbt_basic_5:
    +materialized: table

flags:
  bigquery_use_batch_source_freshness: True

# models/sources.yml
sources:
  - name: dbt_jyeo
    config:
      freshness:
        error_after: { count: 12, period: hour }
      loaded_at_field: updated_at
    tables:
      - name: customers
      - name: orders

3. Run source freshness checking:

$ dbt --debug source freshness

20:41:34  Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'start', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x107aa9110>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x107738590>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x107ab2290>]}
20:41:34  Running with dbt=1.11.7
20:41:34  running dbt with arguments {'empty': 'None', 'invocation_command': 'dbt --debug source freshness', 'use_colors': 'True', 'log_path': '/Users/jeremy/git/dbt-basic/logs', 'profiles_dir': '/Users/jeremy/.dbt', 'write_json': 'True', 'target_path': 'None', 'printer_width': '80', 'use_experimental_parser': 'False', 'cache_selected_only': 'False', 'debug': 'True', 'partial_parse': 'True', 'static_parser': 'True', 'log_format': 'default', 'warn_error_options': 'WarnErrorOptionsV2(error=[], warn=[], silence=[])', 'warn_error': 'None', 'log_cache_events': 'False', 'quiet': 'False', 'indirect_selection': 'eager', 'fail_fast': 'False', 'version_check': 'True', 'no_print': 'None', 'send_anonymous_usage_stats': 'True', 'introspect': 'True'}
20:41:43  Sending event: {'category': 'dbt', 'action': 'project_id', 'label': '44d14259-eb87-4f26-9ac7-a7ad64e5ebd6', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x12146fb50>]}
20:41:43  Sending event: {'category': 'dbt', 'action': 'adapter_info', 'label': '44d14259-eb87-4f26-9ac7-a7ad64e5ebd6', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x101e43210>]}
20:41:43  Registered adapter: bigquery=1.11.1
20:41:43  checksum: b80718358155fc17c3559352095564dd56ada1cc1a1c0ef2e62c857e71583775, vars: {}, profile: , target: , version: 1.11.7
20:41:43  Unable to do partial parsing because of a version mismatch
20:41:43  Sending event: {'category': 'dbt', 'action': 'partial_parser', 'label': '44d14259-eb87-4f26-9ac7-a7ad64e5ebd6', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x10677afd0>]}
20:41:44  Sending event: {'category': 'dbt', 'action': 'load_project', 'label': '44d14259-eb87-4f26-9ac7-a7ad64e5ebd6', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x12171a010>]}
20:41:44  Wrote artifact WritableManifest to /Users/jeremy/git/dbt-basic/target/manifest.json
20:41:44  Wrote artifact SemanticManifest to /Users/jeremy/git/dbt-basic/target/semantic_manifest.json
20:41:44  Sending event: {'category': 'dbt', 'action': 'resource_counts', 'label': '44d14259-eb87-4f26-9ac7-a7ad64e5ebd6', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x1260bd390>]}
20:41:44  Found 2 models, 2 sources, 539 macros
20:41:44  Sending event: {'category': 'dbt', 'action': 'runnable_timing', 'label': '44d14259-eb87-4f26-9ac7-a7ad64e5ebd6', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x1175b5c50>]}
20:41:44  
20:41:44  Concurrency: 4 threads (target='bq')
20:41:44  
20:41:44  Acquiring new bigquery connection 'master'
20:41:44  Acquiring new bigquery connection 'list_cse-sandbox-319708_dbt_jyeo'
20:41:44  Opening a new connection, currently in state init
20:41:44  Sending event: {'category': 'dbt', 'action': 'runnable_timing', 'label': '44d14259-eb87-4f26-9ac7-a7ad64e5ebd6', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x1257ac690>]}
20:41:44  Opening a new connection, currently in state init
20:41:44  Pulling freshness from warehouse metadata tables for 0 sources
20:41:44  BigQuery adapter: Unhandled error while running:
macro get_relation_last_modified
20:41:44  BigQuery adapter: Compilation Error
  list object has no element 0
  
  > in macro get_relation_last_modified (macros/adapters/metadata.sql)
  > called by <Unknown>
20:41:44  Metadata freshness could not be computed in batch: Compilation Error
  list object has no element 0
  
  > in macro get_relation_last_modified (macros/adapters/metadata.sql)
  > called by <Unknown>
20:41:44  Began running node source.dbt_basic_5.dbt_jyeo.customers
20:41:44  Began running node source.dbt_basic_5.dbt_jyeo.orders
20:41:44  1 of 2 START freshness of dbt_jyeo.customers ................................... [RUN]
20:41:44  2 of 2 START freshness of dbt_jyeo.orders ...................................... [RUN]
20:41:44  Re-using an available connection from the pool (formerly list_cse-sandbox-319708_dbt_jyeo, now source.dbt_basic_5.dbt_jyeo.customers)
20:41:44  Acquiring new bigquery connection 'source.dbt_basic_5.dbt_jyeo.orders'
20:41:44  Began compiling node source.dbt_basic_5.dbt_jyeo.customers
20:41:44  Began compiling node source.dbt_basic_5.dbt_jyeo.orders
20:41:44  Began executing node source.dbt_basic_5.dbt_jyeo.customers
20:41:44  Began executing node source.dbt_basic_5.dbt_jyeo.orders
20:41:44  On source.dbt_basic_5.dbt_jyeo.customers: /* {"app": "dbt", "dbt_version": "1.11.7", "profile_name": "all", "target_name": "bq", "node_id": "source.dbt_basic_5.dbt_jyeo.customers"} */
select
      max(updated_at) as max_loaded_at,
      current_timestamp() as snapshotted_at
    from `cse-sandbox-319708`.`dbt_jyeo`.`customers`
    
  
20:41:44  On source.dbt_basic_5.dbt_jyeo.orders: /* {"app": "dbt", "dbt_version": "1.11.7", "profile_name": "all", "target_name": "bq", "node_id": "source.dbt_basic_5.dbt_jyeo.orders"} */
select
      max(updated_at) as max_loaded_at,
      current_timestamp() as snapshotted_at
    from `cse-sandbox-319708`.`dbt_jyeo`.`orders`
    
  
20:41:44  Opening a new connection, currently in state closed
20:41:44  Opening a new connection, currently in state init
20:41:45  BigQuery adapter: https://console.cloud.google.com/bigquery?project=cse-sandbox-319708&j=bq:US:48964da4-cde3-4533-8c9a-40182a3c7c0c&page=queryresults
20:41:45  BigQuery adapter: https://console.cloud.google.com/bigquery?project=cse-sandbox-319708&j=bq:US:d1988066-72aa-462c-a4e7-bba0ff58ff36&page=queryresults
20:41:46  SQL status: OK in 0.401 seconds
20:41:46  1 of 2 PASS freshness of dbt_jyeo.customers .................................... [PASS in 1.97s]
20:41:46  Finished running node source.dbt_basic_5.dbt_jyeo.customers
20:41:47  SQL status: OK in 1.423 seconds
20:41:47  2 of 2 PASS freshness of dbt_jyeo.orders ....................................... [PASS in 2.96s]
20:41:47  Finished running node source.dbt_basic_5.dbt_jyeo.orders
20:41:47  Connection 'master' was properly closed.
20:41:47  Connection 'source.dbt_basic_5.dbt_jyeo.customers' was properly closed.
20:41:47  Connection 'source.dbt_basic_5.dbt_jyeo.orders' was properly closed.
20:41:47  
20:41:47  Finished running 2 sources in 0 hours 0 minutes and 3.66 seconds (3.66s).
20:41:47  Wrote artifact WritableManifest to /Users/jeremy/git/dbt-basic/target/manifest.json
20:41:47  Wrote artifact SemanticManifest to /Users/jeremy/git/dbt-basic/target/semantic_manifest.json
20:41:47  Wrote artifact FreshnessResult to /Users/jeremy/git/dbt-basic/target/sources.json
20:41:47  Done.
20:41:47  Resource report: {"command_name": "freshness", "command_success": true, "command_wall_clock_time": 13.1265545, "process_in_blocks": "0", "process_kernel_time": 0.550304, "process_mem_max_rss": "250527744", "process_out_blocks": "0", "process_user_time": 2.694914}
20:41:47  Command `dbt source freshness` succeeded at 13:41:47.844146 after 13.13 seconds
20:41:47  Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'end', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x107b275d0>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x107b27690>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x10031e650>]}
20:41:47  Flushing usage events
20:41:48  An error was encountered while trying to flush usage events

Relevant log output

Environment

- OS: macOS
- Python: 3.11.9
- dbt-adapters: 1.22.9
- dbt-bigquery: 1.11.7

Additional Context

I overrode the bigquery__get_relation_last_modified macro and printed relations at the start of the macro and it was empty at the time which is likely the cause for:

list object has no element 0