Back to interview questions

100+ Teradata Interview Questions and Answers

100+ Teradata Interview Questions and Answers

Use 120 Teradata interview questions and answers to assess SQL, indexing, skew, loading, transactions, query tuning and production database judgment.

Use 120 Teradata interview questions and answers to assess SQL, indexing, skew, loading, transactions, query tuning and production database judgment.

By

Team Sherlock.sh

Add Sherlock as a preferred source on Google

Hiring a Teradata developer or database specialist means assessing both SQL correctness and the behavior of a parallel analytical system. Recruiters need to know whether a candidate can explain row distribution, join cardinality, optimizer estimates and the operational consequences of a loading or tuning decision. A query that returns quickly on a small sample may still produce incorrect totals or concentrate work on a single AMP at production scale.

This guide to 100+ Teradata Interview Questions and Answers contains 120 questions across beginner, intermediate and experienced levels. It covers architecture, SQL, physical design, analytical functions, transaction handling, loading, recovery and production tuning. Match the interview to the actual Vantage edition and release, and distinguish application development from database administration responsibilities.

How can recruiters tell whether a candidate understands a Teradata plan instead of repeating a prepared explanation?

Ask candidates to predict a join’s row count, trace a duplicate key and diagnose what happens when a once-balanced distribution becomes skewed. Their explanation should connect the logical result to the work performed across AMPs. Changing one condition, such as a selective filter or a stale statistic, reveals whether they can reason through a new plan rather than recognize familiar terminology.

Sherlock AI helps hiring teams assess signals of potential AI cheating, deepfakes, impersonation and unauthorized assistance during remote interviews. Combine those signals with small SQL exercises and evidence-based plan discussions. Signals provide context for a fair review, rather than conclusive proof of misconduct.

Teradata Interview Questions

Select questions that reflect the role’s workload and responsibilities. Start with SQL semantics and distribution, then introduce a plan, a load failure or a changed data pattern. Each question has an identically numbered answer below.

Teradata Interview Questions for Beginners

Establish database architecture, SQL semantics and basic space and object concepts.

  1. What is Teradata used for?

  2. What does massively parallel processing mean?

  3. What is an AMP?

  4. What does the Parsing Engine do?

  5. What is BYNET?

  6. What is a primary index?

  7. How do a primary key and a primary index differ?

  8. How do UPI and NUPI differ?

  9. How is a row assigned to an AMP?

  10. What is a hash collision?

  11. What is data skew?

  12. Why does a low-cardinality primary index often perform poorly?

  13. What is a NoPI table?

  14. What is a secondary index?

  15. How do USI and NUSI differ?

  16. What is a SET table?

  17. What is a MULTISET table?

  18. What is permanent space?

  19. What is spool space?

  20. What is temporary space?

  21. What is a volatile table?

  22. What is a global temporary table?

  23. What does ON COMMIT PRESERVE ROWS do?

  24. What is a view?

  25. What is a macro?

  26. What is a stored procedure?

  27. What is BTEQ?

  28. What is Teradata Parallel Transporter?

  29. How do WHERE and HAVING differ?

  30. What does QUALIFY do?

  31. How does ROW_NUMBER differ from RANK?

  32. What does PARTITION BY mean in a window function?

  33. How does UNION ALL differ from UNION?

  34. How does NULL affect comparisons?

  35. How do COUNT(*) and COUNT(column) differ?

  36. What does COALESCE do?

  37. Why use DECIMAL for exact monetary quantities?

  38. Why are explicit date literals useful?

  39. What does EXPLAIN return?

  40. What are optimizer statistics?

Teradata Interview Questions for Intermediate

Test practical design, analytical SQL, transaction handling and reliable loading.

  1. How would you choose a primary index for a fact table?

  2. How do composite primary indexes affect access?

  3. How do table maps affect colocation?

  4. What is a partitioned primary index?

  5. What is partition elimination?

  6. How would you design monthly date partitions?

  7. What is column partitioning?

  8. What is a primary AMP index?

  9. When is multicolumn statistics collection useful?

  10. How do you decide when to refresh statistics?

  11. What does low confidence in EXPLAIN suggest?

  12. How do redistribution and duplication differ?

  13. When can a join execute locally?

  14. Why can an implicit cast hurt a join?

  15. How do join algorithms differ?

  16. What is a product join?

  17. How do you return the latest row per customer?

  18. How do you find all top-selling products including ties?

  19. How would you calculate a running total?

  20. Why should a window frame be explicit?

  21. How do LAG and LEAD support change detection?

  22. How would you implement a gaps-and-islands analysis?

  23. Why can NOT IN give surprising results?

  24. How can a WHERE predicate accidentally change a left join?

  25. How do you detect a many-to-many join explosion?

  26. How would you implement an idempotent incremental load?

  27. What must you validate before a MERGE?

  28. How would you handle late-arriving data?

  29. What is a safe deduplication process?

  30. How do ANSI and Teradata transaction modes differ?

  31. Why must DDL placement be considered in ANSI mode?

  32. What is a deadlock?

  33. What is the difference between READ and ACCESS locking?

  34. How do you diagnose blocking?

  35. When would you use a volatile table instead of a CTE?

  36. How do you collect statistics on a volatile table?

  37. How do TPT Load, Update and Stream roles differ?

  38. Why are TPT error tables important?

  39. How do you restart a failed load safely?

  40. What is query banding used for?

Teradata Interview Questions for Experienced

Explore tuning evidence, recovery, governance and deployment-specific tradeoffs.

  1. How would you investigate a query that suddenly became slow?

  2. How do you distinguish storage skew from processing skew?

  3. How would you handle a dominant join key?

  4. What should you examine after a spool-space error?

  5. How can stale statistics cause a bad join order?

  6. How would you measure whether a tuning change helped?

  7. Why can selecting fewer columns improve a query?

  8. How do you evaluate a secondary index candidate?

  9. What is a join index?

  10. When might a join index be a poor choice?

  11. How would you plan a primary-index migration?

  12. How do you manage partition growth and retention?

  13. What can go wrong with an overpartitioned table?

  14. How do you tune a workload rather than one query?

  15. What does workload management contribute?

  16. Why can more load sessions reduce throughput?

  17. How should DBQL evidence be used?

  18. How do you preserve privacy in query logs?

  19. How would you investigate an unexpected row-count change?

  20. How do you validate a cross-platform SQL migration?

  21. Why do character set and case rules matter?

  22. How can decimal arithmetic change during migration?

  23. How would you build a safe recovery checkpoint?

  24. How do you handle partial success across systems?

  25. What would you do after an uncertain COMMIT outcome?

  26. How do you design a slowly changing dimension Type 2 load?

  27. How do temporal features differ from ordinary date columns?

  28. How would you design access for an ETL service account?

  29. What is the difference between a user and a database in Teradata?

  30. How would you secure a shared reporting view?

  31. How do you validate backup and restore readiness?

  32. What does fallback protect against?

  33. How do you plan for maintenance without breaking service objectives?

  34. How would you test a release upgrade?

  35. Why should client autocommit settings be recorded?

  36. How do you evaluate VantageCloud deployment differences?

  37. How would you troubleshoot a resource regression after data growth?

  38. How should a DBA communicate a risky tuning recommendation?

  39. What demonstrates strong senior Teradata judgment?

  40. How would you evaluate compression for a large table?

Teradata Interview Questions and Answers

The answers connect business semantics to physical execution. Product capabilities and transaction behavior can depend on the Vantage edition, release and session mode. A proposed plan or an expected fixture result must be distinguished from measured behavior in the actual deployment.

Teradata Interview Questions and Answers for Beginners

These answers explain the architecture and SQL contracts behind ordinary queries. Look for clear distinctions between a primary key and primary index, logical duplicates and physical distribution, and temporary storage and durable data.

1. What is Teradata used for?

Teradata is a parallel analytical database platform used for large-scale SQL analysis, data integration and governed data access. An interview should distinguish SQL semantics from deployment-specific storage and workload features. Confirm the installed release and edition before promising a particular capability or operational behavior.

For a retailer, the platform might combine transaction history with customer and product data to calculate sales trends across billions of records. The useful interview discussion is how those records are distributed, how joins move data and how concurrent workloads share resources. A query that returns one monthly total can still scan and redistribute a substantial population, so result size alone says little about its execution cost.

2. What does massively parallel processing mean?

MPP divides work across cooperating processing units so scans, joins and aggregations can execute concurrently. Performance depends on balanced work and efficient data movement, not simply the number of processors. One overloaded participant can determine the elapsed time of an otherwise parallel request.

Suppose a scan is distributed across many AMPs but one AMP holds a large fraction of the qualifying rows. Most participants can finish quickly while the overloaded AMP continues working. Adding compute does not automatically repair that distribution problem. I would distinguish parallel execution from balanced execution and look for the slowest step's skew, data movement and resource use before concluding that the system needs more hardware.

3. What is an AMP?

An Access Module Processor manages its assigned data and performs database operations on that portion of the workload. AMPs cooperate in parallel query execution. Discuss AMP behavior separately from physical server counts because deployment architecture determines how virtual processing components map to infrastructure.

For a table distributed by customer ID, an AMP processes the rows assigned to it for a local scan or partial aggregation. A full-table aggregate combines work from multiple AMPs; an equality request using a complete primary-index value can target the relevant location. This explains why an AMP is a unit of database responsibility and parallel work, not simply another name for a user connection or physical machine.

4. What does the Parsing Engine do?

The Parsing Engine processes submitted SQL, checks syntax and authorization, supports optimization and coordinates execution steps. It does not mean that every row is processed centrally. Understanding this separation helps explain why distributed work can still depend on planning quality and request-management overhead.

When a client submits a join, the Parsing Engine resolves referenced objects and develops an execution plan that the AMPs carry out. A bad estimate can lead to an expensive redistribution even though every AMP executes its assigned instructions correctly. Conversely, a syntax or authorization failure can prevent execution before the data-access work begins. Separating these stages helps locate the actual cause of a failed or slow request.

5. What is BYNET?

BYNET is Teradata's communication fabric for coordinating and moving information among database components. Broadcast and redistribution steps rely on communication capacity as well as processing resources. A query with small final output can still be expensive if intermediate rows move extensively.

A join may require rows to be redistributed by the join key so matching values meet on the same AMP, or a small relation may be duplicated to participating AMPs. Both choices involve communication as well as local CPU and I/O. Reducing unnecessary columns and rows before an otherwise equivalent movement step can reduce traffic, but changing the query must preserve join and filtering semantics.

6. What is a primary index?

A primary index determines row distribution through hashing and supports access using its complete key. It is a physical design choice, distinct from the relational primary-key concept. Select it using distribution, access and join requirements rather than automatically copying a business identifier.

If most access is by customer ID and customer populations are reasonably balanced, a customer-based primary index may suit an event table. A nearly constant status column would be a poor distribution candidate even if many queries filter on it. Composite indexes require attention to the entire key: an equality condition on only one component does not generally provide the same targeted access as a complete primary-index value.

7. How do a primary key and a primary index differ?

A primary key expresses a logical uniqueness and non-nullability rule. A primary index is a Teradata distribution and access mechanism and can be nonunique. The same columns may serve both purposes, but declaring one should not be treated as a substitute for understanding the other's constraints.

An order table can use a unique order identifier as its logical key while distributing rows by a nonunique customer identifier to support customer-oriented work. Those choices solve different problems: one identifies an order, the other influences placement and access. I would inspect the actual DDL, including uniqueness and nullability constraints, rather than infer the design from a column named primary_id.

8. How do UPI and NUPI differ?

A unique primary index enforces uniqueness of the combined index value, while a nonunique primary index permits repeated values. A frequent NUPI value places many rows together and can create skew. A UPI generally spreads large populations well, but tiny tables can still have uneven AMP occupancy.

For a UPI on order ID, each complete index value identifies at most one row. For a NUPI on customer ID, a customer can have many orders, all associated with that distribution value. One unusually large customer can therefore concentrate work. Choosing a NUPI can be justified by access or joins, but the frequency of the most common values matters as much as the number of distinct customers.

9. How is a row assigned to an AMP?

The database hashes the primary-index column values and uses the table's map to associate the resulting hash with an AMP. Equal relevant values colocate within the applicable distribution scheme. A row's business date or insertion sequence does not independently determine placement unless included in the distribution design.

For two rows with the same complete primary-index value, hashing produces the same distribution input, so the relevant map directs them consistently. A composite key hashes the combination rather than distributing each component independently. Maps are important when discussing colocation across tables: matching column names alone do not prove that two tables' rows are physically arranged identically for a local join.

10. What is a hash collision?

Different index values can produce the same hash value. Teradata retains enough row identification information to distinguish rows rather than assuming a hash is a unique business key. Do not use a hash comparison alone as proof that two full records are identical.

A useful analogy is two names sharing a postal sorting code: the code narrows the destination but does not identify the full recipient. Likewise, a Teradata row hash supports routing and access while the system still distinguishes actual rows. For change detection, compare the relevant columns or use a deliberately designed fingerprint policy with collision handling; equality of a hash alone is not a mathematical guarantee of equality of the source data.

11. What is data skew?

Data skew means rows or bytes are unevenly distributed across AMPs. Repeated distribution-key values are a common cause. Measure both row and byte balance because variable-width rows can produce storage imbalance even when counts look similar.

A table can have balanced row counts but still be byte-skewed if one AMP receives much wider rows. Query skew can also appear only after a filter or redistribution, even when the base table is balanced. I would compare AMP-level consumption and inspect the heavy values in the step's distribution key, rather than assume that a healthy table-wide average explains a spool failure.

12. Why does a low-cardinality primary index often perform poorly?

A key with few distinct values can concentrate a large table on a small subset of AMPs. That limits parallelism and can exhaust space locally. Evaluate actual frequency distributions, including null and default values, rather than relying only on a column's declared data type.

A primary index on a status with values such as OPEN, CLOSED and UNKNOWN offers very few distinct hash inputs for a large table. If UNKNOWN covers most rows, one value becomes a particular hotspot. Adding columns can improve distribution but may weaken common lookup access, so the replacement needs workload evidence rather than simply maximizing the count of index columns.

13. What is a NoPI table?

A NoPI table omits a primary index and can be useful for staging when a suitable distribution key is unavailable or index processing is unnecessary. Loading and INSERT SELECT behavior affect its distribution. NoPI should not be assumed to eliminate skew or provide primary-index lookup performance.

A landing table may receive data before customer identifiers have been validated, making a final business distribution choice premature. A NoPI staging design can support that loading phase, followed by an explicit transformation into a table with a workload-appropriate primary index. The staging table still consumes space and may require scans or redistribution; omitting the index does not turn all subsequent analytical operations into cheap local access.

14. What is a secondary index?

A secondary index supplies an additional access path without replacing the table's primary distribution choice. Its subtables consume space and require maintenance when data changes. Evaluate selectivity and workload benefit against load overhead rather than adding indexes to every filter column.

If a table is distributed by customer ID but frequently retrieves a small number of rows by another selective attribute, a secondary index may offer a useful alternative to scanning. Its benefit must exceed the storage and update cost of maintaining it. An attribute matching most of the table may not justify that path, and the optimizer remains free to choose a scan when it estimates that to be cheaper.

15. How do USI and NUSI differ?

A unique secondary index enforces uniqueness and can locate a specific index value through its distributed index structure. A nonunique secondary index supports access to matching rows but may involve all AMPs. Actual plan selection depends on selectivity, statistics and cost, not the mere presence of an index.

A USI can locate an index entry and then the corresponding base-table row, which need not reside on the same AMP as the secondary-index entry. A NUSI is organized with the associated base-table data and can require checking index subtables across AMPs. Thus “secondary index” does not imply a single-AMP lookup in every case. I would verify the chosen access path in EXPLAIN for a representative predicate.

16. What is a SET table?

A SET table does not permit duplicate complete rows. That is different from requiring a particular business key to be unique. Duplicate handling and checking cost depend on statement form, constraints and session semantics, so validate the actual ingestion behavior instead of assuming every duplicate always raises the same error.

Two rows with the same customer ID but different amounts are not duplicate complete rows, so a SET table does not by itself enforce one row per customer. If uniqueness of that key is the requirement, express it through an appropriate constraint or unique index. For ingestion, test the specific insert or load path and reconcile accepted versus rejected rows rather than treating SET behavior as a substitute for business-key validation.

17. What is a MULTISET table?

A MULTISET table allows duplicate rows unless constraints or indexes prohibit them. It can avoid unnecessary duplicate-row checks for append-heavy workloads. Business-key uniqueness and ingestion idempotency still require explicit design; allowing duplicates is not permission to ignore data quality.

For event ingestion, two identical payload rows may represent a retry or two distinct business events whose identifiers were omitted. MULTISET permits storage of such rows unless other rules prevent it; it does not resolve which interpretation is correct. An idempotent load typically needs a stable event key and a defined retry strategy. Choosing MULTISET should therefore be independent of the decision about how duplicate events are detected.

18. What is permanent space?

Permanent space holds persistent database objects such as table data and associated structures within allocated limits. Space pressure can be uneven across AMPs. Distinguish allocated capacity, current consumption and peak consumption when diagnosing a load failure.

A load can fail despite apparently available overall capacity if the distribution causes a local limit to be reached. I would inspect the affected database or user allocation and AMP-level use, including index and fallback overhead where applicable. Deleting intermediate query results does not necessarily address permanent-table growth, and increasing the allocation alone does not fix a persistently skewed distribution design.

19. What is spool space?

Spool space supports intermediate query results and other session work, including volatile-table storage. A query can exhaust spool because of large intermediates, skew or concurrency even when its final result is small. Increasing a quota without examining the plan can merely postpone the failure.

A many-to-many join can create a very large intermediate before a final aggregation reduces it to a few rows. That intermediate may dominate spool consumption. I would check join cardinality, duplicate keys, redistribution skew and simultaneous requests before asking for more spool. Session-local volatile tables also remain part of the budget while retained, so abandoned intermediates can reduce the headroom available to later steps.

20. What is temporary space?

Temporary space is used for materialized global temporary table instances under the relevant database allocation rules. It differs from the spool charged for volatile tables. Confirm the object type and deployment accounting when investigating temporary-data capacity.

If a pipeline uses both volatile tables and global temporary table instances, diagnosing a capacity failure requires identifying which object is growing. Treating all short-lived data as “temp” obscures the distinction between spool and temporary-space accounting. A persistent GTT definition does not mean all sessions share the same stored rows or that its populated instance is charged in the same way as an ordinary permanent table.

21. What is a volatile table?

A volatile table is private to the creating session, with its definition held for that session and storage charged to spool. It is useful for intermediate analysis without a persistent catalog definition. It is lost when the session ends and is not a durable recovery checkpoint.

The fixture creates a session-local sales table with a customer-based primary index and preserves rows across commits. Reconnecting creates a new session, so it cannot be used as a restart checkpoint after a lost connection. In ANSI mode, commit the DDL before subsequent requests. Populate it only in a disposable sandbox session; later examples depend on its columns but do not reference production data.

CREATE MULTISET VOLATILE TABLE interview_sales (
    customer_id INTEGER,
    event_id INTEGER,
    event_ts TIMESTAMP(0),
    amount DECIMAL(10,2)
) PRIMARY INDEX (customer_id)
ON COMMIT PRESERVE ROWS

CREATE MULTISET VOLATILE TABLE interview_sales (
    customer_id INTEGER,
    event_id INTEGER,
    event_ts TIMESTAMP(0),
    amount DECIMAL(10,2)
) PRIMARY INDEX (customer_id)
ON COMMIT PRESERVE ROWS

CREATE MULTISET VOLATILE TABLE interview_sales (
    customer_id INTEGER,
    event_id INTEGER,
    event_ts TIMESTAMP(0),
    amount DECIMAL(10,2)
) PRIMARY INDEX (customer_id)
ON COMMIT PRESERVE ROWS

22. What is a global temporary table?

A global temporary table has a persistent definition while each session materializes its own instance and rows. This allows a shared schema without sharing session data. Row retention at transaction boundaries depends on its ON COMMIT behavior.

A reporting application can reuse one cataloged GTT definition while each connection inserts its own intermediate results. Session A's rows are not a shared staging area for session B. That distinction is useful with connection pools: work that expects to reuse a populated instance must stay on the appropriate session, and a workflow needing cross-session handoff should use a deliberately managed persistent object instead.

23. What does ON COMMIT PRESERVE ROWS do?

It retains rows in a volatile or global temporary table after a transaction commits. It does not make the table's rows durable beyond their session lifecycle. Explicitly choose the behavior because ON COMMIT DELETE ROWS clears the instance at transaction completion.

With DELETE ROWS behavior, inserting data and committing can leave an existing temporary-table instance empty for the next request. PRESERVE ROWS changes that transaction-boundary behavior, which is often useful for multi-step analysis. It still does not preserve a volatile table through disconnect or system reset. The expected lifetime should be stated explicitly: transaction, session and durable recovery are separate requirements.

24. What is a view?

A view stores a query definition that presents data from underlying objects. It can simplify interfaces and help govern access, but does not normally store a separate result copy. Nested views can hide expensive joins or casts, so inspect expanded logic when tuning.

A sales view might expose approved columns and apply a standard business filter so reports share one definition. Querying it still requires work against the underlying objects, and several nested views can accumulate joins or conversions that are not obvious at the top level. I would inspect dependencies and privileges as well as EXPLAIN before assuming a view provides either free precomputation or a complete access-control policy by itself.

25. What is a macro?

A macro stores a defined sequence of SQL requests that can be executed with parameters. It provides a reusable database interface with established execution semantics. It is not equivalent to an arbitrary procedural program; choose stored procedures when branching and procedural control are needed.

