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
AndyDDC's avatar
AndyDDC
Icon for Most Valuable Professional rankMost Valuable Professional
2 years ago

Well one consideration is that direct lake isn't supported when using GUIDs so that may affect your choice.  I haven't had issues with using rownumber, mostly because I don't do any parallel loading when inserting into dimensions. And with fact tables I am doing parallel loading but they are just looking up existing keys in dim tables.

 

 

  • 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.

    • AndyDDC's avatar
      AndyDDC
      Icon for Most Valuable Professional rankMost Valuable Professional
      2 years ago

      Hi arpost I've been rolling this around my head for a few days, I think with ROW_NUMBER it won't be possible (as you've found) to fully parallelise loading.  I did think about a control table where a load could insert a "start" and "end" value, where start is the last end value inserted + 1.  Then "end" for that partcular batch would be the rowcount that would be inserted into the destination table.  Then ROW_NUMBER could be used with start.  Problem is you'd never know if 2 loads hit the table at the same time and grabbed the same "end" number.

       

      I've been testing GUIDs and a NEWID value converted to varchar takes up 36 bytes, rather than int which takes up 4 bytes (2 in my screenshot as i'm using a smallint).  If a SELECT NEWID (newsequentialid not supported) is inserted into a varchar column, then direct lake would work.  In terms of space, this obviously takes up more space but the overall footprint would depend on how unique the dimensions are.  E.G I added a NEWID to a dimension table with 400 rows in and it increated the underlying parquet file size of the dimension from 11KB to 25KB (hardly anything to write home about).  I then tested with a fact table (1.1 billion rows) and added the newid value from the dimension table, it took the overall fact table (which is just a bunch of INTs) from 16MB to 24MB.  Again nothing to massive.

       

       

      So I guess it's going to be a case of seeing whether using NEWID in a varchar column is going to have much of a size and performance impact with your data.  INTs will always be faster, but perhaps in your case it may be neglible performance.

       

      I can't see IDENTITY/SEQUENCE on the roadmap for Fabric Warehouses either, but it's a requested feature so I can see it being available at some point in 2024/25.

       

      What's new and planned for Synapse Data Warehouse in Microsoft Fabric - Microsoft Fabric | Microsoft Learn

      • v-cboorla-msft's avatar
        v-cboorla-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        2 years ago

        Hi arpost 

         

        We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others.
        Otherwise, will respond back with the more details and we will try to help.

         

        Thanks.