Building a utility data warehouse for energy and emissions

A utility data warehouse earns its keep the first time someone asks a question that spans commodities, sites, and time. How much did electricity cost per square foot across the portfolio last year, weather-normalized, and what were the associated Scope 2 emissions? If the answer lives in a folder of PDF bills, a billing export, and three analyst spreadsheets, you cannot answer it quickly or defensibly. A warehouse fixes that by giving every read, bill, and emission factor one place to live and one shape to live in.

Start with the grain, not the dashboard

The most common mistake is modeling for a specific report. Model for the smallest fact instead. Two fact tables cover most needs: a billing fact with one row per bill line item, per account, per commodity, per period, and a consumption fact with one row per meter, per read or per interval. Everything else is a dimension that describes those facts. Get the grain right and new reports become joins, not migrations.

A data model that survives real utility data

The entities that keep utility data honest are accounts, service points, meters, reads, bills, sites, and commodities. Keep them as separate tables because they change on different schedules and for different reasons.

• Account: the billing relationship with a supplier or distributor. Accounts open, close, and transfer.
• Service point: the physical point of delivery where a commodity is supplied. Meters get swapped, but the service point persists, so it is the durable anchor for history.
• Meter: the device. One service point can see several meters over its life through replacements and upgrades.
• Read: a meter reading, either a monthly register read or an interval value with a timestamp.
• Bill: the invoice and its line items (consumption, demand, delivery, riders, taxes).
• Site: the building or facility, which may aggregate several service points across commodities.
• Commodity: electricity, natural gas, water, steam, and so on, each with its own native units and quirks.

Interval data is where volume shows up. A single 15-minute meter produces 35,040 reads per year, so the consumption fact becomes your largest table and deserves a time-series-friendly layout indexed by meter and timestamp rather than the one-row-per-month design older systems used.

Those numbers set the scale of the problem. US electric utilities reported about 119 million advanced metering installations, roughly 72% of all electric meters, so meter-level data is now the norm, not the exception. On the reporting side, close to a quarter of US commercial floor space already benchmarks in ENERGY STAR Portfolio Manager, which means a warehouse that cannot serve benchmarking and disclosure is already behind.

Multiple commodities and units in one model

Store the native unit on the read (kWh, therms, ccf, cubic metres, GJ, kilolitres) and compute a normalized energy unit for cross-commodity analysis from a governed reference table, never hard-coded in a report. For a shared basis, 1 kilowatt-hour equals 3,412 Btu and 1 therm equals 100,000 Btu. Keeping the native value and the normalized value side by side means you can reconcile to the bill and still compare gas against electricity.

Slowly changing tariffs and rates

Tariffs change. A delivery charge gets updated, a rider expires, a tax rate moves. If you overwrite the old value, last year's recomputed cost stops matching last year's bill and your audit trail breaks. The standard fix is a Type 2 slowly changing dimension: when a rate attribute changes, add a new row with effective-from and effective-to dates and a fresh surrogate key, so every historical fact joins to the rate that was in force at the time. Use the same pattern for account attributes, site characteristics such as floor area and operating hours, and emission factors, which also change by year and by region.

Data lineage from raw bill to serving layer

Finance and ESG users will ask where a number came from, so build the pipeline in refinement layers, the pattern many teams call a medallion architecture: a raw layer that keeps the source bill exactly as received, a cleaned and conformed layer, and a business-ready serving layer. Each stage makes lineage and access control tractable, so you can trace any reported figure back to the specific bill and read that produced it.

Serving BI and ESG without duplicating logic

The warehouse should feed dashboards and disclosure from the same conformed facts. For emissions, Scope 2 activity data comes straight from utility bills, and you apply location-based or market-based factors as governed dimension joins rather than one-off spreadsheet math. For benchmarking, the model already holds the floor area and the 12 months of energy data an ENERGY STAR score requires, where a score of 75 or higher marks top-quartile performance.

Where the warehouse stops

A warehouse is not an ERP and not a BI tool. If you need approvals, payments, and general-ledger postings, integrating utility data with the ERP is the right move rather than rebuilding finance in the warehouse. If you need executive ESG and BI views, the warehouse is the clean source that feeds ESG and BI integration downstream. Keep it focused on being the analysis-ready system of record for consumption, billing, and emissions, and let the neighboring systems do what they do best.