Managing a database can feel a lot like playing Jenga. You have to keep making changes to stay in the game, but every change you make risks the integrity of the structure if you don’t do it carefully.
But thankfully, there are tools and processes that can support good structural changes by keeping everyone aware of what’s happening in real time.
What Is a Schema and a Schema Change?
To fully understand the importance of detecting schema change, it’s important to first understand the definition of a schema and schema change.
What Is a Schema?
A schema is a representation of the database structure, including the tables and the fields in the tables, as well as the relationship between the tables and fields. It also includes the rules that the data has to follow in that database.
The schema of a database plays a crucial role in determining how the data is organized and accessed. An ideal schema is designed to ensure that the database is able to support the needs of the application or process it is being used for. And since applications and processes frequently change, it’s also common to have to make changes to the schema.
What Counts as a Schema Change?
A schema change is any change to the structure of a database that affects the way data is stored or accessed. Not all changes to a database are schema changes — the adjustment has to impact the database’s structure.
True schema changes include:
- Adding a new table or removing an existing one.
- Adding a new column to a table or removing an existing one.
- Modifying a column data type, name, or constraints.
Some examples of changes that are not considered schema changes (because they don’t modify the structure of the database) include:
- Adding or modifying data in existing tables (this is called “data manipulation”).
- Creating new views (a stored query on data that users can query like a persistent database collection object)
These smaller changes that only modify information within the schema but don’t alter the schema themselves don’t need the same level of notification.
Why Detecting Schema Change Is Important
Any change to a database structure could have ripple effects across the data. Think of it like the supporting legs of a kitchen table. If the height of one leg is changed even a little, the consequences will be noticeable and may inhibit the ability of that kitchen table to do its job: be a stable place to set food.
So, whenever a change like that is made, the change needs to be detected immediately in order to make adjustments for that change.
Keeping Data Integration Smooth
When bringing information from multiple sources into a single database or data warehouse, you may need to detect schema changes in the source systems to make sure the integration process is working correctly.
Say a field is added to a source system. The integration process may need an update to include that field in the target database.
Maintaining Database Performance
Adding or removing fields or tables or changing the data type of a field can affect the performance of the database. Adding a new index to a table may improve performance for certain queries, but it can also slow down the overall performance of the database if it is not designed and implemented properly.
If the schema change is not backward compatible with existing applications or processes that use the database, those applications or processes may break or behave unexpectedly.
Data Governance
Many organizations need to track and control changes to the schema of a database to maintain the integrity and consistency of the data. By detecting schema changes, teams can check whether changes are properly documented and approved before they are applied.
If a field is removed or its data type is changed in a way that is incompatible with the existing data, the data in that field may be lost or corrupted. And even a small amount of lost or corrupted data can impact the overall integrity of the data in the database.
Monitoring and Alerting
Detecting schema changes can also be useful for monitoring and alerting purposes. For example, a team could up alerts to notify the right people if a schema change is made outside of a predetermined change window or by an unauthorized user.
Version Control
Any team using a version control system to manage changes to a database schema may need a schema-change detection tool, feature, or automation in place simply to track the work of multiple developers. Many teams have multiple people making changes at once, so tracking and properly merging those changes can be a headache when developers have to manually track schema changes.
How To Get Started With Schema Change Detection
Say a team needs to create a solution that would capture the schema of a third-party-controlled database on a daily basis and then alert the team if the schema has changed.
Using the SnapLogic Intelligent Integration Platform, this can be solved with two pipelines and a scheduled task. Once the pipelines are in place, the system functions autonomously and sends an email alert when it detects schema change.
One thing to note: A vast majority of the Snaps used are core Snaps, and the solution can be extended to other endpoints where schema or metadata can be read.
Because there isn’t enough space in this article to explain exactly how to set this up, we have a video of the process from start to finish. Follow along and start building your own schema-change detection with the SnapLogic Intelligent Integration Platform.
If you want to learn more about our data integration tools and how they can support your teams, download our eBook, A Buyer’s Guide to Application and Data Integration.
