A synthetic property-management analytics platform built on the Databricks Lakehouse: a seeded data generator feeds a governed bronze → silver → gold medallion pipeline and a star schema that answers three executive questions in one place —
Are we full? (occupancy) · Are we getting paid? (collections & arrears) · Is anything about to break? (maintenance & sensor telemetry)
Everything here is reproducible from a fixed seed and rebuilds with a single databricks bundle run.
The one-page executive dashboard, built in Power BI on the Databricks gold layer. It answers the three questions above at a glance and surfaces the data-quality scorecard as a first-class panel. Reproduce it from the measures in powerbi/measures_web.dax by following powerbi/dashboard_build_guide.md.
A property operator's data lives in separate systems that don't talk to each other — a leasing CRM, a rent ledger, a work-order system, and building sensors. Leadership can't answer basic questions across them. This project centralises those sources into one governed model and surfaces a single executive view, with data quality as a first-class, auditable deliverable — not an afterthought.
The layer-by-layer detail behind that picture:
| Layer | What it holds | Notes |
|---|---|---|
| Source | 12 Parquet extracts (~1.4M rows) | Five simulated systems: asset master, leasing CRM, accounting, maintenance, building IoT |
| Bronze | 12 raw tables, dirt intact | +_ingested_at, +_source_file; nothing cleaned |
| Silver | 12 conformed + 3 quarantine | Typed, deduped, dates & currency parsed, statuses standardised, bad rows quarantined (never dropped) |
| Gold | 8 dimensions + 5 facts | Kimball star: IDENTITY surrogate keys, PK/FK constraints (renders an ER diagram), SCD2 dim_tenant, generated dim_date, derived fct_occupancy_daily |
| Quality | gold.dq_check_results |
28 checks per run, append log, dashboard-ready |
The bronze layer is deliberately dirty (the client specifically asked about validation gates and
data accuracy). Injection rates live in generator/config.yaml; the pipeline's
response is verifiable in the silver quarantine tables and dq_check_results.
| Injected on purpose | Where / rate | What the pipeline did |
|---|---|---|
| Duplicate rows | ~2% (ledger, work orders, telemetry) | Deduplicated in silver via QUALIFY row_number() — ~6,700 removed |
| Nulls in required fields | ~1% | Quarantined with a reason — never silently dropped |
Mixed date formats (2026-01-01, 03/01/2025, epoch 1767225600) |
every date column | Parsed to DATE/TIMESTAMP (parse_date / parse_ts) |
Currency as text ($1,250.00, (500.00)) |
every money column | Parsed to DECIMAL(12,2) (parse_money) |
| Orphaned foreign keys | ~0.5% of transactions | Quarantined; gold facts never carry a dangling key |
| Whitespace + inconsistent casing | names, status codes | Trimmed & standardised to a controlled vocabulary |
Result: ~1,950 rows quarantined (each tagged with why, and queryable), ~6,700 duplicates removed, 0 referential-integrity failures in gold (independently verified across all 19 fact→dimension foreign keys), and a 27 PASS / 1 WARN / 0 FAIL data-quality scorecard. The single warning is the telemetry anomaly flag — out-of-range sensor readings surfaced as early maintenance warnings, which is signal, not corruption.
| KPI | Definition | Current |
|---|---|---|
| Physical occupancy | occupied unit-days ÷ total unit-days | ~93% (stable) |
| Economic occupancy | Σ contracted rent ÷ Σ market rent (occupied + vacant) | ~94% |
| Collection rate | payments ÷ charges | 94% |
| Lead → lease conversion | leased leads ÷ total leads | 17% |
| Work-order backlog | open work orders ÷ all work orders | 16% |
| Telemetry anomalies | readings outside a device's physical range | 4,803 (flagged) |
| Data-quality pass rate | passing checks ÷ total checks (latest run) | 96.4% |
# 1 — Generate the synthetic source data (local, reproducible, no network)
python3 -m venv .venv && ./.venv/bin/pip install -r requirements.txt
./.venv/bin/python -m generator.generate # add --profile small for a fast loop
# 2 — Authenticate the Databricks CLI (once)
databricks auth login --host https://<your-workspace> # creates a profile
# 3 — Deploy the pipeline (uploads notebooks, creates the Job)
databricks bundle deploy -t dev -p <profile>
# 4 — One-time: land the raw files in the Unity Catalog volume
databricks fs cp -r --overwrite data/raw dbfs:/Volumes/propsight/bronze/landing -p <profile>
# 5 — Rebuild the entire warehouse with one command
databricks bundle run propsight_pipeline -t dev -p <profile>Output: propsight.bronze (12 tables) → propsight.silver (12 + 3 quarantine) → propsight.gold
(8 dims + 5 facts + dq_check_results), all reconciling to the generator config.
- Databricks Free Edition — serverless compute only, Unity Catalog, no external cloud services.
- Reproducible — fixed seed, committed; the whole 1.4M-row warehouse regenerates byte-for-byte.
- Databricks-native best practices — managed Delta tables, Liquid Clustering (not partitioning),
DECIMALfor money, IDENTITY surrogate keys withRELYPK constraints, predictive optimization, SCD2 via window functions, and an Asset Bundle so the pipeline is version-controlled infra-as-code. - Verified, not asserted — the gold star was checked by an independent multi-agent review across reconciliation, referential integrity, SCD2 point-in-time correctness, and derived-measure correctness.
generator/ seeded, config-driven data generator (Python)
config.yaml volumes, seed, distributions, injected-dirt rates — no magic numbers in code
generate.py CLI entry point
notebooks/ the medallion pipeline (run in order)
00_setup_catalog catalog + schemas + predictive optimization
01_landing_volume UC volume for raw files
02_bronze_ingest raw Parquet -> bronze tables (dirt preserved)
03_silver_clean type, dedup, conform, quarantine
04_gold_dimensions 8 dims, IDENTITY keys, SCD2 dim_tenant
05_gold_facts 5 facts, FK constraints, SCD2 point-in-time joins
06_quality_checks the DQ framework -> gold.dq_check_results
resources/ Asset Bundle job definition
databricks.yml Asset Bundle root config
docs/ architecture.md, data_dictionary.md, img/ (README diagrams)
PLAN.md · CLAUDE.md build plan and engineering guardrails
docs/architecture.md— the design in depth (medallion, star schema, SCD2, DQ).docs/data_dictionary.md— every gold table, grain, and key column.
All data is synthetic. No real company, tenant, or property is represented.