Agile Database Development: Culture and Practices

All architectures become iterative because of unknown unknowns, agile just recognizes this and does it sooner. – Mark Richards

Here are some practices that help Database development, and Data engineering teams in adapting to agile environment, where you don’t know much about requirements or data formats in advance. Also discusses where a separate specialist DB Development is appropriate.

When do you need a DB Development or Data Engineering Team?

In most software development or maintenance projects, the team members who develop the web or  mobile applications also design & develop the #database (DB) objects. Some companies have a separate DB team to handle the DB Development part (This is not DBA group). This DB Team generally works on multiple projects. They create the tables, stored procedures, etc. based on the requirements.

This is especially important if you have data that requires specialists – Terabytes of data, complex data, data collected from multiple sources that need lots of cleansing and standardization, or sensitive data with compliance/security needs. While the Python, .NET, etc developers do know how to create tables and write queries, the requirements of DB performance or compliance mayn’t be met if Application developers do the database tasks.

You will need a separate data engineering team to take care of data cleansing, standardizing, splitting or merging, matching etc. Else, your data analytics / data science team has to do these tasks, and they mayn’t be experts in these, nor happy doing these.

Why this write-up?

Most of the detailed materials (Books, Articles, Videos, etc) available on applying the Agile principles in the Database area are related to #DataWarehousing projects. There are some related to Database Design & Development, but they talk about philosophies, strategies, tools, or high-level practices (e.g.: Refactoring). I wanted to share some of the lessons learned from my database projects, and specific day-to-day practices our teams developed

Conflict between App & DB Teams

For a project to be successful, both the Application (a.k.a. front-end teams) development team & DB teams must be operating at similar pace/speed. I have worked on projects where the production DBAs insisted that they need 4-days’ advance notice to deploy the DB code to production. But, on many occasions, the application team requested DB changes till the day of deployment. You know the typical Stability Vs Functionality arguments.

The DB Teams need to realize the Application development teams, due to market pressures, or other reasons, have embraced Agile processes and tools. We can’t insist that we need some weeks to analyze the requirements & design the DB. Nobody in the project teams have the detailed requirements at the start. We need to embrace agile – incremental & iterative development of DB

Context: Where do these apply?

In an agile environment, you don’t get to know ALL the business requirements, data formats, etc at the start of a project. Think of a hypothetical project that involves storing & processing Driver License (DL) details of all 50 US States. Or, another hypothetical project that involves some data from all 3100 Counties in US. This type of projects are generally multi-year projects. It is unlikely that you will get sample data from all counties, or all Department of Motor Vehicles (DMV or DPS or whatever else) at the start of a project

Let’s say you get a few sample DL data from Texas. The data type of the key column, Driver Licence Number, is integer. You assumed that the Driver Licence Number will always be an integer, and proceed to create tables and other DB objects where you assign INT as the data type for Driver Licence Number. After a while, you get data from California DMV, and there you find Driver Licence Number is alpha-numeric. At this stage, you have created hundreds of DB objects (tables, stored procs, code in SSIS & other places).

Agility starts with culture (i.e. change mindset and attitudes)

You can search the definitions for Agile Mindset, Agility, Agile Principles, etc. If we need to be agile, we should be willing to change the way we work and communicate. Since the release cycle is short (a sprint is 2 to 4 weeks long), speed is essential.

• Forget that you are part of “Back-end Team”, and really be an integral part of the Project Team
• Prefer in-person meetings, quick meetings, “walk over to the Business Analysts & Application Developers and talk” instead of long emails and scheduled weekly project meetings
• Be happy with whiteboard drawings for conceptual data model, process flows, SoP (execution steps) for data engineering / data services projects, etc. (and may be taking photos to share – if your company policies allow), and not spend time in creating a lot of diagrams and lengthy documents. If this is difficult to digest, remember that clients pay for deliverables (results), not long & neat documents

Agile Database Practices

This isn’t a comprehensive list of practices. This is my list, and it is a WIP

• Investigate if NoSQL “databases”, like MongoDB or Cassandra may be appropriate for some projects. Suppose you get input data in JSON format, and there are variations inside the JSON, you may be better off with a Document DB like MongoDB
• When there’s a lot of uncertainty about the volume of data, or processing requirements, consider using #AWS or #Azure, or private cloud (if your org has one). If you don’t know whether you need a 96 GB or 160 GB RAM, 2 TB or 8 TB HDD (storage), you can’t pick a local server. Yes, you can later migrate from a smaller local server to a bigger server, but the complications – including approvals, software licensing, are a lot
• Explore and implement automated, continuous testing / regression testing
• Do a high-level modelling at the beginning of the project or program increment (PI in #SAFe), and light-weight modelling at every Sprint
• Consciously build flexibility in your data structures & code. This helps to deal not only with “Known unknowns”, but also “Unknown unknowns”

Examples for flexibility in data structures & code

• Use Views (even if there’s just one table) in Stored Procedures. If your original table isn’t sufficient and you need to add a table (or even a few tables in a new DB), you just change the View definition. Unless the additional columns are needed in any specific SP, there is no need to change every Stored Procedure
• Use flexible data types – especially if you haven’t received the data from all regions, companies, etc. or you are getting data from outside (e.g.: Govt Agencies). Please see “Context: Where do these apply?” above. Today a key column may be 6 digits, so you may be tempted to define it as INT or NUMERIC(6). But it may become alphanumeric in a few months, or for some regions. So, use VARCHAR instead of INT in places where there’s a potential for data type change.
• Use large data types. Don’t be stingy with the size. You have a 3-character data? Don’t assign CHAR(3). Assign something like VARCHAR(30). You don’t waste much space in Hard-disk/SSD, and you don’t need to change your code, test and deploy if that 3-character becomes 11 later
• Have standards to identify if the DB objects are in-use or not in use. When requirements change, some of your tables and SPs become obsolete. But, you don’t want to drop them right away because it is chaotic. We prefix __ (double underscore) to the names of these objects. You may also use something like __ to identify temporary use DB objects
• Don’t scatter your code. In any #RDBMS, you can write code in SPs, Functions, Triggers, SQL Jobs, SSIS Packages, etc. It’s better if your team limits the code to just a few places. In the companies I have worked, Triggers are big NO.