title: databricks partitioning w/ relation to predicate pushdown
body: <p>I have searched a lot for a succinct answer, hopefully someone can help me with some clarity on databricks partitioning..</p>
  
  <p>assume i have a data frame with columns: <code>Year</code>, <code>Month</code>, <code>Day</code>, <code>SalesAmount</code>, <code>StoreNumber</code></p>
  
  <p>I want to store this partitioned by Year, &amp; Month.. so i can run the following command:</p>
  
  <pre><code>df.write.partitionBy('Year', 'Month').format('csv').save('/mnt/path/', header='true')
  </code></pre>
  
  <p>This will output data in the format of: <code>/path/Year=2019/Month=05/&lt;file-0000x&gt;.csv</code></p>
  
  <p>If i then load it back again, such as:</p>
  
  <pre><code>spark.read.format('csv').options(header='true').load('/mnt/path/').createOrReplaceTempView("temp1")
  </code></pre>
  
  <p><strong>Q1:</strong> This has not yet actually 'read' the data yet, right? i.e. i could have billions of records.. but until i actually query <code>temp1</code>, nothing is executed against the source?</p>
  
  <p><strong>Q2-A:</strong> Subsequently, when querying this data using <code>temp1</code>, it is my assumption that if i include the items that were used in the partitioning in the where clause, a smart filtering on the actual files that are read off the disk will be applied?</p>
  
  <pre><code>%sql
  select * from temp1 where Year = 2019 and Month = 05 -- OPTIMAL
  </code></pre>
  
  <p>whereas the following would not do any file filtering as it has no context of which partitions to look in:</p>
  
  <pre><code>%sql
  select * from temp1 where StoreNum = 152 and SalesAmount &gt; 10000 -- SUB-OPTIMAL
  </code></pre>
  
  <p><strong>Q2-B:</strong> Finally, if i stored the files in parquet format (rather than *.csv).. would both of the queries above 'push down' in to the actual data stored.. but in perhaps different ways?</p>
  
  <p>I.e. the first would still use the partitions, but the second (<code>where StoreNum = 152 and SalesAmount &gt; 10000</code>) will now use columnar storage of parquet? While *.csv does not have that optimisation?</p>
  
  <p>Can anyone please clarify my thinking / understanding around this?</p>
  
  <p>links to resources would be great also..</p>