1. Complex joins and poor performance. 2. Not pre-computing important attributes made them hard to slice and dice on (because they weren’t true dimensions)
2nd gen:
1. Really unmaintainable. Like....really unmaintainable. A rat’s next of ad-hoc common table expressions, that referred to each other in undocumented ways, used inconsistent CASE statements to transform enums to human-readable values, inconsistent time stamp formats (and time zones), etc, etc, etc. Fields in different explores that were named the same but didn’t quite match and no one could explain why. 2. All of it was SQL in LookML and the persistence strategy (e.g. when were the CTEs running, were they writing to disk or ephemeral, etc) was not manageable.
If we had been smarter, we would have introduced DBT after gen 1 and not gotten into the gen 2 mess.
There’s a ton of other nuance here, but that’s the high level.
Is this a roundabout way of saying you wouldn't have exposed looker to the underlying staged data?
We have two layers within our data warehouse: The ingestion/staging layer, and the organized/cleansed/segmented layer. We only give Looker projects access to the latter.
We still have a lot of chaos within our Looker projects (views with select star, dimensions for every column bc of select star, hard-coded schemas, etc...). Slowly working it out though.
I would really appreciate getting your perspective - I'm steve (at) syndetic.co
The legacy players are a mess, the new cloud native offerings are overly Engineering focused and immature, and the few startups in this space are recreating the legacy solutions in a SAAS formats
Go forth and claim the significant checks that I and others plan to write for these mediocre offerings.
We are revamping our management of Snowplow event structures presently to make them more semantic and easier to use.
For our core data marts, we rely on naming conventions for the LookML fields, good tool tips, hold monthly training sessions for new employees, weekly office hours with analysts, and do occasional “train the trainer” sessions to try and make sure there is at least one Looker power-user in each business team.
https://looker.com/platform/blocks/embedded/create-a-data-di...
Airflow -> S3 -> DBT with Spark/EMR or Redshift/Spectrum -> Redshift data marts -> Looker
At least, that’s the way we like our pipelines to work. In practice we have a couple of extractions that land directly in Redshift (we extract Zendesk data, for instance, with Stitch Data). We use Snowplow for click stream analytics. And we’ll likely move from Redshift to Snowflake (or mayybbbeee Bigtable) in Q2 of 2020.
We used to do all of our transforms via DBT within Redshift but have been offloading the heavier-duty pieces (like Snowplow event processing) to Spark jobs because they were too taxing on Redshift.
We’ve gone through 3 “generations” of Looker reporting — gen 1 was just wrapping LookML around our schema and forcing Looker to do the joins and generate SQL for everything we wanted to know. Second generation involved a bunch of common table expressions within Looker itself that worked, but were developed without much thought as to data mart design. Gen 3 is where we are now with super deliberate scoping and implementation of warehouses in DBT. Before any of that we rolled our own tool [0].
Happy to answer any questions. We’ve gone from ~30 to ~1000 employees in the 3 years since we started using Looker and have learned a thing or two along the way.
[0] https://github.com/groveco/django-sql-explorer