Give the data scientists SQL and relational algrebra.
But please don't give them stored procedures and triggers.
But please don't give them stored procedures and triggers.
If you don't track migrations, and track only the latest CREATE statement, you can't deploy to a real system with that.
If you track both migrations and a final state, you'll end up in an awkward situation, because you'll likely have to do your migrations first and rely on automation to render your final state (in how many files? under what criteria?) or manually copy-paste it without making mistakes while coming up with your own structure. Or worse, writing on the final state set and then carefully copying changes back to migrations.
It's certainly not the worst thing in the world, but it is very unfriendly compared to the usual way of development under version control.
We've been experimenting with versioning schemas; deploying new versions of schemas along side existing ones. There is never any question about what version of a stored procedure is in that that schema. Same goes for all of the other schema objects. There is no schema migration tool needed. Of course I have to mention this is in data warehouse scenario and not an operational data store.
Also something similar which uses apgdiff https://github.com/subzerocloud/subzero-cli
But stored procedures are another matter, this could work in a fairly reliable way. Have you used this in any project working in other people? How does it work out?
I'm pretty much with you on stored procedures.
By centralizing data integrity logic in the database, you know where to look and that all apps using the database will abide by it.
I think part of the problem is that there is still this huge chasm between good coding skills and good database skills. It’s hard to have both.
I like the author's point about this:
> The trigger function itself was also written in the “schema.sql” file which meant that everything was clear and documented along with the other relevant schema information, thus easier to track and manage.
In my experience, in most cases the application doing the insert/update/delete is the best place to handle most side effects. That may be an on-demand API, a scheduled/queued job, etc. I've found triggers to be helpful and simple for audit and history tracking. There are definitely plenty of cases where triggers are useful and it probably depends a lot on your architecture.
I'm not as anti-trigger as I am SP. Though I stay away from triggers in any case as the rest of the world is fairly negative on them.