Problem
While integrating NetSuite data into Palantir Foundry, we encountered persistent failures when attempting to preview or ingest certain tables using the native SuiteQL connector. The connector’s metadata/schema discovery process would fail before any data could be retrieved.
In our case, we first hit this issue with the TransactionLine table, but this can happen with any NetSuite table depending on the metadata dependencies the connector attempts to resolve during schema generation.
Symptoms
-
Schema discovery failure: The connector could not retrieve columns for the target table, throwing errors during the
previewSourcerequest. -
Metadata parsing errors: The connector returned invalid object name errors (e.g.,
sys_disconnect) during its internal metadata introspection. -
Permission violations: During schema generation, the connector attempted to access unrelated metadata objects, resulting in permission errors such as:
Permission Violation: You need the “Custom Item Fields” permission to access this page.
Investigation
| Step | Action | Result |
|---|---|---|
| 1 | Ran the same SuiteQL queries directly via Postman against the NetSuite REST API |  Data returned successfully — confirmed the table exists and credentials are valid |
| 2 | Granted additional permissions (including custom field access) |  Schema discovery still failed |
| 3 | Tested other tables through the same connector and role | Many succeeded — only specific tables failed consistently |
Root Cause
The failure occurred during Foundry’s schema and metadata introspection, not during actual data retrieval. The native connector attempts to discover additional metadata objects and custom field definitions as part of its schema generation process. For certain tables, this process triggers cascading failures — schema discovery errors, metadata parsing issues, and permission errors on internal NetSuite metadata endpoints.
Additionally, NetSuite’s SuiteQL REST API requires OAuth 1.0 authentication (Token-Based Authentication with HMAC-SHA256 signatures). Foundry’s standard REST source does not natively support OAuth 1.0 in the way NetSuite requires, which ruled out a simple REST-based workaround.
Solution: Custom SuiteQL Ingestion via External Systems Connector
Instead of relying on the native connector’s table preview and schema discovery, we implemented a custom ingestion pipeline that bypasses the metadata layer entirely and queries the SuiteQL REST API directly. This approach works for any NetSuite table or custom SuiteQL query.
Approach
- Used Foundry’s External Systems connector to securely store and retrieve NetSuite OAuth credentials.
- Implemented OAuth 1.0 (HMAC-SHA256) request signing using the
requests-oauthliblibrary. - Executed SuiteQL queries directly against NetSuite’s REST API endpoint.
- Loaded results into Foundry using a Python transform.
- Added pagination and retries for reliability.
Key Code
Below is the core of the solution — OAuth setup, the SuiteQL request with pagination, and writing results to Foundry. You can adapt the SQL query to target any table or join in NetSuite:
import logging
import time
import pandas as pd
from requests.sessions import Session
from requests_oauthlib.oauth1_auth import OAuth1
from transforms.api import transform, Output
from transforms.external.systems import (
external_systems,
Source,
ResolvedSource,
)
logger = logging.getLogger(__name__)
PAGE_LIMIT = 100
REQUEST_TIMEOUT = (30, 600)
MAX_RETRIES = 5
RETRY_WAIT_SECONDS = 15
@external_systems(
netsuite_source=Source("<your-source-rid>")
)
@transform(
output=Output("<your-output-dataset-rid>"),
)
def compute(output, netsuite_source: ResolvedSource):
logger.info("Starting transform...")
# --- OAuth 1.0 setup using External Systems secrets ---
connection = netsuite_source.get_https_connection()
base_url = connection.url
client: Session = connection.get_client()
oauth = OAuth1(
client_key=netsuite_source.get_secret("additionalSecretConsumerKey"),
client_secret=netsuite_source.get_secret("additionalSecretConsumerSecret"),
resource_owner_key=netsuite_source.get_secret("additionalSecretTokenId"),
resource_owner_secret=netsuite_source.get_secret("additionalSecretTokenSecret"),
signature_method="HMAC-SHA256",
realm=netsuite_source.get_secret("additionalSecretAccountID"),
)
client.auth = oauth
# --- Execute paginated SuiteQL query ---
suiteql_url = f"{base_url}/services/rest/query/v1/suiteql"
# Replace with your own SuiteQL query targeting any table
query = "SELECT * FROM TransactionLine WHERE ..."
all_records = []
offset = 0
has_more = True
while has_more:
for retry in range(MAX_RETRIES):
try:
response = client.post(
suiteql_url,
json={"q": query},
headers={"Content-Type": "application/json", "Prefer": "transient"},
params={"limit": PAGE_LIMIT, "offset": offset},
timeout=REQUEST_TIMEOUT,
)
break
except Exception as e:
logger.warning(f"Request failed (attempt {retry + 1}/{MAX_RETRIES}): {e}")
if retry == MAX_RETRIES - 1:
raise
time.sleep(RETRY_WAIT_SECONDS)
response.raise_for_status()
data = response.json()
items = data.get("items", [])
for item in items:
item.pop("links", None) # Remove NetSuite HATEOAS links from response
all_records.extend(items)
has_more = data.get("hasMore", False)
offset += PAGE_LIMIT
# --- Write results ---
df = pd.DataFrame(all_records)
if not df.empty:
output.write_pandas(df)
logger.info(f"Transform complete. Total records: {len(all_records)}")
Key Details
- Works for any table or query — just change the SuiteQL statement. This is not limited to any specific table.
- OAuth 1.0 signing is handled by
requests-oauthlib, which Foundry supports as a library dependency. - Secrets (consumer key/secret, token key/secret, account ID) are stored securely in the External Systems connector and retrieved at runtime — never hardcoded.
- Pagination uses NetSuite’s
limit/offsetparameters and thehasMoreflag. - Retries with backoff handle transient network or API failures.