5 ms·
At Grove, we are: Airflow -> S3 -> DBT with Spark/EMR or Redshift/Spectrum -> Redshift data marts -> Looker At least, that’s the way we like our pipelines to
by numlocked 7y ago
At Grove, we are:
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 https://github.com/groveco/django-sql-explorer
- pcarolan 7y agoVery similar to our progression, do you have a good way you manage your data dictionary and catalog?
- numlocked 7y agoNo :) 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.
- stevepike 7y agoI don't want to derail the thread, but I recently started a company (https://syndetic.co https://syndetic.co) that's working on this problem. We've been focusing on the external data dictionary use case (how does a data-as-a-service company explain a dataset to their prospective customers) but we've been encountering a number of companies that are evaluating data catalogs and other internal tools for their data science teams. I would really appreciate getting your perspective - I'm steve (at) syndetic.co
- pcarolan 7y agoWas just thinking this would make a great business opportunity. Thanks, we'll check it out!
- dataminded 7y agoThis space desperately needs some new options. 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.
- chrisjc 7y agoNot a complete solution, but if you're already using Looker... https://looker.com/platform/blocks/embedded/create-a-data-dictionary https://looker.com/platform/blocks/embedded/create-a-data-di...
- polskibus 7y agoCan you share details on how much operating that BI SaaS stack costs you monthly?
- numlocked 7y agoWe’ve been battling Redshift scaling problems so it’s fluctuated a lot over the last 6 months (both up and down). $15k per month to an order of magnitude (not counting Looker licensing).
- YourSavingGrace 7y agoThat seems like a lot. Snowflake typically cuts Redshift bills in half if you are hitting limits with RS
- squirrelly 7y agoORLY? Redshift is half the cost of Snowflake for any given level of performance. The bigger your dataset to wider the gap will be.
- ericmjalbert 7y agoJust curious, if you could highlight some of the problems you've experienced with each "generation" of looker reporting? Was there any specific issues that encouraged the re-implementation?
- numlocked 7y agoYep — problems with the first gen were: 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.
- chrisjc 7y ago> If we had been smarter, we would have introduced DBT after gen 1 and not gotten into the gen 2 mess. 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.
- jaunejaunej 7y agoHi, do you manage your transformations both in redshift and EMR using dbt? Do you have a single dbt project/repo covering both or separate? How do you coordinate your dbt run against spark with dbt run against the data warehouse?