Forum Discussion

arpost's avatar
arpost
Icon for Post Prodigy rankPost Prodigy
2 years ago

Are GUIDs a viable substitute for integer Identities/Primary Keys?

Greetings, community. In the world of Fabric data warehouses, there is still no way to truly create int/bigint IDENTITY columns. Some of the proposals I've seen include:   Use Row_Number() as your...
Data Integration
data warehouse
arpost's avatar
arpost
Icon for Post Prodigy rankPost Prodigy
2 years ago

Thanks for the reply, AndyDDC. Regarding OneLake not being supported, didn't know that! Can you point me to a doc listing that limitation?

 

Regarding ROW_NUMBER() and parallel load issues, here's a fake example of our parallel loading situation. It's actually even more streamlined than our actual use case will likely end up being.

 

You have 15 clients sending data for multiple file types with different column structures but similar data:

  1. Type A: Needs to insert into three tables (Customer, Account, Order) and there are key dependencies (e.g., Account -> Customer, Order -> Account).
  2. Type B: Needs to insert into three tables (Customer, Account, Payments) and there are key dependencies (e.g., Account -> Customer, Payments-> Account).

For key assignments in Fabric, you'd need to insert into your Customer table, assign a CustomerKey, then insert into your Account table and match to a CustomerKey, assign an AccountKey, and then finish with the insert into Orders or Payments with a match to an AccountKey and assign an OrderKey/PaymentKey.

 

To achieve this in Fabric with ROW_NUMBER(), you're limited to a few options, none of which are that great:

 

OptionSummaryComment
1. Client Sequential

Run each client's files (Types A-B) through before starting next client.

Totally inefficient and makes each client feed AND file type dependent on other clients'.
2. Type SequentialRun each client's Type A files through one-by-one and then start Type B.Similarly inefficient for each client as in #1, but this at least "checks in" Type A data before starting Type B load. 
3. Client Interspersed

Run all clients' Type A files through, pausing until keys can be assigned, before running Type B.

Requires "pausing" clients' jobs at each key asssignment (CustomerKey, AccountKey, OrderKey/PaymentKey) to make sure no concurrent key assignments take place.

 

A process that you'd normally be able to run fully in parallel now has to get stretched out by several minutes if not hours. Of the choices, #3 is the "best", but that requires a hefty amount of interdependent pipeline logic and planning.