[Feature] Support dbt show with BigQuery's pipe syntax

[Dom-M-C]

Is this a new bug in dbt-bigquery?

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

Current Behavior

dbt is incorrectly compiling queries using pipe syntax with dbt show.

Given a model using pipe syntax, e.g.

from (select 1)

The code is compiled to

  from (select 1)

  limit 500

The behaviour works correctly if a trailing pipe is added, i.e.

from (select 1) |>

dbt show -s pipe_bug
09:44:41  Running with dbt=1.8.7
09:44:41  Registered adapter: bigquery=1.8.3
09:44:42  Found 21 models, 126 data tests, 1 seed, 3 analyses, 27 sources, 603 macros
09:44:42  
09:44:42  Concurrency: 4 threads (target='dev')
09:44:42  
09:44:43  Previewing node 'pipe_bug':
| f0_ |
| --- |
|   1 |

Expected Behavior

dbt show should work without adding a trailing pipe to models

Steps To Reproduce

Given a model using pipe syntax, e.g.

from (select 1)

When running dbt show

dbt show

Then an error is returned

dbt show -s pipe_bug
09:41:35  Running with dbt=1.8.7
09:41:35  Registered adapter: bigquery=1.8.3
09:41:35  Found 21 models, 126 data tests, 1 seed, 3 analyses, 27 sources, 603 macros
09:41:35  
09:41:36  Concurrency: 4 threads (target='dev')
09:41:36  
09:41:37  BigQuery adapter: https://console.cloud.google.com/bigquery?project=temp-data-sandbox&j=bq:EU:7fb0610a-7f18-45d4-9d7b-eeb1cd68789a&page=queryresults
09:41:37  Encountered an error:
Runtime Error
  Database Error in analysis pipe_bug (src/datawarehouse_dbt/analyses/pipe_bug.sql)
    Syntax error: LIMIT not supported after FROM query; Consider using pipe operator `|> LIMIT` or parentheses around the FROM query at [6:3]

Relevant log output

No response

Environment

- OS: macOS Sequoia 15.1.1
- Python: 3.11.6
- dbt-core: 1.8.9
- dbt-bigquery: 1.8.3

Additional Context

No response