This sounds like a major limitation of these query tools. Are these arbitrary queries written by users or created by the tools programmatically? Since dist keys are ideally join keys why do these tools (or users) not understand the join keys? Can the queries be identified via WLM and triaged for optimization?
I am skeptical of any solution that promises to be magic and do the hard work for us. I think the "big data" industry does itself a disservice by perpetuating the myth that hard problems can be solved with mouse clicks.
Reasonable people can disagree on this last claim---we'll publish a v2 of this benchmark in a few months where we do more tuning https://github.com/fivetran/benchmark/issues/1
Where have you run into limitations with dimensional modeling? Is it a lack of engineering resources on the customer side or is the approach limited in some fundamental way?
Thanks for sharing your invaluable experience. I look forward to part two!