When I first joined the programming team at my previous job in an aerospace company, the first observation I made was that their Data Integration System was antiquated – architecture-wise. The system architecture was adopted from legacy mainframes. Most of the business processes have been migrated to modern servers at the time.

But as I dug deeper and deeper into the system, I slowly realized that the architecture stood the test of time and to the changing demands of the modern business world. Old ways of doing things are not necessarily a bad thing when they preserve the fundamental principles of a sound and stable architecture.

Here are some of the things I’ve learned from that experience:

Nothing Beats A Sound Architecture

Everything is anchored in the architecture. You cannot optimize a system that is based on a bad design. You might be skilled in performance tuning but you can only do so much when the problem is the architecture itself. What’s a good design? That’s open to debate. Simply put, a good design is one that allows for stability, flexibility, and scalability.

Leave the Data Validation in ETL

Cleansing of data must be done at the time of the ETL process. The database engine works well with Storing and Retrieving of data. Complicated validation and cleansing routines don’t do the database engine any good. SSIS, just like most ETL Tools, is an in-memory pipeline. Row-By-Row transformation and validations are efficiently handled in memory.

Platform is Just a Tool

One time I asked our Senior System Architect what Server to use – SQL 2008 or 2012 – he said: “I don’t care as long as it runs.” But I gathered what he really meant – that the architecture, when designed correctly, works regardless of the platform, and that a sound architecture does not have to depend on the target platform.

Cursor Doesn’t Scale

At this point, we should just agree that Cursor is a bad thing when there are other viable options that can do the job. Row-by-Row processing doesn’t scale. Period.

Beautiful SQL Doesn’t Translate To Performance

You need a gazillion of JOINs to return a dataset. Good luck with that one. You can write beautiful codes worthy of the Nobel Prize in Literature but the barometer of any codes is their performance. Readability is also a good attribute of good code. Your choice of query optimization techniques is limited by, guess what, the architecture. When you’re spending 70% – 80% of your maintenance effort on the query tuning, then maybe it’s time to re-think the design.

Errors Should Be Actionable

Error Alerts should prompt for action. The process should not just end in error logs or unactionable notifications. Push the errors to the proper stakeholders. The stakeholders should have the ability to correct the data on the fly – probably in a UI context. The corrected data should then be pushed back to the pipeline on the same batch as “late requirements” or exceptions. Don’t wait until the next scheduled batch to reprocess them. A late requirement process should be put in place as part of the system to handle such a case.

Good Design Enables

Good design is an enabler. User enablement is not a feature of the UI. It is an integral part of the system architecture. Good UI functions go as far as the flexibility of the design go – and not further. User Experience is therefore rooted in the architecture.

Institutionalize Peer Review

Let other people scrutinize your work. This is a good practice. There’s a reason peer review happens way before the deployment. Take advantage of the resources and bright minds around you. Let your teammates check your work. Peer Review may reveal potential problems that you might have overlooked during your unit testing or test cases.

You Cannot Over-Document

One cannot just over-document. Documenting is a good practice to develop. Document when necessary. And it’s almost always necessary to document in all circumstances. Sure you don’t have time to put together some quick documentation, but that’s not a good excuse to not do it. Do you want to secure your legacy in the annals of greatest employees? Yes, you got it. Document.

Building a robust system architecture involves a wide array of principles and checkboxes to tick as the project progresses from requirements to delivery. You’ll have a better chance of succeeding when you follow consistent and sensible fundamentals. Old, solid principles don’t die; they don’t fade either.

The post Miscellaneous Notes On System Architecture and Design appeared first on SQL, Code, Coffee, Etc..