Entity: order, item, customer, event
Time: event, snapshot, window
Key: identifier or composite key
Exceptions: cancellation, refund, reopen, backfill
State what one row represents
An order table may have one row per order or per order item. Inventory may be transactions or daily snapshots. Similar names hide different grains, and aggregation or joins can double count.
Write a readable sentence such as “one end-of-day inventory row per store, product, and date.”
Entity, time, and key define grain together
A primary key alone is insufficient. Events repeat for one object, snapshots repeat across dates, and cumulative tables add a window.
Document business and technical keys, time fields, deduplication, and representations of cancellations, refunds, reopening, and backfill.
Compare cardinality before joining
Summing the one-side amount after a one-to-many join inflates it. Many-to-many joins need a bridge or pre-aggregation. Without cardinality and allowed paths, AI can generate valid but wrong SQL.
Label each relation 1:1, 1:N, or N:M with keys, filters, and effective time.
Put grain into tests and semantics
Test key uniqueness, nulls, duplicate ratio, daily row counts, and rows before and after joins. Metric definitions should state calculation grain and valid dimensions.
BuildTable’s AI-friendly modeling direction can carry this metadata; exact contract, test, and publishing capabilities need project confirmation.
Accept with counterexamples
Use orders with multiple items, partial refunds, cross-day state changes, and customers across stores. Reconcile order count, item quantity, and revenue manually.
Acceptance checks not only that SQL runs, but that the query selects the correct grain, relationship, and deduplication and can explain why.
Public references
Build an AI-ready data foundation
Download BuildTable or talk with us about your data modeling scenario.
Download BuildTable