Suppose there is a system ( like an ERP ) that writes to a database ( not too big, less than 100GB ). You need to export the data from this database to a data warehouse ( like RedShift or BigQuery ) as many times in a day as you can, what would be a good solution for that? There is this feature in the system that exports only the delta, so this is what I was thinking:
1 - Write an ETL script to query the delta, format in Avro and save it in a bucket ( GCS or S3 ) 2 - Trigger a function when the object is inserted, get the object and insert into a staging table ( one for each table in the origin DB ) 3 - Trigger a function to merge the staging table into the main table
I'm not too happy with this approach, because it feels so limited. I think I'm missing something here. Should data in a DW be so hard to maintain? I see a lot of examples on how you can insert data into a DW, but very few on how to keep it updated.
Also, suppose that this delta mechanism didn't exist and we had to use a streaming solution ( like Kinesis ). That would make things even harder, because data will be inputed into the bucket much faster, generating lots of files, so how could I handle a scenario like this given that DW are slow to update row by row ( BigQuery even limits the amount of updates/day )?