The situation
A commercial real estate investment firm kept the data about its properties, leases and tenants in many places: several modules of its property management system (general ledger, commercial management, job cost), a space planning dataset, an insurance compliance source, its leasing deal pipeline, and a set of spreadsheets owned by finance, legal and investments. Leadership had a dashboard design. What they didn't have was a data layer that could feed it.
The project is a reporting base in Airtable that mirrors those sources and feeds a dashboard with Property, Lease and Tenant views, plus the acquisition and disposition pipelines. I lead the data architecture and the build, with the client's data owners making the calls on definitions.
Principle 1: mirror, don't edit
Mirrored data is read-only downstream. If a number is wrong, it gets fixed at the source, not patched in the dashboard. A patched copy is a second truth, and second truths drift.
The exception is deliberate and narrow: some facts have no system of record anywhere. Whether a lease is triple-net or gross isn't in the property management system or the finance workbook. Some possession dates aren't tracked in any system at all. Those fields are maintained inside the reporting base, inline on the interface, with per-person edit permission and everyone else read-only. The tenant legal and compliance layer became the largest of these, and it's designed to retire a standalone tracking spreadsheet outright.
Principle 2: define before you display
Most of the work wasn't building screens. It was walking every screen, field by field, with the people who own the data, and pinning down what each word means. A sample of what that turned up:
A tenant can be a lease, an occupant across amendments, a parent operator, or a retail chain. The obvious key, the tenant ID, turned out to identify the parent operator: keying on it merged six separate locations of one national carrier into a single tenant. Matching on names merged 99 different tenants. The reporting layer keys tenants on the occupant identifier only, with no fallback.
The sync feeding the operational bases renamed fields and spelled out coded values. When a business rule arrives as "status C", the data says "Current". Filtering literally returns zero rows and looks like broken data. Every rule gets translated and checked before it's built.
Which of two name fields held the legal name and which held the DBA was an open question after a working session. Rather than wait on a follow-up, I checked all of the roughly 1,800 leases: 76% of one field carried entity suffixes like LLC or Inc, against 19% of the other, and the two differed on 68% of leases. Getting it backwards would have shown the wrong name on most records.
The industry-standard square footage everyone expected to find in the space planning data turned out to measure interior usable area instead. The standard measurement has no system of record. The screen labels its denominator, so it can be swapped later without archaeology.
Every ruling is recorded with its date and the evidence behind it, so a question closed once stays closed.
The finding: why a third of active leases showed $0 rent
On screen, a large share of active leases showed no base rent. The first explanations were the reasonable ones: rent kept on a prior lease amendment, or charged under a category the rollup didn't recognize. A design was drafted to walk amendment chains back to the rent. Then the client's data team suspected a missing filter somewhere.
They were right, and it wasn't in the reporting layer. Three cheap questions, in order, each ruling out a whole class of cause:
The amendment-chain build was shelved before it started. It would have been a sophisticated fix for a problem that lived one layer upstream, and every population it was sized against was inflated by the missing window.
Two more defects in the same sync
The upsert key included a field people can edit. The sync matched rows on a composite key that included a charge's start date. When a start date was corrected upstream, the next run matched nothing, created a second row, and orphaned the first, which froze with whatever it held, including its "currently in effect" flag. An audit found around 200 orphaned rows across more than 120 leases, and a handful of active leases displaying roughly double their real rent. The tell: inside a set of duplicates, the live row is the one whose "last updated" keeps moving.
// WRONG: the start date is editable in the source system
const key = `${buildingId}_${leaseId}_${startDate}_${category}_${frequency}`;
// RIGHT: the source system's own row identifier
const key = String(row.recurringChargeId);A failed item was never retried. Progress was recorded as start position plus success count, so a single failure shifted the window: one item processed twice, the failed one skipped for good.
Rules I took away
These now live in my scripting guidelines and apply to every sync I write.
Where it stands
What this demonstrates
A dashboard is only as good as the definitions behind it and the pipes in front of it. The screens are the visible part of this project. What makes them trustworthy is quieter: every field traced to a source, every definition agreed and dated, and the discipline to stop building when the numbers don't add up and go find out why.
Before you fix what's on the screen, find out what never arrived.