The Informed Company

Chapter Twenty-OneBuilding an SST Example

To build a data warehouse you need to determine what tables and fields you will be including in it. Everyone's data sources are different, so there is no one right way. We will share some iterations we went through several years ago to get from the lake to the warehouse stage and how we responded to feedback and challenges.

First Attempt—Same Tables with Prefixes

In the original design of our data warehouse, we envisioned the schema to include all the needed tables from our raw sources: Chartio database, Salesforce, Stripe, Zendesk, Jira, Intercom, and Hubspot.

At this point there was no concept of a “staging schema” or “base models,” so we would query the raw tables and do data cleanup and transformation on them to build the SST. Because the different sources could have tables with the same names, each table would be prefixed with the name of the original source. Prefixing the table names with their underlying source also helps the user know where the data is coming from and keeps the schema organized as well.

Here is a sample of the tables in this schema:

• Chartio Charts
• Chartio Dashboards
• Chartio Organizations
• Chartio Users
• Intercom Admins
• Intercom Conversations
• Intercom Users
• Jira Issues
• Salesforce Contacts
• Salesforce Leads

What Tables Did We Keep?

In order to choose what tables to bring into the SST, we created a spreadsheet of the list of tables from each source and went through them one by one making the call on whether or ...