Back to the index · Previous: 7b. SQL reference · Next: 7d. Streaming, materialized views and incremental tables
A Python cell runs PySpark in your Spark session, with the DataSimple helpers loaded. Use the helpers to read and write tables: they find the table in the catalog, apply your access rules, register what you write and record lineage.
Ready in every cell#
No import needed:
| Name | What it is |
|---|---|
spark |
Your Spark session |
F |
pyspark.sql.functions (F.col, F.sum, F.when …) |
T |
pyspark.sql.types (T.StringType() …) |
Window |
pyspark.sql.window.Window |
ds |
DS Tables, apply changes, star schemas (chapter 15) |
read_table, save_table, … |
The helpers below |
What shows under the cell: print(...) output, then the cell's last expression. A DataFrame as the last line shows as a result table (up to 200 rows); df.show() prints it as text.
Parameters from the Parameters cell are Python variables: a parameter region is the variable region.
Packages. pandas, NumPy and the standard library are there. Other packages are installed by your administrator; %pip is not available.
Read and write: the five you need#
orders = read_table("bronze.dbo_Orders") # governed read, any format
live = orders.filter("NOT _is_deleted")
new_rows = live.filter("OrderID > 1000") # the rows to add
changes = live.filter("Status = 'Open'") # the rows to update or insert
save_table(live, "silver.orders_live") # create or replace a Delta table
save_table(new_rows, "silver.orders_live", mode="append") # add rows
merge_delta("silver.orders_live", changes, on=["OrderID"]) # update matching keys, insert the rest
old = read_delta_at("silver.orders_live", version=0) # time travel
PySpark DataFrames in short#
df = read_table("silver.sales")
# Choose and rename columns
df.select("region", "order_date", "net_amount")
df.withColumnRenamed("net_amount", "net")
# New columns
df = (df.withColumn("month", F.date_trunc("month", "order_date"))
.withColumn("tier", F.when(F.col("net_amount") >= 1000, "High").otherwise("Standard"))
.withColumn("region", F.upper(F.trim("region"))))
# Filter
df.filter(F.col("net_amount") > 0)
df.filter("region = 'WEST' AND net_amount > 0") # a SQL condition also works
# Aggregate
by_region = (df.groupBy("region", "month")
.agg(F.sum("net_amount").alias("net_sales"),
F.countDistinct("customer_id").alias("customers")))
# Join
customers = read_table("silver.customers")
joined = df.join(customers, on="customer_id", how="left") # inner, left, right, full, left_anti, left_semi
# Window: the latest row per key
w = Window.partitionBy("OrderID").orderBy(F.col("_load_ts").desc())
latest = (read_table("bronze.dbo_Orders")
.withColumn("rn", F.row_number().over(w))
.filter("rn = 1").drop("rn"))
# Sort, de-duplicate, count
by_region.orderBy(F.desc("net_sales"))
df.dropDuplicates(["order_id"])
df.count()
# SQL from Python (use schema.table, not datasimple.schema.table)
spark.sql("SELECT region, SUM(net_amount) AS net FROM silver.sales GROUP BY region")
# To pandas (filter and aggregate first: pandas loads everything into memory)
pdf = by_region.toPandas()
All helpers#
Read#
| Helper | Purpose |
|---|---|
read_table(name) |
Governed read. Finds the format in the catalog (Parquet, Delta or Iceberg), checks SELECT, applies row filters and column masks |
read_delta(name), read_parquet(name), read_iceberg(name) |
The same for a catalog name |
read_delta_at(name, version=None, timestamp=None) |
Delta time travel |
list_delta_history(name, limit=20) |
Delta history as rows |
delta_file_count(name) |
Number of files of a Delta table |
read_share(share, table) |
A DataShare table you may read |
read_staged(connection_id, table, workflow_id=None) |
The DataBridge raw copy behind a Workflow box |
read_jdbc(engine, host, port, database, table, user, password, **opts) |
Read a PostgreSQL or MySQL table directly |
Write#
| Helper | Purpose |
|---|---|
save_table(df, name, mode="overwrite", partition_by=None, merge_schema=False, fmt=None) |
Delta (or fmt="iceberg"); registers the table, applies quality rules that enforce on write, records lineage. Modes: overwrite, append |
write_delta(df, name, mode="append", partition_by=None, replace_where=None, merge_schema=False, expect=None) |
Delta write; replace_where replaces only the matching rows; expect checks rules first |
merge_delta(target, source_df, on, when_matched="update_all", when_not_matched="insert_all") |
Delta MERGE by key |
create_managed_table(df, name, mode="overwrite", partition_by=None, fmt=None) |
A managed Delta, Parquet or Iceberg table |
create_temp_table(df, name) |
A temporary view, only in your session |
quarantine_write(df, target, rules, mode="append") |
Good rows to the target, bad rows to <target>_errors |
read_errors(target), replay_errors(target, fix=None), purge_errors(target, older_than_days=90) |
Work with the error table |
optimize_table(name), optimize_tables(names) |
Compact small files |
drop_table(name, purge=True) |
Drop a table (files deleted only under the managed area) |
Changes from a source (CDC)#
| Helper | Purpose |
|---|---|
ds.apply_changes(target, source, keys, sequence_by, apply_as_deletes=None, stored_as_scd_type=1, except_columns=None, track_history_except_columns=None) |
A change feed to a Type 1 (current) or Type 2 (history) table |
ds.apply_changes_from_snapshot(target, snapshot, keys, stored_as_scd_type=1, snapshot_version=None) |
The same from a full copy; missing keys are deleted or closed |
ds.create_target_table(name, comment=None, columns=None, format="delta") |
An empty registered target |
ds.apply_changes(target="silver.orders_history", source="bronze.dbo_Orders",
keys=["OrderID"], sequence_by="_load_ts",
apply_as_deletes="_op_type = 'D'", stored_as_scd_type=2,
except_columns=["_op_type", "_source_ts"])
Declared tables, star schemas and One Big Tables#
| Helper | Purpose |
|---|---|
@ds.table(name, mode="merge", keys=None, sequence_by=None, expectations=None, comment=None) |
Declare a table; the function returns its DataFrame |
ds.expect(df, name, rule, action="fail") |
A rule: fail, warn or drop |
@ds.dimension(name, keys, scd=1, …), @ds.fact(name, grain, dimensions=…), @ds.obt(name, fact, …) |
Star schema and One Big Table |
ds.read(name) |
A declared table (built first) or any catalog table |
ds.run() |
Build every declared table in dependency order; returns {table: rows} |
ds.status(), ds.reset(), ds.checks(), ds.models() |
Show or clear the declarations, see the join checks |
@ds.table("silver.orders_clean", mode="merge", keys=["OrderID"], sequence_by="_load_ts",
expectations=[{"name": "amount_positive", "rule": "Amount > 0", "action": "fail"}])
def orders_clean():
return ds.read("bronze.dbo_Orders").filter("_op_type <> 'D'")
@ds.table("gold.orders_by_status", mode="overwrite")
def orders_by_status():
return ds.read("silver.orders_clean").groupBy("Status").count()
ds.run() # builds silver.orders_clean first, then gold.orders_by_status
Full example: chapter 15.
Data quality#
| Helper | Purpose |
|---|---|
profile_table(name) |
Profile a table: counts, nulls, distinct values, ranges |
suggest_checks(name) |
Suggested checks from the data |
check_table(name, checks, level="Warning", fail_on_error=True) |
Run checks; the results show in Data Quality |
table_freshness(name) |
When the table last changed |
expect(df, rules) |
Check rules on a DataFrame |
enforce_pk(...), enforce_fk(...), enforce_not_null(...), enforce_unique(...) |
Validate keys and constraints |
check_table("silver.orders_clean", lambda c: c.isComplete("OrderID").isUnique("OrderID"))
Access#
| Helper | Purpose |
|---|---|
grant(privilege, on, principal), revoke(privilege, on, principal) |
Grant or revoke on a table (administrators or MANAGE) |
apply_row_filter(df, expr), apply_column_mask(df, column, expr) |
Apply a filter or mask by hand |
Workflows#
A notebook behind a Workflow box gets WF_TEST_MODE, WF_TARGET_DATABASE, WF_SAMPLE_ROWS and workflow_table(table), which returns the box's target table.
Not available, and what to use instead#
| You may know | In DataSimple |
|---|---|
display(df) |
Make the DataFrame the cell's last line |
dbutils.widgets |
A Parameters cell |
dbutils.notebook.run |
A pipeline with one step per notebook |
dbutils.fs |
Read files as tables (DataBridge), or read_parquet / read_delta by name |
%pip install |
Ask your administrator |
spark.readStream / writeStream |
A streaming table: spark.sql("REFRESH STREAMING TABLE silver.orders_live") is not supported from Python; create and refresh it in a SQL cell (7b, 7d) |
spark.read.load("s3://…") |
read_table("<layer>.<table>"): a path bypasses your access rules |
The editor's autocomplete lists every helper with its signature and description; GET /api/notebooks/spark/helpers returns the same list.
Back to the index · Next: 7d. Streaming, materialized views and incremental tables