GoogleCloudPlatform / professional-services-data-validator

Utility to compare data between homogeneous or heterogeneous environments to ensure source and target tables match
Apache License 2.0
399 stars 114 forks source link

fix: Ensure BigQuery queries are executed in UTC #1174

Closed nj1973 closed 3 months ago

nj1973 commented 3 months ago

This PR ensures BigQuery queries are in UTC which overrides any local project settings.

I don't think we can easily have automated tests for this, unless we create a new internal project and set it to non-UTC.

Tested manually with:

ALTER PROJECT `my-project-01`
SET OPTIONS (`region-eu.default_time_zone` = 'Europe/Helsinki');

Test using our standard table then failed:

data-validation -v validate row -sc=snowflake -tc=bq -tbls=pso_data_validator.dvt_core_types --primary-keys=id --concat='id,col_datetime'

source_agg_value     │ target_agg_value
═════════════════════╪═════════════════════
11970-01-01 00:00:01 │ 11969-12-31 22:00:01
─────────────────────┼─────────────────────
21970-01-02 00:00:02 │ 21970-01-01 22:00:02
─────────────────────┼─────────────────────
31970-01-03 00:00:03 │ 31970-01-02 22:00:03

Switched to this branch and the command above succeeded.

source_agg_value     │ target_agg_value
═════════════════════╪═════════════════════
11970-01-01 00:00:01 │ 11970-01-01 00:00:01
─────────────────────┼─────────────────────
21970-01-02 00:00:02 │ 21970-01-02 00:00:02
─────────────────────┼─────────────────────
31970-01-03 00:00:03 │ 31970-01-03 00:00:03
nj1973 commented 3 months ago

/gcbrun