What is the difference between a system of record and a single source of truth?

A system of record is the specific application where a type of data originates, such as a property management system for rent roll. A single source of truth is a governed model that reconciles data from multiple systems of record into one trustworthy view, without replacing those underlying systems.

Why does entity resolution matter for a CRE database?

The same tenant is often named slightly differently across systems, for example "Acme Corp" versus "Acme Corporation LLC." Without entity resolution mapping these to one canonical ID, portfolio-wide queries silently undercount that tenant's leases rather than throwing a visible error.

What is effective dating and why does it matter for lease data?

Effective dating means recording when a fact was true, with a start and end date, rather than overwriting the old value when something changes. It lets a database answer questions like "what was the rent on this suite last quarter," which a system that only stores current values cannot do.

Can a spreadsheet function as a single source of truth?

A spreadsheet can work at small scale but fails predictably as a portfolio grows, typically due to overwritten history, inconsistent tenant naming, and version conflicts from concurrent editing. These are structural limitations of the tool, not a matter of better spreadsheet discipline.

Does a single source of truth replace the property management or accounting system?

No. It reconciles data produced by those systems into one governed view. Operational staff continue working in the property management and accounting systems; the single source of truth sits on top of them rather than replacing their day-to-day function.

What is data lineage and why does it matter in commercial real estate?

Lineage is the ability to trace a reported number back through its transformations to the original source system, record, and effective date. It matters because investors, lenders, and auditors often need to verify exactly where a figure like portfolio NOI came from, not just see the final number.

How does metric governance fit into a CRE database?

Metrics like NOI and occupancy are calculations, not raw data fields, and different teams sometimes calculate them differently without realizing it. Metric governance means documenting one owned definition for each metric and tying it to specific source fields, so the same question produces the same answer regardless of who asks it.

Where should a firm start when building a single source of truth?

Start by scoring current readiness across identity resolution, transaction capture, effective dating, metric governance, and lineage. The layer with the lowest score is usually where the next data project should focus, rather than starting with a new reporting or visualization tool. Bayaan connects to a customer's governed business data on Microsoft Azure and returns cited, source-traceable answers rather than requiring a rebuilt operational stack. If your team is evaluating what a single source of truth would take to build against your own portfolio, [ARC can walk through your current data model](https://www.askbayaan.com/contact/) and identify where the gaps are. --- ##

Commercial Real Estate Database: How to Build a Single Source of Truth

In this article

Most CRE teams already have a database. It just happens to be spread across a property management system, an accounting platform, three regional spreadsheets, and whatever the last analyst built in Access. Each one answers a slightly different question about the same properties, and none of them agrees perfectly with the others once you look closely at a tenant with two suites and a lease amendment from last quarter.

Building a real single source of truth does not mean replacing all of that with one giant database. It means building a governed layer that resolves who is who, what was true on which date, what each metric means, and where every number ultimately came from. This article walks through what that layer actually requires, using a five-layer blueprint you can apply to your own portfolio.

Key takeaways

A single source of truth is not the same as a system of record. A system of record is where a specific data object originates; a single source of truth is the governed model that reconciles multiple systems of record into one trustworthy view.
Entity resolution, matching a tenant's aliases, DBAs, and affiliated entities to one canonical record, is one of the hardest and most underestimated parts of a CRE database.
Effective dating matters because CRE facts change on specific dates. Overwriting a rent value instead of dating it destroys the ability to answer "what was true as of last quarter."
Metric definitions (NOI, occupancy, same-store) have to live inside the database as governed logic, not as a tribal understanding held by one analyst.
A flat master spreadsheet fails predictably at scale: it cannot handle amendments, joint-venture splits, or concurrent editors without silently losing history.
Lineage, the ability to trace a number back to its source system, transformation, and effective date, is what makes a database defensible in front of investors, lenders, or auditors.

System of record vs. single source of truth

A system of record is the software application where a specific type of data originates and is considered authoritative for that object: the property management system for rent roll, the accounting platform for GL entries, the loan servicing tool for debt data. Most CRE portfolios have five to ten systems of record, each correct for its own slice of data.

A single source of truth is different. It is not a system that replaces those applications. It is a governed data model that pulls from every system of record, resolves conflicts and identity mismatches between them, and presents one reconciled view for reporting and analysis. The property management system remains authoritative for rent roll. The single source of truth is what lets someone ask "what is our portfolio-wide NOI" and get one number that correctly draws from GL, rent roll, and budget data without a human reconciling it by hand each time.

Confusing the two is a common design mistake. Teams that try to build "one database that replaces everything" usually end up duplicating data entry, breaking the systems that operational staff already rely on, and creating a second source of disagreement instead of resolving the first one.

The property, tenant, and lease hierarchy

A CRE database has to model a specific hierarchy correctly before anything else works: properties contain units or suites, units are leased to tenants under one or more leases, and leases can be amended, renewed, or assigned over time. Getting this hierarchy wrong is the single most common cause of double-counted or missing square footage in portfolio rollups.

Complexity shows up quickly at the edges. A tenant can lease multiple suites across multiple properties under separate leases. A single lease can cover multiple suites within one property. An amendment can add space, remove space, or change term without creating a new lease record. A well-modeled hierarchy treats the lease as the parent object, the suite assignment as a child relationship that can change over time, and the amendment as a dated event rather than an edit that erases what came before.

Commercial real estate data source of truth workflow

IDs and entity resolution

Every system in a CRE stack tends to name the same tenant slightly differently. The property management system might list "Acme Corp," the accounting system might list "Acme Corporation LLC," and a lease document might reference a guarantor entity with a different name entirely. Without a resolution step, a portfolio-wide query for "Acme" quietly misses two of its three leases.

Entity resolution is the process of assigning one canonical ID to each real-world entity, property, tenant, and lease, and mapping every system's local identifier to that canonical ID. This has to account for legal-entity structures (a tenant operating under multiple DBAs), corporate actions (a tenant acquired by another company mid-lease), and simple data entry inconsistency (abbreviations, punctuation, trailing suite numbers). Getting this wrong does not throw an error. It silently produces an undercount, which is far more dangerous because nothing flags it as broken.

Effective dating and slowly changing facts

Most CRE facts are not permanent. Rent changes on an escalation date. Occupancy status changes when a tenant moves out. A property's ownership structure changes at refinancing. A database that simply overwrites the old value with the new one loses the ability to answer "what was the rent on this suite as of last March," which is exactly the kind of question that comes up during a variance review or an audit.

The fix is effective dating: every fact that can change gets a start date and, once superseded, an end date, so the record set represents a full history rather than a single current snapshot. This is sometimes called slowly changing dimension modeling. A lease amendment does not overwrite the prior lease terms; it closes out the prior effective-dated record and opens a new one. This lets a query reconstruct the exact state of the portfolio as of any date, not just today.

Metric definitions as part of the database, not an afterthought

NOI, occupancy, same-store growth, and similar metrics are calculations, not raw fields, and different teams often calculate them slightly differently without realizing it. One analyst's NOI might exclude a one-time capital item that another analyst's NOI includes. One team's occupancy might be leased occupancy while another's is physical occupancy.

A single source of truth has to encode these definitions as governed logic inside the database layer rather than leaving them to individual spreadsheet formulas. That means a documented, versioned definition for each metric, tied to the specific GL accounts, lease fields, or occupancy fields it draws from, so that two people asking the same question get the same answer regardless of which tool they used to ask it.

Reconciliation and lineage

Reconciliation is the ongoing process of checking that numbers from different systems of record agree where they should, and flagging where they do not. A rent roll total that does not match the corresponding GL rental income line for the same period is a reconciliation break worth investigating before it reaches a report.

Lineage is the ability to trace any number in a report back through its transformations to the original source system, record, and effective date. This is what makes a portfolio-wide NOI figure defensible when a lender or auditor asks "where did this number come from." Without lineage, a discrepancy becomes a multi-day investigation across several systems. With it, the answer is a traceable path.

Access control and governance

A single source of truth touches sensitive data: rent terms, tenant financials, debt covenants, and often fund-level ownership splits that not every employee should see. Role-based access has to be designed into the database layer itself, not bolted on afterward, so that a leasing associate and a fund-level CFO see different slices of the same underlying model without the database having to be duplicated or manually filtered per user.

Governance also means someone owns each metric definition and each entity-resolution rule, with a change process for updating them. A database without a named owner for its core definitions tends to drift as different teams quietly build their own interpretations on top of it.

Why a flat master spreadsheet fails at scale

The most common starting point for a CRE single source of truth is a master spreadsheet, and it works until it doesn't. Three failure modes show up reliably:

No effective dating. A cell gets overwritten when a lease is amended, and the prior value is gone unless someone happened to save a dated copy of the file.
No entity resolution. Tenant names get typed slightly differently across tabs and by different people, and roll-up formulas silently exclude rows that do not match exactly.
No concurrency control. Two people editing the same workbook at once, or working from stale local copies, produce conflicting versions with no reliable way to tell which one is current.

These are not edge cases. They are the predictable result of using a tool designed for calculation, not for modeling identity, history, and access control at once.

The Five-Layer CRE Database Blueprint — Identity, Transactions, Effective Dates, Metrics, Lineage

A CRE single source of truth can be organized into five layers, each solving a distinct problem. Skipping a layer does not make the database simpler; it just means that layer's problem resurfaces later as a data-quality issue.

Layer	What it solves	Example
1. Identity	Resolves every system's local ID to one canonical entity	"Acme Corp," "Acme Corporation LLC," and a guarantor entity all map to one tenant ID
2. Transactions	Captures the raw events and records from each system of record	GL entries, rent roll snapshots, CapEx line items
3. Effective dates	Tracks when each fact was true, not just what is true now	A rent amount valid from the amendment date until the next escalation
4. Metrics	Encodes governed calculation logic for NOI, occupancy, and similar figures	A single documented NOI formula tied to specific GL accounts
5. Lineage	Traces any reported number back to its source, transformation, and date	A portfolio NOI figure traceable to the GL entries and metric definition that produced it

Worked example: one tenant through all five layers

Acme Corp signs a lease for Suite 204, later expands into Suite 205 under an amendment, and renews eighteen months after that.

Identity: "Acme Corp" in the property management system and "Acme Corporation LLC" in accounting both resolve to one canonical tenant ID.
Transactions: The original lease, the expansion amendment, and the renewal each generate their own transaction records rather than overwriting one row.
Effective dates: The Suite 204-only rent applies from the original signing date to the amendment date; the combined Suite 204/205 rent applies from the amendment date to the renewal; the renewed terms apply going forward.
Metrics: NOI calculations for the relevant period correctly reflect which rent figure was in effect for which months, rather than applying the current rent retroactively.
Lineage: A report showing this tenant's contribution to portfolio NOI can be traced back to the specific lease, amendment, and GL entries that produced it.

Readiness scorecard

Score your current environment from 0 (not present) to 2 (fully implemented) on each layer to identify where to focus first:

Identity resolution — Do tenant, property, and lease records map cleanly to one canonical ID across every system?
Transaction capture — Are amendments and events stored as new dated records rather than overwrites?
Effective dating — Can you reconstruct what was true on a past date, not just today?
Metric governance — Is there one documented, owned definition for NOI, occupancy, and other core metrics?
Lineage — Can any reported number be traced back to its source system and date within a few minutes?

A low score on any layer points directly to where the next data project should start, rather than jumping straight to a new reporting tool.

Turn portfolio questions into governed answers

See how Bayaan helps CRE teams connect governed business data, investigate portfolio questions, and generate trusted outputs.

Talk to Bayaan

Where this falls short

A single source of truth solves data modeling problems. It does not solve every problem a CRE data project runs into.

Building it is a real project, not a purchased feature. Entity resolution rules, effective-dating logic, and metric definitions have to be designed around your specific portfolio's history, including legacy inconsistencies that predate the project. There is no default configuration that fits every firm's tenant-naming conventions or fund structure without customization.

Fund and joint-venture structures add a layer of identity complexity beyond single-entity ownership. The same property can report differently depending on which ownership entity's books are in view, and the identity and access-control layers both need to account for that before the database can be trusted at the fund level.

A single source of truth does not replace operational systems. Leasing teams still work in the property management system and accounting teams still work in the ERP. The database layer reconciles what those systems produce; it is not meant to become the system where day-to-day transactions are entered.