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:
- The Endpoint Limit: The API returns only the first 100 or 1,000 records.
- The Pagination Trap: The remaining 250,000 rows are locked behind opaque cursors (
starting_afterorendCursor). - Data Drift: If you use traditional page numbers, new transactions inserted mid-sync cause duplicate or skipped rows—corrupting your financial metrics.
- 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?
- Secret Security (
<<context.secrets.STRIPE_SECRET_KEY>>): API keys are injected at runtime without being hardcoded into scripts. - Dynamic Context (
{{ctx.start}}/{{ctx.end}}): The pipeline automatically bounds requests to the required accounting date window. - Automated Cursor Handling:
cursor_param: data[-1].idextracts the ID of the last item in the JSON array and injects it as?starting_after=<id>on subsequent calls until no records return. - Resilience & Throttling: Built-in retries handle rate limits, and
throttle: 0.5pauses 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:
- Historical Parquet / Delta Lake: For long-term audit logs and fast OLAP queries.
- 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
show: 5: Logs the first 5 rows of any stage's DataFrame in real-time on the execution panel.show_schema: true: Prints exact column names and DuckDB data types to catch missing fields early.- Pipeline Validation API: Call
GET /v1/data/validate-pipelineto lint your YAML syntax, check schema compatibility, and verify connections before deploying to production.
Summary: Stop Building Glue Code, Start Reconciling
By moving away from fragile Python while loops and slow Power Query refreshes, you get:
- Zero Data Loss: Cursor-based pagination guarantees deterministic, drift-free extraction across millions of SaaS records.
- Built-In Resilience: Automatic exponential backoffs handle API rate limits (
HTTP 429) seamlessly. - In-Flight Data Quality: Deduplicate, flatten, and validate raw JSON payloads in SQL before writing to storage.
- 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.
- Explore our GitHub Documentation to learn more about declarative pipeline specs.
- Learn how Automat BI delivers fully managed $50/mo pipelines with zero engineering overhead.
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!