[Billing] Goal Q2 2023: Cloud cost billing pass through process

[pnasrat]

Context

Based on Q4 2022 Goal #1853 and Quarterly Planning

Rationale: For a lot of clients, we ‘pass through’ cloud billing. We pay the cloud vendors, and then invoice them. This process is currently not well defined, and tgis is a very important part of our long term sustainability.

Definition of done:

• We have a single source of truth of all contracts that we are passing cloud costs through for. This should also link to the specific hub that is being run for those contracts.
• We have code that generates how much each of those contracts should be charged. This involved:
  • Automatically getting this number for GCP in cases with dedicated clusters
  • Automatically getting this number for GCP in cases with shared clusters
  • Instructions on manually getting this number for AWS projects
  • We do not have any Azure clusters we have to pass through for now, so we can skip that.
  • Manual extra service charge as a % of our fixed shared costs (such as pagerduty, uptime alerts, etc)
  • Stretch goal: Automatically getting centralized or shared overhead/infrastructure costs eg uptime alerts, pagerduty or other and splitting (maybe ratioed) between clusters
• There is a monthly process that involves at least two people (redundancy?) for generating invoices for the previous month, and sending it to CS&S
• There is a monthly process for checking-in to see if any previous invoices have not been paid yet.
• There is a ‘catch-up’ process that lets us clear all current invoicing backlogs
• There is documentation on what exactly ‘generating an invoice’ means

Proposal

Cost Reconcilliation / Backlog up to Q1 2023

• 2i2c-org/meta#350
  • Define and document catch-up process to clear any invoice backlog for cloud costs til Q1 2023
• Document historic billing model
  • Define Monthly process to Calculate cloud costs for dedicated clusters on Google Cloud team-compass#498

Define manual billing process for Q2 2023 and beyond

• Create a process for tracking and invoicing cloud infrastructure costs team-compass#519
• Calculate cloud costs for shared hubs on a cluster on Google Cloud team-compass#499
• Create a monthly invoicing process for personnel and cloud costs team-compass#355
• Explore a more sustainable invoicing cadence with CS&S team-compass#546
• 2i2c-org/meta#529
  • [billing] Single source of truth for contracts and method to link hubs to contracts #2456
  • [billing] Manually collect billing costs on a monthly basis for *all* our projects #2465
• Define manual monthly process for checking on outstanding invoices to create accounts receivable
• Documentation on what generating an invoice means

Tooling for Cost Generation for manual process for Q2 2023 and beyond

• Code that generates cost for clusters
  • [Billing] Automatically figure out how much a dedicated GCP project spends in a month #1921
  • Automatically figure out how much a separate AWS project spends in a month #1922
  • [Billing] Automatically figure out how much a shared GCP cluster spends in a month (and map those costs to inner hubs) #1923
  • Terraform to manage GCP based billing alerts #2263

Issue Backlog / Related Issues

• Automate setting up bigquery billing export for new billing accounts #2442
• Produce an 'accounts recievables' google sheets upto March 2023 for cloud costs #2467
• Explore using billing alerting infrastructure directly from the cloud provider features#13
• Recover our un-billed cloud costs from FY 2022 team-compass#555
• Formula for calculating hub-specific cloud costs on a shared cluster #730
• Record communities we serve even if we do not recover costs directly from them team-compass#694
• Ask Code for Science and Society to have a dedicated category and invoice type for our re-billable cloud costs team-compass#663

Updates and actions

2023-04-07: @pnasrat Adding issue backlog to capture historical state
2023-04-06: @pnasrat rework proposal into 3 strands of work

[pnasrat]

Current plan is to use central grafana to

• Add GCP Billing BigQuery datasource to Grafana
  • Configure service account permitting Big Query viewing and annotate pod
  • Create ad-hoc queries via explore using multi data source to test out
  • See if through transformations can create monthly invoice data
• Create permanent GCP shared cluster billing dashboard (inspired by opencost UI)

[pnasrat]

