How to create table with incremental load in BIG QUERY

Question

I have a table EMP, which will be WRITE_TRUNCATE every day. the truncated data is maintained in a historical table called emp_hx along with the record incoming date. like as follows

Actual and Historical data

But due to the space issues we want to maintain only the changed records or new records in the "hx" table, which is like a incremental load kind of table. how to Implement such kind of table or how to select such kind of incremental data in BIG QUERY,

Please guide us.

Expected output like as follows

EXPECTED HX TABLE

score 0 · Answer 1 · answered Dec 08 '18 at 19:18

0

You seem to want:

select min(created_time) as created_time, empid, empname, sal
from emp_hx
group by empid, empname, sal;

answered Dec 08 '18 at 19:18

Gordon Linoff

1,242,037
58
646
786

Hi Gordon Thanks for reply, I want to create hx table with incremental load kind of logic. I don't want to pull data from hx table. Kindly assist me on this – kalyan4uonly Dec 09 '18 at 03:36
@kalyan4uonly - Did you figure out a logic to do this ? I am also looking for a logic to solve this problem – cheapcoder Mar 02 '22 at 00:56

score 0 · Answer 2 · answered Mar 10 '20 at 08:19

0

you can use the row_nummber() function to pull only the most updated row by the created_time

answered Mar 10 '20 at 08:19

Tal Sibony

31
1
3

At the very least, I expect the function to be named `row_number()`. – Tanveer Badar Mar 10 '20 at 08:44

score 0 · Answer 3 · answered Mar 18 '22 at 07:35

BigQuery merge commands can do exactly what is asked Google BQ - how to upsert existing data in tables? Nowadays all the modern data lakes support upserting data using merge commands . To check the syntax you can refer merge commands section of the BigQuery : https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax

How to create table with incremental load in BIG QUERY

3 Answers3