Audit CMDB Data Quality

Use SQL to audit CMDB data and build a governance remediation pack.

Introduction

30 Second Summary

Technology teams make everyday decisions from records that say what exists. Those records also show who supports each system. A stale server record can delay an incident response when every minute matters.

In this project, you will build a quality-control simulation for a Configuration Management Database. DuckDB runs SQL checks that expose defects before controlled remediation proves the scores improved.

What You'll Build

Your local audit presents 12 prioritized exceptions beside a score history showing all four data-quality dimensions at 100.0% after remediation.

By the end of this project, you'll have:

  • A live CMDB audit you can run to profile configuration items by class or source.
  • A prioritized exception register covering missing ownership, duplicate identifiers, stale records, invalid statuses, orphaned CIs, and broken references.
  • A management-ready evidence pack published in Confluence Cloud with three linked Jira Cloud remediation work items plus a reusable interview narrative.
  • Secret Mission: Replace the universal freshness rule with class-specific risk-based thresholds that generate a scalable exception queue.

Do I need anything before starting?

You need Visual Studio Code on a Mac plus an Atlassian account with Jira Cloud and Confluence Cloud enabled.

No prior SQL or CMDB experience is required.

Before We Start

This checkpoint ties every technical decision to six risks in a Configuration Management Database (CMDB): missing ownership, duplicate identifiers, stale CIs, invalid lifecycle values, orphaned CIs, and broken relationships. Management should care because unreliable CMDB data weakens incident impact analysis, change decisions, accountability, and audit evidence.

Set Up the Local CMDB Lab

Enterprise Configuration Management Database investigations need a reproducible workspace. A local lab lets you practise SQL evidence and governance controls without waiting for access to a production ITSM platform.

In this step, you will install DuckDB, create the cmdb-data-quality workspace, add the supplied project files, and load the sample configuration items into a persistent database.

In this step, get ready to:
  • Install DuckDB CLI 1.5.6 and confirm your Atlassian products are available.
  • Create the local workspace in Visual Studio Code.
  • Load the sample CIs and relationships into a persistent DuckDB database.
Verify DuckDB and Atlassian access

The DuckDB CLI runs the project SQL against local CSV files. The project pins version 1.5.6 so your commands and results match the lab.

  • Check whether the required DuckDB version is already installed by running this command:
~/.duckdb/cli/latest/duckdb --version

What does this command do?

This runs the DuckDB executable from the install script's standard location. The version output confirms which CLI release your terminal uses.

✔️ I see version 1.5.6 or higher

Your output begins with DuckDB v1.5.6. Your local SQL engine is ready for the lab.

ⓧ I see an older version

The existing executable points to an older DuckDB release. Running the official installer updates the latest location before checking it again.

  • Install the current DuckDB CLI and verify the updated version by running:
curl https://install.duckdb.org | sh
~/.duckdb/cli/latest/duckdb --version

What do these commands do?

The first command downloads and runs DuckDB's official macOS installer. The second command checks the executable stored at ~/.duckdb/cli/latest/duckdb.

  • Confirm the output begins with DuckDB v1.5.6.

Still seeing the older version?

Close your current terminal window after the installer finishes. Open a new terminal window before running the version check again.

If the installer fails to download, check your internet connection before retrying it.

Help me troubleshoot the DuckDB CLI installation on macOS.

ⓧ Command not found

DuckDB is not installed at the expected location yet. The official installer creates the CLI path used throughout this project.

  • Install the current DuckDB CLI and verify the installation by running:
curl https://install.duckdb.org | sh
~/.duckdb/cli/latest/duckdb --version

What do these commands do?

The installer places the current DuckDB binary at ~/.duckdb/cli/latest/duckdb. The version check proves that your terminal can run it from that location.

  • Confirm the output begins with DuckDB v1.5.6.

Installer or version check failing?

Make sure you copied both commands exactly. Run the version check only after the installer reports that it has finished.

Help me diagnose a missing DuckDB CLI installation.

That is the first dependency sorted. DuckDB 1.5.6 can now create and query the local CMDB database.

The final publication step uses Jira Cloud and Confluence Cloud through your existing Atlassian site. Confirming access now prevents an account problem from interrupting the governance work later.

  • Return to your Atlassian site in the browser.
  • Confirm your site gives you access to Jira Cloud.
  • Confirm your site gives you access to Confluence Cloud.

Missing an Atlassian product?

Use your Atlassian account's product setup flow to add the missing Free product to your site. Ask your site administrator for access if another person manages product availability.

Help me confirm Jira Cloud and Confluence Cloud availability on my Atlassian site.

Create the lab workspace

The workspace separates source data, SQL controls, governance documents, and generated evidence. Keeping those artifacts together makes the audit repeatable.

  • Move to your Desktop by running this command:
cd ~/Desktop

What does this command do?

This changes the terminal's current location to your Desktop. The project folder will be easy to find from Finder and Visual Studio Code.

  • Create the project folder structure and enter the new folder by running:
mkdir -p cmdb-data-quality/{data,sql,docs,output}
cd cmdb-data-quality

How is the workspace organised?

  • The data folder stores the two source CSV files.
  • The sql folder stores the load, profile, audit, and remediation scripts.
  • The docs folder stores the governance pack and interview glossary.
  • The output folder receives evidence exported by later SQL scripts.
  • Press Cmd+Space to open Spotlight.
  • Type Visual Studio Code into Spotlight.
  • Press Enter to open Visual Studio Code.
  • Click File in the top menu bar.
  • Click Open Folder.
  • Select the cmdb-data-quality folder on your Desktop.
  • Click Open.

Visual Studio Code now shows the data, sql, docs, and output folders in the project sidebar.

  • Save the supplied CI dataset as data/cmdb_ci.csv.
  • Save the supplied relationship dataset as data/ci_relationships.csv.

You will see both CSV files inside the data folder.

  • Save the supplied governance evidence pack as docs/governance_pack.md.
  • Save the supplied interview reference as docs/interview_glossary.md.

You will see both Markdown files inside the docs folder.

  • Save the load script as sql/00_load.sql.
  • Save the profiling script as sql/00_profile.sql.

You will see the first two SQL scripts inside the sql folder.

  • Save the audit script as sql/01_audit.sql.
  • Save the remediation script as sql/02_remediate.sql.

The sql folder now contains four scripts in execution order.

✔️ Awesome, I've got everything!

Great. Double check that all eight supplied files are saved inside the matching data, docs, and sql folders.

ⓧ I'd like to double check the full code

Compare each project file with the complete references below. The paths and contents must match before you load the database.

ci_id,ci_name,ci_class,serial_number,owner,support_group,status,data_source,last_discovered
CI001,WEB-01,Server,SRV-1001,Alice Wong,Infrastructure Operations,Operational,Discovery,2026-10-10
CI002,WEB-02,Server,SRV-1002,,Infrastructure Operations,Operational,Discovery,2026-10-11
CI003,DB-01,Server,SRV-1003,Jordan Smith,Database Operations,Operational,Discovery,2026-05-01
CI004,DB-01-COPY,Server,SRV-1003,Jordan Smith,Database Operations,Operational,Discovery,2026-10-12
CI005,APP-PAY,Application,APP-2001,Priya Shah,Application Support,Operational,Manual,2026-10-15
CI006,APP-HR,Application,APP-2002,,,Operational,Manual,2026-10-15
CI007,SW-OLD,Software,SW-3001,Mateo Ruiz,Endpoint Operations,Retired,Import,2026-01-15
CI008,NET-01,Network Device,NET-4001,Chen Li,Network Operations,Operational,Discovery,2026-10-18
CI009,APP-CRM,Application,APP-2003,Dana Brooks,Application Support,Active,Manual,2026-10-17
CI010,SRV-ORPH,Server,SRV-1004,Amina Yusuf,Infrastructure Operations,Operational,Discovery,2026-10-18
CI011,APP-FIN,Application,APP-2004,Leo Martin,Finance Applications,Operational,Integration,2026-10-16
CI012,APP-FIN-2,Application,APP-2005,Leo Martin,,Operational,Integration,2026-10-16

What is in this dataset?

This CSV contains twelve configuration items from four CI classes. Its deliberate ownership, lifecycle, freshness, uniqueness, and relationship defects support the later audit.

relationship_id,parent_ci_id,relationship_type,child_ci_id,data_source
REL001,CI001,Hosts,CI005,Discovery
REL002,CI002,Depends on,CI008,Discovery
REL003,CI003,Connects to,CI008,Discovery
REL004,CI004,Connects to,CI008,Discovery
REL005,CI008,Supports,CI001,Discovery
REL006,CI005,Depends on,CI003,Manual
REL007,CI011,Hosted on,CI001,Integration
REL008,CI012,Depends on,CIX99,Integration

What is in the relationship data?

This CSV contains eight directed relationships. The final relationship references a missing child CI so the audit can detect a referential-integrity failure.

