Skip to content
Back to challenges
Grain and KeysConcept Challengeintermediate20 min

Invoice-line grain

Design a billing fact where invoice number remains queryable on each invoice line without becoming its own low-value dimension.

How this preview works
Preview this problem before signing in. Sign in to save progress and submit work.

Concept

degenerate-dimensions

The primary modeling idea this problem reinforces.

Requirements

4

Business needs the model must satisfy.

Scenario

A SaaS finance team needs monthly recurring revenue, credits, taxes, and usage overages by account and plan. One invoice can contain several line items, and support needs to search by invoice number when customers dispute charges.

Why this matters

Billing facts often fail because the modeler chooses invoice-header grain and hides the charge-level details. Keeping invoice number as a degenerate dimension lets analysts reconcile to finance systems without inventing a dimension that has no descriptive attributes.

Requirements
  • Create an invoice-line fact table with one row per billable invoice line.
  • Keep invoice number and line number on the fact as degenerate dimensions.
  • Add invoice date and line amount fields.
  • Join invoice lines to account and plan dimensions through surrogate keys.
Read-only starter workspace
Inspect the authored starting ERD, then open the workspace to edit and run the checks. Saving your progress needs a free account.

Template

Subscription billing starter workspace

The model users start from in the full workspace.

Entities

2

Seeded tables visible in the public preview.

Dialect

postgresql

Generated SQL target for this starter model.

Mini Map

This preview is read-only. Open the workspace to edit and verify; saving needs a free account.

Open workspace

Try the question first.

The discussion has other people's approaches and solutions. Give it a real attempt before you read them.