3 ms·
Ask HN: What is your minimal Data Warehouse stack?
Hi all.
We're wanting to set up a Data Warehouse. We have several external data sources - all relational - most of them MySQL databases, a couple more Postgres.
We'd like to set up and maintain a single MySQL / Postgres "Data Warehouse" database that houses all this data, so that our analytics team has a single place to access it from.
If you've done something similar please could you share your experience and / or advice?
Any extra info on how you manage your data pipelines would be appreciated. Currently we're just looking at setting up some basic cron jobs that run bash scripts which in turn execute mysqldumps, but we'll also set up replication in cases where live data is important.
Thanks! :-)
- gigatexal 4y agoWhat is the scale of data that you're working with? How many analysts will be querying this data? High level I'd probably do something like this: cdc (debezium) on a read replica of the external sources (or main if a replica doesn't exist) -> kafka/redpanda (optional since debezium can write directly to a destination table but kafka makes things a bit more flexible though it comes with it's own issues) -> destination table (this is the load part of ELT, just load in batches the changes into a staging table) -> great expectations can be useful here to make sure things are in line with what you're thinking -> sql+dbt to do transformations + enrichment -> load into your star-schema'd db from the staging tables, rinse and repeat. Oh and schedule all of this in Airflow or Prefect. I'd consider something like Clickhouse or Percona or Citus on the postgres side to get columnar semantics. You could forgo the whole DB idea and do the data lakehouse (sic) using s3+parquet+trino and a list of other apache projects to basically reinvent the database wheel but you'd get a ton of autonomy and the ability to scale up parts as you need just with a ton of additional complexity.
- herodoturtle 4y agoSome very detailed advice in this response - thank you for taking the time to write it all up. > What is the scale of data that you're working with? It's not all that large. Mostly text data. Around 2 TB all combined. > How many analysts will be querying this data? Again, not that large. Around 4 to 5 analysts building reports, but potentially several thousand users in the field using said reports. That said we can easily scale out reads if need be. I was more just wondering about the ingest side of things (hope I'm using the right terminology here, forgive me if not). Any good books on this topic you'd recommend? Would love to hear your thoughts. Thanks again.
- gigatexal 4y agoThere’s an O’Reilly book on data engineering. I’d recommend that. And then the data engineering podcast from Tobias Macey is really good, too. I’d stand up a set of replicas in a sort of datamart for those thousands of report end users. If the reports are read only and they are then a replica should be just fine. And an even beefier replica for the analysts to create the reports. You might then graduate to something like looker or tableau. Happy to chat more about this. This is what I do all day. I love this stuff.