So I have a dataset spread over multiple and ever-growing excel files all of which looks like:

email order_ID order_date
[email protected] 1234 23-Mar-2021
[email protected] 1235 23-Mar-2021
[email protected] 1236 23-Mar-2021
[email protected] 1237 24-Mar-2021
[email protected] 1238 28-Mar-2021

End goal is to have two distinct datasets as:

First one being Orders: (Public. For analysis, trading emails with user_IDs for anonymity and marking returning for further analyses)

user_ID order_ID order_date is_returning?
1 1234 23-Mar-2021 0
2 1235 23-Mar-2021 0
2 1236 23-Mar-2021 1
1 1237 24-Mar-2021 1
3 1238 28-Mar-2021 0

Second one being: Users (Private for retaining users_info. Have other columns besides email as well but idea is the same)

In pandas, I have following procedure to go about:

  1. Read all files:

     input_directory = 'Data/'
     files =  os.listdir(input_directory) 
  2. Combine them:

     all_data = pd.DataFrame()
     for df in data_dict.values():
     all_data = all_data.drop_duplicates()
  3. Assign user IDs:

     all_data['user_id'] = all_data['email'].factorize()[0]
  4. Assign returning flag:

     all_data['is_returning'] = all_data.user_id.duplicated().astype(int) 
  5. Pushing both to BQ:

     columns = #list of private columns
     all_data.drop(columns, axis = columns).to_gbq(#parameters, table = `public_dataset.orders`)
     all_data[columns].to_gbq(#parameters, table = `private_dataset.users_db`)

Since files are continually coming, I have to reassign user_IDs again and again. (Because searching a corresponding email for each row in customers db is way more expensive than simply reassigning IDs. All analyses are anonymous and based on total numbers so it wont hurt as well. Also mantaining a local DB is also not preferrable as data is incoming in an excel format) I can have a script to push data once a file is come for only that file but cannot because of

  1. Not being able to emulate pd.factorize()
  2. And dupicated() on BigQuery
  3. Not knowing in advance what rows are already there since files may have duplicated records as well,

I have to do this again and again. So how can I transition this whole pipeline to BQ effectively and cost-efficiently? Any help regarding bettering the situation is highly appreciated


1 Answer 1


Possible Solution:

Assign a UUID to each email/user instead of an auto-increment ID (first making sure email does not already exist else use the existing UUID).

UUIDs can be created anywhere - not necessarily in DB - and are almost guaranteed to be unique accross time and space.

For example, the number of random version-4 UUIDs which need to be generated in order to have a 50% probability of at least one collision is 2.71 quintillion, computed as follows:

$${\displaystyle n\approx {\frac {1}{2}}+{\sqrt {{\frac{1}{4}}+2\times \ln(2)\times 2^{122}}}\approx 2.71\times 10^{18}.}$$

This number is equivalent to generating 1 billion UUIDs per second for about 85 years. A file containing this many UUIDs, at 16 bytes per UUID, would be about 45 exabytes.

Note: User Email can also be used as unique ID and makes everything easier.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.