Vincent-Philippe Lauzon looks at how Azure Data Warehouse scales:
Which data gets stored in which database?
As long as you are doing simple select on one table and that your data is distributed evenly, you shouldn’t care, right? The query will flow to the compute nodes, they will perform the query on each database and the result will be merged together by the control node.
But once you start joining data from multiple tables, ADW will have to swing data around from one database to another in order to join the data. This is called Data Movement. It is impossible to avoid in general but you should strive to minimize it to obtain better performance.
This is a look primarily at the underlying mechanics rather than testing a particular load. Check it out.