When executing a suiteql query (via REST API):

SELECT * FROM Customer

to retrieve customer records, I've noticed that columns with

NULL

values are not returned for that "item". Is there a way to find all the columns available for that table via the REST API so that I can wrap it in

NVL(column_name, 'NULL') AS column_name

so that the all the same fields are returned for all items/records?

you can view it in your records browser xxxxxxx.app.netsuite.com/app/recordscatalog/rcbrowser.nl#/record_ss/Customer Just replace the account number in that link above. Or in your account go to Setup > Records Browser And search for customer in the records tab

So i've been using that page, copying/pasting from all the fields without the "dropdown" option (since those seem to represent other tables to join to) and i've been getting errors, some helpful like "column not found" and then some not helpful like "go checkout ticket number 1234" (which i'm wondering if that's due to a permission issue)...

The REST API probably does this to cut down on unnecessary data being sent from NS back to the calling client. Circumventing that is going to make the calls slower as more data will need to be passed back. Could the calling application handle missing properties instead?

https://timdietrich.me/netsuite-suitescripts/suiteql-query-tool/ Theres also this tool from Tim Dietrich that lets you run suiteQL in a suitelet. It has a link to all the tables and their structure with all the field names. A 'better' records browser kind of

If you really want all the data in a record type, the ODBC, JDBC, and ADO.NET connectors can export them as full tables easier and faster, assuming your application is able to use one of those, and you have SuiteAnalytics Connect available in your account.

The JDBC setup for the data pipeline tool (apache beam) is pretty time consuming to setup vs using the REST API. But what I'm seeing is that some fields that are returned by the JDBC connection (select *) are not available via the REST API (even if you explicitly ask for them & account for NULL vals). I could handle missing values downstream in the data warehouse, just figured there was a straight forward way to ask for all columns available regardless of null values.

I wasn’t aware that the REST API did not expose some columns, however are those columns that you need for your use case? I can see two possibilities: you are building an integration or ETL process, in which case you’d usually be targeting specific columns anyway, or you are trying to implement some kind of full backup or copy, perhaps for a data warehouse or similar purpose? If it’s the latter, and you want the most complete backup or copy of all data, then JDBC is still the best option, even with the additional setup required. If its just an integration or ETL process, you can narrow down your query to just the needed columns, and hopefully they are all available via the REST API

yes, i'm building data pipelines that will incrementally pull data from NS and land in in our data lake. so more of a ELT process. Since engineers aren't always sure what will be needed or what won't, it is usually best practice to pull everything available.

Our company uses Fivetran to pull Netsuite data into our data lake daily, and supplements it with the ODBC connector for any real-time data access. Fivetran is excellent in that you just define the tables you want, it it replicates everything available from them (I believe using the SuiteTalk SOAP web services), however it’s not an inexpensive tool.

We have Stitch and the community connector for NetSuite was not reliable. But maybe Fivetran would be an option, depending on cost.

However, I’d actually argue that pulling everything is not best practice, just the one that allow engineers to avoid doing their due diligence up front. Case in point, we built such an integration for our Salesforce environment, and it pulls significant amounts of data daily (tens of gigabytes), yet only a handful of columns from the sObjects pulled are ever used. We incur the costs of time and storage for lot of data that no team ever uses. So, I’d argue that being intentional and pulling just what is required, while a pain to set up and figure out initially, can be the much better long term process. It will run faster and be easier to maintain. Learning from that, we only pull specific columns from Netsuite record types, and what takes hours each night for Salesforce, only take a minute or two for Netsuite, because we don’t pull what we don’t need