ESG Data Engineering:
Carbon Tracking Across 10,000+ Commercial Properties

01 · Carbon Accounting Is a Data Problem

Strip the sustainability vocabulary and the problem is familiar: heterogeneous sources (meters, utility bills, fuel invoices, tenant submissions, supplier estimates), a calculation layer where methodology changes must not silently rewrite history, and consumers — regulators, lenders, investors — who require provable provenance. That is a governed data platform with unusually strict lineage requirements, which is fortunate, because we know how to build those.

The stakes changed with the money: green bonds and sustainability-linked loans price against verified emissions trajectories, and CSRD assurance makes weak data infrastructure a disclosure risk. The reference scenario throughout — a 10K-property commercial portfolio — reflects where this bites hardest: too many buildings for spreadsheets, too much capital riding on the numbers for estimates.

02 · The Architecture: Meters to Audit-Ready Reports

03 · The Data Flow: Three Scopes, Three Data Realities

The three scopes are three different data engineering problems wearing one acronym. Scope 1 and 2 are measurement problems — meters and bills, high quality, automatable. Scope 3 is an estimation problem — tenant behaviour, embodied carbon, supplier chains — where the honest move is carrying the quality tier on every number and publishing the improvement plan: which estimates become metered next quarter. Auditors respect a labelled estimate; they punish an unlabelled one.

04 · IoT Meter Ingestion at Portfolio Scale

A 10K-property portfolio runs 30–80K meters across electricity, gas, water, and submetering — a modest IoT estate by industrial standards (our digital twin architecture handles 100K+ sensors at far higher rates), but with two ESG-specific twists:

gap handling — the ESG-specific discipline (dbt model excerpt)
-- Carbon totals must be COMPLETE per period: a silent meter gap
-- understates emissions, which an assurance review treats as misstatement.
WITH expected AS (
  SELECT m.meter_id, p.period_start,
         m.expected_readings_per_day * p.period_days AS expected_n
  FROM {{ ref('meter_registry') }} m CROSS JOIN {{ ref('periods') }} p
),
actual AS (
  SELECT meter_id, period_start, COUNT(*) AS actual_n,
         SUM(kwh) AS metered_kwh
  FROM {{ ref('silver_readings') }} GROUP BY 1, 2
)
SELECT e.meter_id, e.period_start,
       a.metered_kwh,
       CASE WHEN a.actual_n >= e.expected_n * 0.98 THEN 'metered'
            WHEN bill.kwh IS NOT NULL              THEN 'billed'     -- fallback
            ELSE 'modelled'                                          -- degree-day model
       END AS quality_tier,
       COALESCE(a.metered_kwh, bill.kwh, model.kwh) AS kwh_final
FROM expected e
LEFT JOIN actual a USING (meter_id, period_start)
LEFT JOIN {{ ref('utility_bills') }} bill USING (meter_id, period_start)
LEFT JOIN {{ ref('degree_day_model') }} model USING (meter_id, period_start)

First twist: completeness beats latency — a fraud pipeline tolerates a late event; a carbon total with a silent gap is a misstatement, so every meter-period is reconciled against expectations with explicit fallback tiers. Second: the meter registry is regulated metadata — meter-to-property-to-entity mapping determines organisational boundaries under the GHG Protocol, so registry changes are versioned and effective-dated like the legal documents they reflect.

05 · The Calculation Engine: Emission Factors as Versioned Data

The cardinal sin of spreadsheet carbon accounting is emission factors buried in formulas. Factors change annually (grid factors), vary by region and method, and get restated — the engine treats them as data:

The diff report deserves emphasis: when the grid factor for a region updates, the engine recomputes affected periods and produces a property-level delta report before anything publishes. Sustainability teams review the restatement like finance reviews a ledger adjustment — because under CSRD, that is what it is.

06 · Lineage: Surviving the Assurance Review

The assurance conversation has one shape: pick a reported number, walk it backwards. The platform's answer must be a query, not a meeting:

the auditor's walk — one reported tonne, fully decomposed
-- "Building FR-0447, Scope 2, Q3: 128.4 tCO₂e — show me."
SELECT * FROM lineage.decompose('FR-0447', 'scope2', '2026-Q3');
-- returns: 3 meters · 6,624 readings (metered, 99.2% completeness)
--        + 1 billed correction (utility true-up, doc #UB-88412)
--        × factor v2026.1 (source: national grid operator publication, linked)
--        · method: location-based (market-based parallel: 119.7 tCO₂e)
--        · quality tier: metered · computed 2026-10-02, engine v4.2

This is the same lineage discipline as our production regulatory engagement — Apache Atlas covering 100% of a European bank's data assets to GDPR certification — pointed at carbon instead of customer data. The implementation is identical in spirit: lineage captured automatically from the pipeline graph (dbt + engine metadata), never reconstructed manually after the fact. Manual lineage is fiction with diagrams.

07 · Business Implementation: Scorecards That Move Capital

The compliance infrastructure, once built, becomes product. The reference scenario's arc: a commercial portfolio facing CSRD builds the platform for reporting, then discovers the same gold layer powers investor-facing scorecards — live intensity metrics (kgCO₂e/m²) per property and fund, trajectory vs science-based targets, and the data-quality tier mix improving quarter over quarter. That last chart matters more than it looks: lenders price sustainability-linked instruments against verifiable trajectories, and a portfolio that can show metered (not estimated) numbers with audit-grade lineage clears due diligence that estimates cannot.

In the reference scenario, that verifiability unlocks $50M in green financing — a sustainability-linked facility whose margin ratchet keys to the platform's reported intensity trajectory. The engineering point survives the label: the financing isn't unlocked by being green, it's unlocked by being provably measured. The scorecard architecture is the standard pattern — gold tables, semantic layer, embedded dashboards — that our Snowflake BI engagement ships (6-hour reporting cut to 15 minutes); the novelty is entirely in what the numbers can withstand.

08 · Lessons Learned: The Hard Truths

• The meter registry is the hard part, again. Like parcels in property and patients in healthcare, the identity layer (meter → property → legal entity) consumed the most effort and mattered most. Organisational boundary errors are reporting errors.
• Quality tiers defuse the Scope 3 argument. Teams paralyse over imperfect Scope 3 data. Labelling every number metered/billed/modelled/estimated — and publishing the tier mix — converts a credibility problem into a roadmap.
• Both Scope 2 methods, always, from day one. Computing location-based and market-based in parallel costs nothing; adding the second method later under a lender's deadline costs a quarter.
• Factor updates are restatements; treat them with ledger discipline. The diff-before-publish workflow turned factor-update week from a fire drill into a review meeting.
• Utility bill true-ups will fight your meters. Billed and metered totals disagree within tolerance constantly; reconcile explicitly and document which wins per case, or the auditor finds the discrepancy for you.
• Build for the auditor's walk first. Every architectural decision improved once we asked "how does this look when assurance picks one number and walks backwards?" The walk is the product.

09 · Key Takeaways for Practitioners

The production disciplines composed here: 100%-coverage regulatory lineage, IoT edge-to-cloud ingestion, and executive BI at reporting speed. Sector context on the real estate industry page.

FAQ · Frequently Asked Questions