# Troubleshooting and error handling

### Pagination

The Tabular Data Endpoints (native and SQL) support pagination which is a technique used in API design and development to retrieve large data sets in a structured and manageable manner. The default limit is 100 records with a maximum limitation of 32,000 for both native and SQL queries.

It is recommended to use pagination to retrieve larger datasets and avoid timeouts or memory issues. To further optimize performance, use the HDX API’s filtering capabilities to limit the size of the data payload returned in each request.

#### Native Query (`datastore_search`)

Pagination is supported via the limit and offset parameters:

* `limit`: Number of records to return per page (default is 100, maximum is 32,000).
* `offset`: The starting point (index) for the next page of results.

This example retrieves 100 records starting from the 201st record (offset is zero-based): `https://data.humdata.org/api/3/action/datastore_search?id=<resource_id>&`**`limit=100&offset=200`**

To paginate through the entire dataset:

1. Start with `offset=0`.
2. Increment the offset by the limit (e.g., `offset=100`, `offset=200`, etc.) until `result["records"]` is empty.

#### SQL Query (`datastore_search_sql`)

Pagination is supported via SQL's `LIMIT` and `OFFSET` clauses:

Example: `SELECT * FROM "<resource_id>" WHERE country = 'Kenya' LIMIT 100 OFFSET 200`

Encoded URL: `https://data.humdata.org/api/3/action/datastore_search_sql?sql=SELECT+*+FROM+"<resource_id>"+WHERE+country='Kenya'+LIMIT+100+OFFSET+200`

* Always use `ORDER BY` with pagination in SQL to ensure consistent result ordering across pages.
* SQL-based pagination is more flexible but puts more load on the server; use filters and limits strategically.
* Use SQL filters to retrieve only the data you need and reduce system load.

### Authentication and permissions

* **Symptom: 403 Forbidden**
* Likely cause: Missing or invalid HDX API token in the Authorization header
* Fix: Add a valid token and retry. Example with curl:

`curl -H "Authorization: <your-api-token>" \ "https://data.humdata.org/api/3/action/datastore_search?resource_id=<id>&limit=1"`

If you are testing in a browser, make sure you are logged in to HDX as described in the Authentication section.

### Resource and dataset issues

* **Symptom: 404 Not Found or `{ "success": false, "error": "Not found" }`**
* Likely cause: Wrong resource\_id or the resource was removed or unpublished
* Fix: Confirm the resource\_id from the dataset page or via package\_show and verify the resource still exists. Also verify that the resource has Tabular Data endpoint access enabled. Look for `datastore_active` in the CKAN metadata.<br>
* **Symptom: Empty records with total greater than zero**
* Likely cause: Using an offset beyond the number of rows or incompatible filter values
* Fix: Remove filters and set limit to a small value to confirm data is present: `.../datastore_search?resource_id=<id>&limit=5`
* Then add filters back one at a time.

### SQL query errors

* **Symptom: `{ "success": false, "error": "Error parsing query" }`**
* Likely cause: Unquoted resource\_id or unencoded SQL
* Fix: Quote the table name and URL encode the entire SQL: `sql=SELECT%20*%20FROM%20"%3Cresource_id%3E"%20WHERE%20country%3D'Kenya'%20LIMIT%205`<br>
* **Symptom: Syntax error near `ORDER` or `LIMIT`**
* Likely cause: `ORDER BY` must reference valid column names and comes before `LIMIT` and `OFFSET`
* Fix: Validate column names through a small select first, then add `ORDER BY`, then `LIMIT` and `OFFSET` as shown in the SQL examples.

### Performance and rate limiting

* **Symptom: 429 Too Many Requests**
* Likely cause: High request concurrency or scraping patterns
* Fix: Back off and retry with exponential backoff, reduce parallelism, and page through results. The user’s IP is rate limited at 60 requests per minute so plan for modest throughput.

### Data types and schema drift

*For alerts on any changes to the data resources used within the Tabular Data Endpoints, enable notifications to be alerted about data schema changes. See more information* [*here*](https://docs.humdata.org/find/find-data/staying-up-to-date-with-hdx)*.*

* **Symptom: Type errors or unexpected nulls**
* Likely cause: Mismatched types in filters or recent schema change in the source file
* Fix: Inspect a sample of rows first to confirm types. If `year` is stored as text, pass `"2024"` rather than `2024`. If the maintainer updated the schema, refresh your assumptions and adjust the query accordingly.<br>
* **Symptom: Date filters fail to match**
* Likely cause: Inconsistent date formats in source
* Fix: Use exact string matching that reflects the stored format, or use SQL functions where supported to normalize formats.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.humdata.org/build/hdx-apis/tabular-data-endpoints/troubleshooting-and-error-handling.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.