Pulling data out of NetSuite and into a modern data platform sounds like it should be a solved problem. You have an API, you have a query language, you have a destination. In practice, the gap between "it authenticated" and "the table is landed correctly and the job doesn't run forever" is full of small traps — most of them undocumented in any single place.

This post walks through building a NetSuite-to-Fabric ingestion in a PySpark notebook, and collects the gotchas that cost real time so you don't have to rediscover them.

The setup: OAuth 1.0a and two silent killers

NetSuite's REST API (SuiteTalk) uses OAuth 1.0a Token-Based Authentication (TBA). You need four values, and they come from two different places:

  • Consumer Key and Consumer Secret — from the Integration record
  • Token ID and Token Secret — from the Access Token

In Python, requests-oauthlib handles the signing. But two defaults will fail you silently, both surfacing as an unhelpful 401 INVALID_LOGIN_ATTEMPT:

python
from requests_oauthlib import OAuth1

auth = OAuth1(
    client_key=CONSUMER_KEY,
    client_secret=CONSUMER_SECRET,
    resource_owner_key=TOKEN_ID,
    resource_owner_secret=TOKEN_SECRET,
    realm=ACCOUNT_ID,                 # gotcha #1
    signature_method="HMAC-SHA256",   # gotcha #2
)

Gotcha #1 — the realm formatting. Your account's REST base URL looks like https://<account-id>-sb1.suitetalk.api.netsuite.com — lowercase, hyphenated. But the realm parameter in the signature must be the account ID in uppercase with an underscore, e.g. 1234567_SB1. Mixing these up is the single most common cause of a rejected signature.

Gotcha #2 — the signature method. requests-oauthlib defaults to HMAC-SHA1. NetSuite requires HMAC-SHA256. If you don't set it explicitly, every request bounces.

Querying with SuiteQL

SuiteQL is NetSuite's SQL dialect, exposed through a REST endpoint. It's Oracle-flavoured, which matters for functions like TO_CHAR and TO_DATE. A query is a POST with a Prefer: transient header and a JSON body:

python
def suiteql_page(query, limit=1000, offset=0):
    url = f"{BASE_URL}/query/v1/suiteql"
    headers = {"Content-Type": "application/json", "Prefer": "transient"}
    resp = requests.post(url, auth=auth, headers=headers,
                         params={"limit": limit, "offset": offset},
                         json={"q": query})
    resp.raise_for_status()
    return resp.json()

Two quick tips that save debugging:

  • Reserved words. Columns like key are reserved and will throw a syntax error if selected unquoted. If a table is narrow, SELECT * sidesteps the quoting entirely.
  • The 1000-row cap. SuiteQL returns a maximum of 1000 rows per call. Everything about getting data out is really about how you page through that limit.

Getting data OUT: pagination is the whole game

Because of the 1000-row cap, a real extract is a loop. There are two ways to loop, and the right choice depends on whether your table has a stable key.

Offset pagination (works everywhere, no key needed)

Walk offset forward using the response's hasMore flag:

python
def paged_batches(query):
    offset = 0
    while True:
        page = suiteql_page(query, limit=1000, offset=offset)
        items = page.get("items", [])
        for r in items:
            r.pop("links", None)   # drop NetSuite's per-row metadata
        if items:
            yield items
        if page.get("hasMore"):
            offset += 1000
        else:
            break

This needs no key column, which is its whole appeal. Its weakness: deep offsets get progressively slower, and without a stable ORDER BY you risk duplicates or gaps at page boundaries.

Keyset pagination (faster, but needs a unique column)

If the table has a numeric key, walking that key forward stays fast no matter how deep you go:

sql
SELECT * FROM my_table WHERE id > :last_id ORDER BY id

Each page grabs the next 1000 rows after the highest key you've seen. No offset degradation, no boundary ambiguity. The catch is right there in the query — you need an id. Many NetSuite tables and views don't expose one, at which point you're back to offset.

The trick that makes offset viable at scale: batch by year

Deep offsets are the problem. So don't let them get deep. If the table has a created-date column, load one year at a time — each year restarts the offset at zero, so the maximum offset is bounded by a single year's row count instead of the whole table:

python
for year in range(min_year, max_year + 1):
    query = (
        f"SELECT * FROM my_table "
        f"WHERE created_date >= TO_DATE('{year}-01-01', 'YYYY-MM-DD') "
        f"AND created_date <  TO_DATE('{year + 1}-01-01', 'YYYY-MM-DD') "
        f"ORDER BY created_date"
    )
    for batch in paged_batches(query):
        write_batch(batch)

You get bounded offsets, natural progress logging, and a load that resumes conceptually at the year level. Find the range with a single query up front:

sql
SELECT TO_CHAR(MIN(created_date), 'YYYY') AS min_yr,
       TO_CHAR(MAX(created_date), 'YYYY') AS max_yr
