Chapter 4 — SQL for governance checks

Chapter 4 — SQL for governance checks

Lesson map

Lesson 04 published. Map, example, EX010.

flowchart TD
  N0["Learning outcomes"]
  N1["4.1 Basic queries"]
  N2["4.2 Orphans"]
  N3["4.3 Duplicates"]
  N4["4.4 Reusable checks"]
  N0 --> N1 --> N2 --> N3 --> N4
  N4 --> EX["Worked example"]
  EX --> Q["Exercise EX010"]

Logical drawing

flowchart LR
  N0["Learning outcomes"]
  N1["4.1 Basic queries"]
  N2["4.2 Orphans"]
  N3["4.3 Duplicates"]
  N4["4.4 Reusable checks"]
  N0 --> N1 --> N2 --> N3 --> N4 --> Q["EX010"]

[Learning outcomes]
    |
    v
[4.1 Basic queries]
    |
    v
[4.2 Orphans]
    |
    v
[4.3 Duplicates]
    |
    v
[4.4 Reusable checks]
    |
    v
[Worked example] --> [Exercise EX010]

Learning outcomes

You will (1) write SQL queries to check data; (2) find orphans and duplicates; (3) save queries as reusable checks.

4.1 Basic queries

Using SQLite, Ravi writes queries against synthetic Calder Valley tables. He counts jobs by priority, finds jobs with missing contractors and lists properties without a recorded gas check in the past year. Each query answers one governance question. Ms. Moreau asks him to write the question as a comment above each query, so others understand its purpose. He notes that SQL is often the quickest way to answer quality questions in systems built on relational databases. The comments make his queries easy to review.

4.2 Orphans

An orphan is a record referring to something that does not exist, such as a job referring to a property identifier not in the property table. Ravi uses a left join to find jobs whose property is missing. He finds a handful, created when properties were sold and removed from the system while open jobs remained. He reports them to the repairs steward, who closes the jobs and asks IT to prevent property deletion while jobs are open. IT applies the restriction within a month.

4.3 Duplicates

Ravi looks for duplicate jobs: the same property, the same problem description and dates within two days. He uses a self-join and finds pairs where tenants reported a problem twice and call handlers created two jobs. Duplicates inflate job counts and may send two contractors. He shares examples with the call centre manager, who adds a step to check for open jobs at the property before creating a new one. The number of duplicate jobs falls in the following month, and contractors report fewer wasted visits to properties.

4.4 Reusable checks

Ravi saves useful queries in the repository with a name, purpose, owner and expected result. He builds a small Python script using sqlite3 that runs all saved checks and writes results to a table with the date. Ms. Moreau notes that turning queries into a check library makes them repeatable and reviewable, and that this is how many organisations build their first data quality monitoring before buying any specialised tool. The steward reviews the results table each morning and raises an issue when a check result changes unexpectedly.

Worked example

Ravi writes commented SQL checks, finds orphan jobs from sold properties, finds duplicate jobs from repeat reports and builds a script that runs saved checks and records results.

Synthetic case. Tables are synthetic.

Errors and limits

Uncommented queries lose their purpose; state the question.

Exercises

• EX010 — Orphan cause. Why did orphan jobs exist? Rubric: 3 = properties sold and removed with open jobs;; 2 = partly correct;; 1 = vague;; 0 = none.
• EX011 — Duplicate rule. How did Ravi define a duplicate job? Rubric: 3 = same property and problem within two days;; 2 = partly correct;; 1 = partial;; 0 = none.
• EX012 — Call centre fix. What step did the call centre add? Rubric: 3 = check for open jobs at the property first;; 2 = partly correct;; 1 = vague;; 0 = none.

Continue studying — lesson 04 (chapter 04 of 30)

← Chapter 03 · Course chapter index · Chapter 05 →

Continue to the next authored lesson.