GoogleCloudPlatform / GoogleCloudPlatform/professional-services-data-validator
RTRIM on CHAR datatype creates validation issues
- Dominant language
- Python
- Stars
- 524
- Forks
- 171
- Avg merge
- 5d 15h
- Merged PRs (30d)
- 4
Description
**Describe the bug**
AWS Redshift supports the CHAR datatype while BigQuery does not. The UNLOAD from AWS Redshift, exports the CHAR fields with right padded spaces. This results in the equivalent BigQuery String datatype having unwanted/unnecessary spaces padded on the right side. This would cause the inefficient queries and failures on BigQuery for downstream applications. Unfortunately, DVT does not catch this issue in BigQuery as it performs an RTRIM on both source and target. This means that the field matches even though it technically does not.
**What version of DVT are you using?**
Ex: 7.1.0
**What type of connections are you using for source and target?**
AWS Redshift for source, BigQuery for target
**Steps to reproduce the behavior**
Redshift Query:-
`SELECT 'abc'::CHAR(4096) = 'abc'::VARCHAR(4096) char_varchar_comparison, LENGTH('abc'::CHAR(4096)) char_length, LENGTH('abc'::VARCHAR(4096)) varchar_length;`
The above query on Redshift shows that the length of CHAR is the same as length of VARCHAR for the string "abc". On BigQuery "abc " is not equal to "abc".
**Expected behavior**
RTRIM should not be applied by default on CHAR datatype conversions. We want to make sure trailing spaces are caught in BigQuery. Basically RTRIM should only be applied on the engines which support CHAR and not on other engines.
**Screenshots**
If applicable, add screenshots to help explain your problem.
**How are you executing DVT?**
Cloud Run job
**Additional context**
DVT currently performs an RTRIM on both source and target.
Contributor guide
Assessment
This issue has not been assessed yet.