[ADR]: Dashboard ETL orchestration strategy

[widal001]

Description

In order to populate the delivery dashboard with metrics calculated based on data pulled from GitHub, we need a strategy to run the analytics pipeline created in the analytics/ sub-directory and post the results somewhere that the dashboard UI can access them.

The goal of this ADR is to document our current approach to extracting and transforming the GitHub data into the dashboard metrics, and to evaluate and recommend an orchestration tool to run this analytics pipeline.

Approvers

• @lucasmbrown-usds
• @acouch
• @coilysiren

Options

Note: Since we already have the analytics pipeline to extract and transform the data needed to calculate the metrics, the main options we're considering are for the orchestration layer (i.e. how we're triggering this analytics pipeline)

• GitHub actions
• AWS event bridge
• AWS step functions + event bridge
• Prefect
• Dagster

Decision Criteria

• The orchestration layer requires minimal upfront investment to set up
• The orchestration layer doesn't require a ton of overhead to maintain
• The orchestration layer can support future, more complicated needs such as backfills, dependencies between steps, and retries
• We can this tool to orchestrate other ETL/ELT pipelines in the project

Definition of Done

• [ ] The approvers for this decision have been identified (ideally before work on the ADR starts)
• [ ] The ADR is created and stored in documentation/decisions/adr with status "Accepted"
• [ ] The ADR has been reviewed and approved by the approvers listed above
• [ ] The ADR satisfies requirements that are outlined in the ADR template
• [ ] Any follow-up tickets have been created (if necessary)

[bretthrosenblatt]

Maybe consider AppFlow --> Glue? AppFlow has a GA4 connector, drops extracts into S3, Glue (or lambda/python) adds to Aurora staging tables.

[coilysiren]

I just copied the existing step functions setup for this. For the sprint reports ETL, at least.

Here => https://github.com/HHS/simpler-grants-gov/blob/e801e00ada24fa51d3bdc7b3a9ffb05114968a55/infra/analytics/service/sfn_sprint_reports.tf