Skip to main content
Docs
Feature Requests Onboarding Trust Center Status Page

Community

Use the same schema prefix for multiple databases

Answered
scott wagemann User

I have some applications whose data is in several unique databases.  I realize this isn't ideal but this is the situation.  Each database contains different schemas with different schema names.  I would very much like to use the schema prefix for each of these database since they're all part of the same application.  Fivetran currently doesn't allow this but it should allow the same schema prefix when the two databases don't have any schema names in common.

  • Source:
  •      db1: schema1, schema2
  •      db2: schema3, schema
  • Destination:
  •      db.commonPrefix_schema1
  •      db.commonPrefix_schema2
  •      db.commonPrefix_schema3
  •      db.commonPrefix_schema4

 

Please sign in to leave a comment.

Comments

3 comments

Date Votes
  • Official comment
    Fraser User

    Hi Scott - we have a strong philosophy of "one simple default choice" with minimal configuration.  Our strategy for avoiding schema & table name collisions in the data warehouse is to require the unique prefix. In practice, this simple rule has been incredibly effective - I can't recall a customer ever having a naming collision.

    Our recommendation is a "Enterprise Data Warehouse" architecture, with Fivetran delivering the Raw Schemas, and your users being exposed a transformed set of Schemas.  In your case, that transformation is a simple set of VIEWs that organize the tables into the schema you want.

  • scott wagemann User

    OK, that makes sense.  Thanks Fraser.

  • Jason Scarlett User

    I know the "Enterprise Data Warehouse" architecture described may be common, but for larger warehouses with 10k plus tables, creating and maintaining views like this is unsustainable. We would like more flexibility on the naming of destination schemas. Force prefixes is a problem for us.