A parameterized macro can provide a stable entry point for a predefined reporting request without asking callers to construct the SQL text themselves. The parameter contract should specify types and meaning, and callers need the appropriate execution access. If the task requires conditional branching, looping or procedural exception handling, a macro should not be stretched into something it is not; a stored procedure may fit that control-flow requirement better.

26. What is a stored procedure?

A stored procedure encapsulates SQL and supported procedural logic, such as variables, conditions and error handling. Its usefulness depends on controlled interfaces and appropriate privileges. Avoid assuming that moving row-by-row work into a procedure automatically makes it efficient on a parallel database.

For a controlled processing step, a stored procedure can validate parameters, perform a set-based update and handle specified error conditions. The heavy data operation should still be expressed in a way that allows parallel processing where practical. Fetching and updating one row at a time inside a cursor loop can defeat that advantage. Encapsulation improves reuse and control, but it does not remove the need to analyze the SQL plan.

27. What is BTEQ?

BTEQ is a Teradata command-line utility for submitting SQL and controlling batch-oriented workflows and output. Its commands and SQL are distinct languages within a script. Check return codes, explicit transaction behavior and output formatting when building reliable automation.

A batch script can submit a SQL request and then use a BTEQ control command to branch or terminate based on the reported error code. A database error must propagate to the scheduler rather than be followed by an unconditional success exit. Output formatting also matters: a human-readable report layout is not automatically a safe machine-readable export contract, especially when delimiters, nulls and character encodings are involved.

28. What is Teradata Parallel Transporter?

TPT is a framework for moving data through producer and consumer operators with configurable parallelism. Operator selection determines load behavior, constraints and restart handling. A useful answer explains the data flow and recovery design rather than listing historical utility names only.

For a file-to-table load, a producer reads source records and a consumer operator applies them to the database using the chosen loading method. The design must specify record layout, rejected-row handling, checkpoint behavior and restart ownership. More operator instances can increase throughput only while the source, target and system limits support it; concurrency settings do not compensate for a bad distribution key or an undefined retry policy.

29. How do WHERE and HAVING differ?

WHERE filters rows before grouping, while HAVING filters groups based on aggregate conditions. Moving a predicate between them can change meaning. Filter nonaggregate conditions early when equivalent, but preserve outer-join and null semantics when rewriting.

To find customers whose November sales exceed a threshold, WHERE can restrict input timestamps to November and HAVING can test SUM(amount) after grouping by customer. Placing amount > 100 in WHERE instead answers a different question: it removes individual small sales before computing the sum. The correct location follows the intended grain of the condition, not just its apparent selectivity.

30. What does QUALIFY do?

QUALIFY filters results using ordered analytical functions after their values are computed. It is useful for top-per-group and deduplication patterns without an extra outer query. It does not replace WHERE for ordinary input filtering or HAVING for aggregate-group conditions.

For a latest-event query, compute ROW_NUMBER() within each customer ordered by event time and a unique tie-breaker, then use QUALIFY ... = 1. Filtering timestamps in WHERE first changes the candidate population, whereas QUALIFY selects from the analytical result. A condition on the window value cannot be moved into WHERE as though the ranking had already been calculated there.

31. How does ROW_NUMBER differ from RANK?

ROW_NUMBER assigns distinct sequence numbers within an ordered partition; RANK gives equal ranks to ties and leaves gaps afterward. DENSE_RANK also shares ties but avoids gaps. For deterministic ROW_NUMBER results, provide an ordering that uniquely resolves ties.

For scores 100, 100 and 90, RANK yields 1, 1 and 3, while DENSE_RANK yields 1, 1 and 2. ROW_NUMBER gives three different numbers, but which tied 100 receives 1 requires an additional ordering rule if reproducibility matters. “Top one row” and “all rows tied for first” are different requirements, so choose the function before writing the filter.

Score (descending)

ROW_NUMBER with a tie-breaker

RANK

DENSE_RANK

100, event A

1

1

1

100, event B

2

1

1

90, event C

3

3

2

32. What does PARTITION BY mean in a window function?

It divides result rows into independent analytical groups without collapsing them into aggregate rows. The window's ORDER BY controls sequencing within each group. This logical partitioning is different from physical table partitioning and does not itself guarantee AMP colocation.

With SUM(amount) OVER (PARTITION BY customer_id), every event row can carry its customer's total without losing the event ID. A GROUP BY customer query instead returns one aggregate row per customer. Adding an ordered window changes the calculation's sequencing and frame considerations; it does not establish the final display order. Use an outer ORDER BY when the returned presentation order is part of the requirement.

33. How does UNION ALL differ from UNION?

UNION ALL preserves all rows from its inputs, including duplicates. UNION removes duplicate result rows and may require additional processing. Choose based on intended semantics; replacing UNION with UNION ALL solely for speed is incorrect when deduplication is required.

If January and February extracts legitimately contain repeated projected values, UNION ALL keeps both occurrences. UNION can collapse them after projection, potentially removing meaningful records when identifying columns were left out of the select list. Conversely, if the requested result is the distinct set of customer IDs seen in either month, UNION directly expresses that intention. Compatibility of column positions and types matters for both forms.

34. How does NULL affect comparisons?

A comparison such as column equals NULL does not test for missingness; use IS NULL. Ordinary comparisons involving NULL generally produce unknown, which is not retained by WHERE. Three-valued logic also affects joins, NOT IN and check expressions.

For rows containing amounts 10, NULL and -5, WHERE amount > 0 retains only 10; it does not treat the unknown amount as zero. A NOT IN subquery containing NULL can also produce surprising exclusions because the comparison is unknown. Define the intended treatment of missing keys and use a null-aware pattern rather than adding a blanket replacement value that changes the data's meaning.

35. How do COUNT(*) and COUNT(column) differ?

COUNT(*) counts rows, while COUNT(column) counts rows where that expression is not NULL. COUNT(DISTINCT column) counts distinct non-null values. Select the measure that matches the business definition, especially after outer joins that introduce null-extended rows.

For amounts 10, NULL and 10, COUNT(*) is 3, COUNT(amount) is 2 and COUNT(DISTINCT amount) is 1. After a left join, an unmatched parent still contributes a null-extended result row to COUNT(*), while counting a non-null child key can yield zero matching children. Choosing the wrong expression can therefore inflate a customer-level activity report without any SQL execution error.

36. What does COALESCE do?

COALESCE returns the first non-null expression from its argument list with type resolution applied. It can supply a display default, but replacing unknown values with zero may change business meaning. Use compatible types and avoid hiding missing-data issues inside a convenience expression.

For display, COALESCE(region, 'Unknown') can provide a clear category, but the replacement should not accidentally merge a real region named Unknown with missing data unless that is intended. Likewise, replacing an unknown amount with zero changes averages and totals differently from leaving it missing. I would keep a missingness indicator where needed and check that all arguments resolve to the intended type and width.

37. Why use DECIMAL for exact monetary quantities?

DECIMAL represents fixed-precision decimal values and supports explicit scale rules, avoiding many binary floating-point representation issues. It still has finite range and can overflow or round. Define precision and scale for intermediate expressions as well as stored columns.

An amount column declared DECIMAL(10,2) has eight integral digits and two fractional digits, so its precision is not unlimited. Multiplying it by quantities or summing many rows can require wider intermediate or result precision. Explicit rounding should follow the business rule at the correct stage; rounding every component before summing can produce a different total from rounding only the final result.

38. Why are explicit date literals useful?

A typed literal such as DATE '2026-11-01' states both value and type without relying on ambiguous regional text formats. This improves readability and portability of intent. It does not replace careful handling of timestamps, timezones and session-dependent conversion rules.

For a timestamp column, a November filter can use an inclusive lower boundary and exclusive next-month boundary expressed with appropriate timestamp literals. That avoids assuming an end-of-day fraction for the final date. A DATE literal is valuable for a date-valued column, but converting every timestamp to DATE merely to filter it may affect access paths and intentionally discards time-of-day information.

39. What does EXPLAIN return?

EXPLAIN describes the optimizer's planned steps and estimates for a request rather than executing the request to measure actual elapsed time. Inspect access paths, joins, movement and cardinalities. Use runtime evidence separately to establish whether estimates match real behavior.

If EXPLAIN shows a large table duplicated to all AMPs, I would ask whether the row-count estimate is plausible and why that join geography was chosen. A high-confidence estimate is still an estimate, not proof of actual performance. Runtime logging or monitoring can reveal the actual step counts, skew and elapsed time; tuning should connect the planned operation to that evidence rather than reading the plan as a benchmark result.

40. What are optimizer statistics?

Statistics summarize data demographics so the optimizer can estimate selectivity and choose plans. Missing or stale information can lead to poor cardinality estimates. Collection has a cost, so prioritize columns and combinations relevant to workload predicates, joins and data changes.

For a filter on a strongly skewed status column, knowing that one value dominates can change the cost of an access path or join. Statistics on a column combination can also help when predicates are correlated and independent single-column assumptions are misleading. After a major load or distribution change, compare plan estimates with observed populations and recollect targeted statistics when justified, rather than collecting everything on every run.

Teradata Interview Questions and Answers for Intermediate

These answers examine design and implementation through concrete query behavior. Trace nulls, duplicates, ties and transaction boundaries before evaluating whether an indexing or loading choice is appropriate.

41. How would you choose a primary index for a fact table?

I would compare primary-index candidates against both data distribution and the important workload. Profile distinct values, frequency skew, nulls and growth, then examine frequent joins and tactical lookups. A key that distributes evenly but forces every major join to redistribute may not be the best overall design.

For example, customer ID can colocate customer activity, but one exceptionally large customer may concentrate much of the fact table on one AMP. Adding event ID can spread that customer's rows more evenly while changing which lookups and joins can use the complete primary index.

Evaluate representative volumes and heavy keys rather than a uniform sample alone. Measure AMP-level work and spool, not just total elapsed time from one query. The primary index is a physical distribution choice; it need not be the business primary key.

42. How do composite primary indexes affect access?

A composite primary index hashes the combined column values. A predicate on only one component generally does not provide the complete hash needed for primary-index single-AMP access. This differs from assuming a leftmost-prefix access rule borrowed from a conventional B-tree index.

With PRIMARY INDEX (customer_id, event_id), a complete equality condition on both values can identify the relevant distribution location. A query for every event belonging to one customer lacks event ID and may need a broader access path. Another index or optimizer transformation could still help, so inspect the actual plan.

A wider key can reduce skew but weaken common lookup or join colocation patterns. Check compatible data types and full-key usage across joined tables, and include null and dominant-key combinations in the distribution analysis.

43. How do table maps affect colocation?

A table map determines which AMPs participate in the table's distribution and how hash buckets are assigned. Consequently, compatible key definitions alone do not prove that matching rows from two tables reside on the same AMPs. Their maps and applicable colocation configuration must also be compatible.

This becomes important when a small dimension uses a sparse map while a large fact table uses a broader map. Identical customer values do not automatically imply that a local join can read both rows without movement. Sparse-map colocation settings can affect placement as well.

Inspect table definitions and EXPLAIN for the planned join movement. Do not infer physical compatibility from matching column names or a migration script that copied only index definitions. A map change should be evaluated with the workload and redistribution implications, not treated as a cosmetic metadata edit.

44. What is a partitioned primary index?

A partitioned primary index combines primary-index hashing across AMPs with row partitioning within each AMP. The primary index still determines distribution; a partition expression organizes local rows so qualifying queries may read fewer partitions.

For a sales table distributed by customer and partitioned by month, a January predicate can restrict the local months examined while the customer hash continues to determine AMP placement. Partitioning by date does not spread a dominant customer's rows across more AMPs simply because the dates differ.

The benefit depends on predicates, partition expressions and the cost of maintaining the design. A query covering every month may gain little from partition elimination. Evaluate row uniqueness, supported index combinations and maintenance operations for the deployed release rather than assuming every unpartitioned design transfers unchanged.

45. What is partition elimination?

Partition elimination lets the optimizer avoid partitions that cannot contain rows satisfying a predicate. It reduces the data examined within the distribution strategy, rather than guaranteeing single-AMP execution or removing every other query cost.

If a table has monthly partitions on an event date, a range restricted to March can make the other months irrelevant. A filter expressed through an opaque transformation may make that relationship harder to infer. Prefer clear predicates that match the data type and partition boundaries when they express the same business condition.

Use EXPLAIN to confirm the intended elimination and validate actual workload behavior. Check boundary dates, nulls and any catch-all partitions. Even successful elimination can leave an expensive join or aggregation, so evaluate the complete plan rather than using the presence of partitioning as proof of performance.

46. How would you design monthly date partitions?

Design monthly partitions around query ranges, retention and data-arrival behavior. Define the covered dates and deliberate handling of null or out-of-range values, then test the first and last dates of each boundary. The design must accommodate late records as well as the next month's load.

For example, retaining two years of events while receiving corrections for prior months requires a policy for reopening or retaining the affected historical partitions. A catch-all range can prevent some load failures, but it can also accumulate data outside the intended layout if no one monitors it.

Specify how future ranges are added and expired data is removed, including dependencies and recovery. More granular partitions are not automatically better: daily partitions can add management overhead when nearly every query scans whole months. Verify the release's partition-expression and limit rules before choosing the layout.

47. What is column partitioning?

Column partitioning groups columns into separately stored partitions so a query can avoid reading unneeded groups. It can help a wide analytical table when most queries project only a small subset of its columns. The gain depends on the actual projection and filtering workload.

A query summing two measures from a table containing dozens of descriptive attributes may need less I/O than a row-oriented scan that carries the entire row layout. Conversely, repeatedly reconstructing most columns or applying update-heavy workloads can change the trade-off.

Assess compression, load behavior, row reconstruction and supported index options for the deployed table type. Column partitioning is not just row partitioning rotated in a diagram: physical access and design restrictions differ. Compare representative queries and loads rather than assuming a narrower SELECT list guarantees a proportional speedup.

48. What is a primary AMP index?

A primary AMP index specifies distribution for a column-partitioned table but does not provide the same local row-hash ordering and access behavior as an ordinary primary index. It is nonunique. Its purpose and supported table combinations therefore differ from those of a unique primary index.

A design can use PA columns to place related rows on AMPs without claiming that a complete PA value gives the same row-level access path as a conventional PI. The distribution benefit and local retrieval mechanism should be evaluated separately.

Check the actual column-partitioned table definition and EXPLAIN rather than replacing PI with PA by name alone. In particular, do not infer a uniqueness constraint from PA or assume that every row-table index option is supported. Version and deployment documentation determine the applicable restrictions.

49. When is multicolumn statistics collection useful?

Multicolumn statistics describe joint distributions that separate single-column statistics may not capture. They are useful when correlated predicates or joins cause the optimizer to estimate combinations inaccurately. The goal is better cardinality estimates for important queries, not collecting every possible column subset.

Country and region illustrate the problem: a region may belong to only one country, so treating the predicates as independent can badly misestimate their combined selectivity. Statistics on the combination can communicate that relationship more directly.

Use workload evidence and estimate-versus-actual discrepancies to select combinations. Collection and refresh consume resources, especially on large changing tables. Also check whether the query's expressions and data types let the optimizer use the available demographics. More statistics do not repair an incorrect join grain or a filter with different semantics.

50. How do you decide when to refresh statistics?

Refresh statistics when their demographics no longer represent the data relevant to the workload. Growth matters, but so do changes in value frequencies, newly active partitions and skew. A fixed weekly schedule can miss a major distribution change immediately after a large load.

For example, a new tenant may add relatively few rows overall but dominate a filtered workload or a particular join key. Conversely, an append that preserves the existing distribution may not justify recollecting every statistic at the same frequency.

Use supported automation and thresholds where available, and compare estimate errors with collection cost. Prioritize statistics that affect expensive plans. Record the post-load policy and monitor plan behavior; refreshing statistics is a hypothesis-driven remedy, not a ritual that proves every regression has been fixed.

51. What does low confidence in EXPLAIN suggest?

Low confidence in EXPLAIN indicates uncertainty in an optimizer estimate, often because useful demographic information is missing or the predicate is difficult to estimate. It is a diagnostic clue rather than a verdict that the plan is wrong.

Locate the specific step and compare its estimated rows with observed cardinality from an appropriate execution or controlled count. If an estimate of a few rows actually represents millions, that discrepancy may explain an unsuitable join order, duplication choice or spool allocation.

Inspect relevant statistics, expressions and correlations before changing physical design. A low-confidence estimate can still lead to a good plan, while a confident estimate can be stale. EXPLAIN predicts execution; it does not supply measured runtime, and the response should distinguish plan evidence from observed resource use.

52. How do redistribution and duplication differ?

Redistribution sends rows to AMPs according to a required distribution, often hashing on a join key. Duplication sends copies of an input to the AMPs that need it. Both make join rows available together, but their costs depend on row count, row width and the participating AMPs.

Duplicating a small filtered lookup table can be cheaper than redistributing a large fact table. If the supposedly small input is actually large because of stale statistics or an ineffective filter, the copies can create excessive network traffic and spool.

Read the movement step together with the estimated input size and join result. Reduce unnecessary columns and rows when semantically valid, and verify skew after movement. Avoid treating all redistribution as a defect: some movement is the appropriate cost of a useful query that is not aligned with the stored distribution.

53. When can a join execute locally?

A local join can avoid redistributing its inputs when matching rows are already available on the same AMPs under compatible distribution and join conditions. Complete compatible keys, data types and table maps matter; matching column names alone are not enough.

For two tables distributed compatibly by customer ID, an equality join on that key may allow each AMP to join its local customer rows. A composite PI, a cast or incompatible maps can change that situation. Confirm the actual plan instead of inferring locality from the logical schema.

Local does not mean cheap. If each customer has many matching rows on both sides, the join can generate a large result and exhaust local spool without moving the inputs. Diagnose output cardinality and per-AMP imbalance as well as network movement.

54. Why can an implicit cast hurt a join?

An implicit cast can add per-row conversion work and interfere with access or distribution properties that the optimizer could use for directly compatible columns. It can also change which values compare equal, making this a correctness issue as well as a performance issue.

Joining character code '00127' to numeric 127 may collapse a representation distinction that the business considers meaningful. Character values containing letters can fail conversion, and numeric precision differences can alter matches. Removing the cast blindly may simply replace one incorrect interpretation with another.

Align source schemas where feasible and validate any explicit conversion in staging. Inspect EXPLAIN for changed movement or access, then compare matched and unmatched counts. Do not claim every cast forces a full scan; the effect depends on the expression, types and optimizer capabilities.

55. How do join algorithms differ?

Join strategies trade off access, ordering, memory and input size. Nested access can be effective for a small qualifying input with a suitable way to find matches. Merge-oriented processing benefits from compatible ordering, while hash-oriented processing groups rows by the join key. Applicable methods and details depend on the plan and release.

A strategy that is efficient for a hundred rows can become expensive when an underestimated predicate returns millions. Sorting, redistribution and spool creation may dominate the apparent join operation, so examine the surrounding steps rather than only the algorithm's name.

Start with input cardinalities, statistics, key compatibility and result grain. Correct a mistaken estimate or avoid unnecessary movement before prescribing an algorithm. A local many-to-many join remains expensive regardless of the label, and forcing one approach everywhere can harm other workloads.

56. What is a product join?

A product join evaluates combinations from its inputs and is not automatically a planning error. It can be reasonable when one side is genuinely tiny or when the required predicate is not a selective equality join. Its risk is the size of the combinations it must consider.

A missing join predicate between two large tables is a common failure: 100,000 rows combined with 100,000 rows creates a potential ten-billion-pair workload before further filtering. By contrast, combining a small parameter set with a fact input may be intentional.

Inspect predicates, filtered input estimates and the expected output grain. If a dimension was estimated as tiny, verify the statistics and filter behavior. Do not hide the explosion with SELECT DISTINCT; that adds work after the problematic combinations and may leave aggregated measures incorrect.

57. How do you return the latest row per customer?

Use ROW_NUMBER within each customer and order by the event timestamp descending plus a stable unique tie-breaker. QUALIFY then retains the row numbered one. This expresses a deterministic survivor rule rather than relying on whichever tied row happens to appear first.

The fixture query makes null timestamp placement explicit:

SELECT customer_id, event_id, event_ts, amount
FROM interview_sales
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY event_ts DESC NULLS LAST, event_id DESC
) = 1

SELECT customer_id, event_id, event_ts, amount
FROM interview_sales
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY event_ts DESC NULLS LAST, event_id DESC
) = 1

SELECT customer_id, event_id, event_ts, amount
FROM interview_sales
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY event_ts DESC NULLS LAST, event_id DESC
) = 1

Assuming event ID is non-null and unique within a customer, tied events 1 and 2 at 10:00 select event 2. A null timestamp sorts behind known times. Define whether latest means business event time, arrival time or revision precedence; a late correction can make those differ. Add an outer ORDER BY if the displayed result order matters.

58. How do you find all top-selling products including ties?

Aggregate sales to the intended product grain, then rank those totals within the requested group. Retaining rank one returns every product tied for the maximum. ROW_NUMBER would assign different numbers to tied products and select only one.

If products A and B each have revenue 100 and C has 90, RANK or DENSE_RANK ordered by revenue descending gives both A and B rank one. The gap difference between those functions matters for lower ranks, but not when selecting only the top rank.

Define sales as revenue, units or another measure before aggregation, and handle returns and missing amounts deliberately. Ranking transaction rows instead of product totals answers a different question. If the requirement is exactly one winner, specify a business tie-breaker rather than accidentally suppressing legitimate ties.

