Stitch's NetSuite integration includes a table called
netsuite_deleted which contains a row for every deleted record that supports deletes. Accounting for deleted records is especially important if you’re performing any sort of aggregate function - for example, totaling invoices or balancing your books.
For this reason, we strongly recommend you set this table to sync when selecting tables to replicate.
The attributes of the
netsuite_deleted table include the record type (ex: invoice), name, deleted date, custom record flag, and internal ID. The internal ID is the numerical ID of the record, while the name is more reader-friendly. Here’s a look at some sample records:
It’s important to note that custom record types will look a little different than the NetSuite defaults:
customRecordcolumn will contain true values,
typecolumn will contain a numerical ID, and
Here’s a look at an example of a custom record:
LEFT JOIN to tie deleted records back to the appropriate table. For example, the following SQL query would return all invoice records that exist in both the
netsuite_transaction table and
SELECT * FROM netsuite_transactions tran LEFT JOIN netsuite_deleted del ON tran.internalId = del.internalId AND tran.type = ‘invoice’ AND del.type = ‘invoice’
LOWER function in the following query. Because Redshift is case-sensitive and NetSuite uses camel case, some queries may result in errors. If this occurs, try using
LOWER to resolve the issue.
SELECT * FROM netsuite_transactions tran LEFT JOIN netsuite_deleted del ON tran.internalId = del.internalId AND lower(tran.type) = lower(del.type)
To remove deleted records, you can run a query like the one below:
SELECT * FROM netsuite_transactions tran LEFT JOIN netsuite_deleted del ON tran.internalId = del.internalId AND lower(tran.type) = lower(del.type) WHERE del.deletedDate is null;