When application has been running in production or uat for some time there are some bugs that get reported because of bad data quality. This data is either generated by the application or is created by data migration scripts, data conversion scripts. The application suffers with non-deterministic errors especially is some specific environments while similar errors are not noticed in other environments.
Lots of time hand coded SQL is written in the applications for performance sake, getting data using complex SQL out of the tables. As the database structure changes it becomes really hard to find out all the SQL that needs to be changed. In other cases SQL is generated based on certain conditions in the code and its hard to find if the generated SQL is valid after database changes are done.
When accessing the database using stored procedures for basic Create, Read, Update and Delete (CRUD) functions, or when you want to write triggers that capture the before and after values in tables, or when you want to create Plain Old Java Objects (POJO’s) that match the database objects, writing these by hand takes a lot of effort and also since the requirements are changing in an agile projects at a frequent rate, there will be design changes to meet the requirement changes, so the triggers, CRUD stored procedures or Data Access Objects (DAO) are going to be out of data, instead of hand coding, its better to generate the code, using the metadata of the database
Traditionally the data-team is used to sitting in their own area and working for many project teams by handling requests either via a ticketing system or vi email. The hand-over of work or throwing of work over the wall creates knowledge silos and inefficiencies.
Many a times ER models are created by the data team and are not shared outside of the data team generally for the lack of tools licenses, since its not feasible for the entire team to purchase licenses for the ER modelling tools such as Erwin Data Modeller or Er Studio
In all new development and sometimes during legacy codebase modernization, developers tend to add code quality checks and static analysis of codebase such as style checks, bug finders, cyclomatic complexity checking etc. into the CI/CD pipeline. When we inherit a codebase that has much PL/SQL and there is a desire to put the PL/SQL code base through the same types of code analysis, what options does a developer/dba have?
Loading data into tables is needed many times on projects to load test, Liquibase provides a method to load data into tables with lots of customization. In the example shown below, I’m loading zip code data with the following column layout
"Zipcode","ZipCodeType","City","State","LocationType","Lat","Long","Location","Decommisioned","TaxReturnsFiled","EstimatedPopulation","TotalWages"
Many projects need addition of identical columns to all the tables created by the project. Audit columns are an example of such a requirement. The requirement is to add columns such as created_by, created_date, modified_by and modified_date to all the tables, these columns store, who created the row, when the row was created, who modified the row last and when was it modified. created_by and created_date are required to be present when the row is inserted and thus are required to be not nullable. Adding these columns to each and every table is a lot of work for developers.
In many development shops, developers are not allowed to access the database schema directly, and are not allowed to create tables, indexes, views etc, instead are given access via a different schema that allows SELECT, UPDATE and DELETE access on data. The general reason is to avoid developers creating database objects without
IN many projects, there are tables which need default audit columns such as Created_By, Created_Date, Modified_By, Modified_date and other columns that need to be updated every time some actions are done against the tables and/or columns. This type of functionality can be implemented using triggers.