59. How would you calculate a running total?

Use an ordered SUM window within the relevant entity and specify a row-based frame from the beginning of the partition through the current row. Include a deterministic tie-breaker so intermediate totals have a reproducible order.

This example operates on the session-local sales fixture:

SELECT customer_id, event_id,
       SUM(amount) OVER (
           PARTITION BY customer_id
           ORDER BY event_ts, event_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_amount
FROM

SELECT customer_id, event_id,
       SUM(amount) OVER (
           PARTITION BY customer_id
           ORDER BY event_ts, event_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_amount
FROM

SELECT customer_id, event_id,
       SUM(amount) OVER (
           PARTITION BY customer_id
           ORDER BY event_ts, event_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_amount
FROM

For ordered amounts 10, 20 and −5, the expected totals are 10, 30 and 25. The example assumes non-null amounts and a unique ordering; define missing-value treatment separately. A row-by-row total differs from assigning one total to every event on the same date. The window ORDER BY defines calculation order, not presentation order, so add a final ORDER BY when needed.

60. Why should a window frame be explicit?

An explicit window frame makes the set of rows contributing to an aggregate visible. It distinguishes a cumulative calculation, a fixed-size moving calculation and a whole-partition total instead of relying on function-specific defaults. Frame semantics are separate from PARTITION BY and ordering.

For a moving sum over the current row and two predecessors, a ROWS frame describes three physical rows once enough rows exist. If timestamps tie, their relative order needs an additional key. Otherwise, which tied row falls inside a short moving frame may be nondeterministic.

Test the first rows of each partition, tied timestamps and missing values. Do not add a frame mechanically to functions that do not accept or use one; ranking and offset functions have their own syntax and semantics. Match the frame to the business definition, not merely to a familiar template.

61. How do LAG and LEAD support change detection?

LAG and LEAD retrieve values from preceding or following rows within an ordered partition. They support comparisons such as whether a customer's status changed without writing a self-join. The partition and ordering define what previous or next means.

For statuses NEW, NEW and CLOSED, comparing each status with its preceding value identifies the transition to CLOSED. If two events share a timestamp, include a deterministic event key; otherwise the comparison can change between executions.

A missing neighbor and a real neighboring row whose value is NULL are different cases. Use an additional indicator, such as row position or a non-null event ID from the neighboring row, when that distinction matters. SQL comparisons involving NULL also need explicit treatment so a change to or from missing is not silently omitted.

62. How would you implement a gaps-and-islands analysis?

Start by defining continuity. An island might mean consecutive calendar dates, consecutive sequence numbers or events separated by no more than a specified interval. Order events within the entity, compare each with its predecessor, mark a new island when continuity breaks, and cumulatively sum the boundary flags.

For dates 1, 2, 5 and 6, a consecutive-day rule produces two islands: 1–2 and 5–6. A session rule allowing a three-day gap would produce a different result. The algorithm cannot choose the business definition for you.

Use separate query stages where needed to compute predecessor values and then aggregate window-derived flags. Resolve duplicate dates and missing timestamps before grouping. Finally aggregate by entity and island ID, checking start, end and counts against a small fixture with deliberate gaps.

63. Why can NOT IN give surprising results?

NOT IN can behave unexpectedly when its subquery contains NULL. For an otherwise unmatched value, comparison against a NULL produces unknown, and the WHERE clause does not retain unknown results. NOT EXISTS often states the intended absence-of-match test more clearly.

This fixture deliberately excludes unknown customer IDs and selects rows for customers with no negative-amount event:

SELECT a.customer_id, a.event_id
FROM interview_sales AS a
WHERE a.customer_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM interview_sales AS b
      WHERE b.customer_id = a.customer_id
        AND b.amount < 0
  )

SELECT a.customer_id, a.event_id
FROM interview_sales AS a
WHERE a.customer_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM interview_sales AS b
      WHERE b.customer_id = a.customer_id
        AND b.amount < 0
  )

SELECT a.customer_id, a.event_id
FROM interview_sales AS a
WHERE a.customer_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM interview_sales AS b
      WHERE b.customer_id = a.customer_id
        AND b.amount < 0
  )

It tests existence at customer grain, so one negative row excludes all rows for that known customer. NULL amounts do not satisfy amount < 0; decide whether unknown amounts require separate rejection. The outer null-key policy is explicit rather than assumed. Verify the intended semantics before replacing NOT IN mechanically across unrelated queries.

64. How can a WHERE predicate accidentally change a left join?

A left join preserves unmatched left rows with NULL values for the right-side columns. A WHERE predicate such as right.status = 'ACTIVE' then removes those unmatched rows because the comparison is unknown. That changes the population retained by the query.

If the requirement is all customers with any eligible active match attached, put the active-status restriction in ON. Customers without such a match remain, with null right-side values. If the requirement is only customers having an active match, filtering them out may be intentional.

Test a matched active row, a matched inactive row and a customer absent from the right table. Predicate placement is a semantic decision, not a blanket performance rule. Also check multiple eligible right rows, because preserving left rows does not guarantee one result row per left customer.

65. How do you detect a many-to-many join explosion?

Profile key frequencies on both sides at the intended join grain and compare expected combinations with intermediate row counts. If one key appears m times on the left and n times on the right, an equality join can produce m × n rows for that key.

Two invoice rows joined to three customer-history rows create six rows when customer is the only key. Summing invoice amounts afterward repeats each invoice three times. SELECT DISTINCT may hide some repeated display rows without restoring the correct measure or identifying the valid historical match.

Fix the relationship: join on a fuller key, apply an as-of rule, or aggregate deliberately before joining. Validate uniqueness assumptions as executable data checks. Examine heavy keys separately because an overall row-count ratio can hide one customer's disproportionate contribution to result size and AMP skew.

66. How would you implement an idempotent incremental load?

An idempotent load produces the same target state when the same logical batch is replayed. Define stable event or business keys, stage the batch with a durable identity, resolve duplicates deterministically and apply changes within a controlled transaction and completion protocol.

If a client loses its connection after submitting a batch, the outcome may be uncertain. Replaying raw inserts can duplicate data; a keyed application plus a batch ledger allows the process to establish what committed before deciding how to resume. The completion marker must correspond to the committed target effects.

Define how legitimate corrections supersede prior versions so deduplication does not discard new information. Test replay, partial failure, out-of-order versions and concurrent batches. MERGE can be part of the implementation, but the statement alone does not establish idempotency or recovery correctness.

67. What must you validate before a MERGE?

Before MERGE, establish the target key and ensure the source has the intended cardinality for each match. Multiple source rows targeting the same existing row can make the operation invalid or ambiguous. Validate the deployed Teradata syntax, matching conditions and index-related restrictions rather than importing assumptions from another database.

For a customer update feed, select one deterministic current version per customer in staging before applying changes if that is the business rule. Do not resolve conflicts by arbitrary row order. Distinguish a genuinely new key from a missing or malformed key that should be rejected.

Test matched updates, inserts, duplicate sources, null keys and replay in a disposable environment. Reconcile counts and values after application, and plan transaction boundaries and error handling. A successful statement does not prove that the chosen matching key represents the correct business entity.

68. How would you handle late-arriving data?

Keep event time and arrival time separately, then define how late information affects the target. A late sale may restate the historical period in which it occurred, while another business process may require a current-period adjustment. That policy determines the affected partitions and aggregates.

A watermark based only on the maximum event timestamp can miss a delayed record whose timestamp is older than the watermark. Arrival-based ingestion, a controlled overlap window or a source change sequence can help, combined with keyed deduplication so rereading a range does not duplicate events.

Preserve original and correction identities for audit. Recompute the bounded affected range or apply a validated adjustment, then reconcile downstream summaries. Very late records outside the normal window need an exception path; silently dropping them makes a fast incremental pipeline incomplete.

69. What is a safe deduplication process?

Define the duplicate key and survivor rule before changing data. Exact repeated rows and conflicting versions of the same event are different problems: one may be safely collapsed, while the other needs a rule based on revision, source precedence or business validity.

Build the candidate result in staging with deterministic ordering, then reconcile original, retained and rejected counts. For duplicate payment events, also compare amounts by stable keys so a mistaken rule does not silently remove legitimate transactions. Preserve discarded rows and their reasons when audit requirements call for it.

Only then apply the approved result through a recoverable procedure. SELECT DISTINCT is not a universal deduplication design, because it compares selected values rather than business identity. Re-run the process on its own output to verify that it does not continue changing the data.

70. How do ANSI and Teradata transaction modes differ?

ANSI and Teradata session modes define different transaction boundaries and error behavior. ANSI mode implicitly starts a transaction and requires an explicit closing action such as COMMIT. Teradata mode has implicit request transactions and explicit BEGIN/END TRANSACTION semantics. Client autocommit settings can affect what an application observes.

Do not assume a failed statement always rolls back all preceding work. In ANSI mode, many request errors roll back the request while leaving the transaction open; deadlocks and certain other failures can roll back the transaction. Test the application's actual error path and make closure explicit.

71. Why must DDL placement be considered in ANSI mode?

In ANSI mode, a DDL request must be the last request before the transaction-closing statement. Consequently, creating a table and then immediately issuing DML in the still-open transaction can fail. A script that appears to work in an autocommitting client may behave differently through another driver.

For the volatile-table fixture, complete the CREATE request and commit its DDL transaction before inserting or querying as required by the session behavior. Keep setup boundaries explicit, and verify that ON COMMIT retention matches the intended lifecycle. Do not insert arbitrary commits into a business transaction without considering its atomicity.

72. What is a deadlock?

A deadlock is a cycle of transactions waiting for resources held by one another. For example, transaction A holds a lock on one object and waits for another, while transaction B holds the second and waits for the first. The database breaks the cycle by aborting work according to its transaction rules.

Reduce avoidable deadlocks by accessing shared objects in a consistent order and keeping transactions no longer than necessary. Examine the actual lock sequence rather than assuming the longest-running query caused the cycle.

Retries must be bounded and apply to the correct business transaction. After an abort, establish what rolled back and what external effects already occurred. Idempotent operations and a suitable backoff can make recovery safer; blindly resubmitting only the last statement can leave an incomplete or duplicated business operation.

73. What is the difference between READ and ACCESS locking?

READ and ACCESS locks make different consistency trade-offs. READ locking protects reads against conflicting changes under the applicable rules. ACCESS permits weaker-consistency reading and can expose uncommitted or inconsistent data, so it is not simply a faster spelling of READ.

For a reconciliation report, reading part of a batch before its related updates are complete can produce totals that never represented a committed business state. An ACCESS read might reduce waiting while making that report unsuitable for its purpose.

Choose lock behavior from the required consistency and concurrency, and inspect the complete transaction. Do not apply ACCESS globally to suppress blocking without understanding the data's use. If long reads interfere with loads, consider scheduling, query design or a suitable reporting architecture rather than quietly weakening the report's guarantees.

74. How do you diagnose blocking?

