cswork / libraryOpen practice workspace

Articles

Teradata Interview Questions: Primary Indexes, AMP Skew, and Spool Failures

Prepare for Teradata interviews with an AMP skew calculation, primary-index trade-offs, spool-failure diagnosis, and a measurable query retest plan.

A Teradata interview answer about a slow query should begin with the data's distribution across AMPs, not a generic recommendation to “add an index.” In Teradata, the primary index (PI) determines how rows are hashed for distribution, and skew can concentrate storage and work on a few AMPs. The original exercise below gives you four AMP counts, a query symptom, and a choice of candidate keys. It is a diagnostic practice case, not a report of a particular employer's interview or a real production incident.

The key interview move is to separate row placement, query access, and intermediate spool. A balanced table can still run a skewed join; a heavily skewed table can make the largest AMP a bottleneck even when the average looks healthy. Teradata's primary-index documentation and TableSizeV guidance provide the product-specific basis for this reasoning.

Interview prompt: the average hides the overloaded AMP

Suppose a toy Teradata table Orders has 8,000 rows across four AMPs. An inspection gives AMP row counts 7,200, 400, 300, and 100. The table uses region as its nonunique primary index (NUPI); most orders are in one region. A dashboard query joins Orders to Customers and intermittently fails with a spool-space error. An interviewer asks: What does the distribution tell you? Is changing the PI sufficient? What evidence would you collect before changing physical design?

The mean is 8,000 / 4 = 2,000 rows per AMP; the largest holds 7,200, or 3.6× the mean and 90% of all rows. That is a vivid skew signal. It does not prove that those row counts equal actual bytes, that the failure came from this table rather than a redistributed intermediate, or that one specific PI change will cure the query. Check per-AMP bytes, query steps, estimates versus actuals, and spool use. The toy counts are chosen for arithmetic clarity; real systems may have many more AMPs and uneven row widths.

An answer that says “choose a unique PI” too quickly also misses access patterns. order_id may distribute orders well if values hash evenly and support point lookups, but the cost of joins and range-oriented reports must still be evaluated. A unique primary index (UPI) guarantees uniqueness and can support efficient unique access; a NUPI need not be unique. Neither label automatically proves a good distribution or a good workload fit. Teradata documents that rows with the same PI value go to the same AMP; a highly common region value is therefore a poor distribution candidate in this toy workload.

Question 1: What is a Teradata primary index—and what is it not?

The PI is central to row distribution. Teradata hashes the PI value to select a target AMP, so equal values land together. That design can make a join efficient when large tables are distributed on compatible keys and the join conditions permit AMP-local work. It can also make one AMP hot when one key value dominates. The PI is not merely an ordinary secondary B-tree index pasted onto an existing row layout. In an interview, name the distribution mechanism and ask what the dominant queries actually do with the key.

Do not collapse primary index into primary key. A primary key is a logical uniqueness constraint; a PI is a physical distribution and access choice. A UPI is unique, while a NUPI allows duplicates. It is possible to have a unique logical business key and still choose a different PI for physical reasons, subject to the schema and constraints. Explain the distinction with the toy table: region is repeated and therefore a NUPI; order_id might be unique, but whether to use it as PI depends on distribution and joins, not on the word “primary” alone.

Ask for the table DDL, PI columns, distinct-value counts, frequency of the most common values, and the query predicates. A composite PI can improve cardinality, but a composite key that still contains a dominant pattern or fails the join-access requirements may be the wrong trade. Hash distribution is not a claim that every AMP will have precisely equal rows. You need measured per-AMP distribution after loading realistic data, especially when many rows share one PI value.

Teradata's design guidance asks you to consider distribution, access, and join performance. That is the useful interview framework: if order_id balances storage but forces frequent large redistributions on a customer_id join, the net workload could worsen. Measure representative queries rather than optimizing one error message in isolation.

Question 2: How do you prove skew instead of guessing?

Start with metadata and measurements at the AMP level. Teradata's documentation on DBC.TableSizeV uses per-AMP CurrentPerm to find skewed tables. For a real system, inspect current permanent space by AMP and compare maximum with average; check row counts separately if available. A large difference can make an AMP run out of space while total system capacity still appears ample. Treat 7,200/400/300/100 as a row-count illustration, not an actual TableSizeV output.

