I set up a small data stack for my company and it includes Airbyte to extract and load data from external services to a BigQuery datawarehouse, and it works well, we have now a massive amount of raw data and started to explore it with our dataviz tool.
To improve maintainability of our discoveries, we set up a dbt project to store our queries and help team to improve them.
However we aimed a limitation because while airbyte supports dbt transformation for a single source there is now way (afaik) to configure it when it comes to joining tables from multiple sources since I've no way to ensure that the source B is synced.
What are the best practices for such use cases? Is there any tools that I can plug to this stack to improve data transformation with dbt?
You need an orchestrator that will trigger airbyte then dbt. Check out services like Astronomer or Datacoves.