Principle:Mage ai Mage ai Paginated SQL Extraction
| Knowledge Sources | |
|---|---|
| Domains | Data_Integration, SQL, Pagination |
| Last Updated | 2026-02-09 00:00 GMT |
Overview
A paginated data extraction pattern that loads SQL database records in configurable batches using LIMIT/OFFSET with bookmark-based WHERE clause filtering.
Description
Paginated SQL Extraction addresses the challenge of extracting large datasets from SQL databases without overwhelming memory or network resources. Instead of loading all records in a single query, it uses LIMIT/OFFSET pagination to fetch records in batches (default batch size from BATCH_FETCH_LIMIT config). For incremental streams, it adds WHERE clauses using bookmark values to filter only new or updated records. The pagination loop continues until a batch returns fewer rows than the limit, indicating all data has been fetched.
Usage
Use this principle for any SQL database source connector where tables may contain large volumes of data. It is the default extraction mechanism for all SQL-based sources in the Mage integrations framework.
Theoretical Basis
The pagination algorithm:
- Set offset = 0, limit = fetch_limit (from config or BATCH_FETCH_LIMIT constant)
- Build SELECT query with selected columns FROM table
- If bookmarks exist, add WHERE clause (e.g., updated_at >= bookmark_value)
- Add ORDER BY (bookmark_properties, then key_properties, then unique_constraints)
- Add LIMIT {limit} OFFSET {offset}
- Execute query, yield batch of rows
- If len(rows) >= limit: increment offset, sleep(1), repeat from step 2
- If len(rows) < limit: stop pagination