3

I'm trying to reduce the Data Extensions row size to 8000 bytes as Salesforce recommend but I have problems to know current exact size of a big DE I have.

Is there any way to calculate this? Through SQL, for example...

If not, it shouldn't be too difficult to sum up all columns sizes but then I would need to know size of different data types:

  • Boolean: 1
  • Date: ??
  • Decimal with 8.2 precision??
  • Number ??

Thanks

4
  • Now, in fact, looking for the 8000 bytes limits, I have found 4000 as the limit, here help.salesforce.com/s/articleView?id=000317499&type=1, Jan 7 at 7:20
  • In this article they talk about 8000 characters help.salesforce.com/s/…, so now I also have the doubt about characters/bytes relationship... does a character fits into just 1 byte? Jan 7 at 10:42
  • 1
    If I am not mistaken, then marketing cloud encodes in UTF-16, where a character takes up two bytes (salesforce.stackexchange.com/questions/267842/…). That together with the max length the UI enforces (4000 chars) leads me to believe that one of the two articles is in error, and that the intended statement is 4000 chars / 8000 bytes. Jan 7 at 12:22
  • I agree, I though that but, as you say, documentation is confusing on this matter. If I end involving Salesforce support I will show them this error. Jan 7 at 12:52
7

You can determine a data extension's size through REST API, by its objectID:

GET {{restEndpoint}}/email/v1/dataextensions/YOURDATAEXTENSIONOBJECTID/filesize

Headers

Content-Type:application/json

Authorization: Bearer {{accessToken}}

which returns the size in bytes:

{
"customObjectId": "YOURDATAEXTENSIONOBJECTID",
"filesize": 7430
}

This is of course on DE level, not row level, but using this basic method you can isolate separate effects (number of fields, types of fields, number of rows), by playing with different data extension setups and changing only one thing at a time and observing the effects. I haven't done this myself but those tests and results would be interesting.

Sidenote: To get the ObjectID first (which is not the customer Key) you have to retrieve the DE, say, by name, which you can do e.g. via SOAP API.

POST {{soapEndpoint}}/Service.asmx

Headers

Content-Type:text/xml

SOAPAction: "Retrieve"

Body

<?xml version="1.0" encoding="UTF-8"?>
<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://schemas.xmlsoap.org/ws/2004/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
<s:Header>
<fueloauth>{{accessToken}}</fueloauth>
</s:Header>
<s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
<RetrieveRequest>
<ObjectType>DataExtension</ObjectType>
<Properties>Name</Properties>
<Properties>CustomerKey</Properties>
<Properties>ObjectId</Properties>
<Filter xmlns:q1="http://exacttarget.com/wsdl/partnerAPI" xsi:type="q1:SimpleFilterPart">
<q1:Property>Name</q1:Property>
<q1:SimpleOperator>equals</q1:SimpleOperator>
<q1:Value>NAME OF YOUR DE</q1:Value>
</Filter>
</RetrieveRequest>
</RetrieveRequestMsg>
</s:Body>
</s:Envelope>
11
  • Thanks very much but this is not answering my question. I need to know the row size attending to fields definition. I need this to try adjusting to this best practices help.salesforce.com/s/articleView?id=000317499&type=1 (Any Data Extension spanning a cumulative field width greater than 4000 characters must be avoided) Jan 7 at 7:10
  • Hi, what I meant was: why not set up a DE with one field of type Boolean, and retrieve its size? Then compare this to another DE with just a text field of length 1, ... rinse and repeat for other types? Jan 7 at 12:19
  • oh! I missed that. OK, it's a solution. And perhaps I can make a copy of my DE and try your solution for empty table, with only one minimum data row and with a full data row. Thanks! Jan 7 at 12:54
  • precisely :) Can you let us know the results when done? Jan 7 at 13:25
  • Hi Jonas! well, I've made some tests but I'm not sure if they are going to be useful. I created an empty DE with only one bool field, it's filesize is 0. If I add 1 record with true then file size is 15, but if I add a record with false then file size is 16. 2 records will be around 22 depending on values and 3 records around 28. Filesize depends on the actual field value! Jan 10 at 10:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.