4 ms·

We actually control access to our raw data sources. The vast majority of people looking at data look at data we surface in our BI layer in Looker (or in Aleph i

We actually control access to our raw data sources. The vast majority of people looking at data look at data we surface in our BI layer in Looker (or in Aleph if they know SQL). I didn't focus on this in the article, but in addition to removing sensitive information, that layer simplifies a lot of the complexities of our app's data models. Core calculations like the one in your parenthetical example live in core dashboards that the Data Analytics team owns. Raw access is limited to our applications like Airflow and a small number of authenticated users. So an engineer can easily go into our Airflow repo and add some new columns to a table in the BI layer by updating the SELECT clause of the query that creates that table, without needing to access the raw data themselves.

v64 10y ago
When I say raw data, I mean the data that's already been processed from original sources and stored in the data warehouse (that is, the warehouse data as it existed in our database and not how it was presented through third party tools like Tableau). I agree that for high level stuff like visits, etc, dashboards help out a lot. For some more specific examples where open access broke down: 1) We had a fact table with vendor IDs and service start/end dates. If the service end date was null, that meant service for that vendor was active. To correctly pull the number of active vendors, you would want to do something like "select count(distinct vendor_id) from f_vendor_service where service_end_date is null". Users often queried this table without distinct in the count and without the where clause, resulting in inflated numbers. We did end up adding "Number of Active Vendors" to a dashboard, but then people would return to the warehouse table to get the number of active vendors in 2015, etc. and their clause would be wrong. 2) Our vendors could be classified into one or more categories. Among the vendor's categories, one category was selected as the primary category. There were cases where people wanted to pull counts on how many vendors were in X category, but they would always end up querying for how many vendors had X as the primary category instead of how many vendors had X as any category. We had to provide a dashboard to get these numbers reported correctly. 3) We had a pageview_log table, and people would want to examine URLs to see what the least visited pages were. However, their regular expression ability was lacking, and despite our documentation, people would still use bad regexes and pull bad counts that would be used to justify product decisions for shutting down sections of the site. Again, we put this behind a dashboard so people wouldn't query pageview_log manually. So, from my point of view, curating information behind dashboards isn't giving people access to the raw data. The original desire was for teams to create their own dashboards, but due to their bad queries, the data team ended up having to spend a good chunk of their time maintaining all the dashboards in the company. When you have people writing raw queries in Aleph, how do you prevent them from making the same types of mistakes that I illustrated?
- philipodonnell 10y ago
  This is a great question and I hope there is more discussion around this. Our data group has similar issues with propagation of business rules to end users who want direct DW access but only know enough SQL to be dangerous.
  - dasternberg 10y ago
    Out of curiosity, are these end users building on top of each other's work, and if so what tools are they using to do this?
    - philipodonnell 10y ago
      No, usually there is an enterprising analyst who knows a bit of SQL and wants to write a particular report, but doesn't want to wait for an IT cycle to free up. I'm the same way so I empathize. Most of the time it's Excel, because they all have it by default.
- dasternberg 10y ago
  In my experience you have to strike a balance between allowing exploration that unblocks people outside the team to look into data (with a bit of a buyer-beware attitude) to help them build intuitions while making sure that core metrics that you use internally and/or share publicly are appropriately vetted. Using Aleph or other shared SQL tools like it allows us to quickly look at a query someone else on another team has built and provide support. Making time to walk through core tables and their pitfalls as part of SQL training can also help. Reporting on core company metrics needs to be curated and owned by people with the right expertise. In some cases we'll build additional views on top of our core BI layer that simplify a lot of this information for some of the examples you're describing (like the number of active customers at different points in time) and use to drive our dashboards, and people can query from those. Aleph also supports tagging and ad hoc parameter setting, so you could fix and build off of more novice users' queries, tag them as "official" and allow them to set parameters like time ranges. But at the end of the day, AFAIK there's no silver bullet here - you have to train people in the pitfalls of your data and expect that they'll make some mistakes but it's part of the data team's responsibility to help them learn. As individuals in different groups gain expertise, they can help each other too. The alternative extreme, where a priesthood owns all data analysis will avoid mistakes, but it won't allow your team to work on activities that are both high leverage for them and provide more value to the company.

v64 10y ago

When I say raw data, I mean the data that's already been processed from original sources and stored in the data warehouse (that is, the warehouse data as it existed in our database and not how it was presented through third party tools like Tableau). I agree that for high level stuff like visits, etc, dashboards help out a lot. For some more specific examples where open access broke down: 1) We had a fact table with vendor IDs and service start/end dates. If the service end date was null, that meant service for that vendor was active. To correctly pull the number of active vendors, you would want to do something like "select count(distinct vendor_id) from f_vendor_service where service_end_date is null". Users often queried this table without distinct in the count and without the where clause, resulting in inflated numbers. We did end up adding "Number of Active Vendors" to a dashboard, but then people would return to the warehouse table to get the number of active vendors in 2015, etc. and their clause would be wrong. 2) Our vendors could be classified into one or more categories. Among the vendor's categories, one category was selected as the primary category. There were cases where people wanted to pull counts on how many vendors were in X category, but they would always end up querying for how many vendors had X as the primary category instead of how many vendors had X as any category. We had to provide a dashboard to get these numbers reported correctly. 3) We had a pageview_log table, and people would want to examine URLs to see what the least visited pages were. However, their regular expression ability was lacking, and despite our documentation, people would still use bad regexes and pull bad counts that would be used to justify product decisions for shutting down sections of the site. Again, we put this behind a dashboard so people wouldn't query pageview_log manually. So, from my point of view, curating information behind dashboards isn't giving people access to the raw data. The original desire was for teams to create their own dashboards, but due to their bad queries, the data team ended up having to spend a good chunk of their time maintaining all the dashboards in the company. When you have people writing raw queries in Aleph, how do you prevent them from making the same types of mistakes that I illustrated?

philipodonnell 10y ago
This is a great question and I hope there is more discussion around this. Our data group has similar issues with propagation of business rules to end users who want direct DW access but only know enough SQL to be dangerous.
- dasternberg 10y ago
  Out of curiosity, are these end users building on top of each other's work, and if so what tools are they using to do this?
  - philipodonnell 10y ago
    No, usually there is an enterprising analyst who knows a bit of SQL and wants to write a particular report, but doesn't want to wait for an IT cycle to free up. I'm the same way so I empathize. Most of the time it's Excel, because they all have it by default.
dasternberg 10y ago
In my experience you have to strike a balance between allowing exploration that unblocks people outside the team to look into data (with a bit of a buyer-beware attitude) to help them build intuitions while making sure that core metrics that you use internally and/or share publicly are appropriately vetted. Using Aleph or other shared SQL tools like it allows us to quickly look at a query someone else on another team has built and provide support. Making time to walk through core tables and their pitfalls as part of SQL training can also help. Reporting on core company metrics needs to be curated and owned by people with the right expertise. In some cases we'll build additional views on top of our core BI layer that simplify a lot of this information for some of the examples you're describing (like the number of active customers at different points in time) and use to drive our dashboards, and people can query from those. Aleph also supports tagging and ad hoc parameter setting, so you could fix and build off of more novice users' queries, tag them as "official" and allow them to set parameters like time ranges. But at the end of the day, AFAIK there's no silver bullet here - you have to train people in the pitfalls of your data and expect that they'll make some mistakes but it's part of the data team's responsibility to help them learn. As individuals in different groups gain expertise, they can help each other too. The alternative extreme, where a priesthood owns all data analysis will avoid mistakes, but it won't allow your team to work on activities that are both high leverage for them and provide more value to the company.