Investigate slow QA/QC models

[ian-r-rose]

In recent weeks, CI runs have roughly doubled in length (example here). This increase is dominated by a few newer models:

• quality__row_count_summary
• int_performance__detector_metrics_agg_daily
• performance__station_metric_agg_recent_one_week

I think it would be worthwhile to look into how we can speed it up. A couple of options:

1. There may be some performance optimizations to be found based on appropriate partition/column pruning
2. We may want to make a couple of them incremental
3. We may wan to choose a larger warehouse for them

[jkarpen]

Note to reach out to @summer-mothwood if it seems like the solution will be to convert to incremental models.

[jkarpen]

Possibly also explore this option: https://docs.getdbt.com/docs/build/incremental-microbatch

[JamesSLogan]

Our nightly build time should improve by 20+ minutes, by my estimation.

• int_performance__detector_metrics_agg_daily
  • Made this model incremental. This is the daily performance aggregation, it straddles the line between the 5 min/hourly ones, which are already incremental, and the weekly/monthly ones, which are not.
• performance__station_metric_agg_recent_one_week
  • The second self join in the model explodes to >4 billion records, even on 1 week's worth of data. I reworked it and the query takes <30s on an XS warehouse.
• quality__row_count_summary
  • Bumped up the warehouse size. This model is meant to show an accurate view of row counts across a few different places. It could be made incremental but would likely be forgotten in future full-refreshes and therefore compromise its accuracy. The XL warehouse will be faster and parallelize well with this query.

Pinging @summer-mothwood since one of the fixes does introduce incrementality to a model. (I used detector_id)