Databricks
PySpark

Smart Pricing Pilot

A Databricks medallion pipeline that turns noisy smart-meter telemetry into audit-ready bills, without a raw email address ever reaching analytics.

Role: Independent case study
Year: 2026
Stack: Databricks · PySpark · Delta Lake · Databricks Jobs · Databricks SQL

GreenGrid Solutions is a smart energy startup piloting a Time-of-Use pricing model across 100 residential properties, ahead of a Series B round that depends on the pilot proving itself. Its billing ran on manual CSV exports and spreadsheet formulas, which could not cope with faulty sensor readings or keep customer emails out of the numbers they were joined against. The brief was a Databricks pipeline that validates every reading, protects the customer data it touches, applies the peak-hour rate correctly, and produces a daily billing table the pilot can be judged on.

The problem

100 meters (M-101 to M-200) on a Time-of-Use plan, charged a doubled rate for usage between 18:00 and 21:00. The telemetry feeding that plan was not clean: the December data carried 62 anomalous readings, negative values from sensor interference and phantom spikes registering thousands of kilowatts in a single window. Excel could not filter either kind out, so some of them were reaching customer bills. Separately, the failure rate in the Time-of-Use calculations itself sat at 15%, undercharging during the exact peak windows that make the pricing model worth running, an estimated $12K a month in leakage per 100 homes.

The other half of the problem was who could see what. Customer email addresses lived in plain text on a shared drive, joined directly to usage data, with copies spread across analyst desktops. GreenGrid’s head of product summed up why that mattered more than a typical bug: “If we send one wrong bill during this pilot, customer trust is gone forever. I cannot bill someone five thousand dollars for a toaster glitch.” A pilot that exists to prove a pricing model to investors does not get a second chance at its first bill.

The architecture

The approach was a three-layer Lakehouse: Bronze captures the raw telemetry, PII, and pricing plans exactly as they arrive; Silver is where quality and privacy get enforced, before any billing number is calculated; Gold aggregates the clean, billable results into a table a dashboard can sit on. The sequence matters as much as the logic inside each layer: ingest, then quarantine, then transform and hash, then aggregate. Running billing before quarantine, or writing a join before the email column is hashed, reintroduces the exact problem the pipeline exists to close.

Databricks workspace overview showing 4 notebooks, 10 Delta tables, and 1 scheduled job. The notebook list reads 01_Bronze_Ingestion (ingest raw telemetry and source data), 02_Silver_Quality_Check (validate readings and quarantine anomalies), 03_Silver_SmartBilling (hash PII and calculate TOU pricing), and 04_Gold_DailyUsage (aggregate daily usage and billing). The Delta Tables panel groups them by medallion layer: Bronze holds bronze_readings, bronze_customers and bronze_pricing; Silver holds silver_quarantine and silver_smartbill; Gold holds gold_dailyusage. A Data Flow Overview diagram shows Bronze Layer feeding Silver Layer (quality validation, quarantine and PII hashing), then Silver Layer (TOU pricing calculation and smart billing), then Gold Layer (daily aggregation of peak, off-peak and bill amount). — Four notebooks, ten Delta tables, one scheduled job. The whole pipeline at a glance before any of the notebooks are opened.

Databricks Unity Catalog explorer for the greengridcasestudy schema under the databrickbootcamp catalog, listing six tables (bronze_pii, bronze_plan, bronze_readings, gold_daily_usage, quarantine_readings, silver_smartbill) and one volume (greengriddbvol). — The same pipeline from the catalog side: every Bronze, Silver and Gold table registered under one schema, with a volume holding the raw source files.

Bronze: ingest everything, as it arrived

The Bronze layer’s only job is to land the three source datasets untouched. customer_pii.csv carries the meter-to-email mapping, pricing_plans.csv carries the base rate per plan, and meter_readings.json carries the telemetry itself, with an explicit StructType schema because the readings arrive as JSON rather than a flat file.

Sample of the customer_pii source table with columns MeterID, PlanID and CustomerEmail, showing rows such as M-109 on plan P2 with email rosspatel8@example.org, through M-115 on plan P1. — The PII table. This column is exactly what has to disappear before Silver writes anything.

Sample of the pricing_plans source table with columns PlanID, PlanName and BasePricePerKwh, showing P1 as Saver at 0.1 and P2 as Eco-Green at 0.25. — Two pricing plans, each with its own base rate, before the peak-hour multiplier is ever applied.

Sample of the meter_readings source data with columns kwh, meter_id, status and timestamp, all rows showing status OK with kwh values such as 9.33, 7.49 and 11.25. — A clean slice of the telemetry. The anomalies that failed Excel's logic are further down the same file.

