Ensure consistent time zones for TIME fields in row hash validations

[karcot1]

DVT assumes BQ time fields are in UTC, and only casts time on the Teradata end to GMT to account for this offset. With our customer, we have found this is not always the case. For example when data is loaded to BQ from external source systems, we are seeing data in BQ with EST time zones.

As a result, casting to GMT only on the TD side hashes creates instances where two identical time fields fail a row hash and two very different time values can potentially succeed.

This also creates an inconsistency between the row hash and compare fields validation, as comp fields will pass validation without modifying the timezones.

Is there any way to ensure the casting to a timezone is consistent between both TD and BQ, to avoid false positive/negative validations in the row hash?

Thanks very much!

[sundar-mudupalli-work]

Kash,

You are correct. BQ refers to default timezone - which the documentation says is normally UTC. Customers can set the default timezone for their project/organization to be something different. The documentation says we can set the timezone of a query/connection to a specific value without providing a straightforward code sample. Investigating.

Sundar Mudupalli

[nj1973]

We had this previously for BigQuery timestamps: https://github.com/GoogleCloudPlatform/professional-services-data-validator/pull/1174/files

Strange that the fix did not work for time data but might be worth investigating.