Identify the waiting request, blocker, lock object and transaction age through authorized monitoring. Determine whether the blocking session is actively doing useful work, waiting on another resource, or idle with an open transaction. The visible waiting query is often a symptom rather than the cause.

A client that completed an update but never committed can block readers long after its CPU work ended. Conversely, a large active load may be holding locks as part of an expected maintenance window. Those cases require different responses.

Coordinate with the owner and evaluate rollback consequences before canceling a session. Cancellation can trigger lengthy cleanup rather than immediate relief. Record the cause and correct transaction handling or workload design so the incident does not recur; repeatedly killing blockers is not a durable concurrency strategy.

75. When would you use a volatile table instead of a CTE?

A volatile table materializes a session-local intermediate result that can be reused across requests with an intentional distribution and statistics strategy. A CTE expresses a query subresult, but its physical execution is optimizer-dependent; naming a CTE does not guarantee one-time materialization.

If several later requests repeatedly need the same expensive filtered population, staging it once may reduce repeated work and give the optimizer useful statistics. If it is used only once and is easy to optimize within the original query, materialization may add unnecessary writes and spool.

Compare the entire workflow, including creation, loading, statistics and cleanup. Specify ON COMMIT behavior and transaction mode so rows survive for the intended uses. A volatile table is session-scoped, so a connection-pool handoff or reconnect can make it unavailable to subsequent requests.

76. How do you collect statistics on a volatile table?

Collect supported statistics on the populated volatile table when later queries need better estimates. Choose columns or combinations relevant to filters, joins and grouping, and use representative data. Statistics on an empty or tiny fixture do not describe a production-sized intermediate result.

The following targets the customer distribution and then requests a plan:

COLLECT STATISTICS COLUMN (customer_id)
ON interview_sales;

EXPLAIN
SELECT customer_id, SUM(amount)
FROM interview_sales
GROUP BY

COLLECT STATISTICS COLUMN (customer_id)
ON interview_sales;

EXPLAIN
SELECT customer_id, SUM(amount)
FROM interview_sales
GROUP BY

COLLECT STATISTICS COLUMN (customer_id)
ON interview_sales;

EXPLAIN
SELECT customer_id, SUM(amount)
FROM interview_sales
GROUP BY

EXPLAIN provides estimated steps, not measured runtime. Compare estimates and actual behavior before deciding the collection was worthwhile. The table and its statistics follow the session object's lifecycle, so recreate the required information in a new session. For a single cheap downstream query, collection cost may exceed the benefit; repeated complex uses can justify it.

77. How do TPT Load, Update and Stream roles differ?

Choose a TPT operator from target eligibility, change type, latency and concurrent-access requirements. Load suits eligible bulk-load targets, Update handles bulk changes under utility restrictions, and Stream uses SQL-oriented changes for ongoing lower-latency work. Exact restrictions and supported features depend on release and operator configuration.

Update's bulk protocol can restrict concurrent access to its targets. Stream permits concurrent activity through ordinary SQL locking rather than holding the same utility-wide target lock, but that does not mean updates take no locks or never block.

For a nightly isolated batch, throughput may dominate; for an actively queried table, availability and transaction latency may dominate. Include checkpoints, reject handling and restart behavior in the decision. Benchmark the actual workload instead of asserting that one operator is always fastest.

78. Why are TPT error tables important?

TPT error tables retain rejected-record and load-error evidence needed to reconcile and recover a job. A completed process can still have records that did not reach the target, so exit status alone is not sufficient proof that the business batch is complete.

Separate data-quality rejects from utility or application failures, inspect representative error causes and reconcile accepted plus rejected counts against the source. A malformed numeric field may need source correction, while a duplicate-key conflict may reveal a misunderstanding of target grain.

Keep the artifacts required by the operator's restart procedure until recovery and audit decisions are complete. Cleanup should follow a documented retention policy, not a blanket drop at the start of every run. Record how corrected rejects are reapplied without duplicating records already accepted.

79. How do you restart a failed load safely?

Before restarting, establish the failed operator's state, checkpoint and committed target effects. A job that failed before application is different from one that applied data but lost its final client acknowledgement. Use the operator's documented restart procedure and the required original job identity and artifacts.

Do not drop work, log or error tables merely to make a new invocation start cleanly. Those objects may contain the information needed to resume safely. Likewise, replaying the entire source can duplicate already committed rows unless the load design accounts for that outcome.

Reconcile source, accepted, rejected and target counts after recovery, then confirm the batch completion record. Test restart procedures in a disposable environment before relying on them during an incident. A generic SQL rollback assumption is insufficient for every TPT protocol and checkpoint state.

80. What is query banding used for?

Query bands attach name-value context to requests or sessions, such as application, batch or workload identifiers. Administrators can use that context for attribution and configured workload policies, making it easier to connect resource consumption with a particular process.

For a shared service account, recording an application and batch identifier can distinguish an overnight load from an interactive report. In pooled connections, reset or replace session context when returning or reusing a connection so the next request is not attributed to the previous user or job.

Treat caller-supplied labels as metadata unless an explicitly configured trusted-session mechanism establishes stronger identity semantics. They do not automatically authenticate a person. Keep values within the supported format and avoid placing secrets or unnecessary personal data in context that may appear in monitoring and logs.

Teradata Interview Questions and Answers for Experienced

These scenarios require diagnosis from evidence rather than an automatic tuning prescription. Strong answers separate cardinality error, skew, resource contention and recovery state, then explain how a proposed change will be validated.

81. How would you investigate a query that suddenly became slow?

Compare the current request, parameter values, plan, statistics and runtime metrics with a known good execution. Check data growth, skew, blocking and workload pressure before rewriting SQL. Preserve evidence of the regression so a proposed fix addresses the changed cause rather than a coincidental symptom.

I would first separate time waiting to execute from time spent executing. A request delayed by a workload throttle or blocked by another transaction needs a different fix from a new redistribution-heavy plan. If yesterday's parameters selected a small customer and today's select the largest customer, the SQL text alone is not a comparable baseline. I would retain the query identifier, relevant plan steps and per-AMP measurements so the investigation can explain which change increased work or concentrated it on fewer AMPs.

82. How do you distinguish storage skew from processing skew?

Storage skew concerns uneven persisted rows or bytes, while processing skew concerns uneven work during a particular execution step. A balanced table can produce skew after filtering or redistribution on a hot value. Use per-AMP runtime evidence alongside table-size measurements.

A table distributed by a high-cardinality transaction identifier can look balanced on disk. A later join that redistributes those rows by a status column may send a large fraction of them to the same AMP because one status dominates. Rebuilding the table on a new primary index would not necessarily fix that intermediate concentration. I would compare persisted space with the work distribution at the slow step, then identify the key and predicate responsible. The slowest participating AMP can determine completion while other AMPs have already finished their work.

83. How would you handle a dominant join key?

Measure its frequency and contribution to intermediate results, then consider isolating hot-key processing or changing the design if semantics permit. A special-case branch must preserve duplicates and null behavior correctly. Randomly salting keys without a matching join strategy can produce wrong results or unnecessary duplication.

Suppose an 'unknown customer' identifier accounts for a large share of sales. Redistributing by that identifier concentrates those rows, and joining it to multiple reference rows can also multiply the output. I would decide whether those rows should match at all before designing a performance workaround. If separate hot-key and ordinary-key branches are appropriate, their predicates must be disjoint and collectively complete. A salted design must place each matching pair in the same intended bucket; adding random values independently on both sides loses valid matches.

This read-only Teradata SQL diagnostic uses the earlier interview_sales fixture to show repeated key frequencies. It identifies candidate hot values; it does not measure AMP skew or prove that a redistribution occurs.

SELECT customer_id, COUNT(*) AS key_rows
FROM interview_sales
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY key_rows DESC,

SELECT customer_id, COUNT(*) AS key_rows
FROM interview_sales
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY key_rows DESC,

SELECT customer_id, COUNT(*) AS key_rows
FROM interview_sales
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY key_rows DESC,

84. What should you examine after a spool-space error?

Find the failing step, intermediate cardinality, row width, per-AMP skew and concurrent spool consumers. Check for missing join predicates, poor estimates and excessive projection. Increase allocation only when the workload is valid and capacity supports it, not as a substitute for diagnosing explosive intermediates.

A request may run out of spool even when the total looks modest if one AMP receives a disproportionate intermediate result, depending on the deployment's space-management behavior. I would inspect whether the plan duplicates an input, redistributes on a hot value or builds a large product join. A missing join condition can create a huge row count that more spool merely postpones. Removing unused wide columns or correcting cardinality estimates can reduce pressure, but the revised result must preserve legitimate duplicates and the required business grain.

85. How can stale statistics cause a bad join order?

The optimizer may underestimate a growing or newly skewed input and place it early in a join sequence or choose costly duplication. Refresh targeted demographics and compare the new plan. If the estimates remain wrong, inspect correlated predicates, expressions and data-quality assumptions rather than repeatedly recollecting everything.

For example, a dimension believed to contain a small filtered subset may be duplicated to all AMPs. If the subset has grown sharply, that choice can consume much more memory and communication than expected. I would compare estimated and observed cardinalities near the first divergence, then refresh statistics on the relevant columns or supported column combinations. Correlation matters: separate statistics on region and product may not describe a predicate whose selectivity depends on their combination. Collection is useful when it improves the information driving the decision, not simply because it is repeated.

86. How would you measure whether a tuning change helped?

Use comparable data, parameters and workload conditions, and compare correctness plus elapsed time, CPU, I/O, spool and skew. Include repeated representative runs where variability matters. A faster isolated query that consumes much more shared capacity can degrade the overall service.

I would keep the result contract fixed and compare a representative parameter set, including a hot key and a broad scan rather than only the easiest case. A change that halves elapsed time while greatly increasing AMP CPU may help one request but delay others at peak load. Queue time, execution time and total resource use should therefore be reported separately. If data or concurrency changed between trials, I would explain that limitation instead of attributing the entire difference to the SQL edit.

87. Why can selecting fewer columns improve a query?

Narrower rows reduce intermediate storage, movement and sometimes physical reads, especially where column elimination applies. Remove unused projections early when semantics permit. A final SELECT list reduction may not help if earlier view logic still materializes wide intermediates, so inspect the actual plan.

If a join needs only an identifier and amount, carrying a long description through redistribution adds bytes without helping the join. A narrow intermediate can reduce spool and network movement even when the number of rows is unchanged. I would check that the optimizer already performs projection elimination before introducing a manual staging step. Removing columns can also change semantics when they participate in DISTINCT, grouping or duplicate-sensitive logic, so projection reduction is not always a purely physical optimization.

88. How do you evaluate a secondary index candidate?

Estimate workload selectivity, frequency and saved work, then measure index storage and DML maintenance cost on representative data. Verify that the optimizer actually chooses the path when appropriate. Include load windows and concurrency because an index benefiting one query can harm the broader workload.

