Snowflake will not group by a common field

HilaryKoerner 0
Viewed 24

I have a dataset and I am grouping by several fields.

group by ta.vehicle_name, t.gl_account, t.batch_id, b.batch_type, t.transaction_comment;

I have two rows of data that share the above fields in common. The vehicle_name, gl_account, batch_id, batch_type, and transaction_comment are exactly the same. When I group by those five parameters to sum the amounts, I am still left with two rows. When I remove the transaction comment, the group by function works, and I am left with the sum amount for the two rows.

When I export the two rows to excel, I check to see if the transaction comments are duplicates and they are not. There are no leading or trailing spaces, and I did a format painter, but they are still not registering as the same even thought they appear identical.

I believe there is some underlying format in the data that got sent to Snowflake.

Any ideas?

I did this same group by with those five parameters and it was successful for millions of rows of data. This is the only one that I caught an issue with.

snowflake-cloud-data-platform excel
1 Answers

Likely there is one or more characters that are not readily visible. Try the following query to make them easier to review:

WITH t(comment) AS (
   SELECT * FROM VALUES
   ('this is a comment'),
   ('this is a comment ')
)
SELECT comment, hex_encode(comment) as hex
FROM t;
COMMENT HEX
this is a comment 74686973206973206120636F6D6D656E74
this is a comment  74686973206973206120636F6D6D656E74C2A0

In Excel you can use ENCODEURL to make those characters more obvious:

Comment ENCODEURL(Comment)
this is a comment  this%20is%20a%20comment%C2%A0
this is a comment this%20is%20a%20comment
Dave Welden0
Related