DataSimplePractical GuideLog inOpen DataSimple
DataSimple
Chapter 07cTransform and model

Python reference: PySpark and the DataSimple helpers

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#

Python
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#

Python
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
Python
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
Python
@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
Python
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