Utility CIS / Customer Billing data model
Third-normal-form CIS replica spanning the utility customer-to-cash chain: customer_account → service_address → service_point (IEC 61968 usage point) → meter → meter_reading, with service_agreement carrying the rate tariff and its effective dates per service point, bill / bill_line_item providing the standard header-detail statement, payment posting against the bill, and service_order tracking field work. AMI/MDMS feed meter_reading via the source field. Customer PII (name, address, email, phone, postal code) is masked under GDPR and CCPA.
Normalized replica of a utility Customer Information System (CIS) for customer-of-record, premise, service-point/usage-point, meter, meter readings, service agreements, bills, bill line items and payments — aligned to IEC 61968 (CIM) and patterned on VertexOne, Oracle CC&B, Harris Northstar and SAP IS-U. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.
- 17
- tables
- 165
- columns
- 26
- relationships
- 8
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: PostgreSQL). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
IEC 61968 CIM-aligned customer-to-meter chain
Modeling customer_account → service_address → service_point → meter follows the IEC 61968 Common Information Model for utility customer and metering domains, so the replica is directly mappable to CIM-based MDM/MDMS and outage/AMI exchanges without bespoke translation.
Tariff history kept via a separate service_agreement
Rate tariffs change over time and customers can switch tariffs without changing premise or meter; a separate service_agreement with effective_from / effective_to per service point preserves tariff history without re-stating old bills, mirroring how Oracle CC&B and SAP IS-U keep SA / tariff history.
Bill header / line item split mirrors statement structure
Bills are issued as a header (totals, dates, status) with N line items (energy, demand, fixed, tax, fee, credit, late-fee); the header/detail split lets each line carry its own service_point, tariff and commodity without denormalizing the statement totals.
AMI / MDMS integration via meter_reading source
Meter readings arrive from AMI head-end systems, AMR walk-by routes or manual reads; tagging meter_reading with source plus a read_status quality code lets the warehouse reconcile MDMS-validated reads against billing-quality reads without splitting the table per channel.
CIS is the master for customer-of-record across commodities
A single customer_account can hold service_points across electric, gas, water, sewer and steam; making customer_account the master and joining all premises / meters / bills to it through service_point keeps the customer-of-record single-sourced across commodities, as IEC 61968 prescribes.
Tables in the Utility CIS / Customer Billing model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Tables (10)
customer_account
Grain: one row per CIS customer account (account_number)
IEC 61968 Customer + CustomerAccount — the customer-of-record across commodities.
| Column | Type | Keys |
|---|---|---|
| customer_account_key | bigint | PK |
| utility_company_key | bigint | |
| billing_cycle_key | bigint | |
| account_number | varchar | BK · PII |
| customer_name | varchar | PII |
| account_type | varchar | |
| account_class | varchar | |
| mailing_address | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| account_status | varchar | |
| opened_date | date | |
| closed_date | date | |
| credit_score_band | varchar | |
| deposit_amount | decimal | |
| parent_account_key | bigint |
service_address
Grain: one row per service premise / address
IEC 61968 ServiceLocation / premise — physical location served.
| Column | Type | Keys |
|---|---|---|
| service_address_key | bigint | PK |
| service_address_id | varchar | BK |
| street_address | varchar | PII |
| city | varchar | |
| state | varchar | |
| postal_code | varchar | PII |
| country | varchar | |
| latitude | decimal | |
| longitude | decimal | |
| dwelling_type | varchar | |
| year_built | integer |
service_point
Grain: one row per service point / usage point
IEC 61968 UsagePoint — the meterable delivery point of a commodity.
| Column | Type | Keys |
|---|---|---|
| service_point_key | bigint | PK |
| customer_account_key | bigint | |
| service_address_key | bigint | |
| service_class_key | bigint | |
| rate_tariff_key | bigint | |
| service_point_id | varchar | BK |
| install_date | date | |
| removal_date | date | |
| service_point_status | varchar | |
| voltage_level | varchar | |
| supply_phase | varchar |
meter
Grain: one row per physical meter (badge / serial)
IEC 61968 Meter — physical measuring device installed at a service point.
| Column | Type | Keys |
|---|---|---|
| meter_key | bigint | PK |
| meter_type_key | bigint | |
| service_point_key | bigint | |
| meter_id | varchar | BK |
| install_date | date | |
| removal_date | date | |
| current_status | varchar | |
| last_test_date | date | |
| manufacturer | varchar | |
| model | varchar | |
| multiplier | decimal |
meter_reading
Grain: one row per meter read (reading_id)
IEC 61968 MeterReading — sourced from AMI/MDMS or manual route reads.
| Column | Type | Keys |
|---|---|---|
| meter_reading_key | bigint | PK |
| meter_key | bigint | |
| read_status_key | bigint | |
| reading_id | varchar | BK |
| read_date | timestamp | |
| register_value | decimal | |
| prior_register_value | decimal | |
| consumption_kwh_or_unit | decimal | |
| is_billable | boolean | |
| billing_period_start | date | |
| billing_period_end | date | |
| recorded_by | varchar | PII |
| source | varchar |
service_agreement
Grain: one row per service agreement (agreement_id)
IEC 61968 ServiceAgreement — links customer, service point and tariff over time.
| Column | Type | Keys |
|---|---|---|
| service_agreement_key | bigint | PK |
| customer_account_key | bigint | |
| service_point_key | bigint | |
| rate_tariff_key | bigint | |
| service_class_key | bigint | |
| agreement_id | varchar | BK |
| effective_from | date | |
| effective_to | date | |
| agreement_status | varchar | |
| deposit_required | boolean | |
| contract_demand_kw | decimal | |
| is_net_metered | boolean |
bill
Grain: one row per bill statement (bill_id)
Bill header — one statement issued to a customer for a billing period.
| Column | Type | Keys |
|---|---|---|
| bill_key | bigint | PK |
| customer_account_key | bigint | |
| billing_cycle_key | bigint | |
| bill_id | varchar | BK |
| bill_date | date | |
| billing_period_start | date | |
| billing_period_end | date | |
| due_date | date | |
| total_amount | decimal | |
| current_charges | decimal | |
| previous_balance | decimal | |
| payments_applied | decimal | |
| taxes | decimal | |
| late_fees | decimal | |
| currency | varchar | |
| bill_status | varchar |
bill_line_item
Grain: one row per bill line (bill_line_id)
Bill detail — header/detail with the bill table.
| Column | Type | Keys |
|---|---|---|
| bill_line_item_key | bigint | PK |
| bill_key | bigint | |
| service_point_key | bigint | |
| rate_tariff_key | bigint | |
| service_class_key | bigint | |
| bill_line_id | varchar | BK |
| charge_type | varchar | |
| description | varchar | |
| quantity | decimal | |
| unit_rate | decimal | |
| amount | decimal | |
| currency | varchar |
payment
Grain: one row per payment transaction (payment_id)
Cashiering / payment posting against the bill.
| Column | Type | Keys |
|---|---|---|
| payment_key | bigint | PK |
| customer_account_key | bigint | |
| bill_key | bigint | |
| payment_method_key | bigint | |
| payment_id | varchar | BK |
| payment_date | timestamp | |
| amount | decimal | |
| currency | varchar | |
| payment_channel | varchar | |
| confirmation_number | varchar | |
| payment_status | varchar |
service_order
Grain: one row per service order (service_order_id)
Field service orders against a service point (move-in, disconnect, reread, ...).
| Column | Type | Keys |
|---|---|---|
| service_order_key | bigint | PK |
| customer_account_key | bigint | |
| service_point_key | bigint | |
| service_order_id | varchar | BK |
| order_type | varchar | |
| requested_date | date | |
| scheduled_date | date | |
| completed_date | date | |
| order_status | varchar | |
| technician_id | varchar |
Lookup tables (7)
utility_company
Grain: one row per utility operating company
Utility-of-record operating entity (per IEC 61968 OrganisationRole).
| Column | Type | Keys |
|---|---|---|
| utility_company_key | bigint | PK |
| utility_id | varchar | BK |
| utility_name | varchar | |
| country | varchar | |
| regulated_status | varchar | |
| regulatory_jurisdiction | varchar |
service_class
Grain: one row per utility commodity / service class
IEC 61968 ServiceCategory — the commodity sold at the service point.
| Column | Type | Keys |
|---|---|---|
| service_class_key | bigint | PK |
| service_class_id | varchar | BK |
| description | varchar | |
| regulated | boolean | |
| unit_of_measure | varchar |
rate_tariff
Grain: one row per rate tariff version
Rate / pricing scheme — tariff history retained via effective_from/to.
| Column | Type | Keys |
|---|---|---|
| rate_tariff_key | bigint | PK |
| service_class_key | bigint | |
| rate_tariff_id | varchar | BK |
| tariff_name | varchar | |
| rate_structure | varchar | |
| effective_from | date | |
| effective_to | date | |
| requires_demand_meter | boolean | |
| is_active | boolean |
meter_type
Grain: one row per meter model / technology
Meter catalog — AMI smart, AMR walk-by, or manual read.
| Column | Type | Keys |
|---|---|---|
| meter_type_key | bigint | PK |
| meter_type_id | varchar | BK |
| description | varchar | |
| technology_spec | varchar | |
| is_bidirectional | boolean | |
| read_interval_minutes | integer |
billing_cycle
Grain: one row per billing cycle / route
Read / billing schedule the account is assigned to.
| Column | Type | Keys |
|---|---|---|
| billing_cycle_key | bigint | PK |
| billing_cycle_id | varchar | BK |
| cycle_name | varchar | |
| read_day | integer | |
| billing_frequency | varchar | |
| cycle_group | varchar | |
| is_active | boolean |
payment_method
Grain: one row per supported payment method
Tender types accepted by the CIS cashiering subsystem.
| Column | Type | Keys |
|---|---|---|
| payment_method_key | bigint | PK |
| payment_method_id | varchar | BK |
| description | varchar | |
| processing_fee | decimal | |
| is_electronic | boolean |
read_status
Grain: one row per meter-read quality code
MDMS read-quality / status classification.
| Column | Type | Keys |
|---|---|---|
| read_status_key | bigint | PK |
| read_status_id | varchar | BK |
| description | varchar | |
| is_billable_quality | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- rate_tariff.service_class_key → service_class (many-to-one)
- customer_account.utility_company_key → utility_company (many-to-one)
- customer_account.billing_cycle_key → billing_cycle (many-to-one)
- service_point.customer_account_key → customer_account (many-to-one)
- service_point.service_address_key → service_address (many-to-one)
- service_point.service_class_key → service_class (many-to-one)
- service_point.rate_tariff_key → rate_tariff (many-to-one)
- meter.meter_type_key → meter_type (many-to-one)
- meter.service_point_key → service_point (many-to-one)
- meter_reading.meter_key → meter (many-to-one)
- meter_reading.read_status_key → read_status (many-to-one)
- service_agreement.customer_account_key → customer_account (many-to-one)
- service_agreement.service_point_key → service_point (many-to-one)
- service_agreement.rate_tariff_key → rate_tariff (many-to-one)
- service_agreement.service_class_key → service_class (many-to-one)
- bill.customer_account_key → customer_account (many-to-one)
- bill.billing_cycle_key → billing_cycle (many-to-one)
- bill_line_item.bill_key → bill (many-to-one)
- bill_line_item.service_point_key → service_point (many-to-one)
- bill_line_item.rate_tariff_key → rate_tariff (many-to-one)
- bill_line_item.service_class_key → service_class (many-to-one)
- payment.customer_account_key → customer_account (many-to-one)
- payment.bill_key → bill (many-to-one)
- payment.payment_method_key → payment_method (many-to-one)
- service_order.customer_account_key → customer_account (many-to-one)
- service_order.service_point_key → service_point (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.