Transitioning from a python script for data transformation to BigQuery

Question

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)

user_ID	email
1	[email protected]
2	[email protected]
2	[email protected]
1	[email protected]
3	[email protected]

In pandas, I have following procedure to go about:

1. Read all files:

    input_directory = 'Data/'
    files =  os.listdir(input_directory) 
    files
   

2. 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()
   

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

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.