4 ms·

You can pry the star schema database from my cold dead hands. You can get a lot of work done, without much effort, in multi-dimensional row stores with bare SQ

You can pry the star schema database from my cold dead hands. You can get a lot of work done, without much effort, in multi-dimensional row stores with bare SQL. The ETL's are easier to write than the map reduce jobs I've seen on columnar stores. ETL pipelines get the data in the structure I need before I begin analysis. ELT requires me to know too much about the original structure of the data. Sure, that's useful when you find a novel case, but shouldn't that be folded back into the data warehouse for everyone?

garethrowlands 5y ago
Columnar stores speak SQL these days, though they may map-reduce under the hood. ELT vs ETL just moves some transformation into SQL the data normally still ends up in the data warehouse.
mulmen 5y ago
I absolutely love Looker for this reason. It understands foreign keys so if you model everything as stars it just writes SQL for you. So simple, so powerful. I wish something open source would do this painfully obvious thing.
marcinzm 5y ago
>Sure, that's useful when you find a novel case, but shouldn't that be folded back into the data warehouse for everyone? And this isn't possible to do in the data warehouse why exactly? Most every company seems to use DBT so even analysts can write transforms to generate tables and views in the warehouse from other tables. Hell, even Fivetran lets you run DBT or transformations after loading data.
lightbendover 5y ago
While I agree about your core point (star schema ETL is great at most DW use cases due to simplicity and breadth of roles supported), I fail to understand how columnar stores are any more difficult to work with. My team routinely joins over trillions of records using standard SQL queries without any issue, all the map-reduce happens behind the scenes. In fact, it has been so successful for us that we have replaced almost all of our historical ETL infrastructure based on Spark with a single columnar store; while infrastructure costs have gone up (generally the price to pay for generalization), we have gained a great deal of human efficiency and DW maintainability.

garethrowlands 5y ago

Columnar stores speak SQL these days, though they may map-reduce under the hood. ELT vs ETL just moves some transformation into SQL the data normally still ends up in the data warehouse.

mulmen 5y ago

I absolutely love Looker for this reason. It understands foreign keys so if you model everything as stars it just writes SQL for you. So simple, so powerful. I wish something open source would do this painfully obvious thing.

marcinzm 5y ago

>Sure, that's useful when you find a novel case, but shouldn't that be folded back into the data warehouse for everyone? And this isn't possible to do in the data warehouse why exactly? Most every company seems to use DBT so even analysts can write transforms to generate tables and views in the warehouse from other tables. Hell, even Fivetran lets you run DBT or transformations after loading data.

lightbendover 5y ago

While I agree about your core point (star schema ETL is great at most DW use cases due to simplicity and breadth of roles supported), I fail to understand how columnar stores are any more difficult to work with. My team routinely joins over trillions of records using standard SQL queries without any issue, all the map-reduce happens behind the scenes. In fact, it has been so successful for us that we have replaced almost all of our historical ETL infrastructure based on Spark with a single columnar store; while infrastructure costs have gone up (generally the price to pay for generalization), we have gained a great deal of human efficiency and DW maintainability.