How should reference data be modeled and governed in Clarity (for example gender, country codes)?

How should reference data be modeled and governed in Clarity (for example gender, country codes)?

• A. Store reference values in each fact table
• B. Randomize codes per table
• C. Use free-text fields for codes
• D. Centralized reference tables with stable keys, code sets, versioning, with controlled mapping from source systems and clear semantics

Answer: (D)

The main idea being tested is how to treat reference data as a managed, single source of truth rather than as scattered, ad-hoc values. By using centralized reference tables with stable keys and defined code sets, you establish a single authoritative place for codes like gender or country, and each fact record can point to that shared code through a foreign key. Stable keys (often surrogate keys) ensure that codes remain consistent even if their labels change in the source systems. Versioning lets you track changes to code sets over time, so historical data can be interpreted correctly according to the reference data state at the time the fact was recorded. Clear semantics and controlled mappings from source systems prevent ambiguity about what each code represents, making governance, auditing, and cross-system reporting reliable. This approach avoids duplicating codes in every fact table, which would lead to inconsistencies and maintenance headaches. It also prevents randomizing codes per table, which would destroy comparability, and it rejects free-text codes, which make validation, joins, and aggregations error-prone.

The main idea being tested is how to treat reference data as a managed, single source of truth rather than as scattered, ad-hoc values. By using centralized reference tables with stable keys and defined code sets, you establish a single authoritative place for codes like gender or country, and each fact record can point to that shared code through a foreign key. Stable keys (often surrogate keys) ensure that codes remain consistent even if their labels change in the source systems. Versioning lets you track changes to code sets over time, so historical data can be interpreted correctly according to the reference data state at the time the fact was recorded. Clear semantics and controlled mappings from source systems prevent ambiguity about what each code represents, making governance, auditing, and cross-system reporting reliable.

This approach avoids duplicating codes in every fact table, which would lead to inconsistencies and maintenance headaches. It also prevents randomizing codes per table, which would destroy comparability, and it rejects free-text codes, which make validation, joins, and aggregations error-prone.