So we have this very old reservation system sitting in SQL Server, and we want to extract this data into JSON documents / events. However, one of our JSON models gets data from 130 tables, which are all joined together with various keys and shapes. If we set up a CDC change listener on SQL Server, then whenever a table value changes on one of the 130 tables, we need to be able to pull together a complete new JSON record, containing data from these 130 tables, and therefore we need to JOIN all this data using something like dbt or airbyte SQL syntax. For this to work, tho, you need to keep the entire state of the SQL server available in a store local to Airbyte, so a JOIN can always be performed. Is this use case relevant to Airbyte? Am I barking up the wrong tree here?

Airbyte will help you to transfer the data to your destination. But transformations must be made as a post process using dbt.