GoogleCloudPlatform / GoogleCloudPlatform/professional-services-data-validator
column validation: --sum of time zoned timestamp on BigQuery has side effect on other engines
- Dominant language
- Python
- Stars
- 524
- Forks
- 171
- Avg merge
- 5d 15h
- Merged PRs (30d)
- 4
Description
This problem affects at least SQL Server and Teradata.
You can use the standard dvt_core_types integration test table to reproduce.
When comparing `--sum=col_tstz` on SQL Server and BigQuery then we get an epoch seconds mismatch:
- SQL Server: 259206
- BigQuery: 280806
-
However when comparing SQL Server to itself then the value is 280806, which matches BigQuery.
The problem stems from these lines in `config_manager.py`:
```
elif column_type == "timestamp" or column_type == "!timestamp":
if (
self.source_client.name == "bigquery"
or self.target_client.name == "bigquery"
):
calc_func = "cast"
cast_type = "timestamp"
```
They are saying that if the source or target is BigQuery then cast both source and target to timestamp before doing the epoch seconds expression. The cast to timestamp is cropping the time zone in SQL Server and Teradata.
First thought was to change the SQL Server cast to first convert to UTC but I also think it is wrong that a BigQuery requirement changes what we do on the other engine. If BigQuery needs a pre-cast to TIMESTAMP then it probably shouldn't be catered for in both source and target queries. Or maybe both thoughts are true.
When resolved we should add col_tstz to sum validation in the `test_column_validation_core_types_to_bigquery` integration test for SQL Server and Teradata.
Contributor guide
Assessment
This issue has not been assessed yet.