Deidentify Driver Data

Deidentify Driver Data

Overview

The Deidentify Driver Data utility lets administrators permanently remove a driver's or employee's personally identifying details from their record, while keeping the record itself (and its related assets, liabilities and addresses) in place.

This process is designed to be run annually, or on whatever regular time frame suits your business, with assistance from Catch-e staff. Catch-e will offer a policy recommendation on the retention time, but you can decide your own timeframe and business rules.

When to Use This

Deidentify personal data when:

  • There is no requirement to keep it for the business.

  • You want to delete the personal data of a driver or employee who has no linked quotes, items or contracts, and/or whose record hasn't been edited in more than 2 years.

  • You want to improve system performance by reducing data volume.

Important — Not Reversible

Updates made by a Deidentify Driver Data job cannot be undone. Test and check your updates carefully before running the process in your Live environment.

Where to Find It

Main Page → Workflow → System Administration → System Maintenance → Deidentify Driver Data

This screen has two related views:

  • Deidentify Driver Data Field List — the reference list further down this article, showing exactly which fields are cleared.

  • Deidentify Driver Data Query — the selection query that targets which drivers/employees are included in a run, detailed in the Query section below.

Setup Steps

  1. Review the selection query used for this process — detailed on the Deidentify Driver Data Query page.

  2. If you want to change the query to suit your business, contact Catch-e Support to raise a task for your requested changes.

  3. Determine the timing of the update.

  4. Test the process in your Staging and Support environments.

  5. Train staff on the process.

  6. Arrange for the job to be scheduled in Live.

Query

A query has been written for use in a scheduler deidentify job and is available in the [gb_queries] table.

  • Name: Driver deidentification query

  • Description: Query to identify drivers that qualify for deidentification

Logic

The query's logic is based on the driver having been inactive for two years. This time frame can be easily updated to suit your needs.

Drivers are excluded if:

  • They have any contracts where the Suspend Date is blank, OR

  • They have any items where the Status is 'Active'

Drivers are included if:

  • The driver record has not been edited in the last two years, AND

  • Linked quotes, contracts and items have not been edited in the last two years

Standard Query

A 'field alias' for driver_id is required in the query, or it cannot be allocated to the scheduler job. In the query below, the field alias is seen as SELECT d.driver_id as driver_id, d.created,....

# Query Record 

select * from gb_queries where name = 'Driver deidentification query'
;

# Query

SELECT d.driver_id as driver_id, d.created, d.last_edit,
	date_format(if(d.last_edit = '0000-00-00', d.created, d.last_edit),'%Y-%m-%d') as driver_date,
    COUNT(c.contract_id) AS contracts,
    COUNT(epi.employee_package_item_id) AS items,
    DATE_FORMAT(MAX(st1.last_edit), '%Y-%m-%d') AS last_edit_records

FROM fm_drivers AS d
	LEFT JOIN fm_contracts AS c ON c.driver_id = d.driver_id AND c.suspend_date = '0000-00-00'
    LEFT JOIN sp_employee_package_items AS epi ON epi.driver_id = d.driver_id AND epi.status_flag IN ('active')
   
   LEFT JOIN
    (SELECT q.driver_id, 'quotes' AS origin,  MAX(q.last_edit) AS last_edit
    FROM qt_quotes AS q
    GROUP BY q.driver_id
    UNION 
    SELECT c.driver_id, 'contracts' AS origin, MAX(c.last_edit) AS last_edit
    FROM fm_contracts AS c
    GROUP BY c.driver_id 
    UNION 
    SELECT epi.driver_id, 'items' AS origin,  MAX(a.timestamp) AS last_edit
    FROM sp_employee_package_items AS epi
    INNER JOIN gb_audit AS a ON a.record_id = epi.employee_package_item_id
    INNER JOIN gb_audit_fields AS af ON af.audit_field_id = a.audit_field_id
        AND af.table_name = 'sp_employee_package_items'
    GROUP BY epi.driver_id) AS st1 ON st1.driver_id = d.driver_id

WHERE c.driver_id IS NULL AND epi.driver_id IS NULL
GROUP BY d.driver_id
HAVING driver_date <= DATE_SUB(CURDATE(), INTERVAL 2 YEAR) AND last_edit_records <= DATE_SUB(CURDATE(), INTERVAL 2 YEAR)
    OR driver_date <= DATE_SUB(CURDATE(), INTERVAL 2 YEAR) AND last_edit_records IS NULL
;

If you want to change the query to suit your business, contact Catch-e Support to raise a task for your requested changes.

How It Works

  1. The scheduler job runs, using the selection query to target drivers/employees for this update.

  2. For each field in the Deidentify Driver Data Field List, the field's value is replaced with un-usable data; numeric fields are set to 0.

  3. An audit record is written for each table updated.

  4. Audit records are themselves updated so that their before/after values are replaced with un-usable data; numeric fields are set to 0.

  5. A driver event records the time of the update.

  6. The driver record is then set as read-only.

Fields Removed by Table

Running Deidentify Driver Data clears the following fields. Fields not listed below are left untouched.

Driver (fm_drivers)

  1. Surname

  2. Given Name

  3. Middle Name

  4. Preferred Name

  5. Gender

  6. Marital Status

  7. Date of Birth

  8. Annual Salary

  9. Driver Licence

  10. Driver Licence Card Number

  11. Home Phone

  12. Mobile

  13. Home Email

  14. Landlord Name

  15. Income — Gross

  16. Income — Net

  17. Income — Spouse Gross

  18. Income — Spouse Net

  19. Income — Rental Net

  20. Income — Government Benefits

  21. Income — Superannuation

  22. Income — Investment

  23. Income — Other

  24. Expense — Mortgage

  25. Expense — Rent

  26. Expense — Household

  27. Expense — Credit Card

  28. Expense — Private Education/Childcare

  29. Expense — Vehicle

  30. Expense — Other

  31. Asset — Bank Accounts

  32. Asset — Resident Property

  33. Asset — Investment Properties

  34. Asset — Other Investments

  35. Asset — Motor Vehicle

  36. Asset — Furniture

  37. Asset — Superannuation

  38. Asset — Shares

  39. Liability — Overdraft

  40. Liability — Mortgages

  41. Liability — Investment Mortgages

  42. Liability — Credit Card Limit

  43. Liability — Rent

  44. Liability — Loans

  45. Liability — Other

  46. Generated Password

Driver Assets (fm_driver_assets)

  1. Amount

Driver Liabilities (fm_driver_liabilities)

  1. Amount

  2. Monthly Payment Amount

Driver Bank Accounts (fm_driver_bank_accounts)

  1. Account Name

  2. BSB

  3. Account Number

Addresses (gb_addresses)

  1. Home Unit No

  2. Home Street No

  3. Address 1

  4. Address 2

  5. Area

  6. State

Audit Trail

Deidentification is itself recorded, not hidden:

  • A new audit record is created for each affected table to record that deidentification has occurred. This shows in the audit's Field column as @deidentify.

  • For each individual field that was cleared, its audit history is updated so that its Value Before / Value After entries are replaced with un-usable data; numeric fields are set to 0 — the history isn't deleted, but it no longer carries recoverable personal data.

  • A driver event records the time of the update.

  • Once processed, the driver record is set to read-only.