CREATE OR REPLACE TABLE cis AS
SELECT *
FROM read_csv(
    'data/cmdb_ci.csv',
    header = true,
    columns = {
        'ci_id': 'VARCHAR',
        'ci_name': 'VARCHAR',
        'ci_class': 'VARCHAR',
        'serial_number': 'VARCHAR',
        'owner': 'VARCHAR',
        'support_group': 'VARCHAR',
        'status': 'VARCHAR',
        'data_source': 'VARCHAR',
        'last_discovered': 'DATE'
    }
);

CREATE OR REPLACE TABLE relationships AS
SELECT *
FROM read_csv(
    'data/ci_relationships.csv',
    header = true,
    columns = {
        'relationship_id': 'VARCHAR',
        'parent_ci_id': 'VARCHAR',
        'relationship_type': 'VARCHAR',
        'child_ci_id': 'VARCHAR',
        'data_source': 'VARCHAR'
    }
);

SELECT COUNT(*) AS loaded_ci_count FROM cis;
SELECT COUNT(*) AS loaded_relationship_count FROM relationships;

What does the load script do?

The script creates persistent cis and relationships tables from the CSV files. Its final queries return the loaded row counts.

SELECT
    ci_class,
    COUNT(*) AS ci_count
FROM cis
GROUP BY ci_class
ORDER BY ci_class;

SELECT
    data_source,
    COUNT(*) AS ci_count
FROM cis
GROUP BY data_source
ORDER BY data_source;

SELECT
    100.0 * SUM(CASE WHEN owner IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) AS owner_completeness_pct,
    100.0 * SUM(CASE WHEN support_group IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) AS support_group_completeness_pct
FROM cis;

SELECT
    data_source,
    COUNT(*) AS records_missing_owner
FROM cis
WHERE owner IS NULL
GROUP BY data_source
ORDER BY data_source;

What does the profile script measure?

The profiling queries summarize CI classes and data sources. They also calculate ownership completeness before grouping missing owners by source.

CREATE OR REPLACE TABLE duplicate_serials AS
SELECT
    serial_number,
    COUNT(*) AS serial_count
FROM cis
GROUP BY serial_number
HAVING COUNT(*) > 1;

CREATE OR REPLACE TABLE valid_relationships AS
SELECT r.*
FROM relationships r
JOIN cis parent_ci
    ON r.parent_ci_id = parent_ci.ci_id
JOIN cis child_ci
    ON r.child_ci_id = child_ci.ci_id;

CREATE OR REPLACE TABLE relationship_counts AS
SELECT
    ci_id,
    COUNT(*) AS relationship_count
FROM (
    SELECT parent_ci_id AS ci_id FROM valid_relationships
    UNION ALL
    SELECT child_ci_id AS ci_id FROM valid_relationships
) relationship_endpoints
GROUP BY ci_id;

CREATE OR REPLACE TABLE ci_health_results AS
SELECT
    c.ci_id,
    c.ci_name,
    c.ci_class,
    c.data_source,
    CASE
        WHEN c.owner IS NOT NULL
         AND c.support_group IS NOT NULL
         AND c.serial_number IS NOT NULL
        THEN 1 ELSE 0
    END AS completeness_pass,
    CASE
        WHEN d.serial_number IS NULL
         AND (c.status = 'Retired' OR c.last_discovered >= DATE '2026-07-21')
         AND c.status IN ('Operational', 'Retired')
        THEN 1 ELSE 0
    END AS correctness_pass,
    CASE
        WHEN c.status IN ('Operational', 'Retired')
        THEN 1 ELSE 0
    END AS compliance_pass,
    CASE
        WHEN c.ci_class NOT IN ('Server', 'Application')
          OR c.status = 'Retired'
          OR rc.relationship_count IS NOT NULL
        THEN 1 ELSE 0
    END AS relationship_health_pass
FROM cis c
LEFT JOIN duplicate_serials d
    ON c.serial_number = d.serial_number
LEFT JOIN relationship_counts rc
    ON c.ci_id = rc.ci_id;

CREATE OR REPLACE TABLE dimension_scores AS
SELECT
    'Completeness' AS dimension,
    100.0 * SUM(completeness_pass) / COUNT(*) AS score_pct
FROM ci_health_results
UNION ALL
SELECT
    'Correctness' AS dimension,
    100.0 * SUM(correctness_pass) / COUNT(*) AS score_pct
FROM ci_health_results
UNION ALL
SELECT
    'Compliance' AS dimension,
    100.0 * SUM(compliance_pass) / COUNT(*) AS score_pct
FROM ci_health_results
UNION ALL
SELECT
    'Relationship Health' AS dimension,
    100.0 * SUM(relationship_health_pass) / COUNT(*) AS score_pct
FROM ci_health_results;

CREATE OR REPLACE TABLE quality_score_history AS
SELECT
    'Baseline' AS phase,
    dimension,
    score_pct
FROM dimension_scores;

CREATE OR REPLACE TABLE exceptions AS
SELECT
    ci_id AS record_id,
    'CI' AS record_type,
    'DQ-001' AS rule_id,
    'Completeness' AS dimension,
    'High' AS severity,
    'Owner or support group is missing' AS exception_reason,
    'Mandatory-field control or source mapping is incomplete' AS likely_root_cause,
    'Require ownership fields and reject incomplete inbound records' AS recommended_control,
    'Open' AS exception_status
FROM cis
WHERE owner IS NULL OR support_group IS NULL

UNION ALL

SELECT
    c.ci_id,
    'CI',
    'DQ-002',
    'Uniqueness',
    'High',
    'Serial number identifies more than one CI',
    'Identification rule does not prevent duplicate representations',
    'Review unique identifiers and source precedence before deduplication',
    'Open'
FROM cis c
JOIN duplicate_serials d
    ON c.serial_number = d.serial_number

UNION ALL

SELECT
    ci_id,
    'CI',
    'DQ-003',
    'Timeliness',
    'Medium',
    'Operational CI has not been discovered within 90 days',
    'Discovery schedule or source feed is not updating the record',
    'Monitor discovery execution and escalate missed refresh windows',
    'Open'
FROM cis
WHERE status = 'Operational'
  AND last_discovered < DATE '2026-07-21'

UNION ALL

SELECT
    ci_id,
    'CI',
    'DQ-004',
    'Validity',
    'Medium',
    'Lifecycle status is outside the governed value set',
    'Manual entry permits an uncontrolled status value',
    'Use a controlled value list and validation at entry',
    'Open'
FROM cis
WHERE status NOT IN ('Operational', 'Retired')

UNION ALL

SELECT
    c.ci_id,
    'CI',
    'DQ-005',
    'Relationship Health',
    'High',
    'Operational Server or Application has no valid relationship',
    'Ingestion process does not enforce relationship creation',
    'Validate required relationships before accepting the CI',
    'Open'
FROM cis c
LEFT JOIN relationship_counts rc
    ON c.ci_id = rc.ci_id
WHERE c.status = 'Operational'
  AND c.ci_class IN ('Server', 'Application')
  AND rc.relationship_count IS NULL

UNION ALL

SELECT
    r.relationship_id,
    'Relationship',
    'DQ-006',
    'Referential Integrity',
    'High',
    'Relationship points to a CI that does not exist',
    'Integration mapping does not validate parent and child identifiers',
    'Add lookup validation and route failed relationships to an exception queue',
    'Open'
FROM relationships r
LEFT JOIN cis parent_ci
    ON r.parent_ci_id = parent_ci.ci_id
LEFT JOIN cis child_ci
    ON r.child_ci_id = child_ci.ci_id
WHERE parent_ci.ci_id IS NULL OR child_ci.ci_id IS NULL;

COPY exceptions TO 'output/exceptions.csv';

SELECT * FROM dimension_scores ORDER BY dimension;
SELECT COUNT(*) AS exception_count FROM exceptions;

What does the audit script prepare?

The audit builds multidimensional health checks and a prioritized exception register. It exports the failed records to output/exceptions.csv for evidence and follow-up.

UPDATE cis
SET owner = 'Morgan Lee'
WHERE ci_id = 'CI002';

UPDATE cis
SET owner = 'Taylor Kim',
    support_group = 'People Applications'
WHERE ci_id = 'CI006';

UPDATE cis
SET support_group = 'Finance Applications'
WHERE ci_id = 'CI012';

UPDATE cis
SET last_discovered = DATE '2026-10-14'
WHERE ci_id = 'CI003';

UPDATE cis
SET status = 'Operational'
WHERE ci_id = 'CI009';

DELETE FROM relationships
WHERE parent_ci_id = 'CI004' OR child_ci_id = 'CI004';

DELETE FROM cis
WHERE ci_id = 'CI004';

UPDATE relationships
SET child_ci_id = 'CI001'
WHERE relationship_id = 'REL008';

INSERT INTO relationships (
    relationship_id,
    parent_ci_id,
    relationship_type,
    child_ci_id,
    data_source
)
VALUES
    ('REL009', 'CI006', 'Hosted on', 'CI001', 'Manual'),
    ('REL010', 'CI009', 'Hosted on', 'CI001', 'Manual'),
    ('REL011', 'CI010', 'Depends on', 'CI008', 'Discovery');

CREATE OR REPLACE TABLE duplicate_serials AS
SELECT
    serial_number,
    COUNT(*) AS serial_count
FROM cis
GROUP BY serial_number
HAVING COUNT(*) > 1;

CREATE OR REPLACE TABLE valid_relationships AS
SELECT r.*
FROM relationships r
JOIN cis parent_ci
    ON r.parent_ci_id = parent_ci.ci_id
JOIN cis child_ci
    ON r.child_ci_id = child_ci.ci_id;

CREATE OR REPLACE TABLE relationship_counts AS
SELECT
    ci_id,
    COUNT(*) AS relationship_count
FROM (
    SELECT parent_ci_id AS ci_id FROM valid_relationships
    UNION ALL
    SELECT child_ci_id AS ci_id FROM valid_relationships
) relationship_endpoints
GROUP BY ci_id;

CREATE OR REPLACE TABLE ci_health_results AS
SELECT
    c.ci_id,
    c.ci_name,
    c.ci_class,
    c.data_source,
    CASE
        WHEN c.owner IS NOT NULL
         AND c.support_group IS NOT NULL
         AND c.serial_number IS NOT NULL
        THEN 1 ELSE 0
    END AS completeness_pass,
    CASE
        WHEN d.serial_number IS NULL
         AND (c.status = 'Retired' OR c.last_discovered >= DATE '2026-07-21')
         AND c.status IN ('Operational', 'Retired')
        THEN 1 ELSE 0
    END AS correctness_pass,
    CASE
        WHEN c.status IN ('Operational', 'Retired')
        THEN 1 ELSE 0
    END AS compliance_pass,
    CASE
        WHEN c.ci_class NOT IN ('Server', 'Application')
          OR c.status = 'Retired'
          OR rc.relationship_count IS NOT NULL
        THEN 1 ELSE 0
    END AS relationship_health_pass
FROM cis c
LEFT JOIN duplicate_serials d
    ON c.serial_number = d.serial_number
LEFT JOIN relationship_counts rc
    ON c.ci_id = rc.ci_id;

CREATE OR REPLACE TABLE dimension_scores AS
SELECT
    'Completeness' AS dimension,
    100.0 * SUM(completeness_pass) / COUNT(*) AS score_pct
FROM ci_health_results
UNION ALL
SELECT
    'Correctness' AS dimension,
    100.0 * SUM(correctness_pass) / COUNT(*) AS score_pct
FROM ci_health_results
UNION ALL
SELECT
    'Compliance' AS dimension,
    100.0 * SUM(compliance_pass) / COUNT(*) AS score_pct
FROM ci_health_results
UNION ALL
SELECT
    'Relationship Health' AS dimension,
    100.0 * SUM(relationship_health_pass) / COUNT(*) AS score_pct
FROM ci_health_results;

INSERT INTO quality_score_history
SELECT
    'After remediation' AS phase,
    dimension,
    score_pct
FROM dimension_scores;

COPY quality_score_history TO 'output/quality_score_history.csv';

SELECT *
FROM quality_score_history
ORDER BY dimension, phase;

SELECT COUNT(*) AS remaining_failed_ci_checks
FROM ci_health_results
WHERE completeness_pass = 0
   OR correctness_pass = 0
   OR compliance_pass = 0
   OR relationship_health_pass = 0;

What does the remediation script prepare?

The script applies controlled record and relationship corrections before recalculating the same health checks. It preserves before-and-after scores in output/quality_score_history.csv.

# CMDB Data Quality Audit and Remediation Pack

## Executive summary

The baseline audit found 12 rule exceptions across configuration items and relationships. The lowest aggregate result was Relationship Health at 66.7%. Completeness and Correctness were both 75.0%, while Compliance was 91.7%. The remediation corrected the demonstration records, but the recommended operating model also addresses upstream controls so the same defects are less likely to recur.

Management message: This is not only a bad-record problem. It is a control-design and process-ownership problem involving manual entry, identification logic, discovery cadence, integration mapping, and unclear accountability.

## Scope and data definitions

| Data element | Business definition | Example control use |
| --- | --- | --- |
| Configuration item | A tracked component used to represent enterprise technology or an application | Unit evaluated by CMDB quality rules |
| CI class | Category that groups similar configuration items | Determines applicable rules and ownership |
| Serial number | Identifier used in this scenario to distinguish physical or logical items | Uniqueness and duplicate detection |
| Owner | Person accountable for the CI's business or operational context | Completeness and escalation routing |
| Support group | Team responsible for operational maintenance | Completeness and remediation assignment |
| Lifecycle status | Governed state describing whether the CI is operational or retired | Validity and compliance |
| Data source | Process or integration that created or updated the record | Root-cause segmentation and lineage |
| Last discovered | Most recent source refresh date | Timeliness and stale-CI detection |
| CI relationship | Directed link between a parent CI and child CI | Dependency mapping and relationship health |

## Data quality rule catalogue

The thresholds below are scenario-specific governance decisions, not ServiceNow defaults.

| Rule ID | Dimension | Rule | Measure | Threshold | Exception owner |
| --- | --- | --- | --- | --- | --- |
| DQ-001 | Completeness | Owner, support group, and serial number must be populated | Passing CIs divided by in-scope CIs | At least 95% | CI Class Owner |
| DQ-002 | Uniqueness | Serial number must identify one CI | Unique serial records divided by in-scope records | At least 99% | CMDB Product Owner |
| DQ-003 | Timeliness | Operational CIs must be discovered within 90 days | Fresh operational CIs divided by operational CIs | At least 90% | Discovery Product Owner |
| DQ-004 | Validity | Status must be Operational or Retired | CIs using the governed value set divided by in-scope CIs | 100% | Data Steward |
| DQ-005 | Relationship Health | Operational Servers and Applications must have a valid relationship | Passing scoped CIs divided by scoped CIs | At least 95% | CI Class Owner |
| DQ-006 | Referential Integrity | Parent and child identifiers must resolve to existing CIs | Valid relationships divided by all relationships | 100% | Integration Owner |

## Baseline findings

| Finding | Symptom | Root cause hypothesis | Downstream impact |
| --- | --- | --- | --- |
| Missing ownership | Blank owner or support group | Mandatory fields are not enforced consistently across Discovery, Manual, and Integration sources | Exceptions cannot be routed to an accountable team |
| Duplicate identifier | Two CIs use `SRV-1003` | Identification criteria do not prevent duplicate representations | Incidents and changes can reference conflicting CIs |
| Stale CI | Operational server has not refreshed within the scenario window | Discovery schedule or source feed did not update the CI | Decisions may rely on infrastructure that no longer reflects reality |
| Invalid lifecycle value | Manual record uses `Active` outside the governed set | Free-text or uncontrolled entry bypasses validation | Reporting and lifecycle controls become inconsistent |
| Orphaned CI | Operational Server or Application has no valid relationship | Ingestion accepts CIs without required dependency context | Impact analysis and service mapping are incomplete |
| Broken relationship | Relationship points to `CIX99`, which does not exist | Integration mapping lacks referential-integrity validation | Dependency reports contain invalid topology |

## Root-cause analysis

Use the sequence symptom, pattern, source, process, control gap, owner.

1. Confirm the failed rule and affected records.
2. Segment exceptions by CI class and data source.
3. Identify the upstream process that created the pattern.
4. Distinguish a one-off data defect from a systemic control failure.
5. Assign an accountable owner and a responsible steward.
6. Define preventive, detective, and corrective controls.
7. Re-run the quality rule and retain evidence.

## Control design

| Control type | Scenario control | Evidence |
| --- | --- | --- |
| Preventive | Require owner and support group before accepting an inbound CI | Approved rule, field mapping, or validation design |
| Preventive | Apply governed lifecycle values and identifier rules | Data definition and source-system configuration |
| Detective | Run SQL quality checks and compare results with thresholds | SQL script, exception CSV, and score history |
| Detective | Review exceptions by source and class on an agreed cadence | Confluence review record and Jira backlog |
| Corrective | Route exceptions to the CI Class Owner or Integration Owner | Assigned Jira work item |
| Corrective | Validate the fix by re-running the same rule | After-remediation score and zero-failure query |

## Exception-management workflow

Detect -> log -> triage -> assign -> investigate root cause -> choose correct, accept, or escalate -> remediate -> validate -> capture evidence -> close -> monitor recurrence.

Minimum exception fields: rule ID, affected record, severity, owner, root cause, remediation, due date, validation result, status, and evidence link.

## RACI

| Activity | CMDB Product Owner | CI Class Owner | Data Steward | Integration Owner | Governance Council |
| --- | --- | --- | --- | --- | --- |
| Approve definitions and thresholds | A | C | R | C | I |
| Monitor scores and exception trends | A | C | R | C | I |
| Correct source records | I | A | R | C | I |
| Correct integration mappings | I | C | C | A/R | I |
| Approve risk acceptance | R | C | C | C | A |
| Report improvement to management | A | C | R | C | I |

R means Responsible, A means Accountable, C means Consulted, and I means Informed. Each activity has one accountable role.

## Remediation plan

| Priority | Action | Accountable role | Success measure |
| --- | --- | --- | --- |
| 1 | Enforce ownership and support-group values at ingestion | CI Class Owner | DQ-001 meets or exceeds 95% |
| 2 | Review identification criteria and source precedence | CMDB Product Owner | DQ-002 meets or exceeds 99% |
| 3 | Monitor discovery cadence and stale exceptions | Discovery Product Owner | DQ-003 meets or exceeds 90% |
| 4 | Restrict lifecycle values to the governed set | Data Steward | DQ-004 remains at 100% |
| 5 | Validate relationship endpoints and required links | Integration Owner | DQ-005 meets 95% and DQ-006 remains at 100% |

## Before-and-after results

| Dimension | Baseline | After remediation |
| --- | ---: | ---: |
| Completeness | 75.0% | 100.0% |
| Correctness | 75.0% | 100.0% |
| Compliance | 91.7% | 100.0% |
| Relationship Health | 66.7% | 100.0% |

## Operating procedure

1. Load the latest CI and relationship extracts into the governed workspace.
2. Run the profiling queries and review volume changes by class and source.
3. Run the audit and compare every rule result with its approved threshold.
4. Export failed records to the exception register.
5. Triage by severity, business impact, source, and accountable owner.
6. Investigate the upstream cause before correcting the record.
7. Track systemic remediation in Jira and document decisions in Confluence.
8. Re-run the same controls, retain before-and-after evidence, and monitor recurrence.

## Control evidence retained

- `sql/00_profile.sql`
- `sql/01_audit.sql`
- `sql/02_remediate.sql`
- `output/exceptions.csv`
- `output/quality_score_history.csv`
- Confluence governance page
- Jira remediation work items

What is in the governance pack?

The pack connects six quality rules to root causes, ownership, controls, remediation, and retained evidence. It gives the later SQL results a management-ready operating context.

# Interview Glossary and Talking Points

## Core terminology

| Term | Interview-ready meaning |
| --- | --- |
| ITSM | The processes and practices used to design, deliver, support, and improve IT services. |
| CMDB | A repository that represents configuration items and the relationships between them. |
| Configuration item or CI | A tracked technology component, application, service, or related item whose attributes and relationships support IT operations. |
| CMDB Health | A control framework for monitoring the quality of CI data with KPIs and supporting metrics. |
| Completeness | Whether required data values are present. |
| Correctness | In this CMDB context, whether CIs avoid integrity defects such as duplicates, orphans, and staleness. |
| Compliance | Whether actual values meet defined standards or audit expectations. |
| Relationship Health | Whether CI relationships resolve correctly and represent usable dependency context. |
| Accuracy | Whether data reflects the real-world entity or agreed source of truth. |
| Consistency | Whether related values follow the same rules without contradiction. |
| Timeliness or freshness | Whether data is sufficiently current for its intended use. |
| Uniqueness | Whether one real-world item has one governed representation. |
| Validity | Whether values follow defined formats, types, ranges, or allowed-value rules. |
| Data profiling | Examining structure, distributions, missing values, duplicates, and other statistics before defining or testing controls. |
| Data-quality rule | A testable business expectation applied to data. |
| Measure | The calculation used to quantify performance against a rule. |
| Threshold | The cutoff that determines whether the measured result is acceptable. |
| Exception | A record, relationship, or result that fails a rule and requires correction, approval, escalation, or risk acceptance. |
| Root-cause analysis or RCA | Investigation of the upstream process, ownership, integration, or control failure that created recurring bad data. |
| Data owner | The accountable individual or group responsible for managing a data asset or domain. |
| Data steward | The operational role that maintains definitions, quality standards, rules, metadata, and day-to-day quality processes. |
| Data lineage | The trace of where data originated, how it changed, and where it moved. |
| RACI | Responsible, Accountable, Consulted, and Informed responsibility assignments. |
| Preventive control | A project label for a control that blocks bad data before acceptance, such as mandatory-field validation. |
| Detective control | A project label for a check that identifies a defect, such as the SQL audit. |
| Corrective control | A project label for approved remediation after detection, followed by revalidation. |
| Control evidence | The retained output proving a control was designed, executed, reviewed, and validated. |
| Identification and Reconciliation Engine or IRE | ServiceNow's framework for identifying CIs across sources, preventing duplicates, and controlling which sources may update attributes. |
| Identification rule | Criteria used to determine whether incoming data matches an existing CI. |
| Reconciliation rule | Logic that manages authorization and update priority between data sources. |
| Orphan CI | A CI that lacks the valid relationship context required by its class or use case. |
| Stale CI | A CI that has not been updated within its governed freshness window. |
| Remediation | A managed corrective action applied to a failed quality result. |
| Risk acceptance | A documented decision to tolerate an exception temporarily, with an accountable approver and review conditions. |

## Strong interview phrases

- I did not stop at correcting failed records. I segmented exceptions by source and CI class to identify upstream control gaps.
- I translated business expectations into rule logic, measures, thresholds, exception ownership, and retained control evidence.
- I separated accountability from execution by assigning one accountable owner while the steward remained responsible for day-to-day monitoring.
- I used SQL to quantify the baseline, isolate noncompliant records, validate remediation, and demonstrate measurable improvement.
- I treated duplicates as an identification and reconciliation problem, not merely a deletion exercise.
- I linked detective controls to preventive process changes so improvement could be sustained.
- Where ownership was disputed, I used a RACI and a documented escalation path to drive consensus.

## STAR interview story

Situation: A simulated CMDB contained missing owners, duplicate identifiers, stale CIs, invalid lifecycle values, orphaned CIs, and a broken relationship.

Task: Establish quality rules and thresholds, identify systemic causes, clarify ownership, and provide management with a defensible remediation plan.

Action: I profiled the data in SQL, mapped failures to quality dimensions, generated an exception register, segmented issues by source, completed root-cause analysis, assigned RACI responsibilities, designed preventive and detective controls, and tracked systemic fixes through Jira and Confluence.

Result: The demonstration scores improved from 75.0% Completeness, 75.0% Correctness, 91.7% Compliance, and 66.7% Relationship Health to 100.0% across all four dimensions, with zero remaining failed CI checks in the controlled dataset.

## Stakeholder facilitation response

If two teams reject ownership, start with the data-creation and maintenance process rather than personal preference. Confirm who has decision rights, who can change the source process, who performs daily monitoring, and who bears the business impact. Capture one Accountable role, the Responsible steward, Consulted technical teams, and Informed management. If agreement is still blocked, document the decision, options, impacts, and escalation path for the governance council.

How does the glossary support the project?

The glossary turns the technical investigation into interview-ready language. It includes governance terms, CMDB concepts, a STAR narrative, and a stakeholder-facilitation response.

Missing a project file?

Check that each path uses the exact folder and filename shown in the reference tab. A misplaced CSV or SQL file prevents DuckDB from resolving the relative paths.

Make sure each file is saved before running the loader. An unsaved editor tab does not provide the on-disk content DuckDB needs.

Help me compare my CMDB lab files with the required project structure.

Load the sample CMDB data

A persistent database keeps the loaded tables available across separate DuckDB sessions. The load script creates the database file and replaces both tables from the current CSV sources.

Before you run the loader, what two kinds of records do you expect the database to count?

  • Create the persistent database and load both CSV files by running this command from the cmdb-data-quality folder:
~/.duckdb/cli/latest/duckdb cmdb_quality.duckdb < sql/00_load.sql

What does this command do?

DuckDB opens or creates cmdb_quality.duckdb before executing sql/00_load.sql. Input redirection sends the complete SQL file into the CLI.

The script creates cis from data/cmdb_ci.csv. It creates relationships from data/ci_relationships.csv.

You will see loaded_ci_count as 12. You will also see loaded_relationship_count as 8.

Your lab is live. The persistent CMDB now holds twelve configuration items and eight relationships for the investigation.

Counts missing or loader failing?

Confirm your terminal is still inside the cmdb-data-quality folder. Relative paths fail when the command runs from a different location.

Check that data/cmdb_ci.csv, data/ci_relationships.csv, and sql/00_load.sql match the reference tab exactly.

Help me troubleshoot the CMDB load command and unexpected row counts.

Your local CMDB lab is ready with persistent source tables and the complete evidence workspace. Next up, you will profile the configuration items and expose the limits of a completeness-only review.

Profile the Configuration Items

Your local Configuration Management Database (CMDB) now contains 12 configuration items. It also contains eight relationships.

A completeness-only review can create false confidence. In this step, you will use SQL data profiling to test what the loaded records can actually prove.

In this step, get ready to:
  • Profile configuration-item counts by class.
  • Measure completeness across ownership fields.
  • Segment missing owners by data source.
Map the CMDB shape

Data profiling compresses individual records into patterns you can interpret. DuckDB groups the records so you can see where volume is concentrated.

Before you run the profile, predict whether four summary tables can reveal every quality risk in the CMDB.

  • Switch back to the macOS Terminal from earlier.
  • Profile the loaded tables from the cmdb-data-quality folder by running this command:
~/.duckdb/cli/latest/duckdb cmdb_quality.duckdb < sql/00_profile.sql

What does this profile do?

  • The SELECT keyword chooses the columns or calculations returned by each query.
  • The COUNT(*) expression counts records in each result.
  • The GROUP BY clause creates separate buckets for each class or source.
  • The WHERE clause keeps records with a missing owner in the final query.
  • The ORDER BY clause presents the grouped results in a predictable order.

The class table shows five Application CIs. It also shows five Server CIs.

The remaining class rows show one Network Device CI. They also show one Software CI.

The source table shows six Discovery CIs. It also shows three Manual CIs.

The remaining source rows show two Integration CIs. They also show one Import CI.

Good progress. You have turned 12 individual records into a clear view of the CMDB population.

The script ends after the missing-owner table. It produces no result for duplicates, freshness, lifecycle validity, orphaned CIs, or broken references.

Profile command not working?

Confirm that your Terminal prompt is inside the cmdb-data-quality folder. The relative path to sql/00_profile.sql depends on that location.

If the tables are unavailable, confirm that the command points to the existing cmdb_quality.duckdb database.

Help me diagnose the profiling command.

Trace missing ownership to its source

Completeness measures whether required values are present. The third query calculates separate completeness percentages for owners and support groups.

  • Return to the profile output in the macOS Terminal from earlier.
  • Find the owner_completeness_pct result.
  • Find the support_group_completeness_pct result.

Both completeness results are roughly 83.3 percent. Each measure has two missing values across the 12 CIs.

A total percentage shows the scale of a symptom. Source segmentation narrows where that symptom enters the CMDB.

  • Move to the final result table in the Terminal output.
  • Match each data_source value with its records_missing_owner count.

Discovery contributes one record with a missing owner. Manual contributes one record with a missing owner.

What does source segmentation add?

The source result moves your investigation closer to the process that created each gap. The pattern gives you a focused starting point for root-cause analysis without claiming that the source itself is already proven responsible.

Define what this profile proves

The profile proves that ownership gaps exist across more than one source. Its narrow scope leaves several other quality dimensions unmeasured.

Which risks remain invisible?

  • Duplicate CIs can share one serial number without affecting a missing-field percentage.
  • Stale CIs can keep every required field while carrying an old discovery date.
  • Invalid lifecycle values can remain populated while falling outside the governed value set.
  • Orphaned CIs can pass field checks while lacking usable relationship context.
  • Referential integrity failures can survive when a relationship points to a missing CI.

This is the designed obstacle. The profile exposes missing-field symptoms while other quality dimensions remain invisible.

  • Compare the existing sql/00_profile.sql file with the complete reference below.

✔️ Awesome, I've got everything!

Your profiling file contains four read-only queries. Your Terminal output includes all four result tables.

ⓧ I'd like to double check the full code

Your complete sql/00_profile.sql file should match this reference:

SELECT
    ci_class,
    COUNT(*) AS ci_count
FROM cis
GROUP BY ci_class
ORDER BY ci_class;

SELECT
    data_source,
    COUNT(*) AS ci_count
FROM cis
GROUP BY data_source
ORDER BY data_source;

SELECT
    100.0 * SUM(CASE WHEN owner IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) AS owner_completeness_pct,
    100.0 * SUM(CASE WHEN support_group IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) AS support_group_completeness_pct
FROM cis;

SELECT
    data_source,
    COUNT(*) AS records_missing_owner
FROM cis
WHERE owner IS NULL
GROUP BY data_source
ORDER BY data_source;

How do the four queries fit together?

  • The first query counts CIs by ci_class.
  • The second query counts CIs by data_source.
  • The third query calculates owner completeness and support-group completeness.
  • The fourth query isolates missing owners by data_source.

Before you rerun the profile, predict which two source names will appear in the missing-owner result.

  • Confirm the completed profile by running this command again:
~/.duckdb/cli/latest/duckdb cmdb_quality.duckdb < sql/00_profile.sql

Why rerun the same profile?

Every statement in this script is a SELECT query. The rerun reads the same persistent tables without changing their records.

Your class profile again shows five Server CIs. It also shows five Application CIs.

Two more class rows show one Network Device CI. They show one Software CI.

Both ownership completeness results remain roughly 83.3 percent. The missing-owner profile shows one Discovery record plus one Manual record.

The cis table remains at 12 rows. The relationships table remains at eight rows.

Results do not match?

Confirm that the Terminal is using the cmdb_quality.duckdb file inside your cmdb-data-quality folder.

Check that data/cmdb_ci.csv still contains the original 12 source records from CI001 through CI012.

Help me compare my profile results.

Strong work. You have shown exactly where a completeness-only profile stops.

Next, you will turn broader quality expectations into measurable rules. Those rules will produce a prioritized exception register.

Build the Exception Register

Your first round of data profiling exposed missing ownership in the Configuration Management Database (CMDB). It also proved that a completeness-only view leaves several risks hidden.

You will now use DuckDB and SQL to test multiple data-quality dimensions. These checks convert business expectations into CMDB Health-style scores and accountable exceptions.

In this step, get ready to:
  • Connect six quality rules to measurable SQL checks.
  • Generate dimension scores and a prioritized exception register.
  • Explain the results with CMDB governance terminology.
Connect rules to measurable checks

A data-quality rule states the expected condition. Its measure calculates performance while its threshold defines the acceptable result.

  • In the VS Code Explorer sidebar, select docs/governance_pack.md.
  • Scroll to Data quality rule catalogue.
  • Compare the rule, measure, threshold, and exception owner for DQ-001 through DQ-006.
  • Return to the VS Code Explorer sidebar.
  • Select sql/01_audit.sql.
  • Find the CREATE OR REPLACE TABLE duplicate_serials AS statement.
  • Follow the script from duplicate detection through relationship validation and CI health scoring.

✔️ Awesome, I've got everything!

Your existing sql/01_audit.sql file contains the quality checks, score calculation, exception register, export, and final verification queries.

ⓧ I'd like to double check the full code

Compare your existing sql/01_audit.sql file with this complete reference:

CREATE OR REPLACE TABLE duplicate_serials AS
SELECT
    serial_number,
    COUNT(*) AS serial_count
FROM cis
GROUP BY serial_number
HAVING COUNT(*) > 1;

CREATE OR REPLACE TABLE valid_relationships AS
SELECT r.*
FROM relationships r
JOIN cis parent_ci
    ON r.parent_ci_id = parent_ci.ci_id
JOIN cis child_ci
    ON r.child_ci_id = child_ci.ci_id;

CREATE OR REPLACE TABLE relationship_counts AS
SELECT
    ci_id,
    COUNT(*) AS relationship_count
FROM (
    SELECT parent_ci_id AS ci_id FROM valid_relationships
    UNION ALL
    SELECT child_ci_id AS ci_id FROM valid_relationships
) relationship_endpoints
GROUP BY ci_id;

CREATE OR REPLACE TABLE ci_health_results AS
SELECT
    c.ci_id,
    c.ci_name,
    c.ci_class,
    c.data_source,
    CASE
        WHEN c.owner IS NOT NULL
         AND c.support_group IS NOT NULL
         AND c.serial_number IS NOT NULL
        THEN 1 ELSE 0
    END AS completeness_pass,
    CASE
        WHEN d.serial_number IS NULL
         AND (c.status = 'Retired' OR c.last_discovered >= DATE '2026-07-21')
         AND c.status IN ('Operational', 'Retired')
        THEN 1 ELSE 0
    END AS correctness_pass,
    CASE
        WHEN c.status IN ('Operational', 'Retired')
        THEN 1 ELSE 0
    END AS compliance_pass,
    CASE
        WHEN c.ci_class NOT IN ('Server', 'Application')
          OR c.status = 'Retired'
          OR rc.relationship_count IS NOT NULL
        THEN 1 ELSE 0
    END AS relationship_health_pass
FROM cis c
LEFT JOIN duplicate_serials d
    ON c.serial_number = d.serial_number
LEFT JOIN relationship_counts rc
    ON c.ci_id = rc.ci_id;

CREATE OR REPLACE TABLE dimension_scores AS
SELECT
    'Completeness' AS dimension,
    100.0 * SUM(completeness_pass) / COUNT(*) AS score_pct
FROM ci_health_results
UNION ALL
SELECT
    'Correctness' AS dimension,
    100.0 * SUM(correctness_pass) / COUNT(*) AS score_pct
FROM ci_health_results
UNION ALL
SELECT
    'Compliance' AS dimension,
    100.0 * SUM(compliance_pass) / COUNT(*) AS score_pct
FROM ci_health_results
UNION ALL
SELECT
    'Relationship Health' AS dimension,
    100.0 * SUM(relationship_health_pass) / COUNT(*) AS score_pct
FROM ci_health_results;

CREATE OR REPLACE TABLE quality_score_history AS
SELECT
    'Baseline' AS phase,
    dimension,
    score_pct
FROM dimension_scores;

CREATE OR REPLACE TABLE exceptions AS
SELECT
    ci_id AS record_id,
    'CI' AS record_type,
    'DQ-001' AS rule_id,
    'Completeness' AS dimension,
    'High' AS severity,
    'Owner or support group is missing' AS exception_reason,
    'Mandatory-field control or source mapping is incomplete' AS likely_root_cause,
    'Require ownership fields and reject incomplete inbound records' AS recommended_control,
    'Open' AS exception_status
FROM cis
WHERE owner IS NULL OR support_group IS NULL

UNION ALL

SELECT
    c.ci_id,
    'CI',
    'DQ-002',
    'Uniqueness',
    'High',
    'Serial number identifies more than one CI',
    'Identification rule does not prevent duplicate representations',
    'Review unique identifiers and source precedence before deduplication',
    'Open'
FROM cis c
JOIN duplicate_serials d
    ON c.serial_number = d.serial_number

UNION ALL

SELECT
    ci_id,
    'CI',
    'DQ-003',
    'Timeliness',
    'Medium',
    'Operational CI has not been discovered within 90 days',
    'Discovery schedule or source feed is not updating the record',
    'Monitor discovery execution and escalate missed refresh windows',
    'Open'
FROM cis
WHERE status = 'Operational'
  AND last_discovered < DATE '2026-07-21'

UNION ALL

SELECT
    ci_id,
    'CI',
    'DQ-004',
    'Validity',
    'Medium',
    'Lifecycle status is outside the governed value set',
    'Manual entry permits an uncontrolled status value',
    'Use a controlled value list and validation at entry',
    'Open'
FROM cis
WHERE status NOT IN ('Operational', 'Retired')

UNION ALL

SELECT
    c.ci_id,
    'CI',
    'DQ-005',
    'Relationship Health',
    'High',
    'Operational Server or Application has no valid relationship',
    'Ingestion process does not enforce relationship creation',
    'Validate required relationships before accepting the CI',
    'Open'
FROM cis c
LEFT JOIN relationship_counts rc
    ON c.ci_id = rc.ci_id
WHERE c.status = 'Operational'
  AND c.ci_class IN ('Server', 'Application')
  AND rc.relationship_count IS NULL

UNION ALL

SELECT
    r.relationship_id,
    'Relationship',
    'DQ-006',
    'Referential Integrity',
    'High',
    'Relationship points to a CI that does not exist',
    'Integration mapping does not validate parent and child identifiers',
    'Add lookup validation and route failed relationships to an exception queue',
    'Open'
FROM relationships r
LEFT JOIN cis parent_ci
    ON r.parent_ci_id = parent_ci.ci_id
LEFT JOIN cis child_ci
    ON r.child_ci_id = child_ci.ci_id
WHERE parent_ci.ci_id IS NULL OR child_ci.ci_id IS NULL;

COPY exceptions TO 'output/exceptions.csv';

SELECT * FROM dimension_scores ORDER BY dimension;
SELECT COUNT(*) AS exception_count FROM exceptions;

How is the audit organized?

  • The first three tables identify duplicate serial numbers and valid relationship endpoints.
  • The ci_health_results table records whether each CI passes the four aggregate dimensions.
  • The dimension_scores table converts pass flags into percentage scores.
  • The exceptions table turns failed rules into an accountable work queue.
Trace failures into accountable exceptions

A score shows the scale of a quality problem. An exception identifies the specific record or relationship that needs correction, escalation, or documented acceptance.

  • In sql/01_audit.sql, find the CREATE OR REPLACE TABLE exceptions AS statement.
  • Trace the six UNION ALL branches from DQ-001 through DQ-006.
  • Compare the severity assigned to each rule.
  • Match each exception reason to its likely root cause.
  • Match each root-cause hypothesis to its recommended control.
  • Return to docs/governance_pack.md.
  • Read the Exception-management workflow to identify what happens after detection.

What separates a threshold from an exception?

A threshold is the approved cutoff for acceptable performance. The DQ-001 threshold requires at least 95% of in-scope CIs to contain the required ownership fields.

An exception is a failed record or relationship. These thresholds are governance decisions for this scenario, so they should not be presented as vendor defaults.

Translate the evidence into interview language

  • Data profiling summarizes distributions and defects before detailed controls are applied.
  • A stale CI has missed its governed freshness window.
  • An orphan CI lacks the valid relationship context required by its class.
  • A duplicate CI gives one real-world item more than one governed representation.
  • Referential integrity requires relationship endpoints to resolve to existing CIs.
  • A source-system defect points to an upstream process or mapping failure.
  • The Identification and Reconciliation Engine (IRE) identifies CIs across sources and controls which sources can update attributes.
Run and inspect the audit

The audit starts by removing only the previous generated exception export. Your source CSV files and persistent database remain intact.

Before you run the audit, make a quick prediction about which quality dimension will score lowest.

  • Switch back to the macOS Terminal from earlier.
  • Rebuild the audit tables and export a fresh exception register by running this command:
rm -f output/exceptions.csv && ~/.duckdb/cli/latest/duckdb cmdb_quality.duckdb < sql/01_audit.sql

What does this audit create?

  • The duplicate and relationship tables expose integrity problems that the first profile missed.
  • The ci_health_results table applies Completeness, Correctness, Compliance, and Relationship Health checks to every CI.
  • The quality_score_history table retains the four scores under the Baseline phase.
  • The COPY statement writes the failed records to output/exceptions.csv.

Good work. Your terminal now shows four dimension scores and an exception_count of 12.

  • Return to the VS Code Explorer sidebar.
  • Expand the output folder.
  • Select exceptions.csv.
  • Count the 12 data rows beneath the header.
  • Scan the rule_id column for rules DQ-001 through DQ-006.
  • Check the exception_status column for an Open status on every row.
  • Trace one failed record across its reason, root cause, and recommended control.

What should the register contain?

  • Three DQ-001 exceptions identify missing owners or support groups.
  • Two DQ-002 exceptions identify the duplicate serial number.
  • One DQ-003 exception identifies the stale operational CI.
  • One DQ-004 exception identifies the invalid lifecycle value.
  • Four DQ-005 exceptions identify operational CIs without valid relationships.
  • One DQ-006 exception identifies the broken relationship reference.

Missing the export or seeing a different count?

Confirm that Terminal still points to the cmdb-data-quality folder. The script resolves its input and output paths from that location.

If the count differs, confirm that cis still contains 12 rows and relationships still contains 8 rows.

Check the SQL statement named in the terminal output against the full-file reference above.

Help me diagnose my CMDB audit output.

Your audit now exposes the defects that the first profile could not see. Next, you will trace those exceptions to upstream causes and prove that controlled remediation improves every score.

Remediate and Validate Quality

Your DuckDB audit has turned twelve CMDB defects into a prioritized exception register. The next goal is to trace those patterns back to the processes that created them.

Record correction can improve today's scores without preventing tomorrow's defects. Root-cause analysis connects each exception pattern to an upstream control gap so the improvement can last.

In this step, get ready to:
  • Trace exception patterns to their upstream process causes.
  • Map each cause to preventive, detective, or corrective controls.
  • Run the remediation script to prove measurable improvement.
Trace exception patterns to root causes

A failed record is a symptom. A root cause identifies the source process or control gap that allowed the defect to enter the database.

  • In the Explorer sidebar in Visual Studio Code, select docs/governance_pack.md.
  • Find the Baseline findings section.
  • Choose one exception pattern from the findings table.
  • Trace its visible symptom to the listed root-cause hypothesis.
  • Move to the Root-cause analysis section.
  • Identify the upstream process that created your chosen pattern.
  • Name the control gap that allowed the pattern to recur.
  • Assign the accountable role listed in the governance pack.

You can now explain why a missing owner points to weak mandatory-field enforcement or incomplete source mapping. That explanation moves the investigation beyond one blank field.

Map root causes to layered controls

Sustainable remediation uses several control layers. Each layer handles a different point in the defect lifecycle.

How do the control layers work?

A preventive control blocks defective data before acceptance. A detective control identifies defects that entered the system.

A corrective control assigns approved remediation after detection. Revalidation proves that the corrective action resolved the failed rule.

  • Find the Control design section in docs/governance_pack.md.
  • Match missing ownership to the preventive mandatory-field control.
  • Match recurring quality checks to the detective SQL audit.
  • Match assigned remediation to the corrective control.

The three layers now form a control loop. Prevention reduces new defects while detection and correction handle the defects that still occur.

  • Find the RACI section.
  • Confirm that each activity has one accountable role.
  • Find the Control evidence retained section.
  • Classify the SQL scripts plus output/exceptions.csv as technical execution evidence.
  • Classify the future Jira work items plus the future Confluence page as workflow and governance evidence.
Apply remediation and validate the result

The remediation changes the persistent database while preserving the original CSV inputs. This separation keeps your original audit scenario available for future demonstrations.

The script deletes CI004 because it is the duplicate representation of serial number SRV-1003. Your source file remains unchanged, so the original record stays recoverable.

  • Return to sql/02_remediate.sql in the Visual Studio Code Explorer sidebar.

✔️ Awesome, I've got everything!

Your existing sql/02_remediate.sql script is ready to update the database and export the score history.

ⓧ I'd like to double check the full code

  • Compare your existing sql/02_remediate.sql file with this reference:
UPDATE cis
SET owner = 'Morgan Lee'
WHERE ci_id = 'CI002';

UPDATE cis
SET owner = 'Taylor Kim',
    support_group = 'People Applications'
WHERE ci_id = 'CI006';

UPDATE cis
SET support_group = 'Finance Applications'
WHERE ci_id = 'CI012';

UPDATE cis
SET last_discovered = DATE '2026-10-14'
WHERE ci_id = 'CI003';

UPDATE cis
SET status = 'Operational'
WHERE ci_id = 'CI009';

DELETE FROM relationships
WHERE parent_ci_id = 'CI004' OR child_ci_id = 'CI004';

DELETE FROM cis
WHERE ci_id = 'CI004';

UPDATE relationships
SET child_ci_id = 'CI001'
WHERE relationship_id = 'REL008';

INSERT INTO relationships (
    relationship_id,
    parent_ci_id,
    relationship_type,
    child_ci_id,
    data_source
)
VALUES
    ('REL009', 'CI006', 'Hosted on', 'CI001', 'Manual'),
    ('REL010', 'CI009', 'Hosted on', 'CI001', 'Manual'),
    ('REL011', 'CI010', 'Depends on', 'CI008', 'Discovery');

CREATE OR REPLACE TABLE duplicate_serials AS
SELECT
    serial_number,
    COUNT(*) AS serial_count
FROM cis
GROUP BY serial_number
HAVING COUNT(*) > 1;

CREATE OR REPLACE TABLE valid_relationships AS
SELECT r.*
FROM relationships r
JOIN cis parent_ci
    ON r.parent_ci_id = parent_ci.ci_id
JOIN cis child_ci
    ON r.child_ci_id = child_ci.ci_id;

CREATE OR REPLACE TABLE relationship_counts AS
SELECT
    ci_id,
    COUNT(*) AS relationship_count
FROM (
    SELECT parent_ci_id AS ci_id FROM valid_relationships
    UNION ALL
    SELECT child_ci_id AS ci_id FROM valid_relationships
) relationship_endpoints
GROUP BY ci_id;

CREATE OR REPLACE TABLE ci_health_results AS
SELECT
    c.ci_id,
    c.ci_name,
    c.ci_class,
    c.data_source,
    CASE
        WHEN c.owner IS NOT NULL
         AND c.support_group IS NOT NULL
         AND c.serial_number IS NOT NULL
        THEN 1 ELSE 0
    END AS completeness_pass,
    CASE
        WHEN d.serial_number IS NULL
         AND (c.status = 'Retired' OR c.last_discovered >= DATE '2026-07-21')
         AND c.status IN ('Operational', 'Retired')
        THEN 1 ELSE 0
    END AS correctness_pass,
    CASE
        WHEN c.status IN ('Operational', 'Retired')
        THEN 1 ELSE 0
    END AS compliance_pass,
    CASE
        WHEN c.ci_class NOT IN ('Server', 'Application')
          OR c.status = 'Retired'
          OR rc.relationship_count IS NOT NULL
        THEN 1 ELSE 0
    END AS relationship_health_pass
FROM cis c
LEFT JOIN duplicate_serials d
    ON c.serial_number = d.serial_number
LEFT JOIN relationship_counts rc
    ON c.ci_id = rc.ci_id;

CREATE OR REPLACE TABLE dimension_scores AS
SELECT
    'Completeness' AS dimension,
    100.0 * SUM(completeness_pass) / COUNT(*) AS score_pct
FROM ci_health_results
UNION ALL
SELECT
    'Correctness' AS dimension,
    100.0 * SUM(correctness_pass) / COUNT(*) AS score_pct
FROM ci_health_results
UNION ALL
SELECT
    'Compliance' AS dimension,
    100.0 * SUM(compliance_pass) / COUNT(*) AS score_pct
FROM ci_health_results
UNION ALL
SELECT
    'Relationship Health' AS dimension,
    100.0 * SUM(relationship_health_pass) / COUNT(*) AS score_pct
FROM ci_health_results;

INSERT INTO quality_score_history
SELECT
    'After remediation' AS phase,
    dimension,
    score_pct
FROM dimension_scores;

COPY quality_score_history TO 'output/quality_score_history.csv';

SELECT *
FROM quality_score_history
ORDER BY dimension, phase;

SELECT COUNT(*) AS remaining_failed_ci_checks
FROM ci_health_results
WHERE completeness_pass = 0
   OR correctness_pass = 0
   OR compliance_pass = 0
   OR relationship_health_pass = 0;

How is the script organized?

  • The first section corrects ownership, support groups, freshness, lifecycle status, duplicate representation, and relationship defects.
  • The middle section rebuilds the supporting health tables from the remediated records.
  • The final section appends the new scores and exports the two-phase history.

Before you run the remediation, which dimensions do you expect to improve after every failed condition is corrected?

  • Remove the earlier score-history export and run the controlled remediation by running this command:
rm -f output/quality_score_history.csv && ~/.duckdb/cli/latest/duckdb cmdb_quality.duckdb < sql/02_remediate.sql

What does this command do?

  • The rm -f command removes the earlier exported history file.
  • DuckDB opens the persistent cmdb_quality.duckdb database and executes sql/02_remediate.sql.
  • The update statements correct accountable ownership, freshness, and lifecycle values.
  • The relationship statements remove the duplicate-linked record and repair the broken reference.
  • The insert adds the missing relationships for the orphaned CIs.
  • The final queries rebuild the scores and export output/quality_score_history.csv.
  • Review the score table printed in the terminal.

You'll see four After remediation rows. Completeness, Correctness, Compliance, and Relationship Health each show 100.0.

  • Review the final failure count printed below the score table.

You'll see remaining_failed_ci_checks equal to 0.

  • Select output/quality_score_history.csv in the Visual Studio Code Explorer sidebar.

You'll see eight score records split between Baseline and After remediation phases. The original output/exceptions.csv remains available as baseline detective-control evidence.

That completes the control loop. Your persistent database now contains eleven remediated CIs plus ten valid relationships with zero failed CI checks.

Scores below 100.0?

Compare sql/02_remediate.sql with the full reference above. A missing update or relationship insert leaves the corresponding health check below target.

If the export is missing, confirm that the output folder still exists inside cmdb-data-quality.

If DuckDB reports a missing table, the database no longer contains the baseline audit tables from the previous step.

Help me diagnose my remediation output.

Your remediation now has root-cause reasoning, layered controls, and measurable evidence behind it. Next, you'll publish that evidence as a shared governance pack with tracked remediation work.

Publish the Governance Pack

Your local audit now proves that the remediated CMDB reaches a quality score of 100.0 across every measured dimension. You also have SQL evidence that explains how the result was achieved.

Management needs a shared operating model that turns this evidence into accountable follow-up. Confluence Cloud will hold the governance narrative. Jira Cloud will track the remediation work.

In this step, get ready to:
  • Create a team-managed Jira Cloud space for CMDB remediation.
  • Publish the governance pack with the interview glossary in Confluence Cloud.
  • Embed three Jira remediation work items in the published page.
Create the Jira remediation space

A Jira Cloud space groups the remediation work in one visible queue. A team-managed Kanban space keeps the workflow focused on this audit.

  • Return to Jira Cloud on your Atlassian site.
  • Select Spaces from the navigation.
  • Select Create space.
  • Choose the Kanban template.
  • Select Use template.
  • Enter CMDB Data Quality Remediation as the space name.
  • Select Team-managed.
  • Select Create.

Solid progress. You should see the new CMDB Data Quality Remediation space with its Kanban board ready for work items.

Can't create the space?

Confirm that Jira Cloud is enabled on the same Atlassian site as your account. Your site administrator may need to grant permission if the space creation option is unavailable.

Atlassian is rolling out updated terminology across sites. Follow the equivalent option that creates a team-managed Kanban space.

Help me create the Jira remediation space.

Build the Confluence evidence page

Your local Markdown files contain the approved source material for the page. Publishing both files connects the management controls with the language you can use to explain the work.

  • Switch back to Visual Studio Code.
  • Select docs/governance_pack.md in the Explorer sidebar.
  • Press Cmd+A on macOS or Ctrl+A on Windows to select the file contents.
  • Press Cmd+C on macOS or Ctrl+C on Windows to copy the selected content.

Why publish from the local files?

The local Markdown files are the governed source text for the Confluence page. This keeps your published explanation aligned with the evidence generated by the audit.

The governance pack provides the operating model. The interview glossary provides the explanation layer.

  • Return to Confluence Cloud on your Atlassian site.
  • Select Create from the navigation.
  • Select your personal space or an existing space as the page location.
  • Start a page from scratch.
  • Enter CMDB Data Quality Audit and Remediation Pack as the page title.
  • Paste the governance pack by pressing Cmd+V on macOS or Ctrl+V on Windows.

You should see the governance pack fill the draft. The content begins with its executive summary before moving into the supporting controls.

  • Switch back to Visual Studio Code.
  • Select docs/interview_glossary.md in the Explorer sidebar.
  • Press Cmd+A on macOS or Ctrl+A on Windows to select the file contents.
  • Press Cmd+C on macOS or Ctrl+C on Windows to copy the selected content.
  • Return to the Confluence draft.
  • Place the cursor below the governance pack.
  • Paste the interview glossary by pressing Cmd+V on macOS or Ctrl+V on Windows.

You should now see Interview Glossary and Talking Points below the governance material. The draft now contains both local evidence documents.

Missing content from the draft?

Confirm that you copied the complete contents of each file from Visual Studio Code. Check that the glossary begins below the governance pack instead of replacing it.

Help me fix the Confluence page content.

Embed remediation work items and publish

The page explains the governance model. Embedded Jira work items show that each systemic issue has a tracked remediation path.

  • Select Insert while editing the Confluence page.
  • Select Jira work item.
  • Select Create new work item.
  • Choose the connected Jira instance if Confluence asks for it.
  • Select CMDB Data Quality Remediation in the Space field.
  • Keep the default option in the Work type field.
  • Enter Prevent missing CI owner and support-group values in the Summary field.
  • Select Insert.

You should see Prevent missing CI owner and support-group values embedded in the page draft.

Labels Can Vary by Site

Atlassian is rolling out its work-item experience across Cloud sites. Follow the equivalent labels that create a Jira work item from the Confluence editor if your site looks different.

  • Select Insert while editing the Confluence page.
  • Select Jira work item.
  • Select Create new work item.
  • Choose the connected Jira instance if Confluence asks for it.
  • Select CMDB Data Quality Remediation in the Space field.
  • Keep the default option in the Work type field.
  • Enter Correct duplicate CI identification and stale discovery controls in the Summary field.
  • Select Insert.

You should see Correct duplicate CI identification and stale discovery controls embedded below the first work item.

  • Select Insert while editing the Confluence page.
  • Select Jira work item.
  • Select Create new work item.
  • Choose the connected Jira instance if Confluence asks for it.
  • Select CMDB Data Quality Remediation in the Space field.
  • Keep the default option in the Work type field.
  • Enter Enforce referential integrity for CI relationships in the Summary field.
  • Select Insert.

You should see Enforce referential integrity for CI relationships embedded with the other two remediation items.

Work item options missing?

Confirm that Jira Cloud and Confluence Cloud are connected through the same Atlassian site. Check that your account can create work items in CMDB Data Quality Remediation.

Help me embed the Jira work items.

Before you publish, pause to predict whether the page contains every required governance artifact plus all three remediation links.

  • Select Publish… from the top right corner of the editor toolbar.
  • Confirm the page location in the publication settings.
  • Confirm the page access in the publication settings.
  • Select Publish.

The editor closes when publishing succeeds. You should return to the published page view.

What should I see?

  • The page title is CMDB Data Quality Audit and Remediation Pack.
  • The governance content includes the six-rule catalogue.
  • The governance content includes the root-cause analysis.
  • The governance content includes the RACI.
  • The governance content includes the operating procedure.
  • The interview glossary appears on the same page.
  • The page embeds Prevent missing CI owner and support-group values.
  • The page embeds Correct duplicate CI identification and stale discovery controls.
  • The page embeds Enforce referential integrity for CI relationships.

Page still a draft?

Return to the draft if the editor stays open. Select Publish… from the editor toolbar before confirming the publication settings.

Help me publish the Confluence page.

That's the evidence pack complete. Your SQL results now connect to a published governance model with tracked remediation work.

Secret mission

Replace One-Size-Fits-All Freshness with Risk-Based Thresholds

Your original freshness control applies one cutoff to every configuration item. Replace it with governed class-specific policies that reflect criticality, update cadence, risk appetite, and accountable ownership. The result is a scalable exception queue backed by exportable control evidence.

Clean Up Your Resources

Clean Up Your Resources

Your evidence pack is complete across the local lab, Jira, and Confluence. Your cleanup choices cost $0 on eligible Free plans. You can keep, pause, or delete the evidence set.

Resources you used:

  • Local cmdb-data-quality folder containing the DuckDB database, source data, SQL scripts, governance documents, policy file, and evidence CSVs.
  • Jira Cloud space CMDB Data Quality Remediation containing three remediation work items.
  • Confluence Cloud page CMDB Data Quality Audit and Remediation Pack containing the governance pack, interview glossary, embedded Jira work items, and risk-based freshness explanation.

Keep everything running

No action is needed. Choose this option if you want to demonstrate the audit or reuse its governance evidence.

  • Retain the cmdb-data-quality folder with its database and exported evidence.
  • Keep the CMDB Data Quality Remediation Jira space with its three work items.
  • Keep the CMDB Data Quality Audit and Remediation Pack Confluence page published.

Pause - I'll come back to this later

There are no local background services to stop. Your database, files, Jira space, and Confluence page remain available without further action.

  • Leave the cmdb-data-quality folder unchanged.
  • Leave the Jira space unchanged.
  • Leave the Confluence page published.

Delete - I don't want to use this again

Deleting this evidence set is permanent. Choose this option only when you no longer need the portfolio demonstration.

  • Move out of the cmdb-data-quality folder and delete it by running these commands:
cd ..
rm -rf cmdb-data-quality
ls

What do these commands remove?

  • The first line moves your terminal to the folder that contains the project.
  • The second line permanently removes the project folder and every local artifact inside it.
  • The final line lists the remaining folders so you can verify the deletion.

You should no longer see cmdb-data-quality in the terminal output.

  • Return to the CMDB Data Quality Remediation space in Jira Cloud.
  • Open the space settings.
  • Use the space deletion control to delete CMDB Data Quality Remediation.
  • Complete the confirmation prompts.
  • Return to Spaces.

You should no longer see CMDB Data Quality Remediation in the space list. Its three remediation work items are removed with it.

  • Return to the CMDB Data Quality Audit and Remediation Pack page in Confluence Cloud.
  • Open the page options.
  • Use the page deletion control to move the page to the space trash.
  • Complete the confirmation prompt.
  • Return to the Confluence space settings.
  • Open the trash area.
  • Permanently delete CMDB Data Quality Audit and Remediation Pack from the trash.
  • Complete the final confirmation prompt.

You should no longer see CMDB Data Quality Audit and Remediation Pack in the Confluence content tree or space trash.

Nice Work!

Nice Work!

You did it! You built an end-to-end Configuration Management Database (CMDB) quality investigation in DuckDB. Your finished evidence set now links SQL findings to accountable process remediation.

You've learned how to:

  • Used SQL profiling to expose every hidden CMDB quality risk in the sample data. This covered missing ownership, duplicate identifiers, stale records, invalid lifecycle values, orphaned CIs, and broken references.
  • Built a governed rule catalogue with measurable thresholds. Mapped exceptions to upstream root causes. Defined accountable ownership through a RACI and a complete control model.
  • Proved measurable remediation by reaching 100.0 across all four after-remediation scores. Published the evidence in Confluence. Tracked systemic fixes through three Jira remediation work items.
  • Secret Mission: Replaced one universal freshness rule with class-specific risk-based thresholds. Generated a governed exception queue for three Server CIs.

Ready to quiz yourself?