KajoData / Career & business
10 analytics concepts I want to understand before a job interview
I connect grain, cardinality, cohorts and other analytical concepts to the decisions behind a report, with examples and questions to ask before writing a query.

You can know how to write a SELECT statement and prepare an Excel table, then get stuck on an interview question. Someone asks for churn by cohort, and you do not know what work they are requesting. Looking up a command is easier than discovering the meaning of the task while the interviewer waits.
I chose ten concepts that I want to understand through their use. I do not present them as a universal interview syllabus. I connect the vocabulary to decisions: what I count, how I combine data and why an answer might mislead. For each term, I would practise an example and a limitation.
Grain and cardinality: understand rows and matches first
Grain establishes what one record represents. An order and an order line are different levels of detail. Kimball Group places that decision before selecting measures and dimensions for a fact table.
An example order might contain three products, each with its own row. The row count would then differ from the order count. Before adding an amount, I also need to know whether it belongs to the line or the whole basket. That question can prevent a very impressive mistake.
Time creates a similar issue. A week can cross a month boundary. If I receive weekly totals but need a monthly report, I ask how to allocate them. A weekly total alone does not reveal each day’s sales. I need more detailed data or an explicitly agreed approximation.
Cardinality needs context. For a column, it can refer to the number of distinct values. For a relationship, I want to know how records match across the two sides. Microsoft’s relationship documentation describes patterns including one-to-many and many-to-many. The one side requires unique values.
I would therefore avoid treating cardinality as merely another word for uniqueness. Before joining, I ask whether a customer has many orders and whether the customer table really has one record per customer. My article on SQL JOIN types develops the consequences of those choices.
Recursion and window functions solve different problems
A recursive query can traverse a hierarchy, such as the successive managers above an employee. In PostgreSQL 18, a recursive CTE has an initial part and another part that uses previously obtained results. Termination matters, including the possibility of a cycle in the data.
I want to explain that process in ordinary language. I choose a starting point, find its immediate connection and continue. I also ask whether the structure really is a tree. Two columns containing employee identifiers do not guarantee that the relationships form a valid hierarchy.
A window function calculates across related rows while preserving their separate identities. That is the distinction explained in the PostgreSQL tutorial. For example, I might compare an individual order amount with its customer’s result while keeping the individual orders visible.
For that task, I ask about grouping, ordering and the range of rows involved. A ranking and a running total require different choices. Recognising the term is a start; I also want to explain why the output needs individual rows rather than one summary per group.
Normalization and denormalization concern data structure
Normalization in a relational database organises tables and dependencies to reduce redundant storage and update problems. Microsoft uses the example of a customer address: keeping the information in an appropriate place makes consistent changes easier.
I distinguish this meaning from scaling numerical values. During a discussion about database design, I want to know where an attribute belongs and which key it depends on. A vague statement that the data looks normal would leave that question unanswered.
Alongside normalization, I discuss denormalization, which deliberately introduces some repetition. Adding product descriptions to a prepared analytical table is one possible example. Microsoft’s Power BI modeling guidance explains the distinction and tradeoffs when combining dimension tables.
I would not choose a winner by counting tables. I ask how the solution will be updated, how people will use it and what problem it needs to solve. More joins do not prove that a design is better. One wide table still requires consistency checks.

