SQL, Spark, pipelines, and lakehouse patterns
2026-09-27
Notes on SQL, Spark, pipelines, dimensional modelling, analytics queries, and Chemprop for drug discovery on Databricks.
These chapters started as learning notes and primers.
Notes on using Chemprop, a directed message-passing graph neural network for molecular property prediction, as a managed workflow on Databricks, from loading pretrained models to training multi-task ADMET regressors.
These are my notes on a Databricks Community technical blog post, “AI Drug Discovery Made Easy: Your Complete Guide to Chemprop on Databricks.” All credit for the original workflow and code goes to the Databricks authors, see the link for the full walkthrough and runnable notebooks.
Computational drug discovery got a well-known proof point in 2020, when a graph neural network flagged halicin, a compound already sitting in a screening library, as a candidate antibiotic with a structure unlike anything in existing drug classes. That’s the pitch for pairing a chemistry-aware model like Chemprop with a platform like Databricks: search chemical space faster, and do it on infrastructure that also handles the data engineering and deployment side.
Chemprop is a directed message-passing neural network (D-MPNN) built specifically for molecules. Instead of hand-engineered chemical descriptors, it treats a molecule as a graph, atoms as nodes, bonds as edges, and runs several rounds of message passing so that information about neighboring atoms and bonds propagates through the structure. The result is a learned molecular fingerprint that feeds into a feed-forward network for the actual prediction task, whether that’s a classification (toxic / non-toxic) or a regression (solubility, binding affinity, an ADMET property).
The argument isn’t that Databricks changes the modeling, it’s that it removes friction around everything adjacent to modeling. Unity Catalog gives you a governed, discoverable place to keep datasets and registered models so a chemistry team and a data engineering team aren’t passing CSVs around. MLflow handles experiment tracking and model registry, so a trained Chemprop model can be logged, versioned, and served without a bespoke deployment pipeline. For a workflow that moves from raw SMILES strings to a served prediction endpoint, having compute, governance, and serving in one place is the practical win.
The Databricks post runs these against Unity Catalog data and MLflow’s registry. To show what’s actually happening underneath, the snippets below are trimmed straight from Chemprop’s own example notebooks (Chemprop is MIT-licensed), Databricks just wraps this with governed data access and managed serving.
1. Inference with a pretrained model, loading an existing Chemprop checkpoint and scoring new molecules for a property like aqueous solubility, with no training step at all.
from pathlib import Path
import numpy as np
import pandas as pd
import torch
from lightning import pytorch as pl
from chemprop import data, featurizers, models
chemprop_dir = Path.cwd().parent
checkpoint_path = chemprop_dir / "tests" / "data" / "example_model_v2_regression_mol.ckpt"
mpnn = models.MPNN.load_from_checkpoint(checkpoint_path)
test_path = chemprop_dir / "tests" / "data" / "regression" / "mol" / "mol.csv"
df_test = pd.read_csv(test_path)
smis = df_test["smiles"]
test_data = [data.MoleculeDatapoint.from_smi(smi) for smi in smis]
featurizer = featurizers.SimpleMoleculeMolGraphFeaturizer()
test_dset = data.MoleculeDataset(test_data, featurizer=featurizer)
test_loader = data.build_dataloader(test_dset, shuffle=False)
with torch.inference_mode():
trainer = pl.Trainer(logger=None, accelerator="cpu", devices=1)
test_preds = trainer.predict(mpnn, test_loader)
df_test["pred"] = np.concatenate(test_preds, axis=0)2. Training a single-task classifier, fine-tuning Chemprop on a labeled dataset for a binary property such as toxicity.
from pathlib import Path
import pandas as pd
from lightning import pytorch as pl
from chemprop import data, featurizers, models, nn
input_path = Path.cwd().parent / "tests" / "data" / "classification" / "mol.csv"
target_columns = ["NR-AhR", "NR-ER", "SR-ARE", "SR-MMP"]
df_input = pd.read_csv(input_path)
smis = df_input.loc[:, "smiles"].values
ys = df_input.loc[:, target_columns].values
all_data = [data.MoleculeDatapoint.from_smi(smi, y) for smi, y in zip(smis, ys)]
mols = [d.mol for d in all_data]
train_indices, val_indices, test_indices = data.make_split_indices(mols, "random", (0.8, 0.1, 0.1))
train_data, val_data, test_data = data.split_data_by_indices(all_data, train_indices, val_indices, test_indices)
featurizer = featurizers.SimpleMoleculeMolGraphFeaturizer()
train_dset = data.MoleculeDataset(train_data[0], featurizer)
val_dset = data.MoleculeDataset(val_data[0], featurizer)
test_dset = data.MoleculeDataset(test_data[0], featurizer)
train_loader = data.build_dataloader(train_dset)
val_loader = data.build_dataloader(val_dset, shuffle=False)
test_loader = data.build_dataloader(test_dset, shuffle=False)
mp = nn.BondMessagePassing()
agg = nn.MeanAggregation()
ffn = nn.BinaryClassificationFFN(n_tasks=len(target_columns))
mpnn = models.MPNN(mp, agg, ffn, batch_norm=False)
trainer = pl.Trainer(
accelerator="cpu", devices=1, max_epochs=20, enable_checkpointing=True,
)
trainer.fit(mpnn, train_loader, val_loader)
results = trainer.test(mpnn, test_loader)3. Serving via MLflow’s registry, pulling a registered model back out of Unity Catalog/MLflow for inference, which is the pattern you’d actually use once a model is production-bound rather than sitting in a notebook. This part is Databricks/MLflow plumbing rather than Chemprop code, so see the original post for that snippet.
4. Multi-task ADMET regression, training one model to predict several absorption/distribution/metabolism/excretion/toxicity endpoints simultaneously, which is closer to how a real drug-candidate triage pipeline would be structured than any single-property demo.
from pathlib import Path
import torch
import pandas as pd
from lightning import pytorch as pl
from chemprop import data, models, nn
input_path = Path.cwd().parent / "tests" / "data" / "regression" / "mol_multitask.csv"
target_columns = ["mu", "alpha", "homo", "lumo", "gap", "r2", "zpve", "cv", "u0", "u298", "h298", "g298"]
df_input = pd.read_csv(input_path)
smis = df_input.loc[:, "smiles"].values
ys = df_input.loc[:, target_columns].values
datapoints = [data.MoleculeDatapoint.from_smi(smi, y) for smi, y in zip(smis, ys)]
split_indices = data.make_split_indices(datapoints)
train_data, val_data, test_data = data.split_data_by_indices(datapoints, *split_indices)
train_dset = data.MoleculeDataset(train_data[0])
val_dset = data.MoleculeDataset(val_data[0])
test_dset = data.MoleculeDataset(test_data[0])
output_scaler = train_dset.normalize_targets()
val_dset.normalize_targets(output_scaler)
train_loader = data.build_dataloader(train_dset)
val_loader = data.build_dataloader(val_dset)
test_loader = data.build_dataloader(test_dset)
output_transform = nn.transforms.UnscaleTransform.from_standard_scaler(output_scaler)
ffn = nn.RegressionFFN(n_tasks=len(target_columns), output_transform=output_transform)
chemprop_model = models.MPNN(nn.BondMessagePassing(), nn.MeanAggregation(), ffn)
trainer = pl.Trainer(logger=False, enable_checkpointing=False, max_epochs=1)
trainer.fit(chemprop_model, train_loader, val_loader)
preds = trainer.predict(chemprop_model, test_loader)
preds = torch.concat(preds, axis=1)The interesting part isn’t that Chemprop works, message-passing GNNs for molecular property prediction are well established, it’s the integration story: a specialized chemistry model dropped into a general-purpose data platform’s governance and serving layer, so the same infrastructure that tracks a churn model can track a toxicity classifier. Worth reading the full post for the actual notebook code and the ADMET multi-task setup in detail.
A practical tour of flat, relational, and dimensional modelling, with Databricks SQL examples so you can choose the right shape for analytics and pipelines.
Data modelling is how you decide what your tables mean and how they relate. In Databricks you will often meet three styles: flat, relational, and dimensional. None is universally best. Each trades simplicity against flexibility, and each fits different stages of a lakehouse pipeline.
A flat model is a single wide table: one row per event or entity, with many columns and little or no joining. Think of a CSV of sales with customer name, product name, and region all denormalised onto the same row. Flat models are easy to load, easy to query for beginners, and common for landing zones or feature tables.
The downside is redundancy. The same customer details repeat on every order row. Updates become painful, and storage grows. Flat tables also encourage analytics that are hard to reuse across teams.
CREATE OR REPLACE TABLE sales_flat AS
SELECT
order_id,
order_date,
customer_id,
customer_name,
product_id,
product_name,
quantity,
unit_price,
quantity * unit_price AS line_amount
FROM bronze.orders_raw;Use flat models for exploration, machine learning feature stores, or narrow reporting extracts. Avoid them as the long-term system of record when many teams need consistent definitions.
Relational modelling normalises entities into related tables: customers, products, orders, order lines. Keys link them. This is the classic OLTP and enterprise warehouse style. In Databricks it often appears in silver layers where you clean and conform source systems.
CREATE OR REPLACE TABLE dim_customer_rel (
customer_id STRING PRIMARY KEY,
customer_name STRING,
email STRING,
city STRING
);
CREATE OR REPLACE TABLE fact_order_line_rel (
order_line_id STRING PRIMARY KEY,
order_id STRING,
customer_id STRING,
product_id STRING,
quantity INT,
unit_price DECIMAL(12,2)
);Queries then join on keys:
SELECT c.customer_name, SUM(f.quantity * f.unit_price) AS revenue
FROM fact_order_line_rel f
JOIN dim_customer_rel c ON f.customer_id = c.customer_id
GROUP BY c.customer_name;Relational models reduce duplication and protect integrity. They can be slower for heavy analytics if you join many large tables without good partitioning and clustering. They also ask analysts to understand the schema deeply.
Dimensional modelling organises data for analysis: facts hold measures (sales amount, quantity), and dimensions hold descriptive context (date, customer, product). The classic star schema puts one fact table at the centre with dimensions around it.
CREATE OR REPLACE TABLE fact_sales (
date_key INT,
customer_key BIGINT,
product_key BIGINT,
quantity INT,
sales_amount DECIMAL(14,2)
);
CREATE OR REPLACE TABLE dim_product (
product_key BIGINT,
product_id STRING,
product_name STRING,
category STRING
);Dimensional models shine for BI tools and repeated business questions: revenue by category, orders by month, churn by segment. They are usually built in gold layers after you have cleaned relational or semi-structured sources.
Start from the questions people ask. If analysts need flexible drill-downs across many attributes, lean dimensional. If you are integrating operational systems, lean relational. If you need a fast one-off extract, a flat table is fine, just do not pretend it is a warehouse.
Good modelling is less about dogma and more about clarity: every column should have a clear grain, a clear owner, and a clear path into the next layer of your lakehouse.
How Kimball-style dimensional modelling maps onto a Databricks lakehouse: facts, dimensions, conformed keys, and practical design habits.
Kimball dimensional modelling is a practical way to design analytics data so business questions are easy to ask and hard to get wrong. In Databricks, the same ideas sit comfortably in a medallion lakehouse: cleaned silver entities feed gold stars that BI tools and SQL notebooks consume.
A fact table records measurable events at a declared grain, for example one row per order line per day. A dimension table describes the who, what, where, and when. Surrogate keys (integers or bigints you generate) link facts to dimensions so natural keys from source systems can change without breaking history.
In a star, the fact sits in the middle. Dimensions hang off it with few joins. Snowflakes normalise dimensions further (product to category to department). Stars are usually simpler for analysts; snowflakes can reduce duplication but add join complexity.
CREATE OR REPLACE TABLE gold.fact_orders (
order_date_key INT NOT NULL,
customer_key BIGINT NOT NULL,
product_key BIGINT NOT NULL,
order_id STRING NOT NULL,
quantity INT,
net_amount DECIMAL(14,2),
CONSTRAINT pk_fact_orders PRIMARY KEY (order_id, product_key)
);
CREATE OR REPLACE TABLE gold.dim_date (
date_key INT PRIMARY KEY,
full_date DATE,
year INT,
month_name STRING,
fiscal_quarter STRING
);Customer attributes change. Kimball Type 1 overwrites the current value. Type 2 keeps history with effective dates or version rows. In Databricks, Delta tables make Type 2 practical: merge new versions, expire old ones, and point facts at the correct surrogate key for that point in time.
-- Conceptual Type 2 pattern
MERGE INTO gold.dim_customer t
USING staging.customer_changes s
ON t.customer_id = s.customer_id AND t.is_current = true
WHEN MATCHED AND t.email <> s.email THEN UPDATE SET
t.is_current = false,
t.valid_to = current_date()
WHEN NOT MATCHED THEN INSERT *;Keep SCD logic in one place (a notebook job or dbt model) so every mart inherits the same history rules.
Classic Kimball assumed a relational warehouse bus. On Databricks you still want a bus: shared conformed dimensions published once, then reused. What changes is storage and compute, Delta Lake, Unity Catalog, and SQL warehouses, not the modelling discipline.
Name tables clearly (fact_ and dim_). Avoid
putting descriptive text on facts. Prefer integer date keys for pruning.
Partition large facts by date when queries filter that way, and
liquid-cluster or Z-order high-cardinality join keys when needed.
Kimball is not ceremony for its own sake. It is a shared language: when someone says “the grain is one shipment per day”, everyone knows what a duplicate means and which dashboard is trustworthy. That clarity is what makes dimensional modelling worth learning in Databricks.
The core SQL patterns worth mastering first, filtering, aggregation, joins, CTEs, window functions, and indexing, with runnable examples against a students table.
SQL (Structured Query Language) is a standardized programming language used to manage, query, and manipulate relational databases. It allows users to retrieve, update, insert, and delete data efficiently.
If you’re learning SQL, I’d recommend focusing on the following topics:
We will focus on the most-used concepts, which upon mastery will allow you to accomplish 80% of typical query tasks.
Let’s assume we are tasked with loading data from a students table for students who have achieved grades > 85.
SELECT *
FROM students
WHERE grade >= 85;In this query, SELECT * tells the SQL engine to load all
available columns in the table students and apply the filter to the
grade column.
SELECT * FROM students
WHERE grade BETWEEN 80 AND 90
AND department IN ('Computer Science', 'Math');SELECT * FROM students
ORDER BY grade DESC
LIMIT 10;SELECT department, AVG(grade) AS avg_grade
FROM students
GROUP BY department
HAVING AVG(grade) > 80;Here we can replace AVG with any of the following:
SELECT s.first_name, s.last_name, c.course_name
FROM students s
INNER JOIN enrollments e ON s.student_id = e.student_id
INNER JOIN courses c ON e.course_id = c.course_id;SELECT first_name, last_name
FROM students
WHERE student_id IN (
SELECT student_id FROM enrollments WHERE course_id = 101
);WITH top_students AS (
SELECT student_id, grade FROM students WHERE grade > 90
)
SELECT s.first_name, s.last_name, t.grade
FROM top_students t
JOIN students s ON s.student_id = t.student_id;WITH defines temporary query results.
SELECT student_id, grade,
RANK() OVER (PARTITION BY course_id ORDER BY grade DESC) AS rank
FROM enrollments;Concepts to know:
UPDATE students
SET grade = 95
WHERE student_id = 101;
INSERT INTO students (first_name, last_name, grade)
VALUES ('John', 'Doe', 88);
DELETE FROM students WHERE grade < 50;Extensions that are super important to know:
Note: Always use WHERE with UPDATE and DELETE to prevent accidental changes!
CREATE INDEX idx_students ON students (last_name);Concepts to know:
Reusable SQL patterns for analytics on Databricks: filters, aggregations, window functions, cohort logic, and star-schema joins.
Most analytics work is a small set of patterns repeated with different columns. Learn the patterns once, and Databricks SQL (or Spark SQL) becomes much faster to write, and easier to review.
Start with a clear grain, filter early, then group. Push predicates that prune partitions (especially date) as close to the base table as you can.
SELECT
product_category,
SUM(net_amount) AS revenue,
COUNT(DISTINCT order_id) AS orders
FROM gold.fact_orders f
JOIN gold.dim_product p ON f.product_key = p.product_key
JOIN gold.dim_date d ON f.order_date_key = d.date_key
WHERE d.full_date BETWEEN DATE '2026-01-01' AND DATE '2026-03-31'
GROUP BY product_category
ORDER BY revenue DESC;Window functions compare a row to its neighbours without collapsing the grain. Use them for running totals, previous-period values, and moving averages.
SELECT
full_date,
daily_revenue,
LAG(daily_revenue, 7) OVER (ORDER BY full_date) AS revenue_7d_ago,
AVG(daily_revenue) OVER (
ORDER BY full_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg_7d
FROM daily_sales;Rank within a partition when stakeholders ask for “top products per
region”. Prefer ROW_NUMBER when you need a deterministic
single winner, and DENSE_RANK when ties should share a
rank.
WITH ranked AS (
SELECT
region,
product_name,
revenue,
ROW_NUMBER() OVER (
PARTITION BY region
ORDER BY revenue DESC
) AS rn
FROM region_product_revenue
)
SELECT * FROM ranked WHERE rn <= 5;Cohort analysis groups users by first activity month, then tracks return activity. Keep the cohort assignment in a reusable view so dashboards stay consistent.
WITH first_order AS (
SELECT customer_key, MIN(full_date) AS cohort_date
FROM gold.fact_orders f
JOIN gold.dim_date d ON f.order_date_key = d.date_key
GROUP BY customer_key
)
SELECT
date_trunc('MONTH', fo.cohort_date) AS cohort_month,
date_trunc('MONTH', d.full_date) AS activity_month,
COUNT(DISTINCT f.customer_key) AS active_customers
FROM gold.fact_orders f
JOIN gold.dim_date d ON f.order_date_key = d.date_key
JOIN first_order fo ON f.customer_key = fo.customer_key
GROUP BY 1, 2;Join facts to dimensions on surrogate keys. When a fact relates to many values of one dimension (for example an order with many promotions), use a bridge table rather than exploding the fact grain silently.
Not everything sums. Account balances and inventory snapshots are semi-additive: you may sum across products but not across days. Document that in the catalogue and encode it in metrics views so tools do not invent wrong totals.
SUM for additive facts like revenue and
quantity.Wrap stable logic in views or metric layers. Keep notebooks for exploration, not as the only definition of “monthly revenue”. When a pattern shows up three times, promote it. That is how analytics teams stay organised as the lakehouse grows.
How Apache Spark powers Databricks workloads: DataFrames, transformations, partitioning, and practical habits for reliable large-scale processing.
Apache Spark is the distributed engine underneath most Databricks data engineering. You write transformations against DataFrames (or SQL), and Spark plans them across a cluster. Understanding a few core ideas, lazy evaluation, partitions, shuffle, and caching, pays off quickly when jobs slow down or spill to disk.
Spark works best when you describe what you want in bulk operations: filter, select, join, aggregate. Avoid collecting large results to the driver or writing Python loops over partitions unless you truly need custom logic via pandas UDFs or mapInPandas.
from pyspark.sql import functions as F
df = spark.table("silver.orders")
clean = (
df
.filter(F.col("order_date") >= "2026-01-01")
.withColumn("line_amount", F.col("quantity") * F.col("unit_price"))
.groupBy("customer_id")
.agg(F.sum("line_amount").alias("revenue"))
)
clean.write.mode("overwrite").saveAsTable("gold.customer_revenue")Transformations build a logical plan. Actions such as
count, collect, show, or a write
trigger execution. That laziness lets Catalyst optimise the whole
pipeline. Chain filters before wide joins when you can, and prefer SQL
or DataFrame APIs over opaque UDFs so the optimiser can see your
intent.
Data is split into partitions processed in parallel. A shuffle redistributes data for joins and group-bys. Shuffles are expensive: they move bytes across the network and can create skew when one key dominates.
broadcast() or
rely on auto-broadcast hints when sizes allow.from pyspark.sql.functions import broadcast
facts = spark.table("gold.fact_orders")
dim_prod = spark.table("gold.dim_product")
joined = facts.join(broadcast(dim_prod), "product_key")In Databricks you usually read and write Delta tables. ACID
transactions, time travel, and MERGE make incremental
pipelines safer than raw Parquet folders. Combine Spark transformations
with Delta features: optimise layout, vacuum old files carefully, and
use Change Data Feed when downstream jobs need only the deltas.
Spark shines for large batch and micro-batch ETL, feature engineering, and joins that no longer fit on a single machine. For lightweight transforms on small tables, a SQL warehouse query may be simpler and cheaper. Match the engine to the data volume and latency, not to habit.
Think in DataFrames, respect the shuffle, and keep tables in Delta with clear layers. That combination is the everyday craft of processing big data in Databricks.
A practical guide to Databricks pipelines: medallion layers, incremental loads, orchestration, quality checks, and operational habits that keep data trustworthy.
A data pipeline moves information from sources to something people can trust, tables, dashboards, or models, on a schedule and with clear ownership. In Databricks that usually means notebooks or workflows reading raw files or CDC streams, writing Delta tables through bronze, silver, and gold layers, and publishing into Unity Catalog.
Treat each layer as a contract. Downstream jobs should not reach into bronze for business logic. That separation keeps reprocessing and debugging organised when a source changes shape.
Prefer incremental processing: read only new files, new partitions, or Change Data Feed rows, then merge into silver. Full reloads are fine for small dimensions; they become costly and slow for large facts.
-- Merge daily increments into silver
MERGE INTO silver.orders t
USING staging.orders_day s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;Idempotency matters. Re-running yesterday’s job should not double-count. Use deterministic keys, merge logic, and partition overlays so retries are safe.
Databricks Workflows (jobs) chain tasks: ingest, transform, test, and notify. Keep tasks small and explicit, one responsibility per task, so failures are easy to isolate. Pass parameters for run dates rather than hard-coding paths inside notebooks.
Pipelines without checks eventually lie. Add expectations at silver: non-null keys, valid ranges, referential integrity samples. Quarantine bad rows instead of failing silently. Emit row counts and freshness metrics so on-call engineers can see whether gold is stale before stakeholders do.
from pyspark.sql import functions as F
silver = spark.table("silver.orders")
assert silver.filter(F.col("order_id").isNull()).count() == 0
freshness_hours = (
spark.sql(
"SELECT (unix_timestamp() - unix_timestamp(MAX(_ingested_at))) / 3600 "
"FROM silver.orders"
).first()[0]
)Use Unity Catalog for permissions and lineage. Separate dev, staging, and prod catalogues or schemas. Promote code through repos and job definitions, not by editing production notebooks by hand. Secrets belong in secret scopes, never in cells.
Sources evolve. Version schemas thoughtfully, prefer additive columns, and document breaking changes. Keep visualisation and BI tools pointed at gold views so you can rearrange physical tables without breaking every dashboard.
Good Databricks pipelines are boring in the best way: incremental, tested, observable, and layered. When those habits are in place, modelling and analytics work can move quickly without sacrificing trust.