SPIKE: Add test for Service Ping generated queries for Snowflake
Summary
Find a way to add a check/test for before and after database metrics changes of generated queries for Snowflake.
This allows us to tightly control and review changes to SQL metric queries and notify Data team if a query is changed to properly assess impact to the data warehouse (see #347542).
Proposal
Some proposals here, more ideas could come up:
Use a fixture file with the queries generated and add a test against the generated queries
This was attempted in !82757 (merged), but had to be reverted due to:
- Different behavior for developers with EE Free and EE with license (this could be fixed)
- Failing nightly pipeline for PostgreSQL 11. A query differs between different PostgreSQL versions, so having this check as a spec might not actually be a viable solution:
-"projects_with_enabled_alert_integrations_histogram"=>"WITH \"count_cte\" AS (SELECT COUNT(*) AS count_grouped FROM \"alert_management_http_integrations\" WHERE \"alert_management_http_integrations\".\"active\" = TRUE GROUP BY \"alert_management_http_integrations\".\"project_id\") SELECT WIDTH_BUCKET(\"count_cte\".\"count_grouped\", 1, 100, 99) AS buckets, \"count_cte\".\"count\" FROM \"count_cte\" GROUP BY buckets ORDER BY buckets", "projects_with_error_tracking_enabled"=>"SELECT COUNT(DISTINCT \"projects\".\"creator_id\") FROM \"projects\" INNER JOIN \"project_error_tracking_settings\" ON \"project_error_tracking_settings\".\"project_id\" = \"projects\".\"id\" WHERE \"project_error_tracking_settings\".\"enabled\" = TRUE",
+"projects_with_enabled_alert_integrations_histogram"=>"WITH \"count_cte\" AS MATERIALIZED (SELECT COUNT(*) AS count_grouped FROM \"alert_management_http_integrations\" WHERE \"alert_management_http_integrations\".\"active\" = TRUE GROUP BY \"alert_management_http_integrations\".\"project_id\") SELECT WIDTH_BUCKET(\"count_cte\".\"count_grouped\", 1, 100, 99) AS buckets, \"count_cte\".\"count\" FROM \"count_cte\" GROUP BY buckets ORDER BY buckets", "projects_with_error_tracking_enabled"=>"SELECT COUNT(DISTINCT \"projects\".\"creator_id\") FROM \"projects\" INNER JOIN \"project_error_tracking_settings\" ON \"project_error_tracking_settings\".\"project_id\" = \"projects\".\"id\" WHERE \"project_error_tracking_settings\".\"enabled\" = TRUE",
We would have to play whack-a-mole adding problematic metrics to the ignorelist and there's no telling when a new database metric would cause similar issues. Upgrading PG might also cause this test to fail in the future.
- Using API. Maybe we can have a CI job that can run a check only if we have changed for database metrics and see a diff before after.
There are a bunch of problems to resolve there:
- Queries will have different timestamps between runs, because they are usually constructed using Active Record. In RSpec freezing the timestamps was easy using
timecop
, but outside of Rails this might be non-trivial. Stubbing timestamps in the actual class / API endpoint might be a solution, but we need to ask Data team if they the rely on those timestamps in any way. - The diff must be made between the SQL queries in MR branch and the target branch. This might mean the SQL queries results have to be persisted somewhere (perhaps as a job artifact).
Scenarios
-
Change the scope that is used for a metric.
-
Rename of a column example
-
Comparison of generated report against the API response