# **Opengrants <> Stellar Community Fund Pipeline**

This document outlines the end-to-end process for converting SCF grant data into standardized DAOIP-5 format and computing analytics on the normalized data.

## **Pipeline Overview**

### **Step 1: Source Data Extraction**
- **Source**: Airtable Base
- **URL**: https://airtable.com/app8tLjMIDrjeloWN/shrrNA24K1e0v5Q0R/tbl57OROvn0qQTuiP/viwNf0pOvzy89J0EB
- **Tables Extracted**:
- Build Award Rounds
- Awarded Submissions [Build Only]
- Awarded Projects [Build Only]
- **Output**: CSV files for each table

### **Step 2: File Organization**
- Save all extracted files to date-stamped folders (e.g., `YYYY_MM_DD`)
- Maintains data versioning and audit trail
- **Storage**: https://github.com/metagov/opengrants-platform/tree/master/raw_data/SCF/11_November_2025

---

## **Data Processing Architecture**

### **Medallion (Bronze–Silver–Gold) Model**

#### **Bronze Layer — Raw Data**
- **Purpose**: Direct ingestion from source systems
- **Sources**: Airtable APIs, CSV exports
- **Storage**: PostgreSQL raw tables
- **Characteristics**: No cleaning or normalization
- **Examples**: `bronze_scf_rounds`, `bronze_scf_submissions`

#### **Silver Layer — Cleaned & Normalized Data**
- **Purpose**: Standardize schema to [DAOIP-5 specification](https://github.com/metagov/daostar)
- **Transformations**: Type enforcement, relationship mapping, metadata quality
- **Storage**: PostgreSQL normalized tables
- **Output**: DAOIP-5 compliant datasets

#### **Gold Layer — Analytics & Metrics**
- **Purpose**: Cross-ecosystem analytics and aggregated metrics
- **Storage**: DuckDB for high-performance querying
- **Use Cases**: Dashboard integration, funding trend analysis, ecosystem comparisons

---

## **Implementation Steps**

### **Step 3: Bronze Ingestion**
- **Script**: `scf.py`
- **Location**: https://github.com/metagov/opengrants-platform/blob/master/og_dagster/assets/bronze/scf.py
- **Function**: Ingests raw SCF data into bronze layer

### **Step 4: Silver Normalization**
- **Schema Mapping**: Defined in YAML configuration
- **Config File**: https://github.com/metagov/opengrants-platform/blob/master/og_dagster/configs/schema_maps/active/daoip5_scf.yaml
- **Function**: Transforms bronze data to DAOIP-5 standard

### **Step 5: Gold Metrics Computation**
- **Models**: https://github.com/metagov/opengrants-platform/tree/master/dbt_project/models/gold
- **Metrics**: https://github.com/metagov/opengrants-platform/tree/master/dbt_project/models/gold/metrics
- **Function**: Computes analytical metrics from normalized data

---

## **Standard Metrics & Reporting**

### **Common Metrics (All Grant Programs)**
- **Program Identity**: Name, Description, Active Since
- **Funding Overview**: Total Funds Distributed, Total Applications Awarded
- **Project Analysis**: Top 10 Projects Funded, Projects funded by round and time period
- **Program Details**: Grant Program Overview, Latest round, Grant procedure, Contact information, Additional links

### **Stellar Community Fund Specific Metrics**
- **Financial Tracking**: Total Awarded vs Total Paid
- **Quarterly Analytics**:
- Quarterly Projects Awarded
- Quarterly Funding Distribution (XLM denominated in USD)
- **Round Analysis**: Awarded Submissions by round
- **Category Insights**: Awarded projects by category
- **Currency Highlight**: XLM as native currency emphasis

---

### For SCF Team Review
We'd appreciate your feedback on:

1. Should we adjust any field definitions?
1. Are there any process improvements we can make towards the clarity or efficiency of the data pipeline?
1. What funding metrics or performance insights (including those from other grant programs) would be most valuable for decision making within the SCF program and throughout the Stellar funding ecosystem?

We'd appriciate some claity on:

1. What tools other than Airtable are used in SCF Grant management process?
1. How are milestones tracked for SCF Grantees?
1. What is the process of milestone verfication?

This pipeline ensures your grant data is standardized and ready for cross-ecosystem analysis while maintaining SCF-specific reporting requirements.