A highly selective recurring lookup can justify a secondary access path, but a query returning a large part of the table may still favor scanning. I would identify which index type and access behavior fit the lookup, then examine the chosen plan with representative values. The operational cost includes maintaining the index during inserts, updates and deletes as well as storing it. If a nightly load is already near its window limit, a small reporting gain may not justify extra maintenance or utility restrictions.

89. What is a join index?

A join index stores a maintained derived representation that can support eligible queries, including selected joins or aggregates. It trades storage and update work for reduced query work. Confirm optimizer matching, restrictions and load compatibility before treating it as a universal materialized-view solution.

A useful case is a recurring customer-level aggregate that otherwise scans and groups a large fact table. An eligible aggregate join index can retain an appropriate derived representation for the optimizer to use. The query must match its coverage and semantics; the existence of an index does not establish that a particular request benefits. I would inspect the plan and verify the base-table maintenance path, including supported load utilities. The stored result also consumes space and changes the cost of modifying its underlying data.

90. When might a join index be a poor choice?

High write volume, limited reuse, volatile query shapes or utility restrictions can outweigh query savings. Maintenance can also increase operational complexity and recovery time. Compare it with schema changes, preaggregation or workload adjustments using full lifecycle costs.

If an index saves a few seconds on one weekly query but adds work to millions of daily updates, its lifecycle cost may exceed its benefit. A changing report can also stop matching the index while the maintenance cost remains. I would measure usage and compare it with simpler alternatives, such as changing the query's aggregation grain or using a deliberately managed summary table. Operational restrictions matter too: an otherwise attractive design can conflict with the load utility or recovery procedure the application relies on.

91. How would you plan a primary-index migration?

Create a reviewed replacement design, load or transform data through a controlled migration, and validate counts, constraints and representative query results. Plan privileges, dependencies, statistics, cutover and rollback before touching production. Changing distribution is a data-movement project, not merely a metadata rename.

I would first verify that the candidate distribution improves the important workload without introducing a new hot key. The migration may need a replacement table or a supported alteration path depending on the table and release; neither should be assumed from generic advice. During cutover, writers must be controlled or their changes captured so the replacement is not already stale. Counts alone are insufficient: key-level reconciliations, privileges, dependent views and statistics all need attention. Rollback must account for any writes accepted after switching to the new representation.

92. How do you manage partition growth and retention?

Track future range coverage and retire old data using supported partition maintenance with a verified retention policy. Test boundaries and dependencies before the operation. Distinguish dropping a partition's data from changing a range definition, and preserve recovery options required by the business.

For monthly ranges, I would test an observation on the last instant of the old range and the first instant of the new one, including the treatment of out-of-range and null values. A partition boundary mistake can route new data unexpectedly or reject it. Retention should distinguish when data stops being queried from when it may be irreversibly removed. I would check the actual ALTER operation's effect and recovery requirements rather than infer that a command described as dropping a range merely removes metadata.

93. What can go wrong with an overpartitioned table?

Too many partitions can increase metadata, planning and maintenance overhead without meaningful scan reduction. Queries that touch most partitions may gain little. Evaluate actual predicate patterns and data volume per partition instead of choosing the finest possible granularity.

Daily partitioning is not automatically better than monthly partitioning if most queries scan an entire year. More boundaries can add planning and maintenance work while pruning little additional data. I would compare how often real predicates eliminate partitions and how much data remains in each selected partition. Very small partitions can also complicate the recurring work of extending ranges and applying retention. The design should fit query selectivity and lifecycle needs rather than pursue the largest partition count the feature permits.

94. How do you tune a workload rather than one query?

Group requests by business priority, resource use and service objective, then identify shared bottlenecks and concurrency interactions. Consider scheduling, admission control and physical design alongside SQL. Optimize total service reliability and throughput rather than maximizing one user's isolated benchmark.

An analytical scan that finishes quickly in isolation may interfere with tactical requests when many copies start together. I would identify the service objective for each request class and separate queued time from resource consumption during execution. Changes could include a better distribution design, fewer redundant scans, staggered batch starts or admission controls. The success criterion should include the critical workloads' response times and completion rates under realistic concurrency, not merely the fastest run of the query that motivated the investigation.

95. What does workload management contribute?

It classifies and controls requests using configured priorities, limits and resource policies. This helps protect critical workloads from uncontrolled concurrency. Policies need measured objectives and monitoring; an aggressive throttle can reduce resource pressure while causing unacceptable queue delay.

A queue can protect the system from starting more resource-intensive requests than it can handle effectively. That can improve completed throughput even though an individual request waits longer before execution. I would review classification rules as well as limits: a supposedly tactical request may be misclassified or become expensive after its data changes. Policy tuning needs an agreed trade-off between queue delay, critical response times and batch completion. Raising one class's priority does not create new capacity and can shift the problem to another class.

96. Why can more load sessions reduce throughput?

Additional sessions may saturate CPU, storage or communication and increase contention, skew or logging overhead. Measure throughput and resource curves rather than assuming linear scaling. Tune operator and session counts within supported limits and realistic concurrent workloads.

I would plot completed rows or bytes per second against session count while observing AMP CPU, I/O and any queueing. If throughput plateaus and elapsed time becomes more variable, extra sessions may be adding contention rather than useful parallelism. A skewed target can leave one AMP as the bottleneck regardless of how many client sessions submit work. TPT operator settings also interact with the type of load and utility limits. I would use the supported configuration for the installed version and test alongside the normal reporting workload.

97. How should DBQL evidence be used?

Database Query Logging can provide request and execution details according to enabled logging rules. Correlate the captured identifiers, timing and resource metrics with application context. Logging is not automatically complete; consider retention, overhead and permissions before making claims from absent records.

I would connect a DBQL request identifier to the application's run identifier and parameter context, using a sanitized query band where appropriate. Then I would compare the captured execution details with the plan and current system conditions. A record missing a particular step metric may reflect the logging rule, not zero work at that step. Before exporting evidence, I would check whether SQL text, object information or other sensitive details were collected. Logging configuration and retention belong in the interpretation of the dataset itself.

98. How do you preserve privacy in query logs?

Restrict access, apply retention controls and avoid putting secrets or unnecessary personal data into SQL text or query bands. Parameterization can help but does not remove all logging exposure. Review what the configured logging options actually capture and export only the minimum diagnostic evidence needed.

A query band should carry an opaque run identifier or workload classification, not a password or a customer's raw personal details. SQL literals may also appear in diagnostic records, so I would inspect actual logging behavior rather than assume that application-side parameter binding makes every log safe. A performance investigation often needs counts, identifiers and resource measurements without the full business payload. I would produce that narrower diagnostic extract and keep any exceptional detailed capture restricted, time-limited and tied to an explicit investigation.

99. How would you investigate an unexpected row-count change?

Reconcile source batches, filters, joins, duplicate handling and transaction outcomes at each stage. Compare counts by business partitions and keys, not just one grand total. A matching total can still hide missing and duplicated rows that cancel numerically.

If a daily total is unchanged, I would still compare counts by source batch, date and business key. One missing customer and one duplicated customer can cancel in a grand total. A migration from a duplicate-permitting target to a different duplicate-handling contract can also change output without a changed filter. I would locate the first stage where expected and actual key-level counts diverge, then inspect join cardinality, rejection records and committed batch markers before rerunning the load.

For a contract requiring one row per customer and event, this read-only fixture query identifies violations. An empty result establishes only that particular uniqueness check, not complete equivalence with the source.

SELECT customer_id, event_id, COUNT(*) AS duplicate_rows
FROM interview_sales
GROUP BY customer_id, event_id
HAVING COUNT(*) > 1

SELECT customer_id, event_id, COUNT(*) AS duplicate_rows
FROM interview_sales
GROUP BY customer_id, event_id
HAVING COUNT(*) > 1

SELECT customer_id, event_id, COUNT(*) AS duplicate_rows
FROM interview_sales
GROUP BY customer_id, event_id
HAVING COUNT(*) > 1

100. How do you validate a cross-platform SQL migration?

Create fixtures for nulls, duplicates, ties, decimal overflow, dates, collations and transaction boundaries, then compare intended results. Translate dialect-specific constructs such as QUALIFY deliberately. Execution-plan equivalence is not required, but semantic and operational requirements must be preserved.

For a latest-row query using QUALIFY ROW_NUMBER(), a target platform may require a subquery around the window calculation. The rewrite must preserve the partition keys, null ordering and deterministic tie-breaker, not just return one row per group. I would also test duplicate retention and numeric expression types, because dialect defaults can change them quietly. Operational equivalence includes how the driver commits and reports failures; identical SELECT results do not establish safe recovery for the surrounding load process.

101. Why do character set and case rules matter?

They affect storage, comparisons, sorting and conversion behavior. A join between values differing only in case may behave differently under case-specific attributes or session defaults. Test representative multilingual and trailing-space cases instead of assuming ASCII-only examples prove compatibility.

If the business treats customer codes abc and ABC as distinct, a comparison using different case rules can merge those identities. Conversely, forcing case-sensitive comparison during migration can lose previously accepted matches. I would inspect column attributes, expression conversions and session behavior, then use fixtures with case variants, trailing spaces and non-ASCII characters. A shared display value is not sufficient proof that storage and equality semantics match, especially when values cross character sets or are cast before joining.

102. How can decimal arithmetic change during migration?

Platforms differ in expression precision, scale propagation, rounding and overflow rules. Cast critical intermediate values explicitly and compare boundary fixtures. Checking only formatted output can conceal a different underlying value that affects later aggregation or reconciliation.

Consider multiplying a quantity by a unit price and then aggregating many rows. Casting only the final total cannot recover precision lost or overflow that occurred in the intermediate expression. I would specify the intended units, intermediate precision and final rounding point, then check the platform's documented type rules for each operation. Boundary tests should include maximum magnitudes, negative amounts and values near a rounding boundary. Two reports displaying the same two decimals may still feed different stored values into the next calculation.

103. How would you build a safe recovery checkpoint?

Store durable batch identity, source position and verified target commit status in a consistent recovery design. A checkpoint must not advance before the corresponding data is committed. Session-local volatile tables are unsuitable as the only evidence needed after a disconnect or restart.

A batch that writes target rows and advances a source offset in separate uncoordinated steps has an ambiguity window. If the offset advances first, a restart can skip unwritten data; if data commits first, a blind replay can duplicate it. I would use a transactionally consistent target marker where possible and make the batch operation idempotent. The checkpoint should identify the exact source slice and schema version as well as the run. A volatile staging table can assist processing but disappears with the session and cannot be the recovery authority.

104. How do you handle partial success across systems?

Define idempotent operations and a durable state machine that can reconcile which external and database steps completed. Use compensation or replay according to business semantics where a distributed atomic transaction is unavailable. Never assume a client timeout proves the database operation failed.

