Allocating Shared Database Cost with Tenant Keys

This page solves the hardest number in a chargeback pipeline: splitting the single bill of a shared elastic pool, Aurora cluster, or multi-tenant instance across the tenants that share it, using a usage-share weight per tenant and proving the parts sum back to the pool total to the cent.

Back to: Chargeback Reporting Automation

A dedicated database is trivially attributable — one invoice line, one owner. A shared resource is not: the provider bills the pool as one meter and gives you no per-tenant breakdown, so the split has to be manufactured from a usage signal you can defend. This page builds that allocation as a pure function of a pool total and a table of tenant weights, then persists the result in an idempotent allocation table keyed so a re-run overwrites rather than double-counts. The output is the per-tenant row set that generating monthly chargeback reports with pandas turns into statements. The weights come from usage telemetry that has passed schema validation for billing data, and the allocation totals it produces are what database quota boundary design calibrates real per-tenant budgets against.

The allocation model

Each tenant i receives a share of the pool cost C_pool proportional to its usage weight w_i against the sum of all tenants’ weights in the same closed window:

$$a_i = C_{pool} \cdot \frac{w_i}{\sum_{j} w_j}$$

The weight w_i is a single scalar per tenant per period, derived from whichever usage signal best tracks what the pool actually bills for:

$$w_i = \sum_{t \in \text{period}} u_{i,t}$$

where u is consumed vCore-seconds, DTU-seconds, logical bytes stored, or I/O operations, summed over the period. The invariant that makes the allocation defensible is exact reconciliation — after rounding, the parts must equal the whole:

$$\sum_{i} a_i = C_{pool}$$

Floating-point division cannot guarantee that last equality, which is why the implementation uses Decimal with deterministic residual placement.

Prerequisites

  • Python: 3.10 or newer.

  • Libraries: just the standard library decimal for the math; a database driver (psycopg[binary]) for the idempotent allocation table.

    pip install "psycopg[binary]>=3.1"

  • Inputs: the closed-period pool cost as a Decimal (from your validated, normalized cost store), and a per-tenant usage table for the same window. Where the pool spans providers, the pool cost must already be in one currency via multi-cloud cost normalization.

  • Usage source: vCore-seconds and DTU-seconds come from sys.dm_db_resource_stats (Azure SQL) or sys.elastic_pool_resource_stats; per-schema bytes come from pg_database_size or pg_total_relation_size. The read credential is scoped under security and access control for cost data.

Step-by-Step Implementation

The pipeline is four steps: build the weight vector, allocate with exact reconciliation, persist idempotently, then assert the reconciliation invariant.

Step 1 — Build the tenant weight vector

Sum each tenant’s usage over the closed window into one Decimal weight. The choice of signal matters: use vCore-seconds or DTU-seconds when the pool bills primarily for compute, logical bytes when it bills for storage. Mixing signals in one weight is a category error — pick the one the pool’s dominant meter tracks.

from decimal import Decimal
from collections import defaultdict

def build_weights(usage_rows: list[dict], signal: str) -> dict[str, Decimal]:
    """Sum one usage signal per tenant over the closed period."""
    weights: dict[str, Decimal] = defaultdict(lambda: Decimal("0"))
    for row in usage_rows:
        # e.g. signal="vcore_seconds" -> row["vcore_seconds"]
        weights[row["tenant_id"]] += Decimal(str(row[signal]))
    return dict(weights)

# Expected for a three-tenant pool measured in vCore-seconds:
#   >>> build_weights(rows, "vcore_seconds")
#   {'tenant-acme': Decimal('54000'),
#    'tenant-beta': Decimal('18000'),
#    'tenant-gamma': Decimal('9000')}

Step 2 — Allocate with exact reconciliation

The allocation is the formula above, computed in Decimal, with the rounding residual distributed by the largest-remainder method so the parts sum to the pool total exactly. A pool with zero total usage is an error, not a division — raise rather than produce NaN.

from decimal import ROUND_DOWN

CENT = Decimal("0.01")

