Question 1

What is the difference between partitioning and bucketing in Spark, and when would you use bucketing?

Accepted Answer

**Partitioning**: Physically divides data by column values (e.g., date, region); enables partition pruning; one directory per partition value. **Bucketing**: Divides data within a partition into a fixed number of files via hash of bucketing column(s); co-locates same-key rows. **When to bucket**: Frequent joins or group-bys on a column (e.g., user_id). Same bucket count on both sides enables sort-merge join without shuffle....

Question 2

What is a window function? Explain with an example.

Accepted Answer

**Architectural Logic:** Window functions compute values over a set of rows related to the current row (defined by OVER) without collapsing rows like GROUP BY. **Why windows over self-joins:** Single scan, no explosion; optimal for rankings, running totals, period-over-period. **Scalability:** PARTITION BY and ORDER BY affect sort/spill; large windows with RANGE BETWEEN UNBOUNDED PRECEDING can spill to disk. **Cost:** ROWS vs....

Question 3

How to view Oozie jobs?

Accepted Answer

**CLI**: oozie jobs -oozie http://host:11000/oozie -filter status=RUNNING. **Web UI**: http://oozie-host:11000/oozie. **Job info**: oozie job -info <job_id>. **Filter**: -filter name=value for status, user, etc. **Coordinator**: oozie jobs -jobtype coord....

Question 4

Oozie join condition?

Accepted Answer

Oozie join: control node that waits for multiple job completions before proceeding. Join runs when all parent nodes complete. Use for fork-join workflows. Example: workflow has branch A and B; join waits for both; then run C. Condition: typically no condition—pure sync. For conditional branching use decision node. **Why it matters**: Design choices compound at scale—wrong approach can cause 100× overhead. **Scalability trade-offs**: Profile before optimizing; validate on sample then full....

Question 5

Partitioning a table with card details and transactions?

Accepted Answer

Partition card/transaction tables by: (1) date (transaction_date)—most common for time-series. (2) card_id range or hash for even distribution. (3) Composite: date + card_type. Avoid over-partitioning. Example: PARTITION BY RANGE (transaction_date) or LIST (card_type). For billions of rows: date partition + sub-partition by card hash. **Why it matters**: Design choices compound at scale—wrong approach can cause 100× overhead....

Question 6

Teradata to Hadoop migration and handling data with SCD Type 2?

Accepted Answer

Teradata to Hadoop migration with SCD Type 2 requires careful planning. Key steps: (1) Schema mapping—map Teradata tables to Hive/Spark equivalents; handle Teradata-specific functions. (2) SCD Type 2 logic—maintain current and historical rows using effective_date, effective_end_date, and is_current flag. In Spark: use MERGE (Delta) or upsert logic with window functions to detect changes. (3) Migration strategy—batch historical load first, then CDC for ongoing changes....

Question 7

What is a Kafka topic, and how do you choose the number of partitions for it?

Accepted Answer

Kafka topic: Category/stream of messages. Partitions enable parallelism—each partition is an ordered log. Choosing partition count: (1) Throughput—more partitions = more parallel consumers. (2) Consumer count—partitions ≥ consumer instances. (3) Ordering—same key goes to same partition; balance parallelism vs key cardinality. (4) Retention—more partitions = more segments. Rule: Start with expected peak throughput / single partition throughput; typically 6–20 for moderate topics....

Question 8

What is the role of a partition in Kafka, and how does it impact scalability?

Accepted Answer

Kafka partition: Ordered log shard within a topic. Messages with same key go to same partition. Role in scalability: (1) Parallelism—consumers read partitions in parallel. (2) Ordering—per partition. (3) Throughput—more partitions = more producers/consumers. (4) Consumer scaling—max consumers = partition count. Trade-off: More partitions = more broker metadata and files. Best practice: Partition count ≥ max consumers; use keys for ordering when needed....

Citi SQL Interview Questions

Companies asking these questions

All 8 Questions

More Interview Prep Guides

Get All Answers in PDF Format

Citi SQL Interview Questions

Companies asking these questions

All 8 Questions

More Interview Prep Guides

Get All Answers in PDF Format