[Bug]: Fails Running on BigQuery with PARSE_JSON Error Message

[codigo-ergo-sum]

Overview

I'm trying to use dbt-artifacts on bigquery. Installed from scratch, running dbt run --select dbt_artifacts , I'm getting this error message:

22:56:28  on-run-end failed, error:
22:56:28   Invalid input: Input number: 1687473876.057356 cannot round-trip through string representation; error in PARSE_JSON expression

How to reproduce

1. Add dbt-artifacts to the packages.yml file
2. Run dbt deps
3. Run dbt run --select dbt_artifacts

Expected behaviour

dbt-artifacts would run successfully and upload results :)

Screenshots

(I deleted the URL after "Bigquery Adapter" from the screenshot)

Environment

Results of running dbt --version:

Core:
  - installed: 1.5.1
  - latest:    1.5.2 - 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.5.1 - Update available!

  At least one plugin is out of date or incompatible with dbt-core.
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Please paste the contents of your packages.yml file here:

packages:
  - package: dbt-labs/dbt_utils
    version: 1.0.0

  - package: tnightengale/dbt_meta_testing
    version: 0.3.6

  - package: fivetran/salesforce_formula_utils
    version: [">=0.8.0", "<0.9.0"]

  - package: brooklyn-data/dbt_artifacts
    version: 2.4.2

[codigo-ergo-sum]

Also, looking at one of the insert statements that failed on bigquery, this is at least one of the problem fields that seems to be causing an issue in the PARSE_JSON call: \"created_at\": 1687473876.0002491,

[ERenninger]

My company is getting this same error, also in version 2.4.2 of the package.

It doesn't fail consistently, sometimes it'll error and then succeed in the very next run, but it does consistently error at least once a day for our 4 daily builds.

[codigo-ergo-sum]

It looks like there was an attempt previously to fix this issue here: #345

@jared-rimmer is it possible that there are still some corner cases around the fix you did that is causing this issue?

[jens-koster]

bigquery requires floats to be convertable string->float-> string and produce the same string at the end. That's the "roundtrip". Randomly the created_at timestamp will trigger this error.
There's a parameter for parse_json that allows rounding errors and for the purpose of dbt_artifacts it should be perfectly ok to use it.
bigquery parse_json documentation

select parse_json('{ \"created_at\": 1689080562.991696"}');  --> Invalid input: Input number: 1689080562.991696 cannot round-trip through string representation; error in PARSE_JSON expression
select parse_json('{ \"created_at\": 1689080562.991696}', wide_number_mode=>'round') --> OK

edit: This seems to have been fixed in some of the bigquery__get_<entity>_dml_sql macros, but not all.
I have added the , wide_number_mode=>'round' in all places and so far the error has not shown itself again.
example:
parse_json('{{ tojson(test) | replace("\\", "\\\\") | replace("'","\\'") | replace('"', '\\"') }}', wide_number_mode=>'round') {# all_fields #}

aaaand final edit, there's a PR on the way to fix it: #356