Designing for Data Integrity


Primary Keys and Relationships

  • When a database is designed, primary keys and relationships are put in place to prevent:
    • bad data from being accidentally added
    • quality data from being inadvertently deleted
  • Primary keys have properties and constraints that limit what data can be entered into the database
    • ensures that any new record entered must hold a unique value in that field
  • Diagrams of the database design are a good reference when you need to find where data is stored
  • Other integrity steps to perform:
    • A field can be set in the properties to not allow a value to be null or blank
    • Default values can be set so that a field that is commonly the same value doesn’t have to be manually typed by a human
    • A field can be established to hold a unique value even though it may not be the primary key field
    • Fields can be required to be populated before a record is saved

Validate Data Source

  • Validating the data source is an important step to take in the first stages of troubleshooting
  • knowing where the data comes from is critical to determining if your problem stems from being connected to wrong or bad data

Code Review and Calculations

  • Code reviews should be conducted through a project, as we are building and testing our reports
  • Where you start troubleshooting depends on the issue being reported
    • If you have values that are being provided to the report,
      • likely want to review the code and functions from the data set
      • and move backward until you have found the issue
  • start with checking the connections in reporting tools (SQL, Power BI, Tableau)
  • know the refresh interval of all the data sets involved
  • use monitoring/logging tools to troubleshoot broken or slow reports

Consulting Vendors and Online Sources

  • If your data comes from a third-party vendor,
    • be aware of their change management policies and the impacts it might have on your organization
  • be aware of changes that are being made to the data
  • be prepared to test your reports