Thanks for jumping in! There's a lot going on in the data. I've mapped out an ERD, and it's 15ish tables and dozens of connections. It was my first time, so I hope I didn't overcomplicate it.
I don't want to waste anyone's time, so I'll answer your questions as succinctly as possible. (Just finished. Mission failed.) Below, I'll go into fuller detail to paint a picture.
What type of data: Not sure if this is what you mean, but almost exclusively text. There will be some blank PDF forms and such, but those will be mostly static. If you meant more what is the data supposed to do, see below for details.
Will the data be exported: Airtable will function as a front-end of sorts for another platform, so tables will rarely if ever be exported. I will try to have an "Updates" interface that will let team members know what they need to update on the other platform, sort of our database. (No API or any way to automate).
Why is the data being imported frequently: We're forced for now) to use one platform as a database for PII, and Airtable for workflows, dashboards, and Airtable forms. All changes to the database must be manual, but exporting those changes is semi-automated. To keep Airtable useful throughout the day, frequent updates seem appropriate.
Automations of single records: I have seen that a record changing can trigger an automation, which I assume could also apply to this merging scenario - as in, it doesn't just apply to manual updates from users. I could also trigger it based on the merge happening or something. I think I tenuously grasp the idea that automations live in a separate space from tables, so it could apply over and over to the CSV landing table as it updates. I'm less sure if this would work with formula fields, for example. I guess it all seemed kind of against my intuition about how it should work, and Airtable seems powerful enough that I could get lost for weeks trying to force my solution instead of thinking of it in the "Airtable Way".
Details: we're managing insurance enrollment and credentialing for healthcare providers. The data being loaded in on a schedule is each of those providers' statuses with each of the insurances they're seeking to be enrolled with as well as dates. There's a provider table, an org table, a location table (some orgs have multiple locations, and some providers work at 3-4 locations), and an insurance company table. We're tracking each insurance company's requirements, such as licensure. One goal is to match that against the documents each provider has (a junction table between providers and a doc table). Each org also has specific requirements, so we're trying to link everything so when a team member goes to complete a certain task on behalf of Dr. X, a workflow appears with checklist items tailored to what Dr. X already has on file, and what that insurance or org requires. That's why there's so much linking and processing after the CSV hits the base.
My goal is to capture any manual changes made in Airtable and present a list of changes to be made in the "database", the source of the updating data. (It's a software platform that proved too limited and slow to make improvements for frontend use, but it's safer to leave PII there rather than in Airtable... Not ideal, I know. For now, I have to keep that platform as our source of truth)
When statuses or dates or the provider documents we upload change there, I do want that quickly reflected in Airtable. We're setting up an SFTP, which can be updated up to hourly. Either hourly or daily updates would be okay, but I figured it probably wasn't any harder to do it more frequently. If it adds too much cost or it takes several minutes to process in Airtable, I'd switch to daily.
