There are quite a lot of factors as to whether the staging table will be fragmented, and if so, how much. The most obvious that come to mind are:
- How much data is being added to the table
- How much other tables are adding or deleting rows at the same time
- Where exactly in the database file the table was created and if there are a lot of contiguous free extents
So basically, the answer is that it is quite likely that the table will be fragmented. They won't necessarily cause your other permanent tables to be fragmented much, but again, if they are growing at the same time and need new extents, then yes.
All that said, the more important questions are:
- Does the fragmentation cause a performance problem? Not likely. Fragmentation is not the problem it used to be when you had 3 hard drives in a RAID 5 array. On Azure your data is using 100s of disks, so the combined read and write power reduces the performance impact of fragmentation to almost nil. See Stop Worrying About SQL Server Fragmentation"
- Is there anything I can do about it? Nothing reasonable to stop it unless you want to create a different filegroup on another volume dedicated to the staging tables. You could also rebuild your other tables and indexes with a lower fill factor and that may stop them from becoming fragmented as much if data is being inserted into someplace other than then end of the table (which might indicate poor table design), but again, see point above. Perhaps the most reasonable option is to simply run your reindexing job after the ETL job is done.