I believe this is the best combination of cheap/powerful for early-stage startups. My very non-technical cofounder is able to use metabase's simple GUI interface to create graphs/insights (even joining and aggregating across tables!), and for anything complex I can step in a give a helper SQL query. We have around 10M records we aggregate around for daily insights.
Metabase can also be run as a pseudo-desktop/web application to save additional cash (we don't do this though).
https://github.com/metabase/metabase/issues/4387 opened this issue on Feb 15, 2017
https://github.com/metabase/metabase/issues/2052 opened this issue on Feb 29, 2016
https://github.com/metabase/metabase/issues/6741 opened this issue on Jan 22, 2018
-Another Metabase team member
Metabase is a very easy to use data exploration, data vis and reporting tool designed to give insights into data in an easy/self service way for mostly non-technical users.
I've POC'd both, and would recommend Metabase or redash unless you really know what you need out of superset
I didn't demo redash so can't comment on the actual use comparison.
For our team, using an ELT architecture (as opposed to ETL)  for managing our data warehouse has greatly reduced the complexity of our data processes. Instead of creating ETLs for every table we want to load into the data warehouse, we create the minimum necessary setup to copy the table into our data warehouse. Then, we write transforms, which are simply SQL statements, to generate wide-column tables that our non-technical users can use to explore data without worrying about joins or having to learn esoteric naming conventions.
Custom EL Scripts -> Redshift -> Transform Statements -> Redshift -> Metabase supports the data needs of all our departments with no dedicated data team members.
Also, their API conforms 100% to Zapier’s polling feed structure, and I managed to build an integration that syncs our customer base to Pipedrive in a few hours. A bit more time and I have it automatically moving deals along in the pipeline as users progress with using the product. That alone was game changing for our customer success team.
Thanks, team Metabase!
P.S. I'm another Metabase + Postgres + Bigquery fan. Thanks Team!
I work with many companies helping them set up their data stack, and from what I've seen this is pretty much the optimal set up. These tools all require very little maintenance, are relatively cheap (compared to the man power required to set these things up from scratch internally), and scale well as companies grow and complexity increases.
If you don't have a data team:
Segment -> Amplitude/Mixpanel
If you don't have a dedicated data team, you can do a lot with tools like Amplitude and Mixpanel. Get your engineering team set up with Segment and tracking all of the important interactions within your product, and set Segment to pass those events to Amp/Mix. Self serve analytics in these tools is then pretty easy and will serve you well until you start hiring a data team.
Full disclosure: I work for Dataform (used to be a customer, loved it so much I decided to join them)
Recently we started using PopSQL (https://popsql.com) and love it.
When I don't have a dedicated data team, my philosophy is:
1) Make it difficult to get wrong answers
- Don't use Google Analytics. It's too easy to generate incorrect charts, and too difficult to verify them.
- Have a limited sandbox of reports for non-SQL writers
- Keep the SQL close to the report, so it's easy to verify the underlying query.
- Push people to learn even basic SQL
2) Make it quick and easy to ask iterative questions
- PopSQL is way faster than Mode. Like 20x faster.
3) For metrics that matter (e.g. KPIs), instrument them directly and even build a custom analytics dashboard if it's important. (beware dashboard clutter! https://twitter.com/andrewchen/status/1193619877489192961 )
Most of all, it's really fast. It fundamentally depends on your DB of course, but PopSQL doesn't add any extra bloat the way Mode does.
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 .
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.
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)
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.
Luigi runs our scrapers and other workflow management tasks (e.g. DB backups).
All raw data lives in S3. We make an effort to be able to recreate the whole data warehouse from the raw data, so if any cleaning/normalization process fails, we have this safety net. I'm curious to hear if others use a similar pattern, or if there are better options.
DBT handles both loading the data from S3 into Snowflake (by creating the Snowflake Stages), and transforming the data in Snowflake. This isn't how DBT is used usually, but it felt wasteful to add Stitch or another tool to load the data into Snowflake, as snowflake supports it out of the box. I also created a `setup_snowflake` macro operation that creates our users, warehouses, databases, etc., in Snowflake (a kind of "poor man's Terraform")
I don't think Snowflake requires introduction. It's an amazing tool. We used Postgres before, but Snowflake is much much better, even though our DB is pretty small (~200 GB).
Finally, we use Re:dash as a BI, but I'm checking other options that allow usage without SQL (currently Metabase and Looker).
You still may need to maintain information about what is in S3. If you're already using Snowflake you can probably achieve this via External tables. Otherwise you could run your own hive meta-store or use AWS external tables.
We're also trying to decide whether it's cheaper/easier to store things in S3 or just keep everything internal in snowflake. In some cases the compression is so good on some of our data, it's better keeping data stored in a table than in internal or external staging. Obviously this is bad if we ever have to move away from Snowflake, but we haven't committed to either approach and lots of data is still backed up in S3. Our total data warehouse is about 200TB at the moment and we're projecting significant growth over the next couple of years.
It's an alternative to Looker but more affordable, and more powerful than Metabase.
Goal for us is KISS. Keeping everything as simple as possible -- both in terms of infrastructure, ease of use and cost.
Primary we're using Metabase in front of multiple MariaDB instances. Metabase is absolutely fantastic!
We also have a couple of additional small projects:
- A small Node.js app that receives events live via fluentbit, saves them off in MariaDB and sends text message notifications via Twilio when certain thresholds are exceeded
- A small "ETL" app that polls the Stripe and QuickBooks API to mirror data in a MariaDB database so we can easily access it from Metabase
Lots more documentation here https://about.gitlab.com/handbook/business-ops/data-team/
We have many of our KPIs embedded in the handbook (look for KPI index mapping link).
This is also our primary project where you can see all of our dbt code https://gitlab.com/gitlab-data/analytics/
Without design sessions to figure out your data store design (look up Kimball, Immon), and then monitoring/testing to make sure everything is running smoothly, any data stack will be hell.
Badly designed data brings fatigue and rancor and unnecessary work and is a huge hard-to-quantify money suck.
It's important to say out loud that a lot of analysis also happens within product-local reporting systems, or in "offline" Google sheets.
We are currently working on building out the same stack, terminating in a Powered By Looker instance, for customer-facing aggregate reporting.
The engineering and data science teams do great with Looker, but Tableau still covers use cases for non-engineer business people (think: the finance, customer operations, training, etc) who want to start from source data plus a few sidebar Google sheets, tinker around, and then develop a rough report without waiting for iterative cycles with the data engineering team. We're thinking hard about how to get those use cases into the warehouse + Looker faster.
Reporting infrastructure is manual massaging and lots of powerpoint.
My company isn't that large, but bigger than you'd think for such a "system".
I suspect you underestimate how large some of the organizations are that use a variant of this system.
 - https://github.com/metabase/metabase
