re:dash should support incremental scheduled jobs

[fbertsch]

A lot of our data in Presto is partitioned by submission_date. When we run queries over this data, often times the result is also partitioned by submission_date (for example, MAU/DAU/WAU). That means for historical data, the results don't change, but we re-compute anyways.

Incremental jobs would solve this. The results from yesterday would be joined with the results from today. The query would have to have $SUBMISSION_DATE in it somewhere, which re:dash would automatically fill in with $YESTERDAY. It would be up to the query writer to ensure correctness; i.e. that the query is idempotent.

[washort]

in #28 Arik mentioned an upcoming feature will be making queries based on existing query results. Let's wait until that lands before digging into this.

[washort]

I misunderstood this - I thought outputs from previous queries were needed as inputs to new queries. Just appending new results to old ones should be simple -- the major question is if we need to handle removing results or if they should just accumulate indefinitely.

[fbertsch]

@washort we should certainly remove old results. Here are two options:

• Require a LIMIT clause on every incremental query. This would also require users to sort the results, but would be less work on the re:dash side.
• Include a variable configuration for every incremental query that limits results to the N most recent runs. The re:dash DB would then need to keep a monotonically increasing id with every result set, in order to just include the most recent N (and remove those prior).

[washort]

I don't see how a LIMIT clause would help, since that would just affect the results from a single run. An N-most-recent setting for the scheduler seems reasonable and I'll start there.

[fbertsch]

@washort some other thoughts that came up in regards to this feature:

• How will it deal with failure/rescheduling, when the new day's query fails? When will the query hard fail (after e.g. 3 failures), and will it email the job owner?
• How will the data be backfilled? For example: I create a new query for Firefox DAU, but the incremental query is only for today. I need the past 6 months to show as well. Would this be possible?

Not all of these need to be answered right now, we can improve them incrementally.