Additivity: I cannot sum every number
The sixth concept is measure additivity. In a simple example, sales of 200 and 100 sum to 300 if the amounts describe separate transactions under the same definition. Percentages require me to inspect the numerator and denominator first.
Take an additional fictional example. One group has one purchase from ten visits, giving 10%. Another has nine purchases from thirty visits, giving 30%. Together they have ten purchases from forty visits: 25%. Adding the percentages gives 40%, while their simple average is 20%. Neither operation produces the combined conversion rate.
Kimball Group distinguishes additive, semi-additive and non-additive measures. Where possible, it recommends retaining the components of ratios and calculating the ratio after aggregating those components. A balance also requires care when aggregating across time.
In practice, I ask which dimensions permit summing a measure. A numeric column does not answer that question by itself. When someone requests overall conversion, I want the underlying components as well as the percentages already shown in a report.
Cohort analysis needs a shared reference point
Cohort analysis is one of my favourite topics, and I worked with it professionally. I group people using a defined shared characteristic and observe their behaviour over time. The starting point is often registration or first purchase. Google Analytics explains cohorts through inclusion and subsequent return criteria.
Imagine a fictional subscription service. I want to compare people who joined in January with those who joined in February. First I define joining: account creation, payment or the start of access. Then I choose the behaviour of interest, such as continuing to hold active access.
I would compare the groups at the same time since their start. January’s cohort can reach its third month earlier than February’s. An empty cell for the younger group may mean the observation period has not finished. I would not automatically interpret it as every customer having left.
For churn, I also need to agree on the departure event and the population used as the denominator. Are we interested in cancellation, expiry of paid access or inactivity? Those choices affect the analysis before I choose an application or write a query.
Lineage, incremental loading and SCD explain movement and change
Data lineage traces a metric back through transformations to its source. Microsoft’s lineage overview also describes its use for investigating problems and assessing the impact of changes. For a small project, I would begin by drawing that route.
If a report shows surprising sales, I want to know which view supplies them, where the view obtains statuses and where filters were applied. Knowing the source system’s name is useful. It still leaves the intermediate calculation to investigate.
Incremental loading limits subsequent acquisition to appropriately detected new or changed data. Azure Data Factory documentation describes approaches including a last-updated watermark and change tracking.
I would not assume that selecting higher order IDs is sufficient. An older order can change status. I must decide how that change will be detected and how the destination will reflect it. Adding the status to a key does not automatically solve every update problem.
SCD, slowly changing dimensions, is the tenth concept. It concerns changes to dimension attributes. Microsoft distinguishes Type 1, which overwrites a value, from Type 2, which preserves versions. I ask whether a report needs a customer’s current attribute or the attribute that applied to a historical transaction. Change frequency alone does not settle that decision.
An example customer dimension contains descriptive attributes used to group sales. If a customer’s assigned region changes, I need to establish the recipient’s expectation: should last year’s report preserve the earlier breakdown? That question helps explain the purpose of attribute history. Saying that an address or region changes infrequently leaves the reporting requirement unresolved.
Before an interview, I connect the terms in one exercise
Instead of memorising ten sentences, I would make a small fictional service model with customers, orders and order lines. I would write down the meaning of each row and the matching keys. Then I would explain how to produce monthly sales without multiplying order amounts.
Next I would ask about customers acquired in one month. How long can I observe them? How do I recognise departure? What happens if an old order changes status a week later? One limited task reveals connections between several terms.
I would also practise responding to an ambiguous request. Comparing January and February customers could mean their joining month or their activity month. In the first version I am interested in cohorts; in the second I may be observing the same people in successive periods. Before producing a table, I explain which meaning I am assuming and why I need confirmation.
Then I return to the least familiar term. I might draw three levels of a hierarchy, or trace one metric from a file to a report. If I can explain the drawing without relying on the term itself, I have a better starting point for the next technical exercise.
I do not need to build a complete warehouse for this exercise. I want to name missing information and explain why I need it. Where I get stuck, I return to one concept and its small example. My guide to the skills a data analyst needs provides a wider learning plan.
This practice helps me ask clarifying questions without pretending to know everything. Once I understand the task, I can choose the syntax. Knowing syntax alone still leaves the most basic question open: what am I supposed to calculate?
Key takeaways
Is memorising definitions enough for a technical interview?
I would also practise an example, a risk and a clarifying question for each concept. That helps me turn a term into a decision about data or a calculation.
Does cardinality mean uniqueness?
The context matters. For a column, cardinality can describe the number of distinct values. For a relationship, it describes matching patterns such as one-to-many. A unique key helps establish the one side, but does not cover every use of the term.
How does incremental loading differ from SCD?
Incremental loading concerns acquiring new or changed data. SCD concerns how changes to dimension attributes are handled, for example by overwriting a value or keeping successive versions.
AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.
