title: Spark DataFrame Operations Performance - Multiple Discrete Operations or Single Pipe-lined set of Operations
body: <p>I know that SQL statements are basically broken down to the same Spark calls that the API uses, e.g. "<code>select * from my_data_frame1 inner join my_data_frame2 on id1 = id2</code>" works out to "<code>my_data_frame1.join(my_data_frame2,col(id1)===col(id2),"inner")</code>". Please correct me if I'm wrong or there are nuances.</p>
  
  <p>My question is:</p>
  
  <p>Suppose I have want to get data from 2 sets of 2 tables each, e.g. Internal Clients, External Clients, Internal Addresses, External Addresses, and Phone Numbers and wanted to get them together so it's just everybody and their address and phone number flattened out.</p>
  
  <p>What are the performance implications of doing this sort of thing, both via SQL and directly in Spark dataframe api calls? Particularly doing union&lt;>join vs join&lt;>union, and having separate steps vs no intermediary dataframes/tables?</p>
  
  <pre><code>A = internal clients join internal addresses
  B = external clients join external addresses
  final = A union B join phone numbers
  </code></pre>
  
  <p>vs </p>
  
  <pre><code>final = (internal clients union external clients) 
  join (internal addresses union external addresses)
  join phone numbers
  </code></pre>
  
  <p>vs </p>
  
  <pre><code>A = internal clients union external clients
  B = internal addresses union external addresses
  C = A join B join phone numbers
  </code></pre>
  
  <ol>
  <li><p>Will these work out to a substantially different plan? Especially the last two of the 3?</p></li>
  <li><p>What differences would there be, especially re: caching/broadcasting intermediate data?</p></li>
  <li><p>Are there any best practices that apply to this?</p></li>
  </ol>