def allocate_pool(pool_total: Decimal, weights: dict[str, Decimal]) -> dict[str, Decimal]:
    """Apportion pool_total by weight; the parts sum EXACTLY to pool_total."""
    total_weight = sum(weights.values(), Decimal("0"))
    if total_weight == 0:
        raise ValueError("pool has zero total usage weight; cannot allocate")

    # Exact proportional shares, then floor each to the cent.
    exact = {t: pool_total * w / total_weight for t, w in weights.items()}
    floored = {t: v.quantize(CENT, rounding=ROUND_DOWN) for t, v in exact.items()}

    # Distribute the leftover cents to the largest fractional remainders,
    # breaking ties by tenant_id so the result is fully deterministic.
    residual = pool_total - sum(floored.values(), Decimal("0"))
    steps = int((residual / CENT).to_integral_value())
    order = sorted(weights, key=lambda t: (exact[t] - floored[t], t), reverse=True)
    for t in order[:steps]:
        floored[t] += CENT
    return floored

# Expected for a $1,000.00 pool split 6:2:1 by vCore-seconds:
#   >>> allocate_pool(Decimal("1000.00"),
#   ...     {"tenant-acme": Decimal("54000"),
#   ...      "tenant-beta": Decimal("18000"),
#   ...      "tenant-gamma": Decimal("9000")})
#   {'tenant-acme': Decimal('666.67'),
#    'tenant-beta': Decimal('222.22'),
#    'tenant-gamma': Decimal('111.11')}
#   sum == Decimal('1000.00')

Step 3 — Persist to an idempotent allocation table

The allocation table is keyed on (pool_id, tenant_id, billing_period) so re-running a period overwrites its rows rather than appending duplicates. An ON CONFLICT ... DO UPDATE upsert makes the write idempotent — the same property the whole chargeback pipeline depends on.

CREATE TABLE IF NOT EXISTS cost_allocation (
    pool_id        TEXT           NOT NULL,
    tenant_id      TEXT           NOT NULL,
    billing_period TEXT           NOT NULL,      -- e.g. '2026-06'
    signal         TEXT           NOT NULL,      -- 'vcore_seconds' | 'dtu_seconds' | 'bytes'
    weight         NUMERIC        NOT NULL,
    allocated_cost NUMERIC(18, 2) NOT NULL,
    run_signature  TEXT           NOT NULL,
    PRIMARY KEY (pool_id, tenant_id, billing_period)
);

import psycopg

def persist_allocation(conn: psycopg.Connection, pool_id: str, period: str,
                       signal: str, weights: dict[str, Decimal],
                       alloc: dict[str, Decimal], run_sig: str) -> int:
    """Upsert allocation rows; a re-run replaces the period, never duplicates it."""
    rows = [
        (pool_id, t, period, signal, weights[t], alloc[t], run_sig)
        for t in sorted(alloc)                      # stable insert order
    ]
    with conn.cursor() as cur:
        cur.executemany(
            """
            INSERT INTO cost_allocation
                (pool_id, tenant_id, billing_period, signal, weight,
                 allocated_cost, run_signature)
            VALUES (%s, %s, %s, %s, %s, %s, %s)
            ON CONFLICT (pool_id, tenant_id, billing_period)
            DO UPDATE SET signal = EXCLUDED.signal,
                          weight = EXCLUDED.weight,
                          allocated_cost = EXCLUDED.allocated_cost,
                          run_signature = EXCLUDED.run_signature
            """,
            rows,
        )
    conn.commit()
    return len(rows)

# Expected:
#   >>> persist_allocation(conn, "pool-eu-1", "2026-06", "vcore_seconds",
#   ...                     weights, alloc, "a1b2c3d4e5f60718")
#   3                                # 3 tenant rows upserted; a re-run also returns 3

Step 4 — Assert the reconciliation invariant

Before the allocation feeds any statement, assert the persisted rows sum back to the pool total. This is the guard that makes the whole chargeback defensible — it must be an exact Decimal equality, not a tolerance.

def assert_reconciled(conn: psycopg.Connection, pool_id: str, period: str,
                      pool_total: Decimal) -> None:
    """The sum of persisted allocations must equal the pool total exactly."""
    with conn.cursor() as cur:
        cur.execute(
            "SELECT COALESCE(SUM(allocated_cost), 0) FROM cost_allocation "
            "WHERE pool_id = %s AND billing_period = %s",
            (pool_id, period),
        )
        allocated = Decimal(cur.fetchone()[0])
    if allocated != pool_total:
        raise AssertionError(
            f"reconciliation failed for {pool_id}/{period}: "
            f"allocated {allocated} != pool {pool_total}"
        )

# Expected on a correct run:
#   >>> assert_reconciled(conn, "pool-eu-1", "2026-06", Decimal("1000.00"))
#   (no output; allocations sum exactly to the pool total)

Verification

Run the full path end to end and confirm the invariant holds, including the edge case of a single-tenant pool.

def verify() -> None:
    pool_total = Decimal("1000.00")
    weights = {"tenant-acme": Decimal("54000"),
               "tenant-beta": Decimal("18000"),
               "tenant-gamma": Decimal("9000")}
    alloc = allocate_pool(pool_total, weights)
    assert sum(alloc.values(), Decimal("0")) == pool_total     # exact reconciliation
    assert all(v >= 0 for v in alloc.values())                 # no negative shares
    # A single-tenant pool gets the whole total, no residual lost.
    solo = allocate_pool(Decimal("47.03"), {"only": Decimal("5")})
    assert solo == {"only": Decimal("47.03")}

# Expected:
#   (no output; every assertion passes)

A SQL cross-check confirms the persisted table reconciles per pool:

SELECT pool_id, billing_period, SUM(allocated_cost) AS allocated
FROM cost_allocation
WHERE billing_period = '2026-06'
GROUP BY pool_id, billing_period;

 pool_id   | billing_period | allocated
-----------+----------------+-----------
 pool-eu-1 | 2026-06        |   1000.00

Gotchas & Edge Cases

  • Floating-point weights break reconciliation. A weight or share computed in float reintroduces the drift the largest-remainder method exists to eliminate; the parts will miss the pool total by a cent or two. Keep weights and money in Decimal from the source query onward.
  • Zero total usage. A pool that ran with no attributable usage (fully idle, or a telemetry gap) has total_weight == 0. Dividing produces NaN; the code raises instead so the run fails loudly rather than emitting garbage. Decide policy explicitly — usually charge the pool owner, not a tenant.
  • Idle-but-provisioned tenants. A tenant with zero usage in the period gets a zero weight and thus zero allocated cost — but it still occupies capacity. If your model charges for provisioned share rather than consumed share, add a baseline floor to each active tenant’s weight before allocating, or the idle tenant pays nothing while inflating everyone else’s share.
  • Signal mismatch. Weighting a compute-billed pool by stored bytes over-charges storage-heavy tenants and under-charges compute-heavy ones. Match the weight signal to the pool’s dominant meter; where a pool bills materially for both, allocate compute and storage separately with two weight vectors and sum the results.
  • Storage lags compute. Byte-based weights sampled once at period end miss intra-month growth. Average several snapshots, or integrate pg_database_size over the window, so a tenant that grew mid-month is not weighted only by its end-of-month footprint.
  • Restated pool total. If the provider restates the pool cost after allocation, re-running with the new total upserts corrected rows via the idempotent key — but any statement already sent must carry the correction as an adjustment line next period, never a silent rewrite. This is the same closed-period discipline the parent chargeback workflow enforces, and the allocation totals feed straight into tenant budget boundaries.

Frequently Asked Questions

Which usage signal should weight the allocation?

The one the pool’s dominant meter bills for. An Azure SQL elastic pool billed by eDTU or vCore is best weighted by consumed DTU-seconds or vCore-seconds; a storage-dominated pool by logical bytes. When a pool bills materially for both compute and storage, split the pool cost into a compute portion and a storage portion and allocate each with its own weight vector, then sum per tenant.

How do I guarantee the allocations sum to the pool total exactly?

Compute in Decimal, floor each share to the cent, then distribute the leftover cents to the tenants with the largest fractional remainders, breaking ties deterministically by tenant id. That largest-remainder method places every residual cent, so the sum equals the pool total exactly rather than approximately — which is what lets the reconciliation check be an equality, not a tolerance.

What makes the allocation table idempotent?

The primary key on (pool_id, tenant_id, billing_period) plus an ON CONFLICT ... DO UPDATE upsert. Re-running a period updates the existing rows in place instead of appending, so a retried or replayed run produces the same table state. That property is what lets you safely re-allocate a restated period without double-counting.

How do I handle a tenant with zero usage?

By default it receives a zero weight and zero allocated cost, which is correct for a pure consumption model. If your policy charges for reserved capacity rather than usage, add a baseline floor to each active tenant’s weight before allocating so an idle-but-provisioned tenant still carries its share of the fixed cost.

Where do the per-tenant usage numbers come from?

From engine telemetry captured over the closed window: sys.dm_db_resource_stats and sys.elastic_pool_resource_stats for vCore-seconds and DTU-seconds on Azure SQL, and pg_database_size or pg_total_relation_size for per-schema bytes on PostgreSQL. Those metrics pass schema validation before they weight anything, so a malformed usage row is quarantined rather than skewing an allocation.

Back to: Chargeback Reporting Automation