Sophie Johnston Interview Memorisation Pack: Technical Vocabulary & Concepts

Essential data analytics and business intelligence vocabulary terms and definitions taken from the interview memorisation pack.

Primary key

Uniquely identifies a row in a table.

Foreign key

References a key in another table to create a relationship.

NULL

Unknown or missing value; not automatically zero or blank.

Aggregate

A calculation across multiple rows, such as SUM, COUNT or AVG.

Granularity / grain

What one row in a dataset represents.

Cardinality

The uniqueness or relationship pattern between keys or tables.

Fact table

An event or transaction table containing measurable activity.

Dimension table

Descriptive attributes used to filter or slice facts.

ETL

Extract, transform, load. ELT transforms after loading into the target platform.

Data lineage

A trace of where data came from and how it changed.

Reconciliation

Showing that related sources or outputs align, or explaining valid differences.

Control total

A known count or amount used to validate another result.

KPI

A defined measure tied to an objective and a decision.

Data governance

The framework that defines who owns data, how it should be defined and maintained, who can access it, and what controls apply when it changes.

Master data

The core information about important business entities such as customers, products, suppliers or employees that is reused across multiple systems.

UAT (User Acceptance Testing)

Confirms that a new system, report or change actually works for the business process it was designed to support.

Power Query

The data-preparation and transformation layer in Excel and Power BI used for repeatable import, cleaning, and transformation.

PivotTables

Useful for quickly aggregating and slicing structured data without writing a large number of formulas.

CTE (Common Table Expression)

A named temporary result set defined with WITH, used to break an analytical query into readable logical stages instead of nesting everything into one long statement.

INNER JOIN

Returns only rows where the join key matches in both tables.

LEFT JOIN

Keeps every row from the left table and brings in matching values from the right table; if there is no match, the right-side fields are null.

GROUP BY

Collapses rows into groups based on one or more dimensions so aggregate functions such as SUM, COUNT or AVG can be calculated for each group.

Window function

Performs a calculation across a related set of rows while preserving the individual rows, unlike GROUP BY which collapses them.

Filter context

The set of filters currently affecting a DAX calculation, coming from slicers, visual rows and columns, page filters, report filters or DAX itself.

CALCULATE

Evaluates an expression under a modified filter context by adding, removing, or changing filters around a measure in DAX.

Star schema

A schema featuring a central fact table containing events or transactions surrounded by dimension tables containing descriptive attributes.

Calculated column (Power BI)

Evaluated row by row and stored in the model, making it useful for row-level attributes used in filtering, grouping or relationships.

Measure (Power BI)

Calculated dynamically in the current filter context and typically used for KPIs and aggregations responding to filters.

Row-level security (RLS)

Restricts which rows a user can see based on roles or identity.

Live connection (Tableau)

Queries the underlying source when the view is used, rather than storing a snapshot of the data.

Extract (Tableau)

Stores a snapshot of the data in Tableau’s optimised format and is refreshed on a schedule or manually.