We have all these marketo tables in our data-warehouse:
I understand some of the main tables such activity_open_email, activity_send_email etc. but not other tables. Is there some documentation around what information each activity table captures?
Your integration app created those as individual tables, not Marketo. They originated as Marketo API responses. You don't download SQL tables.
Presumably the activity_* comes from an activity extract downloaded in full and then split on the Activity ID. Or possibly downloaded separately by Activity ID, same result.
Everything here should be self-explanatory, they relate to the same filters and triggers you see in Marketo Smart Lists.