BBampaa

Lessons › SQL › Deep Dive: Database Admin & ETL

SQL lesson

Building a cleaning log table to track changes

A cleaning log is a table where every change you make is written down: which row, which column, the old value and the new one.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

Keeping a record of every fix

CREATE TABLE cleaning_log (record_id TEXT, column_name TEXT, old_value TEXT, new_value TEXT);
INSERT INTO cleaning_log VALUES ('O1004', 'revenue', '-20', '20');
SELECT * FROM cleaning_log

Result:

record_idcolumn_nameold_valuenew_value
O1004revenue-2020

Step by step

  1. One column for each thing you'll want to know later.
    CREATE TABLE cleaning_log (record_id TEXT, column_name TEXT, old_value TEXT, new_value TEXT);
  2. Each fix becomes one row: the record, the column, the old and the new value.
    CREATE TABLE cleaning_log (record_id TEXT, column_name TEXT, old_value TEXT, new_value TEXT);
INSERT INTO cleaning_log VALUES ('O1004', 'revenue', '-20', '20');
  3. SELECT the log to see every change so far.
    CREATE TABLE cleaning_log (record_id TEXT, column_name TEXT, old_value TEXT, new_value TEXT);
INSERT INTO cleaning_log VALUES ('O1004', 'revenue', '-20', '20');
SELECT * FROM cleaning_log;

Practice

Try each one in your head first, then open the answer.

1. Create a table called cleaning_log with columns record_id TEXT, old_value TEXT and new_value TEXT.
CREATE TABLE cleaning_log (record_id TEXT, old_value TEXT, new_value TEXT)
2. cleaning_log (record_id, column_name, old_value, new_value) already exists. Log that order O1004's revenue changed from '-20' to '20'.
INSERT INTO cleaning_log VALUES ('O1004', 'revenue', '-20', '20')
3. cleaning_log (record_id, column_name, old_value, new_value) already exists. Log that O1002's category changed from 'clothing' to 'Clothing', then show everything in cleaning_log.
INSERT INTO cleaning_log VALUES ('O1002', 'category', 'clothing', 'Clothing'); SELECT * FROM cleaning_log
4. Create cleaning_log (record_id TEXT, column_name TEXT, old_value TEXT, new_value TEXT), log that O1004's revenue changed from '-20' to '20', then show the log.
CREATE TABLE cleaning_log (record_id TEXT, column_name TEXT, old_value TEXT, new_value TEXT); INSERT INTO cleaning_log VALUES ('O1004', 'revenue', '-20', '20'); SELECT * FROM cleaning_log
5. cleaning_log already exists. In one INSERT, log two fixes: O1004 revenue '-20' to '20', and O1005 category 'beauty ' to 'Beauty'. Then show the log.
INSERT INTO cleaning_log VALUES ('O1004', 'revenue', '-20', '20'), ('O1005', 'category', 'beauty ', 'Beauty'); SELECT * FROM cleaning_log

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →
About 5–10 minutes · no sign-up needed