Table: `ndb.variablecontexts`#

Description#

Variable Contexts lookup table. Table is referenced by the Variables table.

TODO: Expand this description with: - What data does this table store? - What is the business/research purpose? - How is this data collected or generated? - Are there any important caveats or data quality issues?

Table Structure#

Schema: ndb | Table Comment: Variable Contexts lookup table. Table is referenced by the Variables table.

Statistics#

Metric	Value
Row Count	72
Total Size	56 kB
Table Size	8192 bytes
Indexes Size	16 kB

Relationships#

Primary Key: variablecontextid

No foreign key relationships.

Referenced By:

TODO: Document which tables reference this table (will be auto-detected in validation).

Data Dictionary#

Column	Type	Nullable	Default	Constraints	Description
`variablecontextid`	integer	✗	`nextval('ndb.seq_variableco...`	PRIMARY KEY	An arbitrary Variable Context identification number.
`variablecontext`	character varying(64)	✗	`-`	-	Depositional context. Examples are:
*anachronic – specimen older than the primary deposit, e.g. a Paleozoic spore in a Holocene deposit; may be redeposited from the catchment or may be derived from long distance, e.g. Tertiary pollen grains in Quaternary sediments with no local Tertiary source. A Pleistocene specimen in a Holocene archaeological deposit, possibly resulting from aboriginal fossil collecting, would also be anachronic.
*intrusive – specimen generally younger younger than the primary deposit, e.g. a domestic pig in an otherwise Pleistocene deposit in North America.
*redeposited – specimen older than the primary deposit and assumed to have been redeposited from a local source by natural causes.
*articulated – articulated skeleton
*clump – clump, esp. of pollen grains

`recdatecreated`	timestamp without time zone	✗	`timezone('UTC'::text, now())`	-
`recdatemodified`	timestamp without time zone	✗	`-`	-

TODO: Review column descriptions and add comments where missing.

Usage Examples#

Example 1: Basic Selection#

-- Get recent records from variablecontexts
SELECT *
FROM variablecontexts
ORDER BY variablecontextid DESC
LIMIT 10;

Purpose: Retrieve the 10 most recent records from variablecontexts

Example 2: Count Records#

-- Count total records
SELECT COUNT(*) as total_records
FROM variablecontexts;

Purpose: Get the total number of records in variablecontexts

Example 3: Filter by Date Range#

-- Get records within a date range
SELECT *
FROM variablecontexts
WHERE recdatecreated >= '2024-01-01'
  AND recdatecreated < '2025-01-01'
ORDER BY recdatecreated DESC;

Purpose: Retrieve records from variablecontexts within a specific date range

Example 4: Aggregate Data#

-- Aggregate records by variablecontext
SELECT 
    variablecontext,
    COUNT(*) as count
FROM variablecontexts
GROUP BY variablecontext
ORDER BY count DESC
LIMIT 10;

Purpose: Count records grouped by variablecontext

TODO: Add more specific examples relevant to common research questions or operational tasks.

Data Quality Notes#

Automated Data Quality Tests#

This table is subject to the following automated quality checks:

❌ bix_003: variable_elements_in_use

Severity: WARNING
Status: FAILED
Description: Over time a number of variable contexts, units and elements have been created but not neccessarily used. In some cases this may have resulted from improperly entered data in the Tilia spreadsheet.
Suggested Remediation: - Where possible, remove unused units/elements/contexts - Ensure any near-duplicates of existing units/elements/contexts are using best-practice or accepted notations.

See the Data Quality Report for details.

Maintenance#

Data Owner: TODO: Assign owner
Update Frequency: TODO: Document frequency
Last Major Schema Change: TODO: Document when schema last changed

Table: ndb.variablecontexts#