Mitto and Analytics Database Design

[anon68878319]

This post is for consolidating ideas and best practices around structuring databases, schemas, and tables with Mitto.

Internal Mitto Use Cases

You are using Mitto to pipe data from your organization’s data sources (e.g. Salesforce, Netsuite, and Jira) into a database for analytics.

Single Analytics Database (e.g. Mitto’s PostgreSQL analytics database)

• Source data
  • Schema per “source”
    • salesforce
    • netsuite
    • jira
  • Table per source “endpoint”
    • salesforce.opportunity
    • netsuite.invoices
    • jira.issues
• Data Models
  • Schema per “modeled source” or “use case”
    • salesforce_models - e.g. Opportunities and opportunity line items combined
    • netsuite_models - e.g. Financial models from Netsuite (union of cash sales, credit memos, cash refunds, journal entries, etc)
    • product_models - e.g. Subscriptions from Salesforce combined with support tickets from Jira
  • Table per model

External Mitto Use Cases

You are using Mitto to pipe data from multiple entities (subsidiaries, customers, partners, applications, etc) into a database for analytics.

Single Analytics Database (e.g. Mitto’s PostgreSQL analytics database)

• Source data
  • Schema per “entity” and “source”
    • subsidiary_a_salesforce
    • partner_b_netsuite
    • customer_c_hubspot
  • Table per source “endpoint”
    • subsidiary_a_salesforce.opportunity
    • partner_b_netsuite.invoices
    • customer_c_hubspot.contacts
• Data Models
  • Schema per “entity” and “modeled source” or “use case”
    • subsidiary_a_salesforce_models - e.g. Subsidiary A’s opportunities and opportunity line items combined
    • partner_b_netsuite_models - e.g. Partner B’s financial models from Netsuite (union of cash sales, credit memos, cash refunds, journal entries, etc)
    • customer_c_marketing_models - e.g. Contacts from Salesforce combined with contacts from Hubspot
  • Table per model
• Union of Entities - The purpose of these schemas and tables are to combine data across entities. This is useful when you want to compare the entities or aggregate across entities for broader analytics.
  • Schema per “source”, “modeled source”, or “use case” - These schemas contains tables that combine data across entities
    • salesforce
    • netsuite
    • hubspot
  • Table per “source” or “model” - These tables contain data combined from the individual entity tables. At least one extra column would be added denoting which entity the row came from.
    • salesforce.opportunity - Combination of all Salesforce Opportunity tables across entities.
    • netsuite.invoice
    • hubspot.contacts

Analytics Database per Entity

See Single Analytics Database section above. The only change here is a database per entity.