Suppose a database batch commits and the subsequent file publication fails. The recovery action should resume publication or reconcile the already committed batch, not repeat the database insert automatically. I would track explicit states with stable batch identities and make each transition safe to retry. Compensation must follow business meaning: deleting committed rows may be wrong if another consumer has already used them. The design should document what remains unresolved after a communication failure and which evidence determines the next action.

105. What would you do after an uncertain COMMIT outcome?

Query durable batch or transaction markers through a new authorized connection before retrying the business operation. A lost response can occur after successful commit. Recovery should distinguish committed, uncommitted and unresolved states so an automatic replay cannot create duplicate effects.

A client can lose its connection after the server made the transaction durable but before the success response arrived. I would look up the operation's stable batch marker through a fresh connection and reconcile the expected row set. If that evidence confirms completion, the client should report recovered success rather than submit the insert again. If the outcome is still ambiguous, it should remain unresolved until reconciled. A client exception is evidence of communication failure, not proof of rollback.

106. How do you design a slowly changing dimension Type 2 load?

Identify changes at the business key, close the prior effective interval and insert the new version with consistent boundaries. Ensure only the intended current version exists and define handling for late corrections. Apply related changes atomically and validate nonoverlapping intervals for each key.

I would use a clearly defined interval convention, such as [effective_from, effective_to), so an event at the boundary matches exactly one version. If a customer changes category on November 1, the old version ends where the new version begins; the load must not briefly expose two current versions or neither. Late corrections may require splitting an older interval, not merely updating today's row. Validation should check overlaps and duplicate current rows by business key, and the change-detection rule must handle null attributes deliberately.

107. How do temporal features differ from ordinary date columns?

Temporal features can give the database explicit semantics for valid time or transaction time, depending on supported definitions. Ordinary date columns require application logic to enforce those meanings. Choose based on required querying, audit behavior and release support rather than using temporal terminology for any timestamped table.

A valid-time question asks which fact applied in the business world at a chosen time; a transaction-time question asks what the database recorded at a chosen system time. Those are not interchangeable when a late correction arrives. I would decide which history the application must retain and query before choosing supported temporal definitions. Two ordinary timestamp columns can implement an application convention, but they do not automatically receive the database's temporal behavior. Migration tests need late corrections and historical queries, not only the current row.

108. How would you design access for an ETL service account?

Grant only the object and operation privileges needed for its job, using roles and controlled credential management. Separate deployment authority from routine data movement where practical. Test the job under that exact identity so success does not depend on an administrator's broad permissions.

For a daily load, the service may need to read a staging area and modify particular target tables, but not create arbitrary users or grant privileges. I would test the entire recovery path under the service's real role, including access to error tables and required metadata. Granting broad administrator rights to fix one failed step conceals the missing dependency. The credential lifecycle also needs ownership and rotation, while query bands and diagnostic SQL should avoid exposing secret material.

109. What is the difference between a user and a database in Teradata?

Both participate in the database hierarchy and can own objects or space, while a user also supports logon-related capabilities. Ownership is not the same as every effective privilege granted through roles or other mechanisms. Review the actual authorization chain when diagnosing access.

A database can be used as an ownership and space container without acting as a login identity. A user adds authentication-related capabilities, but that distinction does not answer whether a particular request is authorized. I would inspect the requesting identity, active roles, object ownership and relevant grants together. For example, being able to select through a view does not imply direct access to every underlying table. Troubleshooting should establish the effective authorization path rather than infer permissions from a similar object or account name.

110. How would you secure a shared reporting view?

Define the allowed columns and rows, verify underlying access paths and test as representative consumers. Ensure users cannot bypass the view through broader base-table privileges. Changes to view dependencies and policies should be included in regression and privilege reviews.

If a reporting view excludes sensitive columns but consumers retain direct SELECT access on the base table, the view is not an effective access boundary. I would test both the permitted query and the attempted bypass using representative roles. Row restrictions need similar attention, especially where a session identity or policy determines visibility. A later view change that adds a column or alters a join can broaden what is exposed, so the review must cover the effective result and dependencies rather than only whether the view compiles.

111. How do you validate backup and restore readiness?

Test restoration in an approved environment and reconcile recovered data, metadata, privileges and dependent objects against stated recovery objectives. A successful backup job alone does not prove recoverability. Include utility versions, encryption access and operational runbooks in the exercise.

I would restore a representative backup into an approved isolated target and run business reconciliations, not merely inspect the backup utility's success code. Recovery may fail because encryption access, compatible utilities or dependent object definitions are missing even when the data archive is intact. The exercise should measure how long it takes to recover an actually usable service and how much data can be lost within the stated objectives. Those timings belong in the runbook, with ownership for retrieving keys and validating the restored result.

112. What does fallback protect against?

Fallback maintains additional data protection within the database architecture for specified failures, with storage and maintenance costs. It is not a substitute for backups against accidental logical deletion or broader disaster recovery. Confirm the deployment's actual resilience configuration and supported failure scenarios.

Fallback copies can help the database continue or recover from supported physical failures, but an authorized DELETE is a logical change that is reflected in the protected data too. It does not leave an automatically accessible historical version merely because another copy exists. I would explain the exact failure scenario covered by the installed architecture and the separate backup or recovery process for accidental changes. Secondary index protection also follows documented rules; it should not be assumed independently of the parent table or join index's fallback configuration.

113. How do you plan for maintenance without breaking service objectives?

Inventory workloads and dependencies, estimate locking and resource impacts, and schedule changes with tested rollback or recovery procedures. Communicate the expected effects and monitor objective thresholds. A nominally online operation can still consume enough capacity to disrupt concurrent requests.

A statistics refresh, index build or data redistribution may leave tables available but still consume substantial shared I/O or CPU. I would define a measured stop condition, identify affected workload windows and verify whether cancellation or rollback itself can take significant time. Dependency checks should include loaders and scheduled reports, not only interactive users. Calling an operation online describes part of its availability behavior; it does not establish that its performance impact is harmless during a peak processing period.

114. How would you test a release upgrade?

Use representative SQL, utilities, drivers and workload policies against a compatible test environment. Compare correctness, plans, resource use and error handling, including edge cases and recovery paths. Review documented behavior changes instead of assuming a passing connection test establishes application compatibility.

I would replay a representative workload and compare result semantics first, then investigate important plan changes rather than require every plan to remain identical. Better estimates can legitimately produce a different plan. The test set should include TPT jobs, driver settings, error handling and restart after interruption, because SQL compatibility alone is too narrow. Documented changes in supported utilities, optimizer behavior or deployment features should drive targeted tests. A successful login only proves that the connection path works, not that the application remains correct or recoverable.

115. Why should client autocommit settings be recorded?

They influence when requests become durable and how long locks persist, interacting with session transaction mode. Different tools may submit the same SQL under different boundaries. Record driver and connection settings in reproducible tests and make critical transaction expectations explicit.

In ANSI mode, transaction boundaries and the client's autocommit behavior determine whether later statements can still be rolled back with an earlier change. One tool may commit after each request while another keeps the transaction open, so the same script can hold locks or expose partial progress differently. I would record the transaction mode, driver version and autocommit setting with the test. DDL also has specific ANSI transaction placement rules; a reproduction should preserve those boundaries instead of adding commits until the error disappears.

116. How do you evaluate VantageCloud deployment differences?

Check documentation for the specific edition and release, including storage, compute, utility support and operational responsibilities. Similar SQL syntax does not imply identical capacity accounting or physical design options. Validate recommendations in the actual deployment rather than transferring an appliance tuning rule unchanged.

I would first establish the named deployment and database release, then confirm which documented storage, compute and utility behaviors apply. Advice derived from an Enterprise or IntelliFlex system may not translate unchanged to a Lake configuration with different operational responsibilities. The relevant comparison includes resource accounting, supported object options and how workloads are isolated or scaled. A portable SQL query can still require different physical-design and capacity decisions. I would state the environment behind any recommendation and validate the actual workload there.

117. How would you troubleshoot a resource regression after data growth?

Compare growth by partition, key frequency and row width with changes in estimates and per-AMP resource use. A small increase in total rows can cross a plan or skew threshold. Target the changed distribution or access pattern rather than extrapolating from total table size alone.

A table growing by five percent can slow a join dramatically if nearly all new rows share one key or fall into the partition queried most often. Total row count alone hides that concentration. I would compare key frequencies, selected partition sizes, row widths and optimizer estimates with the earlier successful run. A previously small duplicated input may also cross a practical resource threshold. The remedy should address the changed distribution or estimate rather than assume every resource requirement scales smoothly with total rows.

118. How should a DBA communicate a risky tuning recommendation?

State the observed bottleneck, proposed change, expected benefit and measurable downside, with a controlled validation and rollback plan. Distinguish evidence from assumptions. For example, changing distribution may improve a major join while worsening tactical lookups and requiring a significant reload.

For a primary-index change, I would show the major join whose redistribution is expected to decrease and the tactical lookup that may become more expensive. I would quantify reload space and cutover time as well as the anticipated query benefit, distinguishing measured evidence from prediction. The proposal needs representative acceptance queries and a rollback strategy that accounts for post-cutover writes. A recommendation is easier to assess when it explains which workload pays the cost and what observation would cause the team to stop or revise it.

119. What demonstrates strong senior Teradata judgment?

A strong candidate connects SQL correctness, distributed execution, physical design and operational recovery. They use plans and runtime evidence, respect deployment differences, and avoid destructive experiments on production. Their proposed solution includes a way to verify business results and recover safely when assumptions fail.

I would give the candidate a query with a hot join key, a spool error and an uncertain load commit, then ask them to separate the problems. A strong response does not immediately raise spool, redesign the primary index or replay the batch. It identifies the failing execution step, preserves duplicate semantics and resolves the commit outcome from durable evidence. The final proposal should explain both the distributed-performance mechanism and the operational consequence, including what can be tested safely before changing a shared production system.

120. How would you evaluate compression for a large table?

Compare supported compression options against value repetition, row width, CPU overhead and actual storage or I/O savings. Include load and query workloads in the measurement, because smaller storage does not guarantee lower elapsed time. Verify release and storage-format support before recommending a feature, and preserve exact data semantics.

A low-cardinality status column with repeated values is a different compression opportunity from an already compact field of varied numeric values. I would identify the supported method for the deployment and measure storage, scan I/O and CPU together. A reduction in persisted bytes may help an I/O-bound workload but does not guarantee a faster CPU-bound query. Load and update costs also matter. Exact comparisons, null behavior and returned values must remain unchanged; compression should not be confused with rounding or discarding information to make the table smaller.

Teradata Documentation and Further Reading

Use these official Teradata references for syntax, architecture and operational details. Check each page’s edition and release against the environment being discussed.

Team Sherlock.sh

Building fraud intelligence for live high-stake video interactions (financial discussions, hiring interviews, board meeting etc).

Catch candidate fraud before it costs you.

Sherlock AI helps you detect AI-assisted, proxy, and deepfake candidates, so every hiring decision is backed by evidence.

Ask AI about Sherlock.sh

Similar Posts