Surrogate Key Basics | Dataford Interview Questions

What is a surrogate key, and when would you use one?

Using the supplied customer dimension table, write a PostgreSQL query that returns the current records and demonstrates the surrogate identifier alongside the natural customer identifier.

Output

One row per current customer record.
Columns: surrogate_key, customer_code, customer_name, and source_system.
Exclude non-current records and order by customer_name ascending, then surrogate_key ascending.

Schema

customer_dimension

Column	Type	Description
surrogate_keyPK	INTEGER	Warehouse-generated identifier for the dimension record
customer_code	VARCHAR(20)	Natural customer identifier from the source domain
customer_name	VARCHAR(100)	Customer display name
source_system	VARCHAR(40)	System that supplied the customer record
is_current	BOOLEAN	Whether the record is the current version

Your question is Surrogate Key Basics. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem