Knowing a Data Extension is Shared via the API

Question

Does anyone know if it is possible to determine if a data extension has been shared via the API. I don't see any related properties for the Data Extension in the documentation and I don't see any related information during a describe call or otherwise. My specific use case is to retrieve the data extensions for an Enterprise 2.0 account and be able to determine which data extensions have been shared.

Answer 1

It is possible.

Shared Data Extensions are physically located in Parent BU (ENT or Enterprise context).

They differ from "normal" DEs in the Parent BU in that they are accessible from inside other BU contexts through SQL and AMPscript / SSJS.

So, one vector to approach this is to try an attempt an SQL SELECT operation from child BU:

Set up an installed package in Parent BU, give access to some child BU, enable interactions with Data Extensions and Automations in the package's scope. now, using the credentials from this package, get an OAuth token for (= authenticate in the context of) the Child BU.

See documentation here, setting the account_id when getting the token will determine your context: https://developer.salesforce.com/docs/atlas.en-us.mc-app-development.meta/mc-app-development/access-token-s2s.htm

Host: https://YOUR_SUBDOMAIN.auth.marketingcloudapis.com
POST /v2/token
Content-Type: application/json

{
"grant_type": "client_credentials",
"client_id": "xxx",
"client_secret": "yyy",
"scope": "email_read email_write email_send",
"account_id": "YOUR CHILD BU MID"
}

Set up a dummy data extension in your Child BU with one field. In my usecase, I used the field "targetCustomObjectKey" and the name & Customerkey testDE123.

Now, using the resulting token for your API call, try to create an SQL query activity through the API (fine for doing it once - for repetition, instead update the same query, otherwise, this creates a lot of query activities). see also here: SOAP Envelop for Marketing Cloud - Update Query Activity or here for references: https://gortonington.com/how-to-interact-with-query-activities-via-rest-api/

The SQL query selects data from the Data Extension of your interest, using an "ENT." (=Enterprise) prefix before the DE name.

We can make it query for the hidden field _customObjectkey, which every DE has. Note that for a valid query, we need to rename that field using AS, hence my "targetCustomObjectKey" field from above.

Here is an example "create SQL query" call. Once you have one query, again: use update instead, or this fills up your child BU with query activities.

Change the QueryText "FROM" part to include your DE's name, and if necessary, repeat for your DEs.

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <SOAP-ENV:Header>
        <fueloauth>{{access_token}}</fueloauth>
    </SOAP-ENV:Header>
    <SOAP-ENV:Body>
        <CreateRequest xmlns="http://exacttarget.com/wsdl/partnerAPI">
            <Options></Options>
            <Objects xsi:type="QueryDefinition">
                <PartnerKey xsi:nil="true"></PartnerKey>
                <ObjectID xsi:nil="true"></ObjectID>
                <!--<CategoryID>68371</CategoryID>-->
                <Name>testquery</Name>
                <Description>testquery</Description>
                <QueryText>SELECT _customObjectkey AS targetCustomObjectkey FROM ENT.[YOUR DATA EXTENSION NAME]</QueryText>
                <TargetType>DE</TargetType>
                <DataExtensionTarget>
                    <PartnerKey xsi:nil="true"></PartnerKey>
                    <ObjectID xsi:nil="true"></ObjectID>
                    <CustomerKey>testDE123</CustomerKey>
                    <Name>testDE123</Name>
                </DataExtensionTarget>
                <TargetUpdateType>Overwrite</TargetUpdateType>
            </Objects>
        </CreateRequest>
    </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Two things can happen:

Option a) You get an error due to DE not found. >> This DE isn't shared. It's either in Parent and not shared, or it's in Child.

<StatusMessage>Error saving the Query field.ENT.testjl is not a known data extension or system data view. You can only query existing data extensions or system data views.&lt;br /&gt; </StatusMessage>

Option b) Success >> If you can find an "ENT." data extension from Child context via SQL, then this DE is necessarily Shared from Parent.

In my case, the query creation succeeds:

...
StatusCode>OK</StatusCode>
   <StatusMessage>QueryDefinition created</StatusMessage>
...

Last time I checked... - You will notice that this indeed works from ANY child BU with ANY shared Data Extension regardless of sharing settings on the UI. Yes, that surprised me too.