I know you can export all fields and their api names and display names, but is there a way to get the data types of the fields in bulk (i.e. in a list)?
Yes and no.
The Describe Lead API endpoint returns a datatype for all fields, but it's not actually accurate for the purpose of building an offline SQL db structure, if that's what you're thinking. (The max length of text fields isn't correct.) It should at least get you started, though.
Sanford Whiteman Thanks for the API endpoint, I can the data types from there. I actually am exactly doing that, building a replicated SQL DB holding Lead and Activity data. Can you say more about issues that would arise with data types between Marketo and the SQL structure?
The most problematic area is the text types. If you go by the Describe response you'll end up mapping many Strings to NVARCHAR(255) and will thus lose data, since the actual capacity of many (not all, but you can't know which ones are affected) will be NVARCHAR(2000). As a result, as instances grow toward hundreds of fields (if you're not there already) you need to mark columns SPARSE b/c SQL will not allow the row width otherwise.
In addition, you should never use Currency types in Marketo, period, since they are stored (alarmingly) as Floats. Store value × 100 in an Integer for USD (or just discard fractions entirely).
Sanford Whiteman Thanks for the additional details, Sanford. It looks like the describe endpoint actually gives you the field length (50, 255, or 2000) so I can match my SQL fields for those different field lengths. Additionally, several of the data types could all be captured by using a NVARCHAR data type (email, string, url, phone) seeing as they're just non date or numeric fields.
I'm actually using MySQL as the destination DB platform so I don't have the option of using sparse. I'm not as familiar with MySQL as with SQL so I'll have to research to see if MySQL has an alternative (know of one yourself?). I'm also researching character sets and going down a rabbit hole there, so I'd appreciate any other suggestion you have (seeing as your title is integration architect). Thanks again!
to see if MySQL has an alternative (know of one yourself?).
Well, the same-page row size limitation (iirc) in MySQL is 64K, which gives you a lot more headroom than MSSQL. You can offload data off-page using TEXT columns, butt there's no direct equivalent of SPARSE.
I'm also researching character sets and going down a rabbit hole there, so I'd appreciate any other suggestion you have
In the db? UTF-8, almost certainly. Or are you working in a non-Latin context exclusively?
The Describe REST API call returns all fields with their data types http://developers.marketo.com/rest-api/lead-database/#describe
I don't think there is any bulk export option for this. Only way to get to know all the data types of marketo field is:
Click on create field > Then you can see all options in the dropdown and they are:
4. Date Time