---
# System prepended metadata

title: Areas where DS needs DE help the most

---

# Areas where DS needs DE help the most

## Table of contents
* [Data quality](#data-quality)
    * [Daily DQ job](#daily-dq-job)
    * [Utility bill DQ](#utility-bill-dq)
* [Projects](#projects)
* [Trust in data science products](#trust-in-data-science-products)

## Data Quality
### Daily DQ job
#### Materials
* [Confluence: Pipeline](https://aquicore.atlassian.net/wiki/spaces/APD/pages/843841550/Data+Quality+-+anomaly+detector)
* [Confluence: Dashboard](https://aquicore.atlassian.net/wiki/spaces/APD/pages/892829837/Data+Quality+-+dashboard)
* [Redash dashboard](https://app.redash.io/aquicore/dashboard/dq---overview---prod?p_Data%20Source=2&p_Detectors=1&p_Timeframe=30)
#### Summary of current approach
* [Databricks coordinator job](https://dbc-e4ce7ba6-2672.cloud.databricks.com/?o=2093562134185003#job/331193) calls an [Initial Processing notebook](https://dbc-e4ce7ba6-2672.cloud.databricks.com/?o=2093562134185003#notebook/292562793750149/command/292562793750150)
    * Notebook runs [DataQualityService](https://github.com/aquicore/aq-data-quality/blob/integration/src/aqdq/anomaly_pipeline/services/data_quality_service.py) custom class on last 24h of 15-min data for all eligible electricity and water meters
        * "Eligible" = account is active, building not archived, meter doesn't have DQ set to "off" in D&E
        * Both raw (S3 packets) and platform data are processed. Raw data processed by creating an Athena partition of last two days of S3 data; platform data called from Influx
    * Data is run through various detectors and saves initial results as a JSON for each building to [intermediate results](https://s3.console.aws.amazon.com/s3/buckets/aq-dataquality?region=us-east-1&prefix=dq-results/intermediate/&showversions=false) directory in S3
* The job then calls a [second Databricks notebook](https://dbc-e4ce7ba6-2672.cloud.databricks.com/?o=2093562134185003#notebook/1262131105237568/command/1262131105237569) that performs secondary processing
    * i.e. flatlines below a certain duration are converted to "not an issue" values
    * Each building's JSON pulled from intermediate S3 directory, processed, and then sent to [final results](https://s3.console.aws.amazon.com/s3/buckets/aq-dataquality?prefix=dq-results/final/&showversions=false) S3 directory
* AWS Glue traverses the final results directory every day and creates a schema for AWS Athena
* Redash queries Athena to create summary tables for users

#### Limitations of current approach
* Redash
    * Separate from the platform. Users typically don't like needing to go to multiple places
    * Not interactive - users cannot mark issues as complete or otherwise communicate back to the platform
    * Clicking around too much easily causes Redash to freeze up or fail to display plots, reducing trust in it
* Dashboard focus is "top-down" rather than horizontal
    * Jason only uses the Offline Devices dashboard because it's effectively a to-do list
        * The current Redash dashboard is more of an executive summary. 
        * It's good for providing a percent DQ score, or identifying the number of positive spikes vs. missing data
        * But it's hard to take action since the data is inherently grouped *by account*
    * We tried getting EMs to use the dashboard for their accounts, but there was no adoption
        * Potentially due to lack of interactivity, reliability issues with updating, dashboard freezing
* Dashboard is a security apocalypse waiting to happen
    * Anyone using the dashboard can modify a query, causing a table to break for all users
    * Nothing is version controlled
        * There's also no way to implement version control without copy and pasting code into separate files, then backing up *those* files
    * Internal user sharing their screen can *very* easily reveal sensitive information about other accounts on accident

#### Where DE support would help
* Store DQ results in Redshift tables
    * Make it infinitely easier for DS to query DQ results
        * Formatting JSON files for Athena makes them essentially unreadable for us, and querying Athena is slow and costly
    * Make it infinitely easier to modify the DQ job
        * Adding/removing a column, or changing the dtype of an existing column, can cause the Glue schema or Athena table creation to fail with no indication of what file caused the issue
    * Improve DQ dashboard UX by making the dashboard's data pull reliable

* Switch from Redash to a different BI tool
    * Have infrastructure as code, allowing for version control of any changes to dashboard tables
    * If BI tool is interactive (e.g. users can update Redshift tables with actions telling to ignore certain DQ issues), engagement will likely be higher
    * Create visualizations with much more precision and customization than Redash

### Utility bill DQ
#### Current approach
* Databricks jobs (one per tab in final spreadsheet) that query utility bill data from the Monolith via Postgres
* Save CSVs to a public S3 bucket
* Ingest CSVs to separate tabs in a Google Sheet

#### Limitations with current approach
* Google Sheets is separate from the platform
* Security concerns: S3 bucket must be public for Google Sheets to import data
    * Anyone can access the data if they guess the URL

#### Where DE support would help
* Replace Google Sheets with BI tool that requires Aquicore login
    * Address security concern of non-AQ users accessing our customers' data
* Reference one Redshift table with Utility Bill DQ results
    * We currently imitate this by having a _Google Sheets template_ that serves as a "source of truth." Users can download to get the latest report. 
        * But we really shouldn't be dealing with copies of data
* Make it easier to access utility bills from the sheet
    * We currently use URLs that take the user to the relevant webpage in the platform. Maybe this is a broader software engineering epic, but we should also think about the Utility Bill data model we want (which I think would fall more under data engineering)

## Projects
#### Current approach
* For morning overshoot peak demand projects, we use Databricks jobs that scan a month of data and identify all morning overshoots for that month
    * We then create a row in the AFDD database if certain criteria are met (at least 3 morning overshoots, or at least one is in top 15% of daily peaks for the month)
* We then have a separate Databricks job that pulls in morning overshoot summaries from the AFDD DB and then writes to the Projects DB
    * Morning overshoot projects for the same building are either:
        * Overwritten with fresh data if the building already has a project but is still in `DRAFT` (i.e. a BSE hasn't looked at it yet)
        * Ignored if the project *has* already been looked at by a BSE and is in a different status (e.g. `COMPLETED`, `IN PROGRESS`, `WON'T DO`, etc.)
* These morning overshoots appear in the Projects Center

#### Where DE support would help
* Maybe this is less DE than business in general, but there is low adoption of these projects among BSEs
    * Maybe this reflects more the next section on trust in DS products

## Trust in data science products
#### Current approach
* Confluence pages, meetings with teams, answering support requests

#### Ways DE support would help
* I think it'd be good to have an area in the platform where internal users (or maybe even customers too) could play with each of our models
    * You could specify the inputs to the model and see what output you get. Produce some intuitive visualizations
* I know how to do the above in Flask + JavaScript. It seems like the rest of Product (and Nate in particular) want one consistent set of tools/code, though, so anything in the platform should stay in Java (I guess?)
    * I think it'd be worth pushing a bit and seeing if we could create a sandbox area, though, at least starting in Flask, and then rewriting to Java if needed
        * Given this is a data science product, I want to dig my heels in a bit that this should stay in Python and be easy to edit for current and future data scientists at AQ, rather than needing a Java developer to help