- Amazon Redshift (data sourced from many different backend DBs; e.g. PostgreSQL, MySQL, other Redshift instances etc.)
- BigQuery (Some teams store the data for their projects here. For reporting, they're selectively transferred to Redshift.)
- Tableau (extracts of pre-aggregated data from Redshift)
- Looker (connects live to and executes its own queries on Redshift)
Anything that is based on pre-aggregated (rather small, e.g. n_rows < 5mil) data is visualized on Tableau. If users want to work on a self-service tool they use Looker which can connect to (cleaned) raw data optimized for this use case.
ETLs for raw data run daily on Redshift. Reports are also scheduled on Redshift (with Airflow) but the refresh interval is report-specific.
Postgres read-replica for cheap realtime stuff,
Redshift for the larger sets,
Airflow to manage transfers,
and Metabase to visualize and share.
We also collect data from a bunch of APIs, but those each run via their own job and store in Postgres.
We also try to define wide short tables in Redshift that enable users to use Metabase to ask questions.
I was very happy with Metabase. Being that we can't afford Looker right now (but we would all love to) it is pretty solid.
Everyhting goes through S3 because Snowflake storage is on it.
dbt is amazing, we began using it a month ago and it already transformed the way our data team work. It really is a value multiplier for everyone. Data engineers are happier because they don't need to write and maintain data transformations, analysts are happier because they can maintain their own SQL pipelines & the whole company is happier because we now have a great documentation tool to explore our data.
We also are big fans of Snowflake, make operating a data warehouse a breeze.
Then, we use a mix of Redash & Tableau for reporting.
Redash for static reporting (open to the whole company) & Tableau to create more complex data tools we expose to some internal teams; Marketing, Risk, Finance ...
Segment -> Amplitude (but using it less and less)
I am surprised no one mentioned https://redash.io/ till now (a lot cheaper than looker/mode/Periscope with all the basic functionality that you might need).
Shameless plug: It's basically similar to Looker but it plays well with the product data and integration with Segment as well.
I'm pretty fond of the last stack for streaming dashboards in the sensor data realm.
-> Kafka-connect -> Snowflake -> SQL/sf-tasks -> Snowflake -> Looker
-> Alooma ->
-> custom ->
We probably need to use some kind of ETL tool to replace custom SQL and sf-tasks. Unfortunately, we haven't been able to find a tool that handles this in a non-batch (even if it's micro-batching) form. Snowflake change-streams and tasks allows us to ETL in a streaming-like fashion.
We're ingesting everything from raw/transformed/aggregated events, micro-service DBs (as fast as they sprout up), netsuite/salesforce, mixpanel, MySQL, MongoDB... Billions of rows of data across multiple data-source accessible to internal and external customer in a matter of seconds. It's been an incredible challenge, especially with only a team of 2-5 people.
[ onemedical.com, mixpanel, Google Sheets, Salesforce, etc ] -> S3 (Amazon EMR) -> [ Tableau, Aurora MySQL ]
It's a nice & clean stack for data engineering.
Airflow is used for orchestration and is heavily customized with plugins. Zeppelin notebooks are used by most analysts.
We'll probably be replacing Aurora MySQL w/an MPP database like Snowflake or Redshift. MySQL is a crutch, but easy to deploy and manage to a point.
Several python frameworks also do various ETL & data science functions, but everything generally revolves around S3 & Airflow.
Amazon EMR is a great swiss army knife for ETL. Moving between Pig, Spark & Hive is trivial. Tableau is a bit of a challenge. Tableau seems to give users too much rope to hang themselves with.
Also, we're hiring: https://www.onemedical.com/careers/
TreasureData is a platform like Segment and lets you bring your customer event data as well as data from SaaS tools (like Salesforce, Zendesk) into a data warehouse hosted by TreasureData. It worked great but had the downside that all the data was in TreasureData and we were kind of locked into it. Segment kind of solves that problem because it has connectors to Redshift/Snowflake etc so you can keep ownership of your data warehouse but the warehouse sync delay (in our version) was a problem.
Also, BI was just one of the use cases. We wanted to send the data to 3rd party tools (like Facebook ads) based on some logic (some were simple rules but we had complex ML driven lead scoring models too). TreasureData was more flexible on being able to run those rules and activate the results but ideally we wanted to run them on top of our own warehouse in AWS.
It generates a very beautiful CRUD admin dashboard automatically via reflection.
Allows building drag-and-drop data viz dashboards, saving commonly-used custom queries as "scopes", and even building your own HTML views if you need to get really fancy (think tracking live deliveries on a map, etc).
Also has Stripe and Intercom integrations.
I really can't hype this enough. Have been using this on nearly every app I've built the past three years.
The core team also answered my emails as a never-paying customer within 2-3 days the few times I have mailed them over the years I've used it.
For a smaller company, it makes a lot of sense for us to use off-the-shelf tools like these rather than rolling our own pipelines. Every once in a while we run into issues where Stitch isn't quite as flexible as we'd like (can't set dist/sortkeys etc), but that's the tradeoff you make for its simplicity.
DBT is amazing and I can't recommend it highly enough!
Looker works for analytics, but we're starting to do more data-sciency work, and it doesn't have built-in Jupyter notebooks or anything like that. Does anyone have a solution they use and like for hosting and sharing notebooks alongside a stack like this?
My recommendation would be
- an ETL as a service such as fivetran, stitch, etc
- into a data warehouse such as snowflake, big-query, etc
- transform/aggregate/normalize/magic data using some ETL tool such as dbt, matillion, etc
- analytics tool such as Looker, Tableau, etc...
The other I had to build for a startup with millions of monthly uniques but only seed funding (cant do a 30+k a month data eng bill).
Went with custom event emission->kenisis->(druid & S3)
and used imply (https://imply.io/). Easy real time data analytics, auto "enrichment with druid lookups from a RDBMS, and a simple ui for slice/dice investigation metrics. All in all costed lest then the cheapest looker license.
Pipe stuff to Domo for the investors.
For less technical people > metabase
For automated reporting and storing historic trends > Klipfolio
For near real time automated operational reporting > kloud.io
Currently: Internal Data Warehouse -> RDS -> Internal web app (Django, React)
We initially considered Stitch and other -as-a-service ETL but ~500 lines of Python later we had our own thing.
I also experimented with FDW: https://unwindprotect.com/postgres-fdw
- CTO (chris at vetd.com)
Fivetran -> Snowflake -> Looker
It looks something like this  when you view the aggregated data of the companies in your community, and you can also view each company's individual stack.
 - https://imgur.com/a/UXLN5KJ
Metabase for most of our simple BI metrics - Tableau for some advanced users doing more complicated stuff.
Segment syncs our event data periodically to our data warehouse (Redshift).
We have a readonly replica of the eCommerce DB for live stats (Postgres).
And there is a time series db for system/IoT telemetry (InfluxDB).
Most of our BI queries are done in Mode. Some are done in Grafana (data in our InfluxDB and some live data). Spot check against Google Analytics or FB ad tracker...
We want to switch to postgres because of features, but "The devil you know is better than the devil you don't", so we just kinda sticking with MySQL.
The addition of Dataform into our stack has completely changed the game, allowing us to maintain one source of objective truth and maximise looker (minimising the joining etc. in LookML, instead pointing it straight at tables in BigQuery).
This in turn means our downstream data users are happier to use a fast and reliable looker instance!
Any affiliation with DataForm?
Holistics handles both ETL, transformation and self-service visualiation (Looker alternative), all in 1 tool.
Some spark mixed in at the ingestion and transformation phases.
Like someone else said in this thread, we're currently battling Redshift scaling problems and are looking to offload more of the raw data to S3 using Spark to create read views.
No data catalog right now but the Looker setup is extremely well-maintained. Hoping to deploy Lyft's Amundsen to fix this more generally.
That said I haven't looked closely at how far we could go with Looker, I'll check it out.
I would love to hear the features that you would need in a data catalog system.
Agreed there's more to be done in terms of integrations. Our integrations are pretty focussed on databases and people (https://github.com/lyft/amundsen#supported-connectors). Work on indexing dashboards is being scoped, you can see a rough product spec with a rough mock here: https://docs.google.com/document/d/16cSKgM2sCYvhKq54yfwaHKws...
It covers BI a bit, but mostly the stack that BI sits on top of. It's an open book so we're always looking for suggestions and experiences such as those shared here.
Events are mainly streamed to one of the following: Cloud buckets(S3, etc), HDFS, SQL-db or Cassandra.
Most clients use one of the following visualization tools: PowerBI, Qlik or Tableau.
Our clients are mid to enterprise size.
Disclaimer: I work at Datastreams
Our databases store all our users data. I'm thinking of using something like pgsync to sync all database postgres to a new postgres and then having redash or metabase set up to connect.
Alternatively using locked filters on metabase and embedded questions.
All our data for our users are in postgres and they very much want BI insights. Not sure how easiest way for this...
MySQL will be moved to Postgres for better Performance soon.
Hubspot/Jira/G Suite → (Python) → PostgreSQL → (SQL) → Tableau
Since we are Tableau partners, we have a bunch of internal licences either way. We host Tableau Server, ETL, and PostgreSQL ourselves, all on Centos cloud servers.
I've set up the following stack:
Segment -> (postgres DB, Intercom, Heap Analytics, Full Story)
postgress = Data dump for future usage.
Intercom = CMS + Communication.
Heap Analytics = Product analytics.
Full Story = Session analytics.
We have a variety of data sources, from Mixpanel to PostgreSQL to black-box systems that can only dump out CSV files. We use Airflow to manage pulling from those, doing some transforms, and writing everything into BigQuery. Looker then handles the reporting and browsing.
Reporting platform: Looker
Easy, agile, and cheap.
A nightly rebuild using ETL scripts written in sql. Not cheap or glamorous, but solid for our needs.
This setup has worked pretty well for us so far. I've learned of a few tools from this thread that might help us to better manage data sets and views - specifically DBT and Dataform.
Drop me a message, would love to chat.
The benefit we can also build tooling and workflows, in addition to the metrics, tables and charts.
Early on you don't necessary don't exactly what end up needing, so malleable tool is useful.
After looking at the different way of doing that with their API I think we'll have to use that https://docs.looker.com/reference/embedding/sso-embed for a mix of convenience and security.
(I'm not affiliated with Looker and have no opinion about it ... yet).
MySQL -> Data export using Sqoop through Airflow -> S3 -> Spark -> Jupyter Notebook
PS: Qubole is a data platform which makes ETL pipeline setup easy.
Unless you're working with a lot of data, the technical parts of a data warehouse are relatively easy to grasp (the dbt team wrote some great posts on how they structure their projects ). After that, what really makes a difference is how you structure our data warehouse, how you model your data in a way that allow you to query it efficiently and ask many questions. That's where the Kimball books shine.
More value comes from database engineer skills as well as being able to understand the structure of a business. Little actually comes from the BI platform you choose.
DataCamp.com has some great courses
Tableau has some great online education - many people learned BI from them
Many free resources for getting started with data at different levels (note: this is one of my sites)
Description of common roles in BI/data: https://chartio.com/learn/data-analytics/distinguishing-data...
Business: Segment -> customer.io/Zapier/Heap Analytics + Looker
Support/Success: Intercom+Slack -> frame.ai
We use Druid for time series data, and Postgres for everything else.
Can you elaborate on this? Do you mean enriching events and/or windowing/aggregating/summarizing?
Are you using KSQL?
We also use Kafka streaming with a persisted event window store to deduplicate across a window of 2 hours.
If we had a need for near instantaenous model updates, then I'd definitely go Debezium. We didn't use it because we didn't need it - a generic KC JDBC source with a poll period of 5 seconds met our needs.s
* SAP -> SAP services -> tableau.
* Some depts use Excel -> Python -> tableau.
ELT process so more DBT in the future
HubSpot -> AzureSQL -> Tableau
Fully automated syncing with user friendly signup
Reporting platform: Domo
"Two words combined that won't make sense" etc.
Most probably, Business Intelligence.