PySpark notebook 01_Bronze_Ingestion reading customer_pii.csv and pricing_plans.csv with header=True, and meter_readings.json against an explicit StructType schema for kwh, meter_id, status and timestamp. Each DataFrame gets an ingested_at timestamp and a run_id column from a widget, then writes to bronze_pii, bronze_plan and bronze_readings with saveAsTable in overwrite mode. — The ingestion notebook. Every row picks up an ingested_at timestamp and a run_id, so anything written to Bronze can be traced back to the run that produced it.

Nothing here is filtered or transformed. Bronze exists so that if a later layer’s logic turns out to be wrong, the original data is still sitting there to reprocess against, rather than having been overwritten by the first attempt at cleaning it.

Silver, part one: quarantine before anyone bills

The acceptance criteria were explicit about the range a reading has to fall in: above zero, below one hundred kilowatt-hours. Anything outside it, negative from interference or a phantom spike from a faulty sensor, gets routed to a quarantine table with a reason attached, rather than either failing the job or being silently averaged away.

PySpark notebook joining the readings, PII and plan tables, then computing a RejectedReason column with when(col(kwh) < 0, negative_readings).when(col(kwh) > 100, over_readings).otherwise(unknown), and writing the result to quarantine_readings. — The quarantine logic: a reading either has a clear reason to be here, or it's flagged unknown rather than dropped without a trace.

Rows in the quarantine_readings table, all with status ERR and negative kwh values from -19.17 to -19.99, each carrying its meter_id, PlanID, PlanName, BasePricePerKwh, CustomerHash and ingested_at. — Every quarantined row still carries its plan and hashed customer context, so investigating why a reading failed doesn't require a second lookup.

Routing invalid readings here, before the billing notebook runs, is what keeps a sensor glitch from ever becoming a line item on someone’s bill.

Silver, part two: apply the peak rate

Readings that pass the range check move into the billing transformation. It joins the clean readings against the plan and customer tables, derives an Hours column from the reading’s timestamp, and doubles the base rate whenever that hour falls between 18:00 and 21:00.

PySpark notebook reading bronze_readings filtered to kwh between 0 and 100, joining it to bronze_pii and bronze_plan, adding an Hours column from the timestamp, and a FinalRate column that doubles BasePricePerKwh when Hours is between 18 and 21 and otherwise keeps it unchanged, then writing the result to silver_smartbill. — The billing logic in full: join, derive the hour, apply the peak multiplier conditionally, hash the identifier, write.

Readings joined with plan data and a derived Hours column, showing rows at hours 12, 18, 13 and 19 alongside their kwh, PlanID, PlanName and BasePricePerKwh. — The Hours column this rate decision hinges on. Row two, at hour 18, is right on the edge of the peak window.

Silver, part three: hash the identifier, don’t delete it

Customer email addresses could not reach the analytical tables, but Bill_Amount still has to trace back to the right customer for support queries and audits later. Deleting the email column outright would have broken that traceability; leaving it in plain text was the exact liability the pilot was built to close.

Challenge 01

Keep the join working without keeping the email

Silver_SmartBill has to join readings to the right customer, and Gold has to be able to trace a billing dispute back to a specific meter's history. But no email address is allowed to persist past Bronze, and unhashed PII in production halts the pilot outright. Delete the column and the join breaks; keep it and the pilot fails governance.

Each CustomerEmail value is hashed with SHA-256 (sha2(col("CustomerEmail"), 256)) into a new CustomerHash column in the same transformation that builds the join, and the raw email column is dropped before anything is written. Two rows for the same customer produce the same hash, so a join on CustomerHash still finds the right person consistently, joins and groupings keep working exactly as before, without the pipeline ever storing or transmitting the address itself.

It’s worth being precise about what this does and doesn’t guarantee: the hash isn’t salted, so if the hash values themselves ever leaked, an attacker with a list of likely email addresses could still match them by re-hashing and comparing. A salted hash is the next hardening step, not something this pilot needed in order to ship, since the goal here was keeping the raw address out of every downstream table, not defending the hash values themselves.

The join and transformation logic zoomed in: hour and FinalRate derivation, followed by withColumn CustomerHash, sha2(col(CustomerEmail), 256)).drop(CustomerEmail). — The hash and the drop happen in the same chained expression, so there's no intermediate DataFrame anywhere that still holds the raw email.

Rows from silver_smartbill showing Hours, FinalRate, ingested_at, run_id and CustomerHash columns. FinalRate values include 0.25, 0.2, 0.1 and 0.5, and CustomerHash holds 64-character hexadecimal strings with no email pattern visible anywhere. — No column in this table can be searched for an @ sign. FinalRate of 0.5 on the fourth row is a base rate of 0.25 doubled by the peak multiplier.

Gold: aggregate for reporting

The final notebook reads silver_smartbill, labels each reading Peak or Off-Peak from its hour, and groups by meter and usage date to produce one row per meter per day: peak consumption, off-peak consumption, total usage, and the billed amount for each.

PySpark notebook adding a PeakCategory column of Peak or Off-Peak based on the Hours column, then grouping by meter_id and UsageDate to aggregate peak_kwh, off_peak_kwh, total_kwh, peak_bill, off_peak_bill and total_bill, writing the result to gold_daily_usage. — One groupBy and a handful of conditional sums turns a month of individual readings into one row per meter per day.

Rows from gold_daily_usage for meter M-101 across ten consecutive December dates, showing peak_kwh, off_peak_kwh, total_kwh, peak_bill, off_peak_bill and total_bill for each day, such as 2025-12-04 with 6.89 peak kWh, 3.8 off-peak kWh and a total bill of 2.67. — Ten days of one meter's billing history, computed from the same pipeline that quarantined its bad readings and hashed its owner's email a few steps earlier.

Orchestration: one job, four tasks

The four notebooks become four tasks in a single Databricks Job. Bronze runs first; the quarantine notebook and the SmartBill notebook both depend on it and can run in parallel; Gold depends on SmartBill finishing. processing_date and run_id are set once at the job level and passed into every task’s widgets, so a daily schedule needs no manual input, and each task writes its own table with replaceWhere on the processing date, so re-running or backfilling a single day never touches any other day’s rows.

Databricks Job graph with four tasks. Bronze_layer (01_bronze_ingest) branches to Silver_Quarentine (03_silver_quarentine) and Silver_smartbill (02_silver_smartbill), and Silver_smartbill feeds into Gold_Daily_Usage (04_gold_daily_usage). All four tasks run on serverless compute. — Bronze feeds both Silver notebooks, but only SmartBill feeds Gold. Quarantine is a dead end by design: its job is to hold bad readings, not pass them forward.

The dashboard

With Gold in place, a Databricks SQL Dashboard sits directly on gold_daily_usage: average daily usage per meter, the peak and off-peak share of consumption, average cost per kilowatt-hour, and a running chart of cost over the month.

Green Grid Dashboard with four KPI tiles: Avg Daily Usage Per Meter 24.46, Avg Peak% 13.25%, Avg Off-Peak% 86.75%, and Average Cost per kWh 0.18. A line chart, Cost per kWh Over Time, fluctuates between roughly 22.5 and 25.8 kWh across December 2025. A bar chart, Total Billing Summary, shows Peak at $3.3K, Off-Peak at $10.6K and Total at $13.9K. A donut chart, Meter Distribution by Cost Tier, splits 43 high-cost meters above $0.20 per kWh against 59 standard-cost meters below it. — Peak usage is 13% of consumption but a much larger share of the bill, which is the whole point of a Time-of-Use rate: it prices the expensive hours differently, and this is where that shows up.

Meter-wise Usage and Billing Summary table sorted by peak kWh descending. The top rows are M-113 at 175.19 peak kWh with a $96 total bill, M-116 at 170.48 peak kWh with a $214 total bill, M-122 at 169.73 peak kWh with a $95 total bill, M-197 at 164.00 peak kWh with a $234 total bill, and M-175 at 158.40 peak kWh with a $90 total bill. — High peak usage doesn't always mean a high bill on its own, M-113 and M-197 have similar peak kWh but a $138 gap in their total bill, which comes down to how much off-peak usage sits alongside it.

High-Cost Meter Patterns key findings panel. Top 5 highest billing meters are M-168 at $256.09, M-174 at $253.60, M-144 at $253.48, M-197 at $234.38 and M-108 at $233.11. Peak usage patterns note the highest peak consumers as M-113, M-116 and M-122, the lowest as M-154 at 20.59 kWh, and an average peak usage of roughly 100 kWh per meter. Off-peak usage patterns note a peak-to-off-peak ratio of 6 to 8 times for most meters. Cost efficiency analysis flags meters with peak usage above 150 kWh as candidates for load shifting to off-peak hours. — The pattern the dashboard surfaces: most meters run 6 to 8 times more off-peak usage than peak, so the meters worth targeting for load shifting are the ones breaking that ratio, not just the ones with the highest bill.

What this proves

A medallion pipeline can enforce data quality and privacy before a single billing number is calculated, rather than cleaning up after the fact. Quarantining bad readings ahead of the billing join keeps a sensor fault from ever reaching an invoice, and hashing the customer identifier in the same step that builds the join keeps referential integrity intact without ever writing a plain-text email to a table a report can query. Both protections had to live in the pipeline’s structure, not in a downstream check, because by the time a downstream check runs, the bad bill has already been calculated.