# THIS util records all post-challenge configurations for each state to avoid confusion in the future.
from config import Config
from util.fcc import FCCUtil
from util.format import FormatUtil
[docs]
class BeadUtil:
"""
Util class for Bead post-challenge configurations.
"""
DEFAULT_1DP_1RP = "1DP-1RP" # "AutoGenerate: 1DP:1RP relationship"
PROVIDER_DP_RP_RELATIONSHIP = (
"provided-deployment_reporting.csv" # "State has provided
)
DEFAULT_1PROVIDER_1SA = "1Provider-1SA" # "AutoGenerate: Group all projects by subgrantee uei (1Provider:1SubgrantAgreement)"
DEFAULT_1DP_1SA = "1DP-1SA" # "AutoGenerate: Create one unique SA for each DP"
PROVIDER_SA_RELATIONSHIP = "provided-subgrant_agreements.csv" # "State has provided supp_subgrant_agreements.csv"
PROVIDER_SA_RP_RELATIONSHIP = "provided-subgrant_agreements_rp.csv" # "State has provided RP-based supp_subgrant_agreements.csv"
fp_profiles = {
"MP": {
"gdrive_folder_id": "188bp5cm_6rKhtcTyuRbwGwTPfvF3E-ZY", # testing the controller project delete.
"version_date": "20260928",
"dp_rp_relationship": DEFAULT_1DP_1RP,
"sa_relationship": DEFAULT_1PROVIDER_1SA,
"sga_outside_platform": False,
# MP's 20260928 fp_cai.csv is delivered header-only: no CAI receives
# BEAD funding. stage0_prep downgrades its "CAI source is empty" dbt
# test to a warning for this state; a MISSING fp_cai.csv is treated the
# same way automatically, without this flag.
"allow_empty_cai": True,
},
"AR": {
"gdrive_folder_id": "1Daxok5siXEMJasnlQdVIQDWj69B35lOy",
"version_date": "20260827",
"dp_rp_relationship": PROVIDER_DP_RP_RELATIONSHIP,
"sa_relationship": PROVIDER_SA_RELATIONSHIP,
# DATASME-2051: e-signature flow; the portal owns submittedAt/status
# (DATASME-1412).
"sga_outside_platform": False,
},
"AZ": {
# DATASME-2112: WeLink's SGA executed; KPMG's final Appendix C trims 55 BSLs
# from CM61-BEAD-AZ-36-531 (7,820 -> 7,765) and 41 from -690 (2,658 -> 2,617).
# Deletion-only against the 20260804 package — no locations added and no
# technology/classification/speed changes on survivors — so only
# fp_locations.csv and supp_reporting_project_locations.csv differ (96 rows
# dropped from each); the other files are md5-identical copies. Versioned
# 20260923, the date KPMG delivered the final BSL list.
# Previous package: 1UCLAx3t_sv1ySuao5mZoc2EWwBzShdNA @ 20260804 (the
# DATASME-1866/1870 corrected stage9 re-export, single folder for all envs).
"gdrive_folder_id": "1krHNEFqVBqeRAEYeOgEadyUtVrObrzfm",
"version_date": "20260923",
"dp_rp_relationship": PROVIDER_DP_RP_RELATIONSHIP,
"sa_relationship": PROVIDER_SA_RELATIONSHIP,
# DATASME-2051: SGAs are signed/executed outside the platform, so each
# newly ingested SGA is inserted already submitted and unlocked.
"sga_outside_platform": True,
"excluded_applicant_int_ids": [
# duplicated Wecom Fiber applicants
22,
120,
],
},
"LA": {
"gdrive_folder_id": "122M5NR2maWwsT6nFsWboueHJwL9ztlCk",
"version_date": "20251118",
"dp_rp_relationship": DEFAULT_1DP_1RP,
"sa_relationship": DEFAULT_1PROVIDER_1SA,
# DATASME-2051: SGAs are signed/executed outside the platform, so each
# newly ingested SGA is inserted already submitted and unlocked.
"sga_outside_platform": True,
},
"MN": {
"gdrive_folder_id": "1vfd3ou0CpZ-QAi3JRsKzQo8LHrCTaD4H",
"version_date": "20251222",
"dp_rp_relationship": DEFAULT_1DP_1RP,
"sa_relationship": DEFAULT_1DP_1SA,
# DATASME-2051: e-signature flow; the portal owns submittedAt/status
# (DATASME-1412).
"sga_outside_platform": False,
},
"TX": {
# DATASME-1979: post-withdrawal/DQ reassignment FP set with fabric v9 RC3
# applied. Versioned 20260804 (the submission date, per the DRI); the files
# themselves are the 2026-09-08 revision of that package. 134 DPs / 17
# subgrantees; 228,254 BEAD + 20,354 No-BEAD locations (union 248,608,
# unchanged from the approved FP). The folder also holds the superseded
# August files under UPPERCASE names -- the pipeline reads only the
# lowercase csvs.
# Previous package: 1uqMdOcTi9ZwnOvInYSkYNerhHxwS0Itc @ 20251118.
"gdrive_folder_id": "1D2wSRC5JKfcLUUhUkmE135Vg0cjoou7I",
"version_date": "20261001",
"dp_rp_relationship": DEFAULT_1DP_1RP,
"sa_relationship": DEFAULT_1PROVIDER_1SA,
# DATASME-2051: e-signature flow; the portal owns submittedAt/status
# (DATASME-1412).
"sga_outside_platform": False,
},
"MS": {
# DATASME-2123: Conexon asked that 1413869852 and 1067733764 move from
# reporting project "Southwest 3.1" to "Southwest 3.2" -- the two ids
# DATASME-2061 left on 3.1. Same DP ("Southwest 3").
# Previous package: 1lF7K1lTGgezyHuRS3xrEae8TY5YoRSsQ @ 20260914.
#
# DATASME-2061: Conexon asked (and MS granted) that 37 locations move from
# reporting project "Southwest 3.1" to "Southwest 3.2". Both sit under DP
# CM61-BEAD-MS-CON Lncln1-2712 and deployment_project_id is identical on all
# 37, so the DP layer -- and with it the SGA rollups, funding tile and
# c_cpc_merge_tile_ms_final_proposal -- is untouched. Diffed against the
# 20260630 package, this one changes only supp_reporting_project_locations
# (the 37 RP re-keys; 453 -> 416 and 2,256 -> 2,293, none added or removed)
# plus two off-schema count columns in supp_reporting_projects that no model
# reads. The other seven files are md5-identical, fp_subgrantees.csv included,
# which keeps the 1Provider-1SA DENSE_RANK sa_num -- and so every SGA
# legacyId -- stable across the version bump.
# Previous package: 1n3PYx1VzdCE9eEJnkU3Ua-138vDpkdjL @ 20260630.
"gdrive_folder_id": "1PcmC_co0b8fOla_OU0ZRZ3tMxOi814c-",
"version_date": "20260925",
"dp_rp_relationship": PROVIDER_DP_RP_RELATIONSHIP,
"sa_relationship": DEFAULT_1PROVIDER_1SA,
# DATASME-2051: e-signature flow; the portal owns submittedAt/status
# (DATASME-1412).
"sga_outside_platform": False,
},
"XY": {
"gdrive_folder_id": "1Dp1IKuqiN3XNRzBh4GL1CEMbFamAXu0N",
"version_date": "20260507",
"dp_rp_relationship": PROVIDER_DP_RP_RELATIONSHIP,
"sa_relationship": PROVIDER_SA_RELATIONSHIP,
# DATASME-2051: e-signature flow; the portal owns submittedAt/status
# (DATASME-1412).
"sga_outside_platform": False,
},
# E2E test suite
"ZZ": {
"gdrive_folder_id": "1dvZlLv9Lly4GM1RTrF4FEOgkjoF5l0IW",
"version_date": "20251204",
"dp_rp_relationship": PROVIDER_DP_RP_RELATIONSHIP,
"sa_relationship": PROVIDER_SA_RELATIONSHIP,
# DATASME-2051: e-signature flow; the portal owns submittedAt/status
# (DATASME-1412).
"sga_outside_platform": False,
},
"OK": {
"gdrive_folder_id": "1XVRfNztUdAy4vhTh2D4lzOZ1xm2sOnAT",
"version_date": "20260528",
"dp_rp_relationship": DEFAULT_1DP_1RP,
"sa_relationship": DEFAULT_1PROVIDER_1SA,
# DATASME-2051: e-signature flow; the portal owns submittedAt/status
# (DATASME-1412).
"sga_outside_platform": False,
# Duplicate Resound Networks Applicants sharing a single UEI; mirrors the
# exclusion list already applied in stage1_subgrantees/SQL/OK.sql.
"excluded_applicant_int_ids": [
# Resound Network
45,
48,
111,
258,
# Choctaw Nation of Oklahoma
117,
177,
# Atlink and Southern Oklahoma Connected Communities LLC share same UEI, remove the Southern Oklahoma Connected Communities LLC here.
68,
],
},
"GU": {
# DATASME-2033: OIPD Aug 12 decision — GTA's 5 and IT&E's 26 project
# areas aggregate into one reporting project per provider/SGA. DPs are
# unchanged; the grouping comes from the regenerated
# supp_deployment_reporting.csv / supp_reporting_projects.csv in the
# 20260828 package (previous package: 18GeFKUtGGf8wflTbdTK1FlARWbyf85KS
# @ 20251203). See docs/plans/2026-08-28-datasme-2033-gu-rp-aggregation.md.
"gdrive_folder_id": "1MKc7Ut3VdJaVGlJAH0t3uA7sZsqTmnxR",
"version_date": "20260828",
"dp_rp_relationship": PROVIDER_DP_RP_RELATIONSHIP,
"sa_relationship": DEFAULT_1PROVIDER_1SA,
# DATASME-2051: SGAs are signed/executed outside the platform, so each
# newly ingested SGA is inserted already submitted and unlocked.
"sga_outside_platform": True,
# SpaceX withdrew from BEAD in Guam (Applicant subtree purged by
# breakfix/gu_spacex_removal) but remains in the raw fp_* files.
"excluded_project_ids": [
"CM61-BEAD-GU-PA-001-A",
"CM61-BEAD-GU-PA-003-A",
"CM61-BEAD-GU-PA-005-A",
"CM61-BEAD-GU-PA-006-A",
],
},
"VA": {
# DATASME-1829/1843: reverted the DATASME-1696 config bump
# (folder 1k2xZTda... / version_date 20260414). That 20260414 was an
# Apr-14 stage9 export advanced ONLY in dev; stage/prod GrAPI remained at
# 20260121, so config 20260414 vs data 20260121 was a drift landmine (a
# re-ingest would have stacked ~84k BSL rows per type at the wrong version).
# The hybrid SGA *code* (per-row RP/DP) stays in effect regardless of version.
# DATASME-1895: VA provided a re-scoping diff (client-delivered fp_* files,
# not a stage9 export) — copied to a fresh fp_ingestion folder and advanced
# to version_date 20260709. Hybrid SGA preservation (all providers aggregated
# at provider level except Virginia Everywhere/APB + ZiTEL) confirmed in the
# ticket thread. Incremental append onto 20260121 — old version's rows for
# entities VA removed are handled by a separate breakfix, not by this config
# change. See docs/plans/2026-07-09-datasme-1895-va-diff-update-plan.md.
"gdrive_folder_id": "17PDOKSo6kMdpaYQSTe_BgxswRQB48XWc",
"version_date": "20260709",
"dp_rp_relationship": PROVIDER_DP_RP_RELATIONSHIP,
"sa_relationship": PROVIDER_SA_RP_RELATIONSHIP, # DATASME-1546: RP-based SGA relationships
# DATASME-2051: e-signature flow; the portal owns submittedAt/status
# (DATASME-1412).
"sga_outside_platform": False,
# DATASME-1932: River Street's BEAD award was rescinded (Applicant
# QM7RVM553QH5 archived 2026-07-17 in Prod only -- a portal-access
# revocation, not a data change; Stage's Applicant was never
# archived, which is why Stage's earlier Stage 2 run succeeded
# before this was known). The 20260709 source package still lists
# River Street as active, which can never resolve to a live
# Applicant. Excluded at Stage 0 so Stage 2/3 stop trying to
# insert them. Covers 4 DeploymentProjects, 5 ReportingProjects,
# and 41 CAI locations; the 1 SubgrantAgreement (QM7RVM553QH5-1)
# is excluded automatically since its DP-fallback SGA row is
# derived directly from these same DeploymentProjects.
"excluded_project_ids": [
"CM61-BEAD-VA-RSN002",
"CM61-BEAD-VA-RSN003",
"CM61-BEAD-VA-RSN004",
"CM61-BEAD-VA-RSN006",
],
"excluded_reporting_project_names": [
"RiverStreet MP",
"RiverStreet WP3",
"RiverStreet WP2",
"RiverStreet CRC",
"RiverStreet CR",
],
},
"CA": {
# DATASME-2152/2102: the approved Final Proposal (T-17920 Rev.1) cut,
# replacing the pre-award Feb 17 package (340 DPs) ARC had been running
# on. 83 DPs / 269,322 BSLs / 1,249 CAIs / 20 subgrantees — verified
# row-identical to the CPUC-published approved FP
# (shoreline.r_datasme_2102_fp_*) on every fp_locations column, the CAI
# multiset and DP financials. vs 20260217: 50 project_ids kept, 33
# rebid ids re-keyed 1:1 (same location sets -> rename-in-place), 257
# DP/RP pairs dropped, LEO projects re-cut with ~40% of their BSLs
# (plan as delete+insert, below the rename threshold), Ewiiaapaayp
# Band (JP5BYVUK7K33) no longer a subgrantee. fp_cai location_id is
# float-stringified ("...0"/"nan") as in the previous package; stage0
# normalizes it. Versioned 20260917, CA's as-of date for the cut.
# Previous package: 138dwaf3uFYP_yesuN4QYev32PNVUCMEZ @ 20260217.
"gdrive_folder_id": "1qhHWSYH_7Shn7n_UC4WgaNC-o1bcafL2",
"version_date": "20260917",
"dp_rp_relationship": DEFAULT_1DP_1RP,
"sa_relationship": DEFAULT_1DP_1SA,
# DATASME-2102: CPUC moved SGAs to PandaDoc e-signature on 2026-09-29,
# so CA is an e-sign tenant: SGAs land with status/submittedAt NULL
# (DATASME-1412) and the portal drives submission. Was True under
# DATASME-2051 (grantor upload); the 83 SGAs the 20260917 cut stamped
# InReview in dev/stage are reset by
# pg_sql_breakfixer/SQL/CA_DATASME-2102_reset_sga_submission_status.sql.
"sga_outside_platform": False,
# DATASME-1783: Duplicate Applicants created during the initial ARC
# final-proposal ingestion run in stage. Exclude them so stage1
# matching and stage2 project ingestion bind to the original
# (non-duplicate) Applicants. Mirrors the exclusion list applied in
# stage1_subgrantees/SQL/CA.sql.
"excluded_applicant_int_ids": [
# AMAZON KUIPER COMMERCIAL SERVICES LLC
292,
# AT&T
59,
202,
# MOUNTAIN TOWER & NETWORK COMPANY LLC
189,
209,
# THE SISKIYOU TELEPHONE COMPANY
188,
# VARCOMM BROADBAND, INC.
204,
# ZITO WEST HOLDING, LLC
186,
# EWIIAAPAAYP BAND OF KUMEYAAY INDIANS
300,
# Astound Broadband
301,
# DATASME-2102: duplicate COMCAST CABLE COMMUNICATIONS MANAGEMENT LLC
# created by the 2026-05-20 prod load. Stage1 matched nothing for UEI
# XG3KMFK12C22, so it inserted a new Applicant. The correct org is 67.
302,
],
},
"IN": {
"gdrive_folder_id": ("1zybjT4HQOxlv9zQAh9NNBr1d45VkLKK8"),
"version_date": "20260819",
"dp_rp_relationship": DEFAULT_1DP_1RP,
"sa_relationship": PROVIDER_SA_RELATIONSHIP,
# DATASME-2051: SGAs are signed/executed outside the platform, so each
# newly ingested SGA is inserted already submitted and unlocked.
"sga_outside_platform": True,
},
"UT": {
# DATASME-2029: curing scope-change re-ingest (NTIA-approved; the client
# directed us not to wait on NIST). Switches UT from auto-generated
# 1DP:1RP to provider-supplied reporting projects, which is what makes
# the Navajo Nation LEO / non-LEO split (DATASME-2023) survive an
# ingest -- the 1:1 generator derives one RP per DP from
# deployment_project_id and would silently recreate the merged project.
#
# The folder carries all four supp tables for the WHOLE state:
# supp_deployment_reporting, supp_reporting_projects,
# supp_reporting_project_locations, supp_reporting_project_cai
# (36 DPs / 37 RPs). RP legacyId = deployment_project_id for every
# project except CM61-BEAD-UT-NN21, which splits into
# CM61-BEAD-UT-NN21-TER (1,604 BSLs) and -LEO (488).
#
# sa_relationship deliberately stays 1DP:1SA: the
# supp_subgrant_agreements.csv in the folder is a copy-paste
# placeholder and is NOT part of the ingest set, so stage0
# auto-generates the table. Run
# pg_sql_breakfixer/SQL/UT_DATASME-2028_align_sga_legacy_ids_to_project_num.sql
# BEFORE the first apply in each environment -- UT's SGA legacyIds
# still use the old DENSE_RANK numbering and stage2 would otherwise
# insert duplicates.
#
# Previous config, retained for history (superseded by DATASME-2029):
# "gdrive_folder_id": "1aynmYmM3RxgMXo5FXmuLF3sObKwX7nfC",
# "version_date": "20260506",
# "dp_rp_relationship": DEFAULT_1DP_1RP,
"gdrive_folder_id": "1MRCVT8ho_bRpO9vcoHD_cFyJMhkJuTiE",
"version_date": "20260825",
"dp_rp_relationship": PROVIDER_DP_RP_RELATIONSHIP,
"sa_relationship": DEFAULT_1DP_1SA,
# DATASME-2051: SGAs are signed/executed outside the platform, so each
# newly ingested SGA is inserted already submitted and unlocked.
"sga_outside_platform": True,
},
"ID": {
# DATASME-2028: revised FP submission — 44 of 94 projects survive, the
# rest are being rebid (another revision is expected). fp_no_bead_locations
# is the unchanged 20260626 file carried into the new folder on purpose.
"gdrive_folder_id": "1QBMCp4eBUJnWKRU_G3yZu38gft3UL6c7",
"version_date": "20260825",
"dp_rp_relationship": DEFAULT_1DP_1RP,
"sa_relationship": DEFAULT_1DP_1SA,
# DATASME-2051: SGAs are signed/executed outside the platform, so each
# newly ingested SGA is inserted already submitted and unlocked.
"sga_outside_platform": True,
},
"IL": {
"gdrive_folder_id": "1nQYPZU81p82w1-C0X28l4GGSom797-o9",
"version_date": "20260731",
"dp_rp_relationship": DEFAULT_1DP_1RP,
"sa_relationship": DEFAULT_1PROVIDER_1SA,
# DATASME-2051: SGAs are signed/executed outside the platform, so each
# newly ingested SGA is inserted already submitted and unlocked.
"sga_outside_platform": True,
},
"KS": {
"gdrive_folder_id": "1JWgHI0RtZqtpEjg-WKoMtOBgM_5Gz6ST",
"version_date": "20260916",
"dp_rp_relationship": DEFAULT_1DP_1RP,
"sa_relationship": DEFAULT_1DP_1SA,
# DATASME-2051: SGAs are signed/executed outside the platform, so each
# newly ingested SGA is inserted already submitted and unlocked.
"sga_outside_platform": True,
},
"NE": {
# DATASME-1962: preliminary revised NBO Final Proposal set — DEV/STAGE only,
# do NOT promote to PROD; a follow-up re-ingest ticket covers the final files.
# Points at the flat fp_ingestion copy, not the raw folder linked on the ticket
# (Stage 0 does not recurse into nested dirs). Fabric v6/v9 prereq ingested
# under DATASME-1963.
# Per the ticket: 1 DP : 1 RP with matching RP names (Q1/Q6/Q11) and all
# deployment projects grouped by subgrantee (Q5) — 74 DP / 74 RP / 6 subgrantees.
"gdrive_folder_id": "1Z2B8wN1u2T5WdUE30365ZXxx6SaY_3ca",
"version_date": "20260727",
"dp_rp_relationship": DEFAULT_1DP_1RP,
"sa_relationship": DEFAULT_1DP_1SA,
# DATASME-2051: SGAs are signed/executed outside the platform, so each
# newly ingested SGA is inserted already submitted and unlocked.
"sga_outside_platform": True,
},
}
# NOTE: we are missing alabama_sabcd and michigan_ditroit tenants from this list
state_profiles = {
"AL": {
"table_pre_challenge": "c_cpc_merge_tile_al",
"table_trueup": "c_cpc_merge_tile_al",
"org_id": 4044,
"ffa_version": 42,
"fabric_version": 4,
"true_up": False,
"dsl_mod": True,
"fw_mod": False,
"fabric_version_pre_challenge": 4,
"ffa_version_pre_challenge": 31,
"tenant": "alabama",
},
"AR": {
"table_pre_challenge": "c_cpc_merge_tile_ar",
"table_trueup": "c_cpc_merge_tile_ar_fabric_v4",
"org_id": 2439,
"ffa_version": 33,
"fabric_version": 4,
"notes": "state has missing CAI name issue",
"true_up": True,
"dsl_mod": True,
"fw_mod": False,
"fabric_version_pre_challenge": 3,
"ffa_version_pre_challenge": 29,
"tenant": "arkansas",
"fabric_version_arc": 6,
"ffa_version_arc": 33,
},
"AZ": {
"table_post_challenge": "",
"table_pre_challenge": "", # we didn't have their pre-challenge data
# ARC/FP tile source (DATASME-1859): built by
# dags/datalake/load_location_lake_table/SQL/AZ_final_proposal_tile.sql
# and consumed by phub_validation's check_tileset.
"table_trueup": "c_bead_merge_tile_az",
"fabric_version": 4,
"ffa_version": 38, # confirmed
"notes": "Did not do pre-challenge; just started on post-challenge",
"fabric_version_pre_challenge": 3,
"true_up": True,
"dsl_mod": True,
"fw_mod": False,
"ffa_version_pre_challenge": 29, # not known, need to confirm with the DRI
"tenant": "arizona",
"fabric_version_arc": 6,
"ffa_version_arc": 59,
},
"CA": {
"table_pre_challenge": "c_cpc_merge_tile_ca_fabric_v4",
"table_trueup": "c_cpc_merge_tile_ca_fabric_v4", # no true up needed
"table_post_challenge": "c_cpc_post_challenges_ca",
"org_id": 3556,
"ffa_version": 40,
"fabric_version": 4,
"true_up": False,
"dsl_mod": True,
"fw_mod": True,
"notes": "State has seperated the CAI and BSL into two complete datasets.",
"fabric_version_pre_challenge": 4,
"ffa_version_pre_challenge": 30,
"fabric_version_arc": 6,
"tenant": "california",
"ffa_version_arc": 68,
},
"CT": {
"table_pre_challenge": "c_cpc_merge_tile_ct",
"table_trueup": "c_cpc_merge_tile_ct",
"org_id": 2853,
"ffa_version": 29,
"fabric_version": 3,
"notes": "State has seperated the CAI and BSL into two complete datasets.",
"true_up": False,
"dsl_mod": True,
"fw_mod": False,
"fabric_version_pre_challenge": 3,
"ffa_version_pre_challenge": 29,
"tenant": "connecticut",
},
"DC": {
"table_pre_challenge": "c_cpc_merge_tile_dc",
"table_trueup": "c_cpc_merge_tile_dc",
"org_id": 0,
"ffa_version": 30,
"fabric_version": 4,
"true_up": False,
"dsl_mod": True,
"fw_mod": False,
"fabric_version_pre_challenge": 4,
"ffa_version_pre_challenge": 30,
"tenant": "district_of_columbia",
},
"DE": {
"table_pre_challenge": "c_cpc_merge_tile_de",
"table_trueup": "c_cpc_merge_tile_de",
"org_id": 2532,
"ffa_version": 29,
"fabric_version": 3,
"true_up": False,
"dsl_mod": True,
"fw_mod": False,
"fabric_version_pre_challenge": 3,
"ffa_version_pre_challenge": 29,
"tenant": "delaware",
},
"GU": {
"table_pre_challenge": "c_cpc_merge_tile_gu",
"table_trueup_v5": "c_cpc_merge_tile_gu_fabric_v5",
"table_trueup": "c_cpc_merge_tile_gu", # only available for GU, requested for specific analysis.
"org_id": 0,
"ffa_version": 38,
"fabric_version": 4,
"true_up": True,
"dsl_mod": True,
"fw_mod": False,
"fabric_version_pre_challenge": 4,
"ffa_version_pre_challenge": 38,
"tenant": "guam",
"fabric_version_arc": 6,
"ffa_version_arc": 59,
},
"HI": {
"table_pre_challenge": "c_cpc_merge_tile_hi_fabric_v4",
"table_trueup": "c_cpc_merge_tile_hi_fabric_v4",
"org_id": 0,
"ffa_version": 42,
"fabric_version": 4,
"true_up": False,
"dsl_mod": True,
"fw_mod": False,
"fabric_version_pre_challenge": 4,
"ffa_version_pre_challenge": 31,
"tenant": "hawaii",
},
"IN": {
"table_pre_challenge": "c_cpc_merge_tile_in",
"table_trueup": "c_cpc_merge_tile_in",
"org_id": 1944,
"ffa_version": 29, # using 29 of challenge
"fabric_version": 3,
"true_up": False,
"dsl_mod": True,
"fw_mod": False,
"fabric_version_pre_challenge": 3,
"ffa_version_pre_challenge": 17,
"fabric_version_arc": 6, # IN final proposal (DATASME-1838)
"ffa_version_arc": 59, # IN final proposal (DATASME-1838)
"tenant": "indiana",
},
"KS": {
### we did not do true up for KS
"table_pre_challenge": "c_cpc_merge_tile_ks",
"table_trueup": "c_cpc_merge_tile_ks",
"org_id": 0,
"ffa_version": 56, # was 17 (stale pre-ARC value) — load-bearing for Stage 3's tile SQL (DATASME-1922); see docs/plans/2026-08-10-datasme-1922-ks-arc-onboarding-plan.md 🔴 Incident
"fabric_version": 2,
"true_up": False,
"dsl_mod": True,
"fw_mod": False,
"fabric_version_pre_challenge": 2,
"ffa_version_pre_challenge": 17,
"fabric_version_arc": 6, # KS final proposal (DATASME-1922)
"ffa_version_arc": 56, # FFA as_of_date 2025-05-12, per ticket (DATASME-1922)
"tenant": "kansas",
},
"LA": {
"table_pre_challenge": "c_cpc_merge_tile_la",
"table_trueup": "c_cpc_merge_tile_la_fabric_v3",
"org_id": 0,
"ffa_version": 29,
"fabric_version": 3,
"true_up": True,
"dsl_mod": True,
"fw_mod": False,
"fabric_version_pre_challenge": 3,
"ffa_version_pre_challenge": 29,
"tenant": "louisiana",
"fabric_version_arc": 6,
"ffa_version_arc": 66,
},
"MS": {
"table_pre_challenge": "c_cpc_merge_tile_ms_fabric_v4",
"table_trueup": "c_cpc_merge_tile_ms_fabric_v4",
"org_id": 0,
"ffa_version": 39, # changed per post challenge curing request CUST-2733
"fabric_version": 4,
"true_up": False,
"dsl_mod": True,
"fw_mod": True,
"fabric_version_pre_challenge": 4,
"ffa_version_pre_challenge": 31,
"tenant": "mississippi",
"fabric_version_arc": 6,
# DATASME-2061: was unset, so stage00_controller fell back to
# ffa_version (39, the post-challenge curing value from CUST-2733) and
# would have re-stamped prod's ARC PIT slice from 59 down to 39.
# Yuan: FFA should be the most recent release. 91 is the newest in
# pg_game data.fabric_versions (D25_03sep2026, as_of 2025-12-31), and it
# is what MS_final_proposal_tile.sql already builds from -- that SQL
# reads the UNVERSIONED t_fcc_fabric_fixed_availability, which carries no
# version column and holds only the current release. So the old 59 stamp
# described content it was never built from.
# Also moves stage8: provider_tech_str reads
# t_fcc_fabric_fixed_availability_v{ffa_version_arc}, which was falling back
# to DEFAULT_FFA_VERSION_ARC=68 while the tile was on 91 -- exactly the
# divergence assert_phub_provider_tech_str_matches_lake warns about. Both
# sides now read 91. _v91 confirmed present in shoreline and readydatalake.
"ffa_version_arc": 91,
},
"MP": {
# ARC-only onboarding (CNMI, 2026-09-28): no BEAD challenge process with
# Ready, so no pre-challenge/true-up merge tile exists. Fabric v6 and v9
# were ingested for MP under DATASME-2099; the FP tile builds from v6 like
# every other ARC state (see MP_final_proposal_tile.sql). FFA 92 per
# Yuan, 2026-09-28: the most recent release, following the MS rule
# (DATASME-2061) that FFA should be the newest. stage00_controller fails
# at resolve_config without both *_arc values.
"table_pre_challenge": "",
"table_trueup": "",
"org_id": 0,
"ffa_version": 92,
"fabric_version": 6,
"true_up": False,
"dsl_mod": True,
"fw_mod": False,
"fabric_version_pre_challenge": 6,
"ffa_version_pre_challenge": 92,
"tenant": "northern_mariana_islands",
"fabric_version_arc": 6,
"ffa_version_arc": 92,
},
"MT": {
"table_pre_challenge": "c_cpc_merge_tile_mt",
"table_trueup": "c_cpc_merge_tile_mt",
"org_id": 2515,
"ffa_version": 19,
"fabric_version": 3,
"true_up": False,
"dsl_mod": True,
"fw_mod": False,
"fabric_version_pre_challenge": 3,
"ffa_version_pre_challenge": 19,
"tenant": "montana",
},
"NC": {
"table_pre_challenge": "c_cpc_merge_tile_nc",
"table_trueup": "c_cpc_merge_tile_nc",
"org_id": 0,
"ffa_version": 33,
"fabric_version": 4,
"true_up": False,
"dsl_mod": True,
"fw_mod": True,
"fabric_version_pre_challenge": 4,
"ffa_version_pre_challenge": 33,
"tenant": "north_carolina",
},
"ND": {
"table_pre_challenge": "c_cpc_merge_tile_nd",
"table_trueup": "c_cpc_merge_tile_nd_fabric_v4",
# "table_trueup": "c_cpc_merge_tile_nd_fabric_v4", ## Using customized sql to process, no true up table existing now.
"org_id": 2633,
"ffa_version": 34,
"fabric_version": 4,
"true_up": True,
"dsl_mod": True,
"fw_mod": False,
"notes": "Using customized sql to process, no true up table existing now",
"fabric_version_pre_challenge": 3,
"ffa_version_pre_challenge": 29,
"tenant": "north_dakota",
},
"NH": {
"table_pre_challenge": "c_cpc_merge_tile_nh",
"table_trueup": "c_cpc_merge_tile_nh",
"org_id": 3470,
"ffa_version": 31,
"fabric_version": 4,
"true_up": False,
"dsl_mod": True,
"fw_mod": False,
"fabric_version_pre_challenge": 4,
"ffa_version_pre_challenge": 31,
"tenant": "new_hampshire",
},
"NJ": {
"table_pre_challenge": "c_cpc_merge_tile_nj",
"table_trueup": "c_cpc_merge_tile_nj",
"org_id": 0,
"ffa_version": 42,
"fabric_version": 4,
"true_up": False,
"dsl_mod": True,
"fw_mod": True,
"fabric_version_pre_challenge": 4,
"ffa_version_pre_challenge": 30,
"tenant": "new_jersey",
},
"NM": {
"table_pre_challenge": "c_cpc_merge_tile_nm",
"table_trueup": "c_cpc_merge_tile_nm_fabric_v4",
"org_id": 2689,
"ffa_version": 36,
"fabric_version": 4,
"true_up": True,
"dsl_mod": True,
"fw_mod": False,
"fabric_version_pre_challenge": 3,
"ffa_version_pre_challenge": 29,
"tenant": "new_mexico",
},
"NV": {
"table_pre_challenge": "c_cpc_merge_tile_nv",
"table_trueup": "c_cpc_merge_tile_nv",
"org_id": 2315,
"ffa_version": 16,
"fabric_version": 2,
"true_up": False,
"dsl_mod": True,
"fw_mod": False,
"fabric_version_pre_challenge": 2,
"ffa_version_pre_challenge": 16,
"tenant": "nevada",
},
"NY": {
"table_pre_challenge": "c_cpc_merge_tile_ny",
"table_trueup": "c_cpc_merge_tile_ny_fabric_v4",
"org_id": 2563,
"ffa_version": 34,
"fabric_version": 4,
"true_up": True,
"dsl_mod": True,
"fw_mod": False,
"fabric_version_pre_challenge": 3,
"ffa_version_pre_challenge": 29,
"tenant": "new_york",
},
"OR": {
"table_pre_challenge": "c_cpc_merge_tile_or",
"table_trueup": "c_cpc_merge_tile_or",
"org_id": 3336,
"ffa_version": 29,
"fabric_version": 3,
"true_up": False,
"dsl_mod": False,
"fw_mod": False,
"fabric_version_pre_challenge": 3,
"ffa_version_pre_challenge": 28,
"tenant": "oregon",
},
"OH": {
"table_pre_challenge": "c_cpc_merge_tile_oh",
"table_trueup": "c_cpc_merge_tile_oh", # no true up
"org_id": 2853,
"ffa_version": 29,
"fabric_version": 3,
"true_up": False,
"dsl_mod": True,
"fw_mod": True,
"notes": "customized cellular modification method",
"fabric_version_pre_challenge": 3,
"ffa_version_pre_challenge": 29,
"tenant": "ohio",
},
"OK": {
"table_pre_challenge": "c_cpc_merge_tile_ok",
"table_trueup": "c_cpc_merge_tile_ok",
"org_id": 0,
"ffa_version": 31,
"fabric_version": 4,
"true_up": False,
"dsl_mod": True,
"fw_mod": False,
"notes": "multiple programs at the same time",
"fabric_version_pre_challenge": 4,
"ffa_version_pre_challenge": 33,
"fabric_version_arc": 6,
"tenant": "oklahoma",
},
"PA": {
"table_pre_challenge": "c_cpc_merge_tile_pa",
"table_trueup": "c_cpc_merge_tile_pa_fabric_v4",
"org_id": 3186,
"ffa_version": 33,
"fabric_version": 4,
"true_up": True,
"dsl_mod": False,
"fw_mod": False,
"notes": "state requires to use mapping file to enhance the data quality after true-up",
"fabric_version_pre_challenge": 3,
"ffa_version_pre_challenge": 29,
"tenant": "pennsylvania",
},
"VA": {
"table_pre_challenge": "c_cpc_merge_tile_va",
"fabric_version_pre_challenge": 2,
"ffa_version_pre_challenge": 16,
"notes": "Did not do post-challenge with us",
"fabric_version_arc": 3,
# DATASME-2110: VA's ARC PIT slice is stamped fabric 3 / FFA 75 in GrAPI
# dev, stage and prod -- the pair DATASME-1895's stage3 runs used.
# stage00_controller refuses to plan without it (DATASME-2028), and
# passes it to stage3, so a reload keeps the existing 3/75 slice.
"ffa_version_arc": 75,
"tenant": "virginia",
},
"WV": {
"table_pre_challenge": "c_cpc_merge_tile_wv",
"table_trueup": "c_cpc_merge_tile_wv", # NOT STARTED
"org_id": 0,
"ffa_version": 16,
"fabric_version": 2,
"true_up": False,
"dsl_mod": True,
"fw_mod": False,
"fabric_version_pre_challenge": 2,
"ffa_version_pre_challenge": 16,
"tenant": "west_virginia",
},
"WY": {
"table_pre_challenge": "c_cpc_merge_tile_wy",
"table_trueup": "c_cpc_merge_tile_wy",
"org_id": 3507,
"ffa_version": 29,
"fabric_version": 3,
"true_up": False,
"dsl_mod": True,
"fw_mod": False,
"fabric_version_pre_challenge": 3,
"ffa_version_pre_challenge": 29,
"tenant": "wyoming",
},
"TX": {
"table_pre_challenge": "c_cpc_merge_tile_tx",
"table_trueup": "c_cpc_merge_tile_tx",
"org_id": 0,
"ffa_version": 51,
"fabric_version": 5,
"true_up": False,
"dsl_mod": False,
"fw_mod": False,
"fabric_version_pre_challenge": 5,
"ffa_version_pre_challenge": 43,
"tenant": "texas",
"fabric_version_arc": 6,
"ffa_version_arc": 51,
},
"MN": {
# NOTE: from MN SBO email response: `Minnesota will not have BEAD Funding available to use for CAIs`
"table_pre_challenge": "", # we didn't have their pre-challenge data
"table_trueup": "c_bead_merge_tile_mn",
"org_id": 0,
"ffa_version": 32,
"fabric_version": 4,
"true_up": False,
"dsl_mod": True,
"fw_mod": True,
"fabric_version_pre_challenge": 4,
"ffa_version_pre_challenge": 32,
"fabric_version_arc": 6,
"ffa_version_arc": 59,
"tenant": "minnesota",
},
"UT": {
"table_pre_challenge": "",
"table_trueup": "",
"org_id": 0,
"ffa_version": 76,
"fabric_version": 7,
"true_up": False,
"dsl_mod": True,
"fw_mod": False,
"fabric_version_pre_challenge": 7,
"ffa_version_pre_challenge": 76,
"fabric_version_arc": 7,
"ffa_version_arc": 76,
"tenant": "utah",
},
"ID": {
# ARC-only onboarding (CUST-4788) — no BEAD post-challenge process with
# Ready, so no pre-challenge/true-up merge tile exists. ffa_version=42
# (2024-10-30 release) is the latest FFA aligned with the Fabric v4
# (12-31-2023) release, before v5 started 2024-11-13. See
# dags/datalake/load_location_lake_table/SQL/ID_final_proposal_tile.sql.
"table_pre_challenge": "",
"table_trueup": "",
"org_id": 0,
"ffa_version": 42,
"fabric_version": 4,
"true_up": False,
"dsl_mod": False,
"fw_mod": False,
"fabric_version_pre_challenge": 4,
"ffa_version_pre_challenge": 42,
"fabric_version_arc": 4,
"ffa_version_arc": 42,
"tenant": "idaho",
},
"IL": {
# Need clarification on these values.
"table_pre_challenge": "",
"table_trueup": "",
"org_id": 0,
"ffa_version": 29,
"fabric_version": 3,
"true_up": False,
"dsl_mod": False,
"fw_mod": False,
"fabric_version_pre_challenge": 3,
"ffa_version_pre_challenge": 29,
"fabric_version_arc": 3,
"ffa_version_arc": 29,
"tenant": "illinois",
},
"NE": {
# ARC-only onboarding (CUST-4833) — Nebraska never went through Ready's BEAD
# challenge process, so no pre-challenge/true-up merge tile exists (confirmed:
# no c_cpc_/c_bead_/c_bob_merge_tile_ne in shoreline). Fabric v6 (NTIA-format)
# ingested under DATASME-1963: 798,429 NE rows in c_fabric_sbo_v6.
# ffa_version=68 is the latest FFA release aligned with the Fabric v6 vintage —
# verified in Athena: all 793,518 NE FFA locations in v68 exist in
# c_fabric_sbo_v6 (100%), while v69+ drops to 99.36% (next fabric vintage).
# Load-bearing for Stage 3: initialize_lake_params reads ffa_version from the
# state profile, not from a DAG param.
"table_pre_challenge": "",
"table_trueup": "",
"org_id": 0,
"ffa_version": 68,
"fabric_version": 6,
"true_up": False,
"dsl_mod": False,
"fw_mod": False,
"fabric_version_pre_challenge": 6,
"ffa_version_pre_challenge": 68,
"fabric_version_arc": 6,
"ffa_version_arc": 68,
"tenant": "nebraska",
},
# Demo tenant
"XY": {
"table_pre_challenge": "c_cpc_merge_tile_xy",
"table_trueup": "",
"org_id": 0,
"ffa_version": 33,
"fabric_version": 4,
"fabric_version_arc": 6,
"true_up": False,
"dsl_mod": True,
"fw_mod": False,
"fabric_version_pre_challenge": 4,
"ffa_version_pre_challenge": 33,
"tenant": "demo_base",
},
# E2E test suite
"ZZ": {
"table_pre_challenge": "c_cpc_merge_tile_zz",
"table_trueup": "",
"org_id": 0,
"ffa_version": 33,
"fabric_version": 4,
"fabric_version_arc": 6,
"true_up": False,
"dsl_mod": True,
"fw_mod": False,
"fabric_version_pre_challenge": 4,
"ffa_version_pre_challenge": 33,
"tenant": "test_base",
},
}
S3_ROOT = "s3://463002504035-shoreline/raw/uploads"
post_challenge_approved = {
"AZ": {
# "post_challenge_location": f"{S3_ROOT}/Arizona/Post Challenge Locations + CAI/post_challenge_locationswithPA.csv",
"post_challenge_location": f"{S3_ROOT}/Arizona/Post Challenge Locations + CAI/post_challenge_locations_01062025.csv",
"post_challenge_cai": f"{S3_ROOT}/Arizona/Post Challenge Locations + CAI/post_challenge_cais_11222024.csv",
},
"AR": {
"post_challenge_location": f"{S3_ROOT}/Arkansas/unzipped/post_challenge/post_challenge_locations.csv",
"post_challenge_cai": f"{S3_ROOT}/Arkansas/unzipped/post_challenge/post_challenge_cai.csv",
},
"OR": {
"post_challenge_location": f"{S3_ROOT}/Oregon/unzipped/post-challenge/post_challenge_locations.csv",
"post_challenge_cai": f"{S3_ROOT}/Oregon/unzipped/post-challenge/post_challenge_cai.csv",
},
"MN": {
"post_challenge_location": f"{S3_ROOT}/Minnesota/bead_prepare/MN Challenge curing 1-9-25/post_challenge_locations.csv",
# "post_challenge_cai": f"{S3_ROOT}/Minnesota/bead_prepare/post_challenge_cai.csv",
},
# "LA": {"post_challenge_location.csv": "", "post_challenge_cai.csv": ""},
}
[docs]
@staticmethod
def get_arc_final_proposal_config(state_abbr: str) -> dict:
"""
Get the FP profile for the state.
"""
profile = BeadUtil.fp_profiles.get(state_abbr.upper(), None)
return profile
[docs]
@staticmethod
def bead_profile(state_abbr: str, check_pre: bool = True) -> dict:
"""
table_pre_challenge: the table name before the challenge
table_trueup: the table name after the true-up. Before the challenge results applied. This table is only for data processing. Not for actual frontend functionality
org_id: the organization id for the state
ffa_version: the post challenge version of the fabric fixed availability data
fabric_version: the post challenge version of the fabric data
true_up: whether the state has true-up or not
dsl_mod: whether the state has DSL modification or not
fw_mod: whether the state has Cellular fixed wireless modification or not
tenant: the name of the tenant in the grapi database
:param str state_abbr: the state abbreviation in upper case
:param str, optional check_pre: whether to check the pre-challenge tables. Default is True.
:return: the post challenge configuration for the state
NOTE: this function does call the AWS Athena and AWS Glue APIs to check the existence of the tables.
BEWARE: When use outside of a DAG/@task i.e. as top level import, must set `check_pre` to False.
"""
print(f"Getting BEAD profile for {state_abbr=}, {check_pre=}")
profile = BeadUtil.state_profiles.get(state_abbr.upper(), dict())
table_pre_challenge = profile.get("table_pre_challenge", "")
if profile and table_pre_challenge and check_pre:
import awswrangler as wr
assert wr.catalog.does_table_exist(
table=table_pre_challenge, database=Config.DATA_LAKE_DB
), f"Table {table_pre_challenge=} does not exist in {Config.DATA_LAKE_DB}!"
MIN_SIZE_PRE_CHALLENGE_MERGE_TILE_TABLE = 5_000
assert (
wr.athena.read_sql_query(
f"SELECT count(*) AS cnt FROM {table_pre_challenge}",
database=Config.DATA_LAKE_DB,
).iloc[0, 0]
> MIN_SIZE_PRE_CHALLENGE_MERGE_TILE_TABLE
), f"Table {table_pre_challenge=} has fewer than {MIN_SIZE_PRE_CHALLENGE_MERGE_TILE_TABLE} rows!"
return profile
[docs]
@staticmethod
def get_pre_ffa_version(state_abbr: str) -> str:
"""
Get the FFA version for the state.
"""
profile = BeadUtil.bead_profile(state_abbr, check_pre=True)
table_pre_challenge = profile.get("table_pre_challenge", "")
if not table_pre_challenge:
return {}
fabric_version_pre_challenge = profile.get("fabric_version_pre_challenge", 4)
ffa_version_pre_challenge = profile.get("ffa_version_pre_challenge", 0)
print(
f"QUERYING fabric_versions meta: {state_abbr=}; {fabric_version_pre_challenge=}; {ffa_version_pre_challenge=}"
)
# get latest ffa_version info corresponds to fabric_version
ffa_config = FCCUtil.get_latest_fabric_version(
fa_ver=fabric_version_pre_challenge, fs_ver=ffa_version_pre_challenge
)
# convert to string format to allowing passing to xcom
version_date = FormatUtil.to_date_string(ffa_config["version_date"])
update_date = FormatUtil.to_date_string(ffa_config["update_date"])
ffa_version_pre_challenge = ffa_config["version"]
process_uuid = ffa_config["process_uuid"]
return {
"table_pre_challenge": table_pre_challenge,
"fabric_version_pre_challenge": fabric_version_pre_challenge,
"ffa_version_pre_challenge": ffa_version_pre_challenge,
"version_date": version_date,
"update_date": update_date,
"process_uuid": process_uuid,
}
[docs]
@staticmethod
def get_postchallenge_states() -> list:
"""
Get the list of states that have post-challenge configurations recorded.
This does not check the pre-challenge tables, so you would need
to call `bead_profile` to check individual states.
"""
return list(BeadUtil.state_profiles.keys())
[docs]
@staticmethod
def get_post_challenge_approved_files(state_abbr: str) -> dict:
"""
Get the post challenge approved files for the state.
"""
return BeadUtil.post_challenge_approved.get(state_abbr.upper(), {})
[docs]
@staticmethod
def get_all_pre_cha_lake_table() -> dict:
"""
Get all pre challenge lake table for all states
"""
return {
state: profile.get("table_pre_challenge", "")
for state, profile in BeadUtil.state_profiles.items()
}
[docs]
@staticmethod
def get_all_ready_sbo() -> list:
"""
Get all ready SBOs for the states.
This is used to check if the state has a pre-challenge table.
"""
return [key for key in BeadUtil.state_profiles.keys()] + [
key
for key in BeadUtil.post_challenge_approved.keys()
if key not in BeadUtil.state_profiles.keys()
]
[docs]
@staticmethod
def get_state_abbr_by_tenant(tenant: str) -> str:
"""
Get the state abbreviation by tenant name.
:param tenant: the tenant name
:return: the state abbreviation
"""
for state_abbr, profile in BeadUtil.state_profiles.items():
if profile.get("tenant") == tenant:
return state_abbr
raise ValueError(f"Tenant {tenant} not found in BEAD profiles.")