Accidentally removing the wrong column and losing data is something that could be a bad dream, but what if I told you it’s possible to retrieve data lost this way yourself?

While working with SSJS some time ago I realized you are able to not only see what columns previously existed in the data extension you are looking data from, but you can actually see the values previously stored there.

This is basically “ghost data”:

• it is not displayed in the interface
• it is not exported
• it’s retrievable (working methods are listed below)
• it contains a history of all fields (and their values!) that ever existed in the DE

Let me show you how it works in normal situations and with removed fields.

How it works normally

Let’s create a simple data extension to create “ghost data”:

Let’s retrieve this data with SSJS:

<script runat="server">
Platform.Load("core", "1");

var de = 'GhostData';
var deData = Platform.Function.LookupRows(de, ['LookupBait'], [true])

Write(Stringify(deData));
</script>

This give us the following result as a JavaScript Array:

[
    {
        "_CustomObjectKey": 1,
        "SubscriberKey": "003AhC4621RavenAAC",
        "LookupBait": true,
        "Ghost": "Ayre",
        "_CreatedDate": "2023-10-27T12:59:52.673"
    }
]

Looks normal, but we already see some cool stuff:

• _CustomObjectKey – the unique identifier of the data extension row
• _CreatedDate – the time the record was written

Creating Ghost Data

Let’s remove the Ghost field (column or attribute or however we want to call it) from our data extension and see what happens (the behavior is the same for both Email Studio and Contact Builder).

This is a GIF showing what the SSJS script detects before removing the Ghost column – notice what will be returned afterwards:

The newly returned value:

[
    {
        "_CustomObjectKey":1,
        "SubscriberKey": "003AhC4621RavenAAC",
        "LookupBait": true,
        "___ghost_1": "Ayre",
        "_CreatedDate": "2023-10-27T12:59:52.673"
    }
]

Our Ghost attribute is now officially deleted, but our SSJS LookupRows function has found a ___ghost_1 attribute with the same value! Seems like it hasn’t really been removed, but attribute name has been adjusted slightly.

We won’t see that attribute when we preview the records in Contact Builder:

Spooky, isn’t it? Yeah, it kind of is if you REALLY want to delete some sensitive data. We’ll verify soon if we can use this curio in some ways.

Naming Convention

Before we jump into that, let’s see how the naming changes when we add an attribute with the same name and keep deleting it?

The numeric suffix just gets increased with each instance of this happening:

[
    {
        "_CustomObjectKey": 1,
        "SubscriberKey": "003AhC4621RavenAAC",
        "LookupBait": true,
        "___ghost_1": "Ayre",
        "_CreatedDate": "2023-10-28T13:02:47.840",
        "___ghost_2": "Ayre",
        "___ghost_3": "Ayre"
    }
]

With this out of the way, let’s see if we can put this to use.

Use Cases

Can we get this data with SQL queries?

Let’s see in Query Studio:

Seems there is no problem when we assign an alias – data extension field names just can’t start with underscores like ___ghost_1.

Automation Studio

Query Studio is quirky, so let’s see if we can use an extended version of that query in Automation Studio:

SELECT
    SubscriberKey,
    LookupBait,
    ___ghost_1 AS Ghost,
    _CustomObjectKey AS CustomObjectKey,
    _CreatedDate AS CreatedDate
FROM
    GhostData

The query validates without problem and is capable of retrieving the deleted attribute (plus the _CustomObjectKey and _CreatedDate technical record attributes):

With this verified, we now have a method of retrieving data from columns that might have been deleted accidentally.

The technical attributes can be also retrieved, should you ever be in a situation in which you must do a very deep audit of your data flows.

Can we get deleted values with Ampscript?

Yes, all 5 lookup functions seem to be capable of retrieving the ___ghost_1 attribute:

• ✔️ Lookup
• ✔️ LookupRows
• ✔️ LookupRowsCS
• ✔️ LookupOrderedRows
• ✔️ LookupOrderedRowsCS

Example Ampscript that works currently:

%%[
set @deName = 'GhostData'
set @fieldName = '___ghost_1'
set @output = ''

set @deRows = LookupOrderedRowsCS(@deName, 0, 'SubscriberKey ASC', 'LookupBait', 1)
set @row  = row(@deRows, 1)
set @field = field(@row,"___ghost_1")
set @output = Concat(@output, "LookupRows: ", @field)
OutputLine(Concat(@output,'')) 
]%%

This will simply output the value we had stored in the attributed we deleted.

Can we get deleted values with SSJS?

We know LookupRows can retrieve the ___ghost_1 value, but what about the other functions?

Platform Functions

Those functions just work and retrieve the “deleted” data:

• ✔️ Platform.Function.Lookup
• ✔️ Platform.Function.LookupRows
• ✔️ Platform.Function.LookupOrderedRows

Core Functions

With core functions the first cracks start showing:

✔️ Rows.Lookup

This function works as well:

var deKey = 'AC6-4U6M3N73DHUM4N-C4-621-R4V3N'
var deInit = DataExtension.Init(deKey);
var deData = deInit.Rows.Lookup(["LookupBait"], [1]);
Write(Stringify(deData));

This returns the following data:

[
    {
        "_CustomObjectKey": 1,
        "SubscriberKey": "003AhC4621RavenAAC",
        "LookupBait": true,
        "___ghost_1": "Ayre",
        "_CreatedDate": "2023-10-27T12:59:52.673"
    }
]

❌ Rows.Retrieve

This is the only method I tested that doesn’t retrieve the “removed” field:

var deKey = 'AC6-4U6M3N73DHUM4N-C4-621-R4V3N'
var deInit = DataExtension.Init(deKey);
var deData = deInit.Rows.Retrieve();
Write(Stringify(deData));

It’s the only method that didn’t return “deleted” attribute along with the technical record data (_CustomObjectKey and _CreatedDate):

[
    {
        "SubscriberKey": "003AhC4621RavenAAC",
        "LookupBait": "True"
    }
]

WSProxy

Platform and Core functions give us plenty of methods of retrieving the data, but sometimes we would want to use WSProxy to be able to retrieve data from more business units at once, so let’s see how this method works with the removed fields.

Retrieve the specification of deleted fields

Before we jump into straight record retrieval, let’s check if we can dynamically extract the columns of our DE with the DataExtensionField – this should allow us to find our removed fields without having to name them explicitly. Will WSProxy be able to retrieve the removed field specification and let us know what data exact data type they had an when were they modified?

❌ No, we can’t retrieve the information – the “hidden” fields (or basically all of the attributes with an underscore prefix) are not retrieved this way. The resulting array would just include our SubscriberKey and LookupBait columns.

Retrieve deleted data

Ok, we can’t get the field specification, but maybe we could explicitly list the ___ghost_1 column among the data we want to retrieve from the DataExtensionObject.

Let’s use Ivan Razine’s WSProxy code to try retrieving the data:

<script runat="server">
Platform.Load("core", "1.1.1");

var api = new Script.Util.WSProxy();

try {

    var dataExtensionName = 'GhostData';

    var req = api.retrieve("DataExtension", ["CustomerKey"], {
        Property: "Name",
        SimpleOperator: "equals",
        Value: dataExtensionName
    });

    var deCustomerKey = req.Results[0].CustomerKey;

    var cols = [
        "SubscriberKey", 
        "LookupBait",
        "___ghost_1"
    ];

    var proxyResult = api.retrieve(
        "DataExtensionObject[" + deCustomerKey + "]", cols
    );

    Write(Stringify(proxyResult));
    
} catch(error) {
    Write(Stringify(proxyResult));
}	
</script>

❌ This results in an error:

{
    "Status": "Error: The Request Property(s) ___ghost_1 do not match with the fields of DataExtensionObject retrieve",
    "RequestID": "70b3fb69-c5bf-1234-abcd-1cb50645b94a",
    "Results": null,
    "HasMoreRows": false
}

Seems we just can’t use WSProxy to work with the deleted data.

TL;DR Use Case Viability

Here’s a short summary what methods of retrieving data are able to access the removed attributes:

Please note:
Those methods work now, but might stop working later if Salesforce decides to stop allowing us accessing data like this or changes the field removal to actually delete the data stored in the back-end.