The Subtle Power Of SQL’s Overlooked DML Function

One function you gloss over has the power to save you hours of development time — and preserve data accuracy.

Like many 90s-2000s kids, I’ve been watching the mini series Quiet On The Set, the behind-the-scenes chronicle of misdeeds on Nickelodeon sets, with a mixture of horror, disbelief and morbid curiosity. The show is interspersed with interviews and clips of shows featuring the stars being interviewed. In the midst of the darkness, one of the funny revelations was revisiting old clips and rewatching what we, as child viewers, believed qualified as profanity. One word stood out among the nonsensical rest: “Crud.”

Aside from being a helpful supplement for adult language, in data operations CRUD is one of the first acronyms every SQL student learns, shortly after DML (data manipulation language) and DDL (data declaration language).

If you’re unfamiliar, CRUD stands for the four operations that can be performed on most databases, including:

• Create
• Read
• Update
• Delete

As a data engineer, my work typically falls into the Create, Read and Delete categories. To deliver data in a timely manner, it’s impractical for a data pipeline to simply drop and re-create an entire table day-over-day. So we use DELETE statements to delete data corresponding with certain date windows, like 7, 14…