Agriculture EDW data model
Data Vault 2.0 raw vault for agriculture: hubs (farm/field/crop/equipment/operator), links (farm-field, field-crop, equipment-field, and a transactional field-operation link), and satellites for farm, field, crop, and equipment history plus a field-crop effectivity satellite and a harvest-yield transactional satellite. Hash keys and load metadata are generated by the DDL layer.
Precision-agriculture enterprise data warehouse as a Data Vault 2.0 raw vault: farm, field, crop, equipment, and operator hubs; the relationships among them including a non-historized field-operation event link; and descriptive plus effectivity and transactional satellites — designed to consolidate data from many farm-management, machine-telemetry, agronomy, and weather systems with full historization and traceability. 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.
- 16
- tables
- 102
- columns
- 15
- relationships
- 1
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: BigQuery). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Field as a first-class hub
A field is referenced by farms, crops, equipment, operations, and yields; modeling it as a hub keeps those relationships as independent links so new telemetry and agronomy sources attach without rework.
Non-historized field-operation link
Planting, spraying, and harvesting are immutable events, so the field-operation link is insert-only and carries its measures (area, input product/quantity) directly — no descriptive satellite needed.
Harvest yield as a transactional satellite on field-crop
Yields are measured per field-crop assignment and arrive over the season; a transactional satellite on the field-crop link captures each measurement with its load metadata for trend and benchmark analysis.
Field-crop effectivity satellite
A field's crop and growth stage change across the season and from year to year; an effectivity satellite records the valid period of each field-crop assignment for as-of reporting and rotation traceability.
Tables in the Agriculture 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_farm
Grain: one row per unique hub_farm business key
Farm operation by its farm identifier.
| Column | Type | Keys |
|---|---|---|
| farm_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_field
Grain: one row per unique hub_field business key
Field / management zone by its field identifier.
| Column | Type | Keys |
|---|---|---|
| field_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_crop
Grain: one row per unique hub_crop business key
Crop / variety by its crop code.
| Column | Type | Keys |
|---|---|---|
| crop_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_equipment
Grain: one row per unique hub_equipment business key
Machine / implement by its serial number.
| Column | Type | Keys |
|---|---|---|
| equipment_serial | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_operator
Grain: one row per unique hub_operator business key
Field operator by employee identifier.
| Column | Type | Keys |
|---|---|---|
| operator_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
Links (4)
lnk_farm_field
Grain: one row per lnk_farm_field relationship
Fields belonging to a farm.
| Column | Type | Keys |
|---|---|---|
| farm_hk | binary | |
| field_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_field_crop
Grain: one row per lnk_field_crop relationship
Crop planted in a field for a season.
| Column | Type | Keys |
|---|---|---|
| field_hk | binary | |
| crop_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_equipment_field
Grain: one row per lnk_equipment_field relationship
Equipment assigned to a field.
| Column | Type | Keys |
|---|---|---|
| equipment_hk | binary | |
| field_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_field_operation
Grain: one row per field operation event (non-historized link)
Transactional (non-historized) link — insert-only field-operation events.
| Column | Type | Keys |
|---|---|---|
| field_hk | binary | |
| equipment_hk | binary | |
| operator_hk | binary | |
| operation_id | varchar | BK |
| operation_ts | timestamp | |
| operation_type | varchar | |
| area_covered_acres | decimal | |
| input_product | varchar | |
| input_quantity | decimal | |
| input_unit | varchar | |
| load_date | timestamp | |
| record_source | varchar |
Satellites (6)
sat_farm_details
Grain: one row per farm_hk per change
Farm descriptive history.
| Column | Type | Keys |
|---|---|---|
| farm_hk | binary | PK |
| load_date | timestamp | PK |
| farm_name | varchar | |
| owner_name | varchar | PII |
| county | varchar | |
| state | varchar | |
| total_acres | decimal | |
| operation_type | varchar | |
| certification | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_field_details
Grain: one row per field_hk per change
Field descriptive history.
| Column | Type | Keys |
|---|---|---|
| field_hk | binary | PK |
| load_date | timestamp | PK |
| field_name | varchar | |
| acreage | decimal | |
| soil_type | varchar | |
| irrigation_type | varchar | |
| latitude | decimal | |
| longitude | decimal | |
| slope_pct | decimal | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_crop_details
Grain: one row per crop_hk per change
Crop / variety descriptive history.
| Column | Type | Keys |
|---|---|---|
| crop_hk | binary | PK |
| load_date | timestamp | PK |
| crop_name | varchar | |
| variety | varchar | |
| crop_family | varchar | |
| maturity_days | integer | |
| seed_supplier | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_equipment_details
Grain: one row per equipment_hk per change
Equipment descriptive history.
| Column | Type | Keys |
|---|---|---|
| equipment_hk | binary | PK |
| load_date | timestamp | PK |
| equipment_name | varchar | |
| equipment_type | varchar | |
| manufacturer | varchar | |
| model_year | integer | |
| horsepower | integer | |
| engine_hours | decimal | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_field_status
Grain: one row per field-crop assignment per effectivity change
Effectivity satellite on the field-crop link.
| Column | Type | Keys |
|---|---|---|
| field_crop_hk | binary | PK |
| load_date | timestamp | PK |
| crop_status | varchar | |
| growth_stage | varchar | |
| effective_from | date | |
| effective_to | date | |
| is_current | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_harvest_yield
Grain: one row per field-crop per harvest measurement (transactional satellite)
Transactional satellite — measured harvest yields on the field-crop link.
| Column | Type | Keys |
|---|---|---|
| field_crop_hk | binary | PK |
| load_date | timestamp | PK |
| harvest_date | date | |
| yield_quantity | decimal | |
| yield_unit | varchar | |
| yield_per_acre | decimal | |
| moisture_pct | decimal | |
| commodity_price | decimal | |
| load_end_date | timestamp | |
| record_source | varchar |
Lookup tables (1)
ref_soil_class
Grain: one row per USDA soil classification
| Column | Type | Keys |
|---|---|---|
| soil_class_code | varchar | BK |
| soil_class_name | varchar | |
| drainage_class | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- lnk_farm_field.farm_hk → hub_farm (many-to-one)
- lnk_farm_field.field_hk → hub_field (many-to-one)
- lnk_field_crop.field_hk → hub_field (many-to-one)
- lnk_field_crop.crop_hk → hub_crop (many-to-one)
- lnk_equipment_field.equipment_hk → hub_equipment (many-to-one)
- lnk_equipment_field.field_hk → hub_field (many-to-one)
- lnk_field_operation.field_hk → hub_field (many-to-one)
- lnk_field_operation.equipment_hk → hub_equipment (many-to-one)
- lnk_field_operation.operator_hk → hub_operator (many-to-one)
- sat_farm_details.farm_hk → hub_farm (many-to-one)
- sat_field_details.field_hk → hub_field (many-to-one)
- sat_crop_details.crop_hk → hub_crop (many-to-one)
- sat_equipment_details.equipment_hk → hub_equipment (many-to-one)
- sat_field_status.field_crop_hk → lnk_field_crop (many-to-one)
- sat_harvest_yield.field_crop_hk → lnk_field_crop (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.