How to keep a data warehouse updated?

Question

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 )?

Answer 1

I don't think the typical DW captures transactional data the way you seem to be describing.

If you are attempting to create a real-time or near-real-time clone of the OLTP database, the appropriate technologies are log shipping or replication, the setup of which are platform-dependent.

A data warehouse is typically composed of summarized, immutable data for reporting and analysis purposes, and typically accepts batched inserts, not near-real-time updates. For example, at the end of a billing period, after the books are closed and you know the numbers will never change again, you might query and summarize that data and insert a record into the data warehouse that represents the billing period.