When a business is getting started with data, people are analyzing it in live systems. While this is ok for tools like Salesforce or Google Analytics, we need to take separate precautions for data in a database.
To use any source data in a database you’ll want to create:
- Read-only Access Users (be careful)
- Read-only Replica
Create and use a read-only user account to analyze data on your production database. This will prevent users from accidentally making any updates to the data during your analysis, granted this is unlikely but is still a good precaution. It also makes it possible to grant other people access for analysis purposes and guard against their errors.
This functionality exists across database providers. However this will effect your app’s performance so it is best to separate your analytics from your application.
To query the data without impacting the performance of your application, create a read-only replica of the production database. This creates a copy of your data in a new database which can be queried without concern.
While creating a read-only replica is easy if you’re using cloud providers with hosted databases like RDS, it can be hard on other platforms.
These databases may double the cost of your database spend, but they remove the risk of an analytic query affecting your application.
Even small teams should set up permissions to analyze data:
- Create Read-Only Users for analyzing data in a database (be careful of impacting the apps performance)
- Create a Read-Only Replica database to perform analytical queries while removing the performance impacts to the production database.