Transitioning from a python script for data transformation to BigQuery So I have a dataset spread over multiple and ever-growing excel files all of which looks like:




email
order_ID
order_date




user1@email.com
1234
23-Mar-2021


user2@email.com
1235
23-Mar-2021


user2@email.com
1236
23-Mar-2021


user1@email.com
1237
24-Mar-2021


user3@email.com
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)




user_ID
email




1
user1@email.com


2
user2@email.com


2
user2@email.com


1
user1@email.com


3
user3@email.com




In pandas, I have following procedure to go about:

Read all files:
 input_directory = 'Data/'
 files =  os.listdir(input_directory) 
 files


Combine them:
 all_data = pd.DataFrame()
 for df in data_dict.values():
     all_data=pd.concat([all_data,df]).drop_duplicates().reset_index(drop=True)
 all_data = all_data.drop_duplicates()


Assign user IDs:
 all_data['user_id'] = all_data['email'].factorize()[0]


Assign returning flag:
 all_data['is_returning'] = all_data.user_id.duplicated().astype(int) 


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

Not being able to emulate pd.factorize()
And dupicated() on BigQuery
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
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.