[BUG] Unit Testing with bigquery adapter "expected" values for timestamp returns null

[ajunghare]

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

Current Behavior

Given
Bigquery adapter,
source with numeric value for timestamp (values are in timestamp_seconds eg. '1111930985') lets say, column "TS",
model to be generated has Timestamp as datatype for column "TS"
Then
While writing unit test in yaml format, the values for TS are converted as safe_cast('''1111930985''' as TIMESTAMP)
this results in null if quotes are added in Test data,
for int value error generated as cannot convert INT64 to TIMESTAMP

Expected Behavior

For time stamp values with Bigquery
TIMESTAMP_SECONDS should be used for converting numeric data
and use PARSE_TIMESTAMP("%S",'') for String data

Steps To Reproduce

1. choose bigquery adapter(1.8.4)
2. create table TS_TEST in dataset having column TS with datatype as String
3. populate TS_TEST with epoch data for timestamps
4. create incremental Model TS_Test_new, this should read from TS_TEST & store TS in timestamp datatype
5. write unit test using Yaml format, provide input & expected values in terms of epoch data for TS
6. dbt test should fail, even if model query is correct. because while generating actual and expected results, parsing of timestamps fails.

Relevant log output

Case1: String value for TS
actual differs from expected:

@@, col1, col2, TS
---,1   ,2    , null

Case2: Numeric value for TS
Database Error
 Invalid cast from INT64 to TIMESTAMP at [60:128]

Environment

- OS:Ubuntu 20.04
- Python:3.12
- dbt-adapters: bigquery

Additional Context

No response