Argthtjtr

I have a the following tables as an example:

1. Events(id, business_uuid, event_category, event_name, created,
row_updated)


2. Bank_accounts(id, business_uuid, bank_name, Account_no, etc, created, row_updated)


3. orders (id, uuid, business_uuid, qty, item, created, row_updated)

where created and row_updated are timestamps. for each business I want to capture the 1st time something happened for that business in each table. I am on redshift. I have my query like:

with universal as(
select business_uuid from events
union
select business_uuid from bank_accounts
union
select business_uuid from orders
)
, ev as(
select business_uuid
, min(case when name = 'created_account' then created end) as created_account
, min(case when name = 'enter_business_detail' then created end) as enter_business_detail
, min(case when name = 'accepted_terms' then created end) as accepted_terms
from events
group by 1
)
, bank as(
select business_uuid
, min(row_updated) as entered_banking_details
from Bank_accounts
group by 1
)
, od as(
select business_uuid
, min(created) as first_order
from orders
group by 1
)
select u.business_uuid
natural left join ev
natural left join bank
natural left join od

giving me an output of:
result(business_uuid, created_account,enter_business_detail, accepted_terms, entered_banking_details,first_order)

the way these events occur is that they may get filled at different times, if I am pulling the data every 10 minutes, some columns will be null and later be filled in, I am dealing with a large table and I cant do full loads each time, I would like to do incremental loads (new + update) and hopefully no delete. I introduced a column last_update here result(business_uuid, created_account,enter_business_detail, accepted_terms, entered_banking_details,first_order, last update)

and I rewrote the query above as:

with universal as(
select business_uuid from events
union
select business_uuid from bank_accounts
union
select business_uuid from orders
)
, ev as(
select business_uuid
, min(case when name = 'created_account' then created end) as created_account
, min(case when name = 'enter_business_detail' then created end) as enter_business_detail
, min(case when name = 'accepted_terms' then created end) as accepted_terms
from events
where row_updated > (select max(last_updated) from result)
group by 1
)
, bank as(
select business_uuid
, min(row_updated) as entered_banking_details
from Bank_accounts
where row_updated > (select max(last_updated) from result)
group by 1
)
, od as(
select business_uuid
, min(created) as first_order
from orders
where row_updated > (select max(last_updated) from result)
group by 1
)
select u.business_uuid
natural left join ev
natural left join bank
natural left join od

It does not work out when I compare my full load plus the attempted incremental load, there is slight difference in row counts.

if you find the above in clear manner, I would like a suggestion on how to best solve it.

for anything further info, I use Apache-Airflow as my ETL tool to execute my sql statements