4 ms·

Hi mulmen - I work with George (author). Dist and sort are absolutely an invaluable tool for optimizing your common BI workloads. What we are seeing with our

Hi mulmen - I work with George (author). Dist and sort are absolutely an invaluable tool for optimizing your common BI workloads. What we are seeing with our customers is extensive use of arbitrary querying via tools like Looker or Tableau which often do not use the sort or dist key. This is becoming the most common workload for Fivetran customers b/c of the vast number of frequently updating dashboards that are powered by those queries.

mulmen 9y ago
Thank you for your insight. My team uses both Tableau and Redshift but we don't do a lot of frequently updating dashboards. 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.
- georgewfraser 9y ago
  It's not a limitation of the query tools. The problem is that users are running so many different queries against the same schema that you can't optimize for them all. Therefore, it's most important how a data warehouse performs when the optimization "tricks" don't work. 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 https://github.com/fivetran/benchmark/issues/1
  - mulmen 9y ago
    There will always be trade-offs so disagreements are to be expected. My interest is largely in what models work best for a given system, specifically Redshift. What data models have you seen in use? Are the schemas dimensionally modeled or are they flat tables? 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!

mulmen 9y ago

Thank you for your insight. My team uses both Tableau and Redshift but we don't do a lot of frequently updating dashboards. 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.

georgewfraser 9y ago
It's not a limitation of the query tools. The problem is that users are running so many different queries against the same schema that you can't optimize for them all. Therefore, it's most important how a data warehouse performs when the optimization "tricks" don't work. 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 https://github.com/fivetran/benchmark/issues/1
- mulmen 9y ago
  There will always be trade-offs so disagreements are to be expected. My interest is largely in what models work best for a given system, specifically Redshift. What data models have you seen in use? Are the schemas dimensionally modeled or are they flat tables? 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!