Epic: Cost Monitoring

[evansiroky]

User story / feature request

As a Cal-ITP cloud account owner,
I want to have detailed insights into cloud costs,
So that I can know what is causing our bill to get larger each month.

Acceptance Criteria

Perhaps building on top of #3541, have a way to more easily monitor the cost of various things in the pipeline at a finer-grained analysis than what is currently available in the cloud costs dashboard.

Notes

Based on this, hopefully we can reduce our bill or minimize it's growth.

[erikamov]

I just saw in BigQuery two recommendations that may help reducing some costs.

1 - Open BigQuery and click the Recommendation button (top right side):

2 - Click in View details:

3 - See two recommendations:

• Details of the first recommendation:

• Details of the second recommendation:

[ohrite]

Paired with @erikamov today to discuss how to control increasing costs over time. Erika will write up a brief summary for review with the group and @evansiroky.

[erikamov]

The brief summary is here.

[ohrite]

As discussed at planning, @erikamov will meet with @evansiroky and analysts to figure out the lowest-hanging fruit. After discussion, we want to discuss GTFS-RT validation outputs.

[erikamov]

Here a list of buckets and BQ tables with sizes.

We can use this list to define our next steps.

[erikamov]

To start we could change transform_warehouse to not run on Saturdays reducing few days of processing per month.

For the other DAGs that runs every day, is there a need to run on weekends (except the full refresh on Sunday)?

We could also reduce how many times sync_littlepay, unzip_and_validate_gtfs_schedule_hourly, parse_littlepay, parse_and_validate_rt_v2, and airflow_monitoring run per day, to reduce costs.

Change sync_ntd_data_api and sync_ntd_data_xlsx to run before create_external_tables, would not reduce costs, but would be an improvement to have the new scraped data available at the same day.

I don't want to tag anyone during holidays, so we can discuss when everybody is back, but if you see this comment please feel free to add your input.

Complete list and notes to discuss are available here.

[ohrite]

One way we can introspect on BigQuery compute usage is by adding labels to individual tables/views: https://docs.getdbt.com/reference/resource-configs/bigquery-configs

[erikamov]

We found a way to save on BigQuery costs, by changing the billing mode:
Reducing BigQuery physical storage cost with new billing model

[erikamov]

On Wed Jan 22nd I meet with @evansiroky and @ohrit to change the billing mode to Physical for these three datasets below.
I am monitoring and will post the results as soon as I have the final numbers.

<style type="text/css"></style>

Dataset Name	Forecast Logical Cost	Forecast Physical Cost	Forecast Cost Difference
mart_gtfs	$ 1,048.93	$ 394.69	$ 654.23
staging	$ 213.63	$ 82.16	$ 131.47
mart_ad_hoc	$ 39.11	$ 4.21	$ 34.90

[erikamov]

I created a new task to Cleanup Test Buckets

[erikamov]

Here our Cost Reduction Plan For approved recommendations, we would need to create tickets to track the work. :)

[erikamov]

@evansiroky @ohrite
The billing report already shows a reduction on daily BigQuery storage costs using the Physical Mode.

$20.77 on Jan 22nd with Logical Billing mode (Blue)
comparing to
$12.95 Physical mode (Red) + $0.07 remaining models on Logical mode from Jan 24th

[erikamov]

Since the Physical mode is proved to save costs, I finished changing the remaining datasets that showed us a little bit of savings (sandbox, audit, gtfs_rt_logs, views).

List of datasets using Physical Billing mode for reference:

• mart_gtfs
• staging
• mart_ad_hoc
• sandbox
• audit
• gtfs_rt_logs
• views

[evansiroky]

@evansiroky @ohrite The billing report already shows a reduction on daily BigQuery storage costs using the Physical Mode.

$20.77 on Jan 22nd with Logical Billing mode (Blue) comparing to $12.95 Physical mode (Red) + $0.07 remaining models on Logical mode from Jan 24th

@erikamov nice work on this easy win!

[evansiroky]

Here our Cost Reduction Plan For approved recommendations, we would need to create tickets to track the work. :)

I have reviewed this and think we're all on the right track with being better about labeling storage and dbt models. I'm looking forward to future work on this!