Skip to main content

How To Paginate SQL Data Explorer Results

Updated

SQL Data Explorer caps results at 2,000 rows with no explicit warning, just the row counter showing 2,000. This article covers two ways around that: using Data Access (a Snowflake data share) for larger or unlimited exports, or paginating within SQL Data Explorer itself using LIMIT and OFFSET with a consistent ORDER BY.

Overview:

The SQL Data Explorer tool is used to create reports and run ad-hoc analysis on Analytics data collected from a game. It provides a panel for entering a SQL query, a results table and a simple charting tool. 

However, the result panel is limited to displaying 2,000 rows of data. 

Solutions:

This article contains two solutions to this limitation. 

  1. The Data Access feature should be used for more advanced analysis and exports without limits.
  2. The LIMIT and OFFSET SQL commands can be used to paginate results. 

Details:

  1. Data Access is a Data Share that enables you to work with your Analytics data within your own Snowflake data warehouse. It is far better suited to running more intensive queries than would be feasible in a web browser-based tool like the SQL Data Explorer. You can export much larger data sets, run ETLs and combine your Analytics data with other data sources with Data Access.
     

  2. It is possible to paginate query results in the SQL Data Explorer using the LIMIT SQL command in conjunction with an OFFSET. For example, a SQL query that returns a large number of rows can be LIMITed to display a maximum of results, the OFFSET command determines which record number to start with. 

    e.g. To view the results of a data set containing 365 entries in pages of 100 values or less at a time. 

    select EVENT_DATE, count(distinct USER_ID) from EVENTS
where EVENT_DATE > CURRENT_DATE-365
group by EVENT_DATE
order by EVENT_DATE desc
LIMIT 100 OFFSET 0

    then

    select EVENT_DATE, count(distinct USER_ID) from EVENTS
where EVENT_DATE > CURRENT_DATE-365
group by EVENT_DATE
order by EVENT_DATE desc
LIMIT 100 OFFSET 100

    then

    select EVENT_DATE, count(distinct USER_ID) from EVENTS
where EVENT_DATE > CURRENT_DATE-365
group by EVENT_DATE
order by EVENT_DATE desc
LIMIT 100 OFFSET 200

    then

    select EVENT_DATE, count(distinct USER_ID) from EVENTS
where EVENT_DATE > CURRENT_DATE-365
group by EVENT_DATE
order by EVENT_DATE desc
LIMIT 100 OFFSET 300


    Note: Be sure to include an ORDER BY command in your SQL to ensure the results are ordered consistently across your pages. 

    Note: High OFFSET values can be slow and costly, since Snowflake has to scan and sort all preceding rows before discarding them. Anchoring your query with a fixed timestamp (e.g. EVENT_TIMESTAMP < '2026-10-01 00:00:00') instead of a relative range like CURRENT_DATE-365 avoids rows shifting, duplicating, or getting skipped if new data is ingested between pages.

 

Related to

Related articles