Feat: Implement data lifecycle policies

[bmtcril]

We would like to make data lifecycle part management a default part of Aspects. Currently we are unable to gracefully age-off old data, creating some potential compliance issues and making it difficult to implement data best practices. Primarily this is because time-series tables are not partitioned, making ClickHouse deletes force a complete rewrite of the table. To enable lifecycle management we need to do the following:

• Partition all time-series tables by year-month
• Create a configurable list of tables that are managed in the lifecycle, defaulting to all of our time-series tables
  • These tables must all be partitioned by year-month
• Create a command to drop old partitions with a configurable number of months to keep
• Document this work, including an ADR on this topic and a guide to how to run the command

[Ian2012]

This handles the first part: openedx/aspects-dbt#42 however, I'm not sure if the main xapi table should be partitioned too?

@bmtcril

[bmtcril]

Yeah, especially that table should be on the list. It'll have to be another Alembic migration on that table, sadly.

[Ian2012]

Should we also implement some clean-up workflow on the event_sink tables?

[bmtcril]

Hmm I suppose so, though I think it would make sense to have that be separate from the user data. Like if you want to keep user data for 2 yrs and course data for 5 I think we should support that. Maybe a setting per table, or some kind of table grouping. We should remember the vector tables as well.

[pomegranited]

@bmtcril @Ian2012

I'm not sure if the main xapi table should be partitioned too?

Yeah, especially that table should be on the list. It'll have to be another Alembic migration on that table, sadly.

I said in the ADR that we'd be putting partitions into dbt instead of using alembic. Was I incorrect?

[bmtcril]

@pomegranited it needs to be both since some tables aren't able to be managed in dbt (yet, we're working on it).

[Ian2012]

The recommended approach by Altinitty is to use TTL with the setting ttl_only_drop_parts to improve performance. We will continue with this approach

[bmtcril]

@Ian2012 is pushing this forward in dbt-clickhouse here: ClickHouse/dbt-clickhouse#254

[bmtcril]

@Ian2012 can we consider this done now?

[Ian2012]

Yes, it's

[bmtcril]

Actually I need to reopen this since we didn't get to the documentation. @Ian2012 do you think you can write up a "concepts" doc in openedx-aspects describing the TTL, how it works, and the setting options?

[Ian2012]

Sure