[CT-2848] [Bug] `dbt show` adding decimal places to non-decimal values

[dave-connors-3]

Is this a regression in a recent version of dbt-core?

• I believe this is a regression in dbt-core functionality
• I have searched the existing issues, and I could not find an existing issue for this regression

Current Behavior

dbt show is adding decimal places to numeric/integer values incorrectly. This is causing problems for users in the dbt Cloud IDE, as the IDE uses that command to render the preview tab.

Expected/Previous Behavior

Previously (we think!) this was not an issue, and decimal places were properly handled by dbt show

Steps To Reproduce

1. use dbt-snowflake>=1.5.0
2. create a model in your project:

# in my_model.sql
select
  cast(1 as numeric) as _numeric,
  cast(1 as integer) as _integer,
  cast(1 as decimal) as _decimal

3. run dbt show -s my_model --output JSON
4. get this!

❯ dbt show -s my_model --output json
20:33:15  Running with dbt=1.5.3
20:33:16  Registered adapter: snowflake=1.5.2
20:33:16  Unable to do partial parsing because of a version mismatch
20:33:17  Found 3 models, 4 tests, 0 snapshots, 0 analyses, 436 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics, 0 groups
20:33:17  
20:33:18  Concurrency: 8 threads (target='dev')
20:33:18  
20:33:19  {
  "node": "my_model",
  "show": [
    {
      "_NUMERIC": 1.0,
      "_INTEGER": 1.0,
      "_DECIMAL": 1.0
    }
  ]
}

Relevant log output

No response

Environment

- OS: mac
- Python: Python 3.9.16
- dbt (working version): unknown
- dbt (regression version): 1.5.3

Which database adapter are you using with dbt?

snowflake

Additional Context

No response

[dave-connors-3]

did some more local testing on other versions of 1.5 and observed the same behavior - -this may not be a regression, but just a regular bug!

[jtcohen6]

Copying from #8167 (great minds think alike @dave-connors-3):

What's going on?

We're just using the agate library's type system and JSON serialization:

dbt-core/core/dbt/task/show.py

Lines 78 to 83 in eeb0570

	table = result.agate_table
	# Hack to get Agate table output as string
	output = io.StringIO()
	if self.args.output == "json":
	table.to_json(path=output)

This is being returned and saved as a decimal.Decimal type, rather than a Python int — and the decimal.Decimal type serializes to JSON number (1.0) instead of JSON integer (1)

ipdb> table
<agate.table.Table object at 0x1078d1360>
ipdb> [list(row) for row in table]
[['0005', 'false', Decimal('10'), False, '01T00000aabbccdd'], ['0006', 'true', Decimal('20'), True, '01T00000aabbccde']]
ipdb> table.print_json()
[{"stringnum": "0005", "stringbool": "false", "intfield": 10.0, "boolfield": false, "dtstring": "01T00000aabbccdd"}, {"stringnum": "0006", "stringbool": "true", "intfield": 20.0, "boolfield": true, "dtstring": "01T00000aabbccde"}]
ipdb> type(table[0][2])
<class 'decimal.Decimal'>

[graciegoheen]

Thanks to 2 great minds for opening this up!

It does look like this was an issue with prior versions of the IDE (before the dbt show command existed), so it's not a regression.

That being said, we definitely want to enable users to be able to spot check their data using dbt show / preview in the IDE.

[booster-analytics-team]

Experiencing the same issue here, running dbt version 1.5.0. Following for updates.