0

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

2

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.

3
  • Agree with you. Customers want a DW solution because it's a buzz word, but they don't really understand the trade-offs that comes with it. So, you wouldn't consider using a DW for data that is updated on a daily basis? Even if the data comes from multiple sources? – julianomontini Jul 1 '20 at 23:56
  • Also, for the near-real-time clone, you think that a conventional RDBMS ( Postgres, MySQL ... ) would suffice ? – julianomontini Jul 2 '20 at 0:02
  • Any platform that supports log shipping or replication (or even active-active clustering in a pinch) would work. – John Wu Jul 2 '20 at 19:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.