Manually created service account billing-bigquery-reader@two-eye-two-see.iam.gserviceaccount.com

TODO: Figure if we need to generate support serviceaccounts, currently this is for a single cluster so setting up manually first before automating.

[pnasrat]

Note I misread the permissions for datasources are only in Grafana Enterprise and Cloud not in the open source version. Given non-admin users are currently Viewer we can limit billing dashboard (and Explore which is currently) to Admin only via dashboard permissions https://grafana.com/docs/grafana/latest/administration/roles-and-permissions/#dashboard-permissions

[pnasrat]

Grafana query works eg

SELECT
  invoice.month as month,
  project.id as project,
  (
    SUM(CAST(cost AS NUMERIC)) + SUM(
      IFNULL(
        (
          SELECT
            SUM(CAST(c.amount AS NUMERIC))
          FROM
            UNNEST(credits) AS c
        ),
        0
      )
    )
  ) AS total_with_credits
FROM
  `two-eye-two-see.cloud_costs.gcp_billing_export_resource_v1_REPLACE_ME`
WHERE
  invoice.month >= "202301"
  AND invoice.month <= "202303"
  AND project.id = "two-eye-two-see"
GROUP BY
  1,
  2
ORDER BY
  invoice.month ASC;

[colliand]

Follow up memorializing an exchange in the 2023-04-04 Product and Engineering meeting.

Over recent months, @jmunroe has been gathering cloud billing reports and sharing those with CS&S for invoicing. As our billing systems advance, we should move cloud billing under @colliand and partnerships giving James more bandwidth to focus on product and community. James and Jim will work with Engineering and 2i2c to strengthen our cloud billing back office.

[choldgraf]

More AirTable automation for our account transactions

I've been iterating a bit more with Joe, and now have an AirTable that is still manually updated but with much fewer steps and complexity than before. Here's a brief summary:

• Overview
  • CS&S will run a job daily that gives us a "raw dump" of transactions from their Xero financial accounting platform.
  • These will be put into this Google Sheet (which may not be accessible to some because it is in a CS&S google drive folder)
  • This can be "direct imported" into AirTable via their Google Sheet integration.
  • I've updated our Accounting Transactions sheet with the proper field names at this AirTable URL.
• Process for updating the data
  • Delete all records on the page.
  • Go to Accounting Transactions -> Import Data -> Google Sheets -> Google sheets account
    
  • You'll see a list of Google Sheets in our account. Import the one titled 2i2c FYE23 Account Transactions - Auto Generated.
  • Check the "Skip first row" option during the import, so that we don't import the section title names.
  • That should be it
• Visualizing the data
  • I've also started working on some dashboards to visualize this data at this Interface link.

Will provide more updates as I get them from Joe, but this is a good step towards a (mostly) automated source of truth for our accounting data, that is updated in real-time.

[pnasrat]

@choldgraf great. Could you possibly add the table schema - you should be able to do this with your airtable api key and the metadata api and include it here while I don't have airtable access.

Completely untested but should be doable - eg

https://pyairtable.readthedocs.io/en/latest/metadata.html and based on the kpis repo pyairtable use

import os
import json
from pyairtable import Base, Table


if "AIRTABLE_API_KEY" not in os.environ:
    raise ValueError("Environment variable AIRTABLE_API_KEY not defined")
api_key = os.environ["AIRTABLE_API_KEY"]  

# Base ID for `Accounting`: https://airtable.com/appbjBTRIbgRiElkr
base_id = "appbjBTRIbgRiElkr"
table = Table(api_key, base_id, 'TABLE_NAME')

json.dumps(metadata.get_table_schema(table))
 

[pnasrat]

Note metadata API need personal access token https://airtable.com/developers/web/guides/personal-access-tokens not the legacy api key https://airtable.com/api/meta

[choldgraf]

OK I got an updated table from Joe after he incorporated some of my feedback, so I've updated the description here for how to access and we can add it to the team compass once we know that the process is settled for now.

Also note that I gave @pnasrat the AirTable password and I believe she now has access to the Schema