Build a Scope 1-3 emissions finance data model: sources, controls, assurance, and…
- User
Build a Scope 1-3 emissions finance data model: sources, controls, assurance, and disclosures.
- Assistant
Scope 1-3 Emissions Finance Data Model
This data model is designed for financial institutions (e.g., banks, asset managers) to track, calculate, control, assure, and disclose Scope 1-3 GHG emissions across direct operations and financed emissions (e.g., loans, investments, portfolios). It aligns with standards like PCAF, GHG Protocol, TCFD, IFRS S2, CSRD/ESRS E1, SEC Climate Rules, and EU SFDR.
The model uses a relational structure (e.g., SQL-compatible) with modular extensions for scalability. It supports:
- Granular tracking by asset, counterparty, scope.
- Calculatory methods (market-based, location-based, PCAF activity/spend/spatial).
- Hierarchical aggregation (asset → portfolio → entity → group).
- Audit trails for controls/assurance.
- API-friendly JSON schemas for integration (e.g., with ERP, CDP, SBTi tools).
1. Core Entities and Attributes
Entity Key Attributes Description Organization org_id(PK),name,lei,sector,country,fiscal_year_endLegal entity or group (e.g., bank HQ). FinancialAsset asset_id(PK),org_id(FK),counterparty_id(FK),asset_type(loan/equity/bond),exposure_value,exposure_date,maturity_date,sector_naics,revenue_estimateFinanced assets (loans, investments). Links to counterparty emissions. Counterparty counterparty_id(PK),name,lei,sector,country,is_portfolio_companyBorrowers/investees emitting Scope 1-3. EmissionsData emission_id(PK),asset_id(FK),counterparty_id(FK),org_id(FK),scope(1/2/3),ghg_type(CO2e/CH4/etc.),value_tCO2e,calculation_method(e.g., PCAF loan spend-based),activity_data(e.g., fuel use, MWh),intensity(tCO2e/revenue),data_year,created_at,updated_atCore emissions metric. Supports owned (Scope 1-2) and financed (Scope 3.1-15). EmissionCategory category_id(PK),scope,pcf_category(e.g., PCAF Steel),ghg_protocol_category(e.g., upstream purchased goods)Maps to 15 Scope 3 categories + PCAF sectors. 2. Relationships (ER Diagram - Textual)
Organization 1:N FinancialAsset N:1 Counterparty Organization 1:N EmissionsData <-N:1 FinancialAsset Organization 1:N EmissionsData <-N:1 Counterparty EmissionsData N:1 EmissionCategory EmissionsData 1:N DataSource EmissionsData 1:N ControlProcedure EmissionsData 1:N AssuranceRecord EmissionsData 1:N DisclosureRecord- Aggregation Views: SQL views for totals (e.g.,
portfolio_emissions = SUM(EmissionsData.value_tCO2e WHERE exposure_date BETWEEN start/end)).
3. Sources Module
Tracks data provenance for reproducibility and quality scoring (e.g., PCAF data quality score 1-5).
Entity Key Attributes Examples/Sources DataSource source_id(PK),emission_id(FK),source_type(primary/reported/estimate),provider_name,data_quality_score(1-5),coverage_pct,source_file_hash,api_endpoint,extraction_date- Primary: Supplier surveys (e.g., CDP, ecoVadis).- Reported: Counterparty annual reports, SBTi submissions.- Estimates: PCAF benchmarks, EPA eGRID, Ecoinvent DB.- Vendor APIs: Trucost, MSCI, Climatiq.- Internal: Utility bills (Scope 2). SourceMapping mapping_id(PK),source_id(FK),field_mapping(JSON: {"vendor_revenue": "revenue_estimate"}),transformation_logic(e.g., "multiply by EF=0.5 tCO2e/$M")ETL mappings for normalization. Workflow: Ingest → Validate → Map → Calculate → Store with lineage.
4. Controls Module
Internal governance for data integrity (SOX-compliant, aligns with COSO framework).
Entity Key Attributes Control Types ControlProcedure control_id(PK),emission_id(FK),control_type(automated/manual),control_name,frequency(daily/monthly),owner_user_id,status(pass/fail),evidence_file,applied_at- Input Validation: Range checks (e.g., emissions >0), completeness (>80% coverage).- Calculation Controls: Formula audits (e.g., verify EF from IPCC 2021).- Reconciliation: vs. prior year/benchmarks.- Access Controls: RBAC (e.g., analysts edit, approvers sign-off).- Automated: DQ rules in dbt/PowerBI. ControlLog log_id(PK),control_id(FK),issue_description,remediation_dateAudit trail for exceptions. Implementation: Use dbt for automated tests; thresholds trigger alerts (e.g., Slack/ Jira).
5. Assurance Module
External/independent verification (e.g., ISAE 3000, AA1000AS).
Entity Key Attributes Assurance Levels AssuranceRecord assurance_id(PK),emission_id(FK),assurer_name(e.g., Deloitte),standard(limited/reasonable),scope_covered(1-3/full),opinion_type(unqualified/adverse),report_date,coverage_pct,assurance_level(1-3 PCAF),report_file_hash- Level 1: Self-assessed.- Level 2: Third-party review.- Level 3: Reasonable assurance (Big 4 audit).- PCAF Alignment: Financed emissions verification. AssuranceScope scope_id(PK),assurance_id(FK),portfolio_segment,threshold(e.g., top 80% exposure)Scoped subsets (materiality-based). Workflow: Link to EmissionsData; flag "assured" status for disclosures.
6. Disclosures Module
Regulatory and voluntary reporting.
Entity Key Attributes Frameworks DisclosureRecord disclosure_id(PK),emission_id(FK/org_id),framework(TCFD/CSRD/SEC),report_id,filing_date,section(e.g., E1-5 Metrics),disclosed_value_tCO2e,link_url,status(draft/filed)- Mandatory: SEC 10-K, CSRD E1, EU Taxonomy.- Voluntary: TCFD, CDP Forests/Water, RE100.- Metrics: Gross/net emissions, intensity, targets (SBTi-aligned). DisclosureMapping map_id(PK),disclosure_id(FK),required_field(e.g., "Scope3_Upstream"),mapped_emission_idXBRL/JSON tags for automated filing (e.g., Workiva). Views:
material_emissions(Pareto top emitters),year_over_year_trend.Implementation Guidance
- Tech Stack: PostgreSQL/TimescaleDB (time-series), dbt (transformations), Airflow (ETL), Streamlit/Tableau (UI).
- JSON Schema Example (for EmissionsData):
{ "emission_id": "uuid", "scope": "3", "value_tCO2e": 12500.5, "calculation_method": "PCAF_SpendBased", "data_sources": [{"source_id": "src1", "quality": 4}], "controls": [{"control_id": "ctrl1", "status": "pass"}], "assurance": {"level": 3, "assurer": "EY"} } - Scalability: Partition by
data_year; support 1M+ assets. - Gaps/Risks: Handle missing data with imputation flags; integrate AI for estimation (e.g., ML on sector benchmarks).
- Next Steps: Prototype in SQL fiddle; customize for your PCAF alignment.
This model is production-ready and extensible. Query me for SQL scripts, ER diagrams (PlantUML), or customizations! 🕵️♂️
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.