LIMIT has not been working for me in dbt from last week.
I run this query every week in dbt where I get all the rows in the table (37,000k) and then download it from dbt into a csv. However, when I run this query I am getting an error (Redshift error: syntax error at or near “limit” in context) and when I remove the limit in my query, I get 500 by default which can be changed upto 10,000. I need the whole table so I could download it.
I tried looking for a solution from the Help Assistant and looking up the error and couldn’t find anything that works. I did look at dbt show and while it did run, it did not have any export option for me.
Some example code or error messages
Code
WITH cte_a AS (
SELECT companyname, email, SUM(totaltimespent_sec) as totaltimespent_sec, SUM(category_market_intelligence_time_sec) as catintel_sec, SUM(categories_benchmark_time_sec) as benchmark_sec,
SUM(supdiscotimeinsec) as supplier_dis_sec, SUM(mmdtimeinsecs) as mmd_sec, SUM(suppliermonitoringtimeinsec) as supplier_mon_sec,
SUM(timespentviewingreportsinsec) as catintelreportview_sec
FROM {{ref('mt_table_user_level')}}
GROUP BY 1,2
)
SELECT *
FROM cte_a
LIMIT 100000
Error
Redshift adapter: Redshift error: syntax error at or near "limit" in context "cte_a
LIMIT 100000