Bundling data from multiple companies in different CRM tools into one Postgres database

Slack Knowledge Base Q&A Connector Questions
, , , ,
1

Summary

Exploring the most effective way to consolidate data from multiple companies using different CRM tools into a single Postgres database while maintaining the ability to distinguish between companies.


Question

Hi there folks, I’m a SaaS developer relatively new to the ETL space and have few questions regarding the possibilities of Airbyte. For my client I’m building a platform where companies can create an account, and see their key metrics in a dashboard, a multi-tenant architecture so to say. The metrics we want show, should be extracted from the companies’ CRM and Accounting tool. Therefore we will have a lot of incoming connections with many difference CRM tools and Accounting tools. Now my question is:
Imagine I have 5 different companies utilizing Hubspot, what’s the most effective way to bundle them in one Postgres database? Important: I still need to be able distinguish all the different companies. Is this possible?


This topic has been created from a Slack thread to give it more visibility.
It will be on Read-Only mode here. Click here if you want
to access the original thread.

Join the conversation on Slack

["saas-developer", "etl", "airbyte", "multi-tenant-architecture", "crm", "accounting-tool", "hubspot", "postgres-database"]
2

It depends how you want to organize the data. You can have different companies in different databases in single PostgreSQL instance; or in different tables in the same database – for that scenario Stream Prefix might be useful

3

Hi <@U05JENRCF7C> thank you for your message!

For the sake of easy querying, the most convenient solution for us would be to put all Hubspot customers in the same tables but just with an extra colum with our foreign key. Is that possible with Airbyte?

4

I think only Cloud version allows any transformations https://docs.airbyte.com/cloud/managing-airbyte-cloud/dbt-cloud-integration or you would have to customize destination connector.
Another idea it to have https://www.postgresql.org/docs/current/sql-createtrigger.html|trigger in PostgreSQL that will move data to single table.