DataFrame API

Databricks · Apache Spark on Databricks

Theory

A Spark DataFrame is a distributed table with named, typed columns, like a SQL table spread across the cluster. You create one with spark.read.table("main.sales.orders"), spark.read.format("csv").load(path) or spark.createDataFrame(rows). You then chain transformations: select picks columns, filter (or where) keeps rows, withColumn adds or replaces a column, groupBy().agg() aggregates, join combines DataFrames and orderBy sorts. Functions come from pyspark.sql.functions, usually imported as F.

Every transformation returns a new DataFrame; the original is never changed. For example, orders.filter(F.col("amount") >= 900).groupBy("region").agg(F.sum("amount").alias("total")) returns region totals for big orders only. Spark's Catalyst optimizer turns the chain into an efficient plan, so DataFrame code and the same query in SQL run at the same speed.

Why we use it

The DataFrame API lets you build transformations in Python with variables, functions, loops and tests, while Spark still optimizes and distributes the work. It is the main way data engineers write ETL in PySpark.

When to use it

Use the DataFrame API for ETL logic, reusable functions and dynamic pipelines. Use SQL when the logic is a simple query or the audience is SQL users. Both compile to the same plan, so choose by readability.

How it works

  1. Read a table into a DataFrame
  2. Filter rows with filter / where
  3. Group with groupBy and aggregate with agg
  4. Sort with orderBy
  5. Need to keep the result? Yes: Write with saveAsTable. No: display() it for analysis.
  6. Catalyst runs the optimized plan

Syntax

from pyspark.sql import functions as F
df.select(cols) | df.filter(cond) | df.withColumn(name, expr)
df.groupBy(cols).agg(F.sum(c).alias(n), F.count("*"))
df.join(other, on, how) | df.orderBy(F.desc(c)) | df.dropDuplicates([cols])
df.write.mode("overwrite").saveAsTable(name)

Example

from pyspark.sql import functions as F

orders = spark.read.table("main.sales.orders")

big_by_region = (orders
    .filter(F.col("amount") >= 900)
    .groupBy("region")
    .agg(F.sum("amount").alias("total"),
         F.count("*").alias("orders"))
    .orderBy(F.desc("total")))

display(big_by_region)
big_by_region.write.mode("overwrite").saveAsTable("main.sales.big_orders_by_region")

Key points

Databricks topics

Databricks Fundamentals

Compute

Notebooks & Development

Apache Spark on Databricks

Delta Lake

Unity Catalog & Governance

Data Ingestion

Declarative Pipelines (Lakeflow)

Lakeflow Jobs

Databricks SQL & BI

Architecture, Performance & Cost

Machine Learning & AI

Certification & Interview

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Databricks

The lakehouse platform from the ground up: Apache Spark, Delta Lake, Unity Catalog, Lakeflow pipelines and jobs, Databricks SQL and MLflow. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice SQL / Spark SQL Sharpen the SQL you use in Databricks SQL and Spark SQL: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Databricks  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Databricks needs a real workspace — there's no in-browser engine for it. Click Copy above, then paste it into a notebook or the SQL editor in Databricks Free Edition (free, no credit card) and run it on serverless compute.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic