ADX (KQL) - Unable to query data older than 3 months

Question

I have a situation where I am unable to query historical data (older than 3 months) from our ADX database.

• My database has a retention period of 3600 days and cache period of 270 days.
• There are no ingestion failures

I ran the following queries:

MyTable
| where ingestion_time() between (datetime(2024-10-01) .. datetime(2024-12-31)) 
| summarize count()

The result was 4 000 000+ records

I then try to retrieve any of the records

set query_datascope='all';
set query_timeout = time(10m);
MyTable
| where ingestion_time() < datetime(2024-11-01)
| order by ingestion_time() desc
| take 100

But no data is returned.

Any suggestions why I am not able to access the data?

Pieter

Answer 1

@pjbcoetzer

Welcome to Microsoft Q&A platform and thanks for posting your query here.

It sounds like you're encountering an issue with querying older data in Azure Data Explorer (ADX).

I have tried to reproduce the issue in different date customization with 10000 records as I don't have data ingested 3 months ago

at first, I have set the date scope where I didn't ingest the data it as expected didn't get the data

I have set the query date forwarder more than 3 months from my data ingestion because I ingested it today and it worked, and I got the data over 10000 records as you can see in the image I have provided

As I look the query works perfectly with given dates for me

so could you please check below steps and see whether it resolve the issue

1. Ensure that the query_datascope is set correctly. You've already set it to 'all', which is good. This setting allows the query to access both hot and cold data.
2. Verify that the cache period is correctly configured. Since your cache period is 270 days, data older than this period might not be readily accessible without additional configurations.
3. Double-check the retention policy to ensure that data older than 3 months is indeed retained as expected. Your retention period of 3600 days should cover this, but it's worth confirming.
4. The query_timeout setting is set to 10 minutes, which should be sufficient. However, if the dataset is very large, you might need to increase this timeout.
5. Ensure that the ingestion_time() function is being used correctly. Sometimes, there might be discrepancies between the ingestion time and the actual data timestamp.
6. Check the performance and health of your ADX cluster. If the cluster is under heavy load, it might affect query performance and data retrieval.

here are some helpful links

:Azure Data Explorer: Why does it take longer to query 24h of data from 120 days ago than data from 60 days ago

:Adjust query timeout in Azure Data Explorer client tools/interfaces

I hope the above steps will resolve the issue, please do let us know if issue persists. Thank you