Warehouse Business Keys

Business Keys should be unique identifiers for business entities that are meaningful outside of the context of the warehouse. A business user should be able to extract a basic understanding of the entity from looking at the key.

Keys are hashed in the warehouse in order to enable parallel load processes independent of the existence of other data.

Arivohub

Application

Application Hash Key is an MD5 computation of the following string concatenation. In the future, if applications come from another source, the AH can be replaced with that source indicator. e.g. 202102TX-AH402032 In the future, including the dealer state as a natural attribute of the application may be helpful in allowing independent hashes here.
year || month || COALESCE(defState, dealerState, '') || '-AH' || applicationId as hashId

Borrowers

Borrower Hash Keys are an MD5 computation of the first four letters of the borrowers last name, padded with following zeros, followed by the surrogate of the borrower with the type represented by two characters at the end, e.g. REIL123456CO, CARR390565PR

RPAD(SUBSTRING(lastName, 0, 4), 4, '0') || borrowerId || UPPER(SUBSTRING(type, 0, 2)) as hashId

Dealers

Dealers are represented with the state followed by the surrogate key in the source system. All surrogate keys come from Arivohub, not DeFi. DeFi dealerships that are not in arivohub are not represented in the warehouse. This key also uses an MD5 hash.
state || '-' || dealerId as hashId

Dealer Reps

Dealer reps are simply an MD5 hash of the dealer representatives email address with Arivo Acceptance.

Connect

All connect business keys are simple identifiers from Amazon. These identifiers are unique enough and contain enough contextual information that it is useful outside the context of the warehouse. Nothing fancy was created here. In the future, if the need arises, a different system can be developed. Unless specified, all keys are MD5 hashed.

Agents

The Agent ARN in AWS

Contacts

The Contact Id in AWS Connect

Agent Events

The Event Id on AWS Connect

Queues

The Queue ARN in AWS

Customer Endpoints

The phone number for the customer, including the leading '+'