joeharris76

1) create a table (and then query it) - seems very common 2) merge more into it (and then query it) - also seems very common 3) delete (a tiny bit) from it (and then query it) - also seems very common 4) delete (a little more) from it (and then query it) - also seems very common 5) delete (a decent chunk) from it (and then query it) - also seems very common 6) delete (all data for 1 key) from it (aka "GDPR request") - new problem, but common

I sense a pattern here. Not sure what makes these tests "absurd" since everyone does them.

"A grand don't come for free"

It's interesting that they link to (and show graphs from) their earlier post but fail to mention that they had to disable a very large chunk of the features in the checklist to get that performance.

Their earlier post: https://www.onehouse.ai/blog/apache-hudi-vs-delta-lake-trans... Responding to: https://databeans-blogs.medium.com/delta-vs-iceberg-vs-hudi-...

I notice that they "borrowed" most of the table from you - kudos I guess. My problem with your post is the it presents the choice between Lakehouse formats as a feature checklist. All features are not created equally. All implementations are not equally easy to use.

Personally, I find Brooklyn Data's post to be more practically useful since they used each format to do real work and show how they perform in practice. And they provide all their code so a reader can run the same testing themselves.

https://brooklyndata.co/blog/benchmarking-open-table-formats

> While performing the benchmarks, we noticed that the Snowflake pre-baked TPC-DS dataset had been recreated two days after our benchmark results were announced. An important part of the official benchmark is to verify the creation of the dataset. So, instead of using Snowflake’s pre-baked dataset, we uploaded an official TPC-DS dataset and used identical schema as Snowflake uses on its pre-baked dataset (including the same clustering column sets), on identical cluster size (4XL). We then ran and timed the POWER test three times. The first cold run took 10,085 secs, and the fastest of the three runs took 7,276 seconds. *Just to recap, we loaded the official TPC-DS dataset into Snowflake, timed how long it takes to run the power test, and it took 1.9x longer (best of 3) than what Snowflake reported in their blog.* https://databricks.com/blog/2021/11/15/snowflake-claims-simi...

Yes, we did. https://docs.aws.amazon.com/redshift/latest/dg/stored-proced...

Nice answer.

Recommend checking who the account emails are configured to be sent to. Often they go to a finance person who may not understand the importance of some communications such as this.

Redshift offers hard disk based nodes with huge amounts of storage at low cost for precisely the use case you mention. The performance of these is actually very good, especially with a little effort applied to choosing sort keys and dist keys.

Spectrum extends that even further, allowing you to have recent and reference data locally stored and keep archival data in S3 available for query at any time.

We're very interested in trying AWS' new Database Migration Service to see if it provides a nice, automated way to get a mirror of the source into Redshift. https://aws.amazon.com/dms/

I do have experience with ETL tools. The dirty secret of ETL tools is that they are slow in the best case. In the worst case extremely, head meltingly, how-could-it-possibly-take-that-long slow. Before MPP and columnar DBs (Vertica, Greenplum, etc.) were widely available ETL tools were a good trade off. You were offloading work that (in many cases) literally could not be completed on either the source DB or the target data warehouse.

When we (my company at the time) got Netezza in 2004 we immediately realized that we needed to move onto ELT. I.e. the ETL tool (SQL Server DTS at the time) became just a scheduler running plain SQL against Netezza. It was massively faster to do it that way and we could put the ETL server budget into Netezza.

ETL vendors, e.g., Informatica, now have "pushdown" options where that you design your workflow in their tool but it executes as SQL inside your database. However, when you use a tool like this you're very constrained in the types of processing you can do, for example Informatica doesn't have the ability to parse timezones (yes, really).

We evaluated basically everything that claimed to support Redshift and couldn't find anything that was viable for what we need. Some tools were close though so maybe someone can close the gap.

We've struggled with the same issues. We also keep a "base" schema in Redshift as a close mirror to source systems (tweaked a little for distribution keys). In our case we control the extracts but schema changes have to be manually added. If a new column is important and we miss it then we have to run time consuming backfills. Then only way we've been able to ameliorate it somewhat is to be more involved in the upstream release process but that is time consuming in itself.

The solution I'd like to implement is having the extracts and loads be built dynamically whenever a change is detected. E.g. have a process that runs separately checking the schema of our sources for changes, dynamically updating the ETL processes and then publishing them to Azkaban. Looking forward though our biggest data sources are going non-relational so that approach will entail a great deal more complexity.

Regarding schema knowledge / metadata, this is a perennial problem. I once worked on a huge Teradata DW system at a global bank who had a large internal website for just this purpose which was hugely out of date at all times.

When I started building out the analytic capability at my current company I made a fork of "Rocco" (https://github.com/joeharris76/rocco) for doing inline documentation as Markdown inside SQL block comments. http://pow.cx/docs/ is a good example of what I was hoping to achieve. However even our smallest components have many, many files and the docs were too difficult to navigate using the "Jump To…" dropdown menu.

So, while this produces some very nice docs, sadly they turned out to be largely unhelpful to anyone who didn't already understand the code. Our current approach is to try and keep individual components small, include a comprehensive README in each component and use Slack comms to fill in the gaps.