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