07. Data Management Best Practices

Data management is the process of collecting, storing, cleaning, organizing and maintaining data. As is clear, there are many steps to ensure good data management, and so it is helpful to clarify and keep note of the different parts of this long process and the best practices for each.

1. Data collection via ONA form
2. Data Connectors
3. Database Structure
4. Rules/Good practices
5. Data cleaning
6. Scripts
7. Queries
8. QA

Data collection via ONA form

Data management really begins with data collection so it makes sense to consider how the data being collected is stored. When making an ONA form for a project, make sure to be:

• Consistent in naming the data fields
• Descriptive and short in naming truncated columns

Data Connectors

Data connectors will likely vary from project to project but there are things to look out for during their creation that will help with data management and long-term maintenance. The short list below highlights a few good practices:

• If available, prefer the use of standard connectors (e.g. OpenSRP)
• Separating repeat-groups into their own table/s
• Separating labels into their own table/s
• Ensuring correct column name spellings
  • This work can be done alongside Canopy engineers during QA for one-off connectors. Do not accept connectors that have spelling issues unless the issues originate at the source

Database Structure

The rule of thumb for consistent database structure is to have/name schemas based on the data's source and use what's appropriate for your specific project.

Here are examples of the basic schemas that apply to most projects:

• ona_data
• csv
• open_srp
• gsheet
• temp
  • You can use this schema to test queries and create temporary views testing
  • This schema can be deleted at any time
• project-related schema
  • This schema is created whenever the DB should/could be used for multiple projects eg. ESCT in UNICEF Zimbabwe
• extended
  • Tables from a source (e.g. ona_data) that are augmented with fields from other tables/views
  • Please find an example of this at this link.
• reference
  • Used for tables/views that define standards and are often joined with tables from multiple schemas (e.g. months, administrative views)
  • Example
• Custom design
  • For more complicated setups
  • A good example of this would be the MESH project

Database management good practices

Here are additional practices that will help us maintain consistency with our data structure.

• Don't save views/tables in the 'public' schema
• Nothing that does not get used in reporting should be in the database, unless it's in the 'temp' schema
• No final charts or client deliverables should be attached to temp tables/views
• Make sure to stay hydrated and snack/eat as you need! 😋

Data cleaning/Sanity check

Data cleaning is of course a must if we want to ensure accurate data analysis and long-term data maintenance. It won't always be easy to spot errors but here are some quick checks you can look for:

Data error	Fix
Duplicates eg. Duplicate submissions or rows	If possible (i.e. if the data is static), always clean the data at source
Poorly formatted dates	Dates should be formatted as YYYY-MM-DD
Data is missing a unique identifier	Add row numbers or concatenate fields 1

¹: Remember that ids need to remain constant over time so don't use fields that will change over time

Scripts

Scripts are necessary for our team to keep track of what views have been created and help us keep a record of what was needed for a project. Considering their importance, here are some standards we can maintain as a team:

• All code stored in GitHub - Please make sure that we aren't storing views on Superset.
• Every project should have it's own branch - Please commit as you make changes to your scripts
• For now, we should have very few scripts per project:
  • Each script should be a mini migration, which means they:
    • can be executed top-bottom at once
    • end statements with ;
    • use create or replace view statements
    • use create table if not exists statements
    • dependencies are managed in the order of view creation e.g. if view B references view A, view A will be created in the same script but above view B
  • A good example of these standards can be found here.
• After each statement, run "alter table/alter view" command to change ownership to object
  • For example, you would include/run alter view reporting.district_aggregate_month_who owner to fcdo_somalia; to ensure scripts can be run in SQLEditor and DBeaver without future permission issues.
  • Eventually, we'll manage all this with sqitch

Creating tables/ views

Honestly, queries are a data analyst's best friends. They make up a good chunk of our work and set a standard for our projects. It is important for us to not only maintain standards in order to deliver accurate analysis but also to ensure team mates are able to quickly pick up from where we last left a project. To help smooth this process, here are some steps to consider (assuming we're creating a view):

• Preface the query with a comment describing why the view exists and what its supposed to do
• There should be as few views as is reasonable. For example:
  • If 2 views differ only because of a filter, then there should only be 1 view with filters set at the chart level
  • If 2 views differ only for 1 column, but have the same number of rows, then there should only be 1 view with all the necessary columns included
  • If 2 tables have the same content (i.e. same columns) and both are needed for reporting, we should have a single view from the union of both tables
• Remember to maintain descriptive view names, keeping in mind:
  • Good names are plural, understandable, and describe what each table row represents (e.g. "users")
  • Some more naming examples:
    • Main tables/views - registrations, clients, events, etc.
    • Aggregation views - daily_events, monthly_vaccinations, etc.
  • Views should not be repeated. A bad example of repeated naming would be having partner_data and partner_data_2.
  • If you really need to duplicate views for testing/temporary purposes, numbering is better. (e.g. partner_data_2' is better than partner_data_final` because you don't know you're done until you're done)
  • The question to ask yourself is will someone else other than me understand what these tables do/contain?
• Remember to maintain descriptive column names within views/tables.
  • A bad example of this would be a table with multiple variations of field_a e.g field_a → field_a_cleaned → field_a_final → field_a_field_w_adjustment
  • The question to ask yourself is will someone else other than me understand what field to use/why this was necessary?
• Limit nesting within a query
  • If you need to include complex nesting in your query, make sure to nest at the top of the query using a WITH statement
    • For example, WITH esct.unclean_payments as (...
  • Also, GROUP BY statements are generally only needed with aggregations
• Remember that cross joining/unnesting is expensive
  • You can improve the performance of your script by completing cross joins before other joins
• Make sure that your views have unique identifiers
  • We generally want a unique id per row per view
• If you are doing something temporary, as will often happen, add a comment inline prefaced by date and name
  • For example:
    • You could add the following note explain a specific table join - Bella O. 2021.02.17 Join needs to be replaced when connector is completed
  • If you're doing something complicated within the query, add an inline comment for future clarity
  • If you call something unique make sure that is is unique
    • Any fields called id or unique_id should be unique. Foreign ids in a new table should be renamed to reference the table where they were unique i.e. event_id

QA

Once analysis is complete and we've created charts to display our findings, the final crucial step involves QA'ing our queries and calculations to confirm that they are working as expected. At this point, there are a few things we can do:

• Firstly, if possible, create test cases:
  • Enter your data manually, calculate the metrics manually, and check that each calculated metric corresponds with the metrics calculated from your views
  • If you can't enter your data, filter your data to a manageable amount and manually calculate the expected outcome
  • If your project includes an app, enter some data with the app and match your submission to metrics calculated in your views
  • You can copy the following document to create your own test cases. Last Mile Health is an OpenSRP project so take away what is most relevant to your current project.
• Secondly, reach out to team mates to get an outside perspective
  • Make sure to create a push of your views to the production repo before sharing any deliverables with the client. You can then have your PR (and scripts!) approved by a peer or Alessandro.
• Lastly, there is currently QA team in the works! Currently this TBD and we will be learning more on what that means and how we'll use the QA team following updates from Jonathan (last edit B.A.O. → 2021-02-18).