An RK – a retained key – is a type of surrogate key (non-intelligent/artificial key) that remains the same (retained/persist/durable) when a record in a table is changed. RKs (e.g. party_rk) are generated based on a business key (e.g. party_id plus additional business attributes that define a unique instance of a party) and are usually part of a composite primary key in a table that tracks changes of over time. That other part of the composite key is typical a date/time based column. Let’s take a simplified example from health insurance using a party table: party_rk valid_from_dttm party_id party_type_cd valid_to_dttm Party_status_cd 1 01/01/2000 ABC Provider 01/31/2000 Non-certified 1 02/01/2000 ABC Provider Certified 2 01/01/2000 XYZ Member 02/28/2000 Current 2 03/01/2000 XYZ Member Past party_rk is the retained key party_rk + valid-from_dttm is the primary key party_id is a non-composite business key (business keys can be composites too, e.g. name + birthday + gender). Business keys are sometimes called natural keys. party_status_cd a column for which values have changed over time for an instance of a business key Whenever a value of a business key changes or a new one gets introduced a new RK will be generated. The reason why we don’t use business keys as part of the primary key is because they are often unstable: subject to change if business rules change; difficult to construct if they are sourced from multiple places (especially true for data warehouses); have a tendency to changes values over time and sometimes getting reused. In case of the insurance_policy table the policy_rk can be generated based on any unique combination of non-key attributes (excludes policy_rk and valid_from_dttm) that are candidates for a unique business key, and this can be different from customer to customer. For instance, policy_no could be a unique business key for one customer, and yet for another customer policy_no + policy_version might represent a unique policy identifier. RKs are frequently used as part of the primary key design in normalized data warehouse and ODS implementations that manage history. Hope this answers the question about use of RKs in SAS DDSs.
... View more