"limit 100" in query is not always respected

There are times when the “Preview” returns no rows. In those cases I have to use “Run”. That’s fine, but when I submit a query where I provide a “limit 1000”, sometimes Dremio submits a query to Snowflake that has no limit at all. That is, Dremio requests the entire table from Snowflake. This essentially breaks my ability to craft a query on some tables because it takes forever for the query to return. Submitting the same query to Snowflake is nearly instant because it has the “limit 100”.

Why does Dremio do this? I can imagine it would have to do this in case of some tables that have, e.g. s3-stored csv files. But in the case of Snowflake, this should never happen.

Is there some way to prevent Dremio from doing this?


I see you have 2 questions

#1 Why does Preview sometimes does not return any result - This could happen if the filter is applied on the sample. Do you have a profile?

#2 Dremio retrieves all records from Snowflake - This should not happen, what can happen though is that Dremio would have selected a batch and even though there is a limit, the batch will go through but that still not be all the records in the Snowflake table, any chance you have the profile for this too?

I replied to the other thread about Preview sometimes not returning any results.

Regarding getting a profile for this “limit 100” thing, here is a profile:
37f3dcab-46f7-40d4-876d-369b44ca3177.zip (22.1 KB)

This is the query:

cast(player_start_date as date) as player_start_date,
cast(event_date as date) as eventDate,
datediff(cast(event_date as date),cast(player_start_date as date)) as days_between
limit 100

It returns with the expected results, but I can see in Snowflake that Dremio submitted a query to get all the rows from the table rather than just 100.