For a project, we have several groups that will produce numbers using models. Input and output data will be in the DB. Each group is independent and works in their own different way; however, the outputs they produce will need to be combined, aggregated and queried.
I am debating whether to create one database with a lot of tables or many smaller databases which belong to each group. I am leaning towards creating multiple databses because of easier access management. However, I am concerned about performance.
Will querying tables across databases affect Teradata performance?
If you go the route of one database, based upon your description, you'll be forcing the dba team to maintain object level permissions, never a good idea. If you create a db for each team, your security paradigm is much simpler.
Creating a db for each team will allow them the flexibility to alter their own schema without impacting any of the other teams. Combining, Aggregating and Querying their "common" results will not result in performance degradation with a few caveats, make sure the "output" table DDL is as close to identical as possible to reduce the amount of processing in the "combined" view, ensure that every "output" table has the same Primary Index, NUPI or UPI doesn't matter, if you assign them all the same PI, you'll be designing in AMP local work, which is where Teradata shines.
Set up an aggregate view in a reporting/analysis db separate from the specific team db's and grant select on the base view databases for each individual team.