Next, look at the skewed values. A frequency distribution of region can validate the proposed mechanism. If 90% of rows share one region, Teradata's equal-PI-to-equal-AMP rule explains why the row layout can be concentrated. If the source frequency is balanced but AMP storage is skewed, investigate row width, compression, hash collisions, or other design details rather than asserting the same cause. Check when the skew appeared: an ingestion change, new tenant, or changed predicate can turn an initially acceptable design into a bottleneck.

Finally, read the actual query plan and runtime diagnostics. Is the join AMP-local, redistributed by hash, duplicated to all AMPs, or collecting intermediate rows? Are statistics current enough for estimates to resemble actual cardinality? A stale estimate can push the optimizer toward a bad path, while fresh statistics cannot make an intrinsically huge intermediate small. Keep the claims separate: table skew is about storage placement; step skew is about work or spool created while the query runs. Both matter, and one does not prove the other.

Question 3: Why does a spool failure occur on a SELECT?

Spool is temporary working space used by query operations. A SELECT can need it for joins, sorting, aggregation, or redistributed intermediate results even though it does not write permanent table rows. Teradata's space-management guidance explicitly connects skew to spool exhaustion on reads and permanent-space exhaustion on writes. Thus “there is free space overall” is not a diagnosis. A single AMP may become the limiting point.

Work backward from the failing step. What is the estimated and observed number of rows entering it? Did a many-to-many join multiply data because the join key is not unique? Is the query projecting wide columns it does not need? Does a low-selectivity filter arrive too late? Did a redistribution send one popular key's rows to a single AMP? Are statistics misleading the optimizer? These are testable hypotheses. Show the investigator's sequence rather than naming every possible tuning feature.

For the toy query, changing Orders to order_id PI might improve its base distribution. It does not guarantee that the Orders–Customers join stops redistributing, and it cannot repair an accidental many-to-many match. Conversely, reducing intermediate width or correcting a join predicate may remove a spool failure without touching the PI. State which intervention addresses which measured mechanism. If the query has a valid many-to-many relationship, pre-aggregation at the correct grain may bound the intermediate—but only if it preserves the requested result.

Do not use “more spool space” as the first and only answer. Raising a quota can buy time, but it may hide a cardinality explosion or skewed join that will recur at higher volume. An operational response may include safely unblocking the job while you investigate, with a rollback plan and guardrails. A design answer should still explain why space was consumed and how you will verify a durable fix.

Question 4: How would you choose and validate a new PI?

Make a candidate table with at least three columns: value distribution, dominant access paths, and large-join compatibility. In the toy case, region has poor cardinality under the given distribution. order_id could provide even distribution and efficient unique order lookups; customer_id might align with a high-volume join but could still skew if a few customers generate most orders. A composite such as (customer_id, order_date) changes distribution and access again. There is no winner until you test it against realistic data and the queries that matter.

Define a measurable baseline: maximum and average permanent bytes per AMP, maximum and average spool bytes for the failing query, elapsed time, step cardinalities, and one or two representative point and range queries. Build the alternative in a safe test environment or controlled migration path, load production-shaped data, collect appropriate statistics, and compare. The goal is not a perfect uniformity score at any cost; it is an acceptable workload trade-off with no new severe skew or regression.

Include lifecycle costs. A physical redesign may require copying large tables, updating dependent SQL or ETL assumptions, and scheduling capacity. If a secondary access structure, partitioning strategy, or query rewrite better addresses a narrow workload, evaluate that too. Teradata's indexes and maps documentation shows that PI is one part of a broader physical design. In an interview, a cautious rollout and before/after evidence are stronger than an absolute prescription from one row-count snapshot.

The answer pattern interviewers can evaluate

A concise strong answer might sound like this: “The proposed NUPI on region is suspicious because identical values hash to one AMP, and 90% of rows share one value in this fixture. I would confirm per-AMP permanent bytes and query-step spool, inspect the frequency distribution and join cardinalities, and read the plan before changing design. Then I would compare order_id and customer_id PI candidates on representative joins and lookups. I would not claim the PI alone fixes a spool failure until the failing intermediate is measured.” That answer contains mechanism, evidence, alternatives, and verification.

The numbers are only a worked example, but the reasoning transfers. A candidate who can state 7,200/2,000 = 3.6×, distinguish row placement from intermediate redistribution, and ask for query evidence has done more than memorize that “skew is bad.” If the interviewer supplies additional facts—perhaps Customers has duplicate IDs or a single tenant dominates—revise the hypothesis visibly. Adaptability is part of the skill.

Sources and Further Reading