The Stripe & Shopify Trap: Reconciling High-Volume SaaS Data into Excel without Missing Records

The Stripe & Shopify Trap: Reconciling High-Volume SaaS Data into Excel without Missing Records

If you are a finance controller, analytics engineer, or operations manager at a fast-growing B2B or e-commerce business, you know the drill:

Every month-end, executive leadership asks for a complete, reconciled sales and transaction report. You need to pull hundreds of thousands of transactions out of SaaS platforms like Stripe or Shopify and get them into an Excel workbook or financial dashboard.

You open Excel or Power BI, plug in a Web Connector, or spin up a Python script, and hit The SaaS Data Wall:

  1. The Endpoint Limit: The API returns only the first 100 or 1,000 records.
  2. The Pagination Trap: The remaining 250,000 rows are locked behind opaque cursors (starting_after or endCursor).
  3. Data Drift: If you use traditional page numbers, new transactions inserted mid-sync cause duplicate or skipped rows—corrupting your financial metrics.
  4. The Rate Limit Crash: Halfway through page 150, the API responds with HTTP 429 Too Many Requests. Your script crashes, and you have to start all over again.

In this guide, we will break down why traditional API pagination breaks spreadsheet tools, why cursor-based pagination is essential for financial reconciliation, and how to build a production-grade, declarative pipeline using AnkaFlow (the pipeline engine behind Automat BI) that extracts, back-off retries, deduplicates, and materializes SaaS data straight into Excel (.xlsx), Parquet, or Delta Lake.


Why SaaS APIs Break Financial Reconciliation

When software engineers build APIs for platforms like Stripe or Shopify, they prioritize system stability. They don't want a single script pulling millions of transactions at once, so they enforce strict page limits and rate throttles.

1. The Danger of "Offset" Pagination in Accounting

Older APIs paginate using offset numbers (?page=1, ?page=2). If your ingestion job takes 3 minutes to run while customers are actively checking out on Shopify, offset pagination creates Data Drift:

