search

2i2c-org / infrastructure

Infrastructure for configuring and deploying our community JupyterHubs.
https://infrastructure.2i2c.org
BSD 3-Clause "New" or "Revised" License
103 stars 63 forks source link

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

Closed pnasrat closed 9 months ago

pnasrat commented 1 year ago

Context

Based on Q4 2022 Goal https://github.com/2i2c-org/infrastructure/issues/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:

Proposal

Cost Reconcilliation / Backlog up to Q1 2023

Define manual billing process for Q2 2023 and beyond

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

Issue Backlog / Related Issues

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 commented 1 year ago

Current plan is to use central grafana to

pnasrat commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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:

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 commented 1 year ago

@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 commented 1 year ago

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 commented 1 year ago

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