Real Estate EDW data model
Data Vault 2.0 raw vault for real estate: hubs (property/owner/tenant/lease/unit), links (property-owner, property-unit, unit-lease, lease-tenant, rent payments), and satellites (property, owner, tenant, unit details + lease-terms effectivity + rent ledger). Hash keys and load metadata are generated by the DDL layer.
Real estate enterprise data warehouse modeled as a Data Vault 2.0 raw vault: property, owner, tenant, lease, and unit hubs; ownership, unit, and leasing links plus a non-historized rent-payment link; and descriptive, effectivity, and ledger satellites — built for integrating property-management, CRM, and accounting systems with full historization and the tenant/owner-data lineage (GDPR) a property operator must retain across its portfolio. It is a Data Vault 2.0 raw vault: hubs for business keys, links for relationships and satellites for descriptive history, with hash keys and hash diffs generated in the DDL.
- 17
- tables
- 111
- columns
- 16
- relationships
- 12
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Hubs hold only business keys
Separating immutable identifiers (property_id, lease_id, unit_id) into hubs lets new accounting and CRM source systems attach without reworking existing structures — the core agility of Data Vault for a growing portfolio acquired piecemeal.
Non-historized rent-payment link
Rent payments are immutable ledger events, so the rent-payment link is insert-only and carries its measures directly — no satellite needed.
Effectivity satellite on the lease
Lease terms and status (active, expired, renewed, terminated) change over time; an effectivity satellite records the valid period of each version for as-of occupancy and revenue reporting.
Rent ledger split into its own satellite
Balances and aging update far more frequently than lease terms; a separate high-rate-of-change satellite keeps history compact and collections reporting efficient.
GDPR-driven tenant and owner PII handling
National-id full-masking, email hashing, and per-attribute classification satisfy GDPR on tenant and owner records, while load_date + record_source give the lineage to honour access requests.
Tables in the Real Estate EDW model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Hubs (5)
hub_property
Grain: one row per unique hub_property business key
| Column | Type | Keys |
|---|---|---|
| property_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_owner
Grain: one row per unique hub_owner business key
| Column | Type | Keys |
|---|---|---|
| owner_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_tenant
Grain: one row per unique hub_tenant business key
| Column | Type | Keys |
|---|---|---|
| tenant_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_lease
Grain: one row per unique hub_lease business key
| Column | Type | Keys |
|---|---|---|
| lease_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_unit
Grain: one row per unique hub_unit business key
| Column | Type | Keys |
|---|---|---|
| unit_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
Links (5)
lnk_property_owner
Grain: one row per lnk_property_owner relationship
Which owner holds title to which property.
| Column | Type | Keys |
|---|---|---|
| property_hk | binary | |
| owner_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_property_unit
Grain: one row per lnk_property_unit relationship
Which units belong to which property.
| Column | Type | Keys |
|---|---|---|
| property_hk | binary | |
| unit_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_unit_lease
Grain: one row per lnk_unit_lease relationship
Which lease covers which unit.
| Column | Type | Keys |
|---|---|---|
| unit_hk | binary | |
| lease_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_lease_tenant
Grain: one row per lnk_lease_tenant relationship
Which tenants are party to which lease.
| Column | Type | Keys |
|---|---|---|
| lease_hk | binary | |
| tenant_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_rent_payment
Grain: one row per posted rent payment (non-historized link)
Transactional (non-historized) link — insert-only rent ledger events.
| Column | Type | Keys |
|---|---|---|
| lease_hk | binary | |
| tenant_hk | binary | |
| payment_event_id | varchar | BK |
| payment_ts | timestamp | |
| payment_type | varchar | |
| payment_method | varchar | |
| amount | decimal | |
| currency | varchar | |
| load_date | timestamp | |
| record_source | varchar |
Satellites (6)
sat_property_details
Grain: one row per property_hk per change
Property descriptive history.
| Column | Type | Keys |
|---|---|---|
| property_hk | binary | PK |
| load_date | timestamp | PK |
| property_name | varchar | |
| property_type | varchar | |
| address | varchar | |
| city | varchar | |
| postal_code | varchar | |
| year_built | smallint | |
| total_units | integer | |
| assessed_value | decimal | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_owner_details
Grain: one row per owner_hk per change
Owner descriptive history (PII).
| Column | Type | Keys |
|---|---|---|
| owner_hk | binary | PK |
| load_date | timestamp | PK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| national_id | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| owner_type | varchar | |
| mailing_address | varchar | PII |
| load_end_date | timestamp | |
| record_source | varchar |
sat_tenant_details
Grain: one row per tenant_hk per change
Tenant descriptive history (PII).
| Column | Type | Keys |
|---|---|---|
| tenant_hk | binary | PK |
| load_date | timestamp | PK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| date_of_birth | date | PII |
| national_id | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| credit_score_band | varchar | |
| employment_status | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_lease_terms
Grain: one row per lease term per effectivity change
Effectivity satellite tracking lease terms and lifecycle over time.
| Column | Type | Keys |
|---|---|---|
| lease_hk | binary | PK |
| load_date | timestamp | PK |
| lease_status | varchar | |
| effective_from | date | |
| effective_to | date | |
| is_active | boolean | |
| monthly_rent | decimal | |
| security_deposit | decimal | |
| term_months | smallint | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_unit_details
Grain: one row per unit_hk per change
Unit descriptive history.
| Column | Type | Keys |
|---|---|---|
| unit_hk | binary | PK |
| load_date | timestamp | PK |
| unit_number | varchar | |
| floor | smallint | |
| bedrooms | smallint | |
| bathrooms | decimal | |
| square_feet | integer | |
| occupancy_status | varchar | |
| market_rent | decimal | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_rent_ledger
Grain: one row per lease per ledger update (high-rate-of-change satellite)
Rent-ledger satellite split out for its high rate of change.
| Column | Type | Keys |
|---|---|---|
| lease_hk | binary | PK |
| load_date | timestamp | PK |
| balance_due | decimal | |
| amount_paid | decimal | |
| late_fees_accrued | decimal | |
| days_past_due | integer | |
| ledger_status | varchar | |
| statement_date | date | |
| load_end_date | timestamp | |
| record_source | varchar |
Lookup tables (1)
ref_property_type
Grain: one row per property type
| Column | Type | Keys |
|---|---|---|
| property_type_code | varchar | BK |
| property_type_name | varchar | |
| asset_class | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- lnk_property_owner.property_hk → hub_property (many-to-one)
- lnk_property_owner.owner_hk → hub_owner (many-to-one)
- lnk_property_unit.property_hk → hub_property (many-to-one)
- lnk_property_unit.unit_hk → hub_unit (many-to-one)
- lnk_unit_lease.unit_hk → hub_unit (many-to-one)
- lnk_unit_lease.lease_hk → hub_lease (many-to-one)
- lnk_lease_tenant.lease_hk → hub_lease (many-to-one)
- lnk_lease_tenant.tenant_hk → hub_tenant (many-to-one)
- lnk_rent_payment.lease_hk → hub_lease (many-to-one)
- lnk_rent_payment.tenant_hk → hub_tenant (many-to-one)
- sat_property_details.property_hk → hub_property (many-to-one)
- sat_owner_details.owner_hk → hub_owner (many-to-one)
- sat_tenant_details.tenant_hk → hub_tenant (many-to-one)
- sat_lease_terms.lease_hk → hub_lease (many-to-one)
- sat_unit_details.unit_hk → hub_unit (many-to-one)
- sat_rent_ledger.lease_hk → hub_lease (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.