FROM my_table

Probe for the column, then fall back gracefully

The created-date column is named differently across tables and instances. Rather than hard-coding, probe for it and degrade to a plain full load if none exists:

python
def column_exists(column, table):
    try:
        suiteql_page(f"SELECT {column} FROM {table}", limit=1)
        return True
    except requests.exceptions.HTTPError as e:
        if e.response is not None and e.response.status_code == 400:
            return False   # invalid column
        raise              # don't mask an auth error as "column missing"

Note the deliberate re-raise: only a 400 means "column doesn't exist." A 401 is an auth problem, and you never want that silently misread as a missing column that triggers a different (also-failing) code path.

Landing into the Lakehouse

Land raw data as all strings

It's tempting to let Spark infer types from a pandas DataFrame. Don't — not for a raw layer. NetSuite returns nulls in plenty of fields, and NaNs mixed into otherwise-text columns make Spark's type inference flaky. Cast everything to string, preserve nulls explicitly, and type-cast later in a silver layer:

python
from pyspark.sql.types import StructType, StructField, StringType

def rows_to_string_sdf(rows):
    pdf = pd.DataFrame(rows).astype(object)
    pdf = pdf.where(pd.notnull(pdf), None)
    for col in pdf.columns:
        pdf[col] = pdf[col].apply(lambda v: None if v is None else str(v))
    schema = StructType([StructField(c, StringType(), True) for c in pdf.columns])
    return spark.createDataFrame(pdf, schema=schema)

Watch for schema drift with SELECT *

Here's a subtle one: SuiteQL omits null-valued keys from its row JSON. That means two different pages can return different column sets — a column that happens to be null for every row in one batch simply won't appear. When you append batch by batch, that causes schema mismatches. Enable schema merging on every write so new columns get added and missing ones fill null:

python
writer = sdf.write.option("mergeSchema", "true")
writer.mode("append").saveAsTable(TABLE_NAME)   # overwrite on the first batch

For a production pipeline, the cleaner fix is to select an explicit column list instead of *, pinning the schema down.

Your file went where?

If you write a CSV to the notebook's local working directory, it lands on the driver node's ephemeral scratch space — there's no download link, and it vanishes with the session. To produce something durable and downloadable, write to the Lakehouse: a table via saveAsTable(...) (queryable in SQL and Power BI), or a file under the Lakehouse Files/ path. And to just see the records in the notebook, display(df) renders an interactive grid with its own export button.

POC hygiene: cap the pull, release the compute

Don't pull three million rows for a proof of concept

If the source has millions of rows but you only need a representative sample, cap it. Page at the API limit, stop when you hit the cap:

python
def fetch_capped(query, max_rows):
    rows, offset = [], 0
    while max_rows is None or len(rows) < max_rows:
        page = suiteql_page(query, limit=1000, offset=offset)
        items = page.get("items", [])
        for r in items:
            r.pop("links", None)
        rows.extend(items)
        if not page.get("hasMore"):
            break
        offset += 1000
    return rows if max_rows is None else rows[:max_rows]

A 100,000-row cap is 100 API calls instead of thousands — plenty to validate the shape of the data and the downstream model.

Stop the session when the load finishes

Interactive Spark sessions keep running (and keep consuming capacity) after your code finishes. Once the data is landed, release it explicitly:

python
try:
    notebookutils.session.stop()
except Exception as e:
    print(f"Session stop skipped: {e}")

Wrap it defensively so a runtime that behaves differently just logs a skip rather than erroring out a load that already succeeded. Put it after your final display() so you still see the result before compute is released. In scheduled pipeline runs the session tears down on its own — this matters mainly for interactive development.

One notebook trap worth its own line

If you write a script that reads sys.argv and run it inside a notebook, you'll get mysterious input like -f showing up as your first argument. That's the Jupyter/Fabric kernel's own connection-file flag leaking in — sys.argv is never empty in a notebook kernel. Use plain configuration variables at the top of the cell instead of command-line parsing.

Putting it together

The shape of a robust NetSuite-to-Fabric extract ends up being:

  1. Authenticate with OAuth 1.0a — HMAC-SHA256, correctly formatted realm.
  2. Query with SuiteQL, mindful of reserved words and the 1000-row cap.
  3. Page through results — keyset if you have a key, offset otherwise, year-batched to keep offsets shallow.
  4. Land as an all-string raw table with schema merging on, then type and model downstream.
  5. For POCs, cap the pull and stop the session when done.

None of these steps is hard on its own. The value is in knowing which default is about to bite you before it does.

The code samples here use placeholder credentials and a generic account URL. Fill in your own Integration and Access Token values, and never commit real secrets to source control — pull them from a secrets store or key vault instead.