The Haunted Dataset👻How To Find The Ghosts In Your Corporate Systems Data And Fix Them.
It's almost Halloween and in the 'spirit' of the season, I've put together a fun Halloween post that will hopefully also help you find and eliminate those annoying errors in the data.
I know I have spent more time than I care to admit poking around in the dark corners of hundreds of databases. And I can tell you, with the confidence, that every asset register is a little bit haunted.
Yes, but not in a dramatic, flickering-lights way. It is quieter than that. A row here that points at a property that no longer exists. An asset there that was decommissioned in 2014 but still turns up on the depreciation schedule every year, like a very committed poltergeist. These things do not go bump in the night. They go bump in the councillor briefing, right when someone asks why the numbers do not add up. 😱
So, in the spirit of the season (and sometimes dealing with data anomalies can feel like its own kind of horror show!), let me introduce you to the three ghosts most likely to be rattling around your data.
More importantly, let me show you how to find them and send them on their way.
Welcome to the Iamdata Solutions Asset Management Newsletter – October 2026 (The Halloween Edition 🐈⬛ 🎃🕷️)
Ghost One: The Orphans in Your Data
Orphaned records are the saddest of the lot. These are the rows that reference a parent that has left the building. A task assigned to a property that was merged or removed. An asset component whose parent asset was deleted. A CRM linked to a responsible officer who retired three restructures ago.
The record itself sits there quite happily, still counted, still queried, still throwing off totals. But the thing it belongs to is gone, so it drifts through your reports with no home and no context.
How do I find orphaned records with a simple SQL query?
The trick is an anti-join. You go looking for child records whose parent simply is not there.
SELECT t.task_id, t.property_id
FROM Tasks AS t
LEFT JOIN Properties AS p ON t.property_id = p.property_id
WHERE p.property_id IS NULL;
The LEFT JOIN keeps every task, and the WHERE clause keeps only the ones where no matching property came back. Anything in that result set is an orphan. Run the same pattern across every parent-child relationship you rely on and you will quickly learn where your referential integrity has quietly given up.
How do I clean up orphaned records?
Resist the urge to reach straight for DELETE. An orphan is a symptom, and if you only clear the symptom, you will be back here next quarter.
Ask why the parent vanished. Was it a legitimate merge that should have cascaded? A dodgy manual delete? A migration that dropped half the relationship? Fix the cause, then decide whether the orphan should be re-parented, archived, or removed. And always run your SELECT first so you can see exactly what you are about to touch.
Ghost Two: The Zombies in Your Data
Oh no, the zombies. Decommissioned but not departed. These are the assets you have officially retired, disposed of, sold, demolished or written off, and yet there they are, still shuffling around the register, still being depreciated, still inflating your asset count and quietly distorting your renewal forecasts.
Zombie assets are especially dangerous because they look completely alive. They have all their fields filled in. They pass validation. Nothing is technically broken. They are just no longer real, and no one told the database.
How do I find zombie records in my asset register with a simple SQL query?
Look for the contradiction between disposal information and active status. If an asset has a disposal date but is still flagged active, or still carries a written-down value it should have shed, you have found your undead.
SELECT asset_id, asset_name, disposal_date, status, written_down_value
FROM Assets
WHERE disposal_date IS NOT NULL
AND status = 'Active';
A second, sneakier variant is the asset that has quietly sailed past its expiry date and is still being valued as though it will last forever. Assets with an expiry in the past and a condition score that has not moved in a decade are worth a very hard look.
How do I safely retire a decommissioned asset?
The professional move here is almost never a hard delete. Councils need history, audit trails and the ability to answer 'what did we own in 2019' long after the fact. So, reach for a status change or an archive flag rather than the DELETE key. Move disposed assets to a retired state, exclude that state from your live valuation and renewal views, and keep the record for reporting. Your future self, mid-audit, will thank you.
Ghost Three: The Phantom Duplicates in Your Data
The classic. The one that makes your totals swell for no obvious reason. Phantom duplicates are rows that appear more than once, and they come in two flavours. There are the genuine duplicates, where the same record was entered twice by two well-meaning people. And there are the far more common phantoms, the ones a join conjured out of thin air.
I have chased this exact ghost many times. A report was reporting far more rows than the source held, and the culprit turned out to be a join to an address table that returned multiple rows per property. One asset, 2 address records, 2 copies of the asset. Multiply that across a register and your counts balloon spectacularly.
How do I find the phantom duplicate records?
Start by confirming the haunting is real. Count rows per key and see what turns up more than once.
SELECT asset_id, COUNT(*) AS row_count
FROM AssetRegister
GROUP BY asset_id
HAVING COUNT(*) > 1;
If that comes back clean but your final report still doubles up, the phantom is being summoned by a join further downstream. Test each join in isolation. The moment your row count jumps after adding a particular table, you have found the seance.
How do I stop a join creating duplicate rows in my SQL query?
For a join that fans out, the fix is to collapse the many side down to the one row you actually want before it ever multiplies your data. OUTER APPLY with a TOP 1 is my weapon of choice, because it lets you pick exactly which row survives.
SELECT a.asset_id, a.asset_name, addr.full_address
FROM Assets AS a
OUTER APPLY (
SELECT TOP 1 s.full_address
FROM Addresses AS s
WHERE s.property_id = a.property_id
ORDER BY s.seq_num
) AS addr;
The ORDER BY is doing the real work. It decides which address is the canonical one (here the primary sequence number), so every asset gets exactly one, and the phantoms evaporate. For true entered-twice duplicates, ROW_NUMBER partitioned by your natural key lets you rank the copies and keep only the first.
Your data ghost-hunting kit
If you want to make this a habit rather than a panic, here is the short list. Pin it above your desk.
Investigate before you delete
Every clean-up starts with a SELECT, never a DELETE. See the ghost clearly before you decide its fate.
Treat the cause, not the symptom
An orphan, a zombie or a phantom is usually telling you something upstream is broken. Fix the process and the ghosts stop coming back.
Prefer archiving over annihilation
Councils live and die by history. Soft-delete, status flags and archive tables keep the audit trail intact while cleaning your live views.
Test your joins one at a time
When a row count balloons, add your joins back in one by one and watch where the numbers jump. That is your haunting, located.
Schedule the cleansing
A dataset left alone accumulates ghosts. Book a regular data spring clean, and end of financial year is the perfect excuse to walk the whole register with a torch.
Under all the spooky nonsense. A haunted dataset is not a sign that someone did a bad job. It is the completely normal result of real systems, real people and real years of change. Data drifts, Relationships break. Assets get retired faster than the register can keep up. None of that is a failure. It is just entropy, and entropy is very patient.
FAQ Section
I’ve included this FAQ section, which I hope you find useful.
Bonus Frequently Asked Questions Section
This post is meant to be a bit of holiday fun, but it also contains some very important and useful information to help you keep your invaluable corporate data Current, Correct, and Complete.
What are orphaned records in an asset register?
Orphaned records are rows that reference a parent that no longer exists, such as a task linked to a property that was merged or removed, or an asset component whose parent asset was deleted. The record still gets counted and queried, but the thing it belongs to is gone, so it quietly distorts your totals. You find them with an anti-join: a LEFT JOIN from the child table to the parent, keeping only the rows where no matching parent comes back.
What are zombie assets and why are they a problem?
Zombie assets have been decommissioned, disposed, sold or demolished but were never removed from the register. They still look active, still get depreciated, and still inflate your asset count and renewal forecasts. You find them by looking for the contradiction between disposal information and active status, for example an asset that has a disposal date but is still flagged as active.
What are phantom duplicates in a data report?
Phantom duplicates are rows that appear more than once. Some are genuine, entered twice by different people. Most are conjured by a join that returns several rows from a related table, such as multiple address records for one asset, which multiplies that asset across your report. Confirm real duplicates by counting rows per key with GROUP BY and HAVING COUNT of more than one, then test each join to find where the row count jumps.
How do I stop a SQL join creating duplicate rows?
Collapse the many side down to the single row you actually want before it multiplies your data. An OUTER APPLY with SELECT TOP 1 and an ORDER BY lets you choose exactly which related row survives, so every parent record gets one match instead of several. For genuine duplicate entries, use ROW_NUMBER partitioned by your natural key and keep only the first row.
How often should councils clean their asset data?
Regularly, and ideally on a schedule rather than in a panic. Registers naturally accumulate orphaned records, zombie assets and duplicates as systems and assets change over time. A recurring data review, with end of financial year a natural checkpoint, keeps the data trustworthy. Always investigate with a SELECT before you delete, treat the root cause rather than the symptom, and prefer archiving or status flags over hard deletes so you keep your history.
The councils with genuinely trustworthy data are not the ones that never had ghosts. They are the ones that go looking for them on purpose, regularly, with a good query, a stored procedure, and a bit of humour.
So, give it a go, run a few anti-joins, build a stored procedure so you can run scheduled data checks regularly, and say hello to whatever is lurking in your data. It is far less frightening once you can see it.
Happy haunting 👻
I have worked on many different projects with my Local Government clients, from designing and developing Power BI Reports, to building SQL Server databases for spatial data, to managing and maintaining GIS and the Asset Management systems. If you'd like to discuss how we might work together, then please email Jill at ➡️ jill.singleton@iamdata.solutions
If you would like to receive the latest Newsletter Blog straight to your inbox, please subscribe here: ➡️ https://www.iamdata.solutions/subscribe
You can read all our Newsletters and Blogs here:➡️ https://www.iamdata.solutions/blog
You may also be interested in our Projects Page:➡️ https://www.iamdata.solutions/past-projects
Check out what our clients say about us here:➡️ https://www.iamdata.solutions/reviews
If you would like to see a particular topic covered in these newsletters, then please let me know about it. The chances are other people will be interested and would like to hear about it too! Please email me at: ➡️ jill.singleton@iamdata.solutions with your suggestions.
Comments