For simple data validation like Price > 0, you can implement in the application loading into the table, or in the application reading from the table, or you can use table constraints.
* reusable — What if another source system (beside LE) needs to load into this table? Table constraints are automatically reusable with zero effort.
* testing — Table constraints are extremely simple and reliable that we need not test them.
* Table constraints are easier to switch on/off. We can drop/add each constraint individually, without source code migration and regression tests
* flexible — we can adjust a table constraint more easily than in application. Drop and create a new constraint. No source code change. No test required. No regression test either.
* modular — table constraints are inherently more modular and less coupled than application modules. Each constraint exists on its own and can be removed and adjusted on its own. They don’t interfere with each other.
* risk — Table constraints are more reliable and there will be less risk of bad data affecting our trailers. Validation in application can fail due to bugs. That’s why people measure “test coverage”.
* gate keeper — Table constraints are more reliable than validations in application. They are gate keepers — There’s absolutely no way to bypass a constraint, not even by bcp.
* visible — Table constraints are more visible and gives us confidence that no data could possibly exist in violation of the rule.
* data quality — You know …. both suffer from data quality. They know they should validate more, but validation in application is non-trivial. Reality is we are short on resources.
* if we keep a particular validation as a table constraint, then we don’t have to check that particular validation inside the loader AND again in the downstream trailer calculator (less testing too). You mentioned reusable valiation module. This way we don’t need any.
* hand-work — In contingency situations, it’s extremely valuable to have the option to issue SQL insert/update/bcp. Table constraints will offer some data validation. From my experience, i have not seen many input feed tables that never need hand work. I believe within 6 months after go-live, we will need hand insert/update on this table.
* Informatica — Informatica is a huge investment waiting for ROI and we might one day consider using it to load lot data. Table constraints work well with Informatica.
* LOE — The more validation we implement in application, the higher the total LOE. That’s one reason we have zero constraint in our tables. We are tight on resources.
* As a principle, people usually validate as early as possible, and avoid inserting any invalid data at all. Folks reading our
tables (perhaps from another team) may not know “Hey this is a raw input table so not everything is usable.” Once we load stuff into
a commissions table, people usually think it’s usable data. Out of our 100+ tables, do you know which ones can have invalid data?