Redshift throws Value too long for character type character varying(100) even when values are within the range
Redshift can store multi byte strings into varchar field. But if you define your field as varchar(100) it does not mean 100 characters. Instead it means 100 bytes. So if all the characters in the string are two byte characters, then the field can store at max 50 characters.

From the documentation,


  Use a VARCHAR or CHARACTER VARYING column to store variable-length strings with a fixed limit. These strings are not padded with blanks, so a VARCHAR(120) column consists of a maximum of 120 single-byte characters, 60 two-byte characters, 40 three-byte characters, or 30 four-byte characters.


The problem is LENGTH function only returns the number of characters and not number of bytes excluding the trailing blanks. So getting the length on a multi byte character only returns 1. This is documented here.

The alternative OCTET_LENGTH can return the number of bytes instead of number of characters. 

Running OCTET_LENGTH revealed trouble maker and it is fixed now.