Mini-dimension pattern

Core dimension: stable identity

Mini-dimension: governed profile combination

Fact: customer key plus profile key

Historical view: state at event time

Current view: separate versioned mapping

Do not duplicate an entire customer row for every score change

Tier, activity band, risk segment, lifecycle stage, and price sensitivity may be recomputed daily. Putting all of them into a type-2 customer dimension can create a new full row whenever one value changes, producing far more dimension rows than customers.

A mini-dimension isolates frequently changing, enumerable profile attributes. The core customer dimension retains stable identity, while each event fact stores both customer_key and profile_key. Historical analysis then sees the profile at the event without repeatedly copying stable attributes.

Classify attributes by velocity, cardinality, and analytical need

A candidate should change materially faster than the entity, have governed values, have controllable combinations, and be needed as event-time state. Age band and activity tier may qualify; address text, device ID, and unrestricted tags usually do not.

Rapid change alone is insufficient. Exact balances belong in periodic snapshots or facts, while age can be derived from birth date and event date. Sensitive inferred attributes require a legal, necessity, access, and retention review before modeling.

Give the core and mini-dimensions distinct responsibilities

dim_customer contains the durable business key, surrogate key, creation date, and slow attributes. dim_customer_profile contains a profile key, controlled buckets, a combination fingerprint, rule version, and effective metadata. The fact carries both keys at its declared grain.

Keeping only customer_key rewrites history through the current profile. Keeping only profile_key loses the customer entity. The two keys represent identity and event-time state; they are not competing versions of the same dimension.

Version bucket definitions instead of silently changing meaning

If high value changes from 5,000 over 90 days to 8,000, the label has acquired a new meaning. Retain definition_version, thresholds, currency, observation window, timezone, effective date, and approval. Do not update old profile rows in place.

Distinguish as-was analysis under the historical rule from restated history under today’s rule. The latter requires a reproducible reclassification and an explicit version. Both views can be useful, but they must never be mixed without disclosure.

Control combination growth

Do not prebuild the Cartesian product of every attribute value. Upsert only combinations that occur, using a normalized fingerprint, then monitor total combinations, daily additions, changes per customer, and the long tail.

Explosive growth often reveals free text, excessive numeric precision, or inconsistent null encoding. Separate mini-dimensions only when refresh cadence and query patterns justify extra foreign keys. High-cardinality measures are generally better retained in facts.

Resolve the profile at event time

During fact loading, resolve customer identity and select the profile using business event time, not job arrival time. If profiles exist only daily, state whether events use start-of-day, end-of-day, or the previous complete day.

Late facts need the historical profile. Late profile changes may require controlled restatement. Record original and reclassified profile keys, reason, run, and approval; a silent bulk overwrite is not reproducible history.

Model current-state questions separately

What current premium customers bought last year differs from what customers classified as premium at purchase bought. Maintain a customer-to-current-profile mapping or current profile key, while semantic views explicitly select current or as_was logic.

A common counterexample joins every historical event through today’s segment. Trends then change after every daily scoring run. Metric names, rule versions, and watermarks should make the chosen view visible.

Govern unknown and sensitive states

Use controlled members such as Unknown, Not calculated, and Not applicable. Missing source data, an unrun model, and true inapplicability have different quality meanings and should not collapse into Other.

A profile surrogate can still reveal sensitive inference. Apply column access, minimum-group policy, retention, deletion propagation, and monitoring to the derived key as well as the descriptive columns.

Accept with conservation, growth, and replay tests

Verify fact counts and amounts are conserved, every fact resolves exactly one event-time profile, fingerprints are unique, rule versions are traceable, late facts replay, and current and historical views differ intentionally.

Test high-velocity customers, unchanged customers, rule changes, cross-midnight events, late arrival, deletion, and a contaminated high-cardinality field. Replay a meaningful period and inspect combination growth rather than approving a few clean rows.

BuildTable.ai boundary

Pilot one profile domain: inventory attributes and sensitivity, freeze bucket rules, build the dual-key fact, replay history, and publish explicit current and as-was semantic views. Monitor growth, unknowns, restatements, and query misuse.

BuildTable.ai may be evaluated for model and result-table construction. This article does not establish automatic detection, mini-dimension generation, sensitive-profile governance, or replay support in the current product; connectors, orchestration, permissions, and publishing must be verified in the target environment.

Customer segments change every day. Should every change create a new SCD2 row?