Can Amplitude handle complex queries as data source?

  • 27 February 2023
  • 1 reply

Over the last couple of days I run a handful of test when creating a data source connection from Snowflake.

Amongst them, a trial to have a “complex” query. In my case, this is a WITH query with a handful of CTEs, some of them with WHERE clauses, other with JOINs.

Interestingly, the query passed the test, but now the ingestion seems to be going nowhere.

Admittedly, we are talking about ~5M rows, that might play a role, but it has now been more than three hours since I defined the data source. Also worth mentioning, I see no Ingestion jobs listed at all.

A few days ago I did a test connection over a dataset of about ~1M rows and it had completed it in no time, and by a couple minutes I could see multiple ingestion jobs running.

Hence the doubt that maybe Amplitude doesn’t like this type of queries.

Also, since this is meant to be a time-based sync, and I guess it’s fair to assume that the queries are being templated with something akin to Jinja post first ingestion to add the WHERE timestamp > max_timestamp_last_load, could it be that the presence of a WHERE clause in one of the CTEs throws it off somehow?


Best answer by Amplitude Admin 28 February 2023, 22:19

View original

1 reply

Userlevel 6
Badge +9

Thanks for posting here @Igor Markovic. Were you able to figure this out? This past post deals with a similar topic. Keep us posted if you need additional help!