Ingestion Job reads Page 1 (Items 1-1,000)...
[Transaction #1 ... Transaction #1,000] fetched!

*5 NEW TRANSACTIONS OCCUR AT CHECKOUT*

Ingestion Job requests Page 2 (OFFSET 1,000)...
Database returns items based on NEW positions.
RESULT: Transactions #996 through #1,000 are fetched AGAIN on Page 2.
5 older transactions on Page 2 get pushed to Page 3 and skipped entirely.

In financial reporting, silent row duplication or missing sales transactions ruin revenue reconciliation.

2. The Cursor Solution: Opaque Anchor Tokens

Modern APIs use Cursor-Based Pagination. Instead of a page number, the server returns an opaque token pointing to the exact anchor of the last item fetched (e.g., starting_after: "ch_3MtwL2Lkd9SQyDOJ1Jz2v23").

Request 1 ──► API returns 1,000 records + nextCursor: "tok_1000"
Request 2 ──► Send ?cursor=tok_1000 ──► API returns next 1,000 records + nextCursor: "tok_2000"
... Repeat until nextCursor is empty/null.

While cursors offer 100% deterministic, drift-free page views, they are nearly impossible to handle with basic Excel formulas or Power Query UI without writing complex recursive List.Generate M code.


The Architecture: From Raw REST/GraphQL to Pivot-Ready Excel

Instead of writing fragile Python scripts with while loops or wrestling with Power Query, we use AnkaFlow—a declarative, YAML-defined ETL engine powered by an in-memory DuckDB transformation layer.

┌─────────────────────────────────────────────────────────────────────────────┐
│                             ANKAFLOW DATA PIPELINE                          │
│                                                                             │
│  ┌──────────────────────┐      ┌──────────────────────┐     ┌─────────────┐ │
│  │  SaaS APIs           │      │  In-Stream DuckDB    │     │ Sinks       │ │
│  │  • Stripe (REST)     │ ───► │  • Cursor Traversal  │ ──► │ • Parquet   │ │
│  │  • Shopify (GraphQL) │      │  • Window Dedup (SQL) │     │ • Delta Lake│ │
│  └──────────────────────┘      │  • Schema Flattening │     │ • Excel     │ │
│                                └──────────────────────┘     └─────────────┘ │
└─────────────────────────────────────────────────────────────────────────────┘

AnkaFlow pipelines consist of structured stages (source, transform, sink, pipeline, sql). Let's build a production-grade pipeline step-by-step.


Step 1: Ingesting Cursor-Paginated REST Data (Stripe Charges)

Below is an AnkaFlow pipeline stage that authenticates against Stripe, paginates through charge records using body/query cursor tokens, handles exponential backoffs on HTTP 429, and logs execution schemas.

# ducts/reconcile-stripe-sales.yaml
- kind: source
  name: raw_stripe_charges
  connection:
    kind: Rest
    client:
      base_url: https://api.stripe.com/v1
      auth:
        method: header
        values:
          Authorization: "Bearer <<context.secrets.STRIPE_SECRET_KEY>>"
    request:
      endpoint: /charges
      method: get
      query:
        limit: 100
        created[gte]: "{{ctx.start}}"
        created[lte]: "{{ctx.end}}"
    response:
      locator: data
      handler:
        kind: CursorPagination
        cursor_locator: body
        cursor_param: data[-1].id
        cursor_disposition: query
        cursor_name: starting_after
    max_retries: 5
    initial_backoff: 1.0
  throttle: 0.5
  show: 5
  show_schema: true

What happens under the hood?

  1. Secret Security (<<context.secrets.STRIPE_SECRET_KEY>>): API keys are injected at runtime without being hardcoded into scripts.
  2. Dynamic Context ({{ctx.start}} / {{ctx.end}}): The pipeline automatically bounds requests to the required accounting date window.
  3. Automated Cursor Handling: cursor_param: data[-1].id extracts the ID of the last item in the JSON array and injects it as ?starting_after=<id> on subsequent calls until no records return.
  4. Resilience & Throttling: Built-in retries handle rate limits, and throttle: 0.5 pauses 500ms between page requests to stay well within Stripe's limits.

Step 2: Handling High-Volume GraphQL Bulk APIs (Shopify Orders)

For platforms like Shopify, fetching 100,000 orders via standard REST pagination can take hundreds of HTTP calls. Shopify's GraphQL API allows bulk pagination using cursor edges.

AnkaFlow natively handles GraphQL connection cursors and flattens nested structures in SQL:

- kind: source
  name: shopify_raw_orders
  connection:
    kind: Shopify
    params:
      query: |
        query($cursor: String) {
          orders(query: "created_at:>='{{ctx.start}}' AND created_at:<='{{ctx.end}}'", first: 250, after: $cursor) {
            edges {
              node {
                id
                name
                createdAt
                totalPriceSet { shopMoney { amount currencyCode } }
                customer { id email }
                lineItems(first: 50) {
                  edges { node { sku quantity unitPrice } }
                }
              }
            }
            pageInfo { hasNextPage endCursor }
          }
        }

- kind: transform
  name: flattened_shopify_sales
  connection:
    kind: SQL
    query: |
      SELECT 
        o.id AS order_id,
        o.name AS order_number,
        CAST(o.createdAt AS TIMESTAMP) AS order_timestamp,
        CAST(o.totalPriceSet.shopMoney.amount AS DOUBLE) AS total_amount,
        o.totalPriceSet.shopMoney.currencyCode AS currency,
        o.customer.email AS customer_email,
        li.sku AS item_sku,
        CAST(li.quantity AS INT) AS item_quantity,
        CAST(li.unitPrice AS DOUBLE) AS item_price
      FROM shopify_raw_orders o,
      UNNEST(o.lineItems.edges) AS t(li_edge),
      LATERAL (SELECT li_edge.node.*) AS li

Step 3: In-Stream SQL Deduplication & Reconciliation

Even with cursor pagination, SaaS systems occasionally emit updated records or webhooks multiple times during an extraction window. Before exporting to Excel or financial storage, we must deduplicate records using window functions in DuckDB SQL.

- kind: transform
  name: reconciled_transactions
  connection:
    kind: SQL
    query: |
      WITH ranked_transactions AS (
        SELECT 
          order_id,
          order_number,
          order_timestamp,
          customer_email,
          item_sku,
          item_quantity,
          item_price,
          (item_quantity * item_price) AS line_total,
          ROW_NUMBER() OVER (
            PARTITION BY order_id, item_sku 
            ORDER BY order_timestamp DESC
          ) AS row_num
        FROM flattened_shopify_sales
        WHERE total_amount > 0
      )
      SELECT 
        order_id,
        order_number,
        order_timestamp,
        customer_email,
        item_sku,
        item_quantity,
        item_price,
        line_total
      FROM ranked_transactions
      WHERE row_num = 1

Step 4: Multi-Sink Materialization (Parquet, Delta Lake & Excel)

Now that the stream is cleaned and reconciled, we can sink the data into multiple formats simultaneously:

  1. Historical Parquet / Delta Lake: For long-term audit logs and fast OLAP queries.
  2. Formatted Excel (.xlsx): For immediate financial reporting and pivot table analysis.
# 1. Sink to Delta Lake (ACID Storage with Merge)
- kind: sink
  name: delta_financial_ledger
  connection:
    kind: Deltatable
    locator: s3://finance-data-lake/shopify/orders_delta
    data_mode: merge
    partition:
      - currency

# 2. Sink to Partitioned Parquet
- kind: sink
  name: parquet_monthly_snapshot
  connection:
    kind: Parquet
    locator: s3://finance-data-lake/snapshots/{{ctx.start|date_format('%Y/%m')}}/sales.parquet

# 3. Sink directly to Excel for Controllers
- kind: sink
  name: excel_monthly_report
  connection:
    kind: File
    params:
      kind: xlsx
      sheet_name: "Monthly Reconciled Sales"
    locator: outputs/Monthly_Sales_Reconciliation_{{ctx.start|date_format('%Y_%m')}}.xlsx
  show: 10

Interactive Development & Validation

One of the main flaws of traditional Python ETL scripts is that you don't know if your code works until the entire script finishes running (or crashes at 99%).

AnkaFlow provides an interactive execution environment where you can develop and validate pipeline YAMLs incrementally:

Debugging & Observability Features


Summary: Stop Building Glue Code, Start Reconciling

By moving away from fragile Python while loops and slow Power Query refreshes, you get:

  1. Zero Data Loss: Cursor-based pagination guarantees deterministic, drift-free extraction across millions of SaaS records.
  2. Built-In Resilience: Automatic exponential backoffs handle API rate limits (HTTP 429) seamlessly.
  3. In-Flight Data Quality: Deduplicate, flatten, and validate raw JSON payloads in SQL before writing to storage.
  4. Universal Output: Land data simultaneously in open columnar formats (Parquet/Delta) and business-ready Excel workbooks (.xlsx).

Ready to Eliminate Your SaaS Ingestion Headaches?

AnkaFlow makes data integration simple, version-controlled, and robust.


How does your finance team currently reconcile high-volume SaaS data? Are you using custom Python scripts, Power Query, or a managed pipeline engine? Let us know in the comments below!

←