BigQuery update multi tables

Question

i'm holding huge transactions data on daily multi tables according the business date. trascation_20140101 trascation_20140102 trascation_20140103..

the process flow is like that: 1.i''m loading the batch of new files that that arrive to temp table 2.i group by the transcation_date field in order to notice on which date is belong - for each date i query the temp table on this date and insert it to the proper trasaction_YYYYMMDD table. 3.i'm doing part 2 in parallel in order to save time, because the temp table might contain data that belong to 20 days..

my challenge is what to do if one these process failed and other not.. i can't run it all again , since it will cause for duplications for the table that been already successfully update.

i solve these issue by managing this update, but it's seems to be too complex.

Is this best practice to deal with multi tables? i will be glad to get some best practice in order to understand how others deals when they need to load the data to multi tables according to business date and Not just insert date(this is easy..)

Answer 1

You could add an extra step in the middle, where instead of moving directly from today's temp table into the permanent business-date tables, you extract into temporary daily tables and then copy the data over to the permanent tables.

1. Query from today's temp table, sharded by day into tmp_transaction_YYMMDD. Use WRITE_EMPTY or WRITE_TRUNCATE write disposition so that this step is idempotent.
2. Verify that all expected tmp_transaction_YYMMDD tables exist. If not, debug failures and go back to step 1.
3. Run parallel copy jobs from each tmp_transaction_YYMMDD table to append to the corresponding permanent transaction_YYMMDD table.
4. Verify copy jobs succeeded. If not, retry the individual failures from step 3.
5. Delete the tmp_transaction_YYMMDD tables.

The advantage of this is that you can catch query errors before affecting any of the end destination tables, then copy over all the added data at once. You may still have the same issue if the copy jobs fail, but they should be easier to debug and retry individually.

Answer 2

Our incentive for incremental load is cost, and therefore we interested in "touching each record only once". We use table decorators to identify increment. We manage the increments timestamps independently, and add them to the query on run-time. It requires some logic to maintain, but nothing too complicated.