Q: How do you keep a specific column on top in SQL?

**SELECT**: List column first: SELECT priority_col, col2, col3 FROM t. **View**: CREATE VIEW v AS SELECT priority_col, col2, col3 FROM t. **Table**: No guaranteed physical order in SQL model. Presentation layer (BI, app) controls display order.

Question 1

Tell me about yourself and your experience.

Accepted Answer

**Situation**: I joined the data org when our pipelines were monolithic, causing 4+ hour delays and frequent outages affecting downstream dashboards and ML models.

**Task**: I was tasked with redesigning the data platform to support real-time decisioning while improving reliability and cost efficiency.

**Action**: I led a cross-functional team of 5 engineers to architect a medallion (Bronze/Silver/Gold) architecture on Delta Lake....

Question 2

Explain the concept of checkpointing in Spark and why it is important.

Accepted Answer

Checkpointing breaks RDD lineage by materializing to storage, trading storage I/O for DAG truncation. **Why it exists**: Long lineage (e.g., 1000+ stages in iterative algorithms like PageRank or ML) causes stack overflow in the driver and makes fault recovery expensive—Spark would replay the entire DAG. **Scalability trade-off**: Checkpoint is a full write; at scale, this is costly (network, disk). For batch, prefer persist/cache when lineage is < ~50 stages....

Question 3

How do you drop columns with null values in PySpark?

Accepted Answer

Two distinct operations: (1) **Drop columns that are entirely null** (no non-null values): null_cols = [c for c in df.columns if df.filter(col(c).isNotNull()).count() == 0]; df = df.drop(*null_cols). **Caveat**: count() triggers a full scan—expensive on large tables. (2) **Drop rows with null in specified columns**: df.dropna(subset=["col1", "col2"]). **Scalability**: The column-null check is O(partitions × columns) and can be costly; consider sampling or inferring from schema/sample....

Question 4

Explain Dynamic Partition Pruning error and how to fix it.

Accepted Answer

**Architectural Logic**: DPP in Spark pushes dimension filters to fact table to skip partitions—type mismatch or missing broadcast breaks it. **Mechanism**: When dimension is filtered and broadcast, Spark pushes partition filter to fact. **Error Causes**: Partition column type mismatch (e.g., INT vs STRING); filter not pushed; broadcast hint missing. **Fix**: (1) Align join key types with partition column. (2) `broadcast(dim_df)` for small dimension....

Question 5

How do you convert 3 rows into one column in SQL?

Accepted Answer

**STRING_AGG** (PostgreSQL): SELECT STRING_AGG(col, ', ' ORDER BY id) FROM (SELECT col FROM t LIMIT 3) sub. **LISTAGG** (Oracle), **GROUP_CONCAT** (MySQL), **ARRAY_AGG** (returns array). For exactly 3 rows: LIMIT 3 in subquery. **Determinism**: ORDER BY in agg....

Question 6

How do you count occurrences in a column in SQL?

Accepted Answer

**Per value**: SELECT col, COUNT(*) FROM t GROUP BY col. **Specific value**: SELECT COUNT(*) FROM t WHERE col = 'X'. **Non-null**: COUNT(col) excludes NULLs; COUNT(*) counts all. **Distinct**: COUNT(DISTINCT col). **Conditional**: COUNT(CASE WHEN cond THEN 1 END)....

Question 7

How do you keep a specific column on top in SQL?

Accepted Answer

**SELECT**: List column first: SELECT priority_col, col2, col3 FROM t. **View**: CREATE VIEW v AS SELECT priority_col, col2, col3 FROM t. **Table**: No guaranteed physical order in SQL model. Presentation layer (BI, app) controls display order.

Question 8

Explain read and write modes in Spark.

Accepted Answer

**Why it matters**: At scale, design choices directly impact reliability, latency, and cost. Wrong decisions compound across jobs and teams.

Spark read modes: spark.read loads data (default overwrites for file sources). Write modes: (1) overwrite—replaces target; (2) append—adds data; (3) ignore—no-op if exists; (4) errorIfExists—fail if exists. Example: df.write.mode('append').parquet(path). For Delta: overwriteSchema; replaceWhere for conditional overwrite....

Globant Data Engineer Interview Questions

+12 More Questions with Expert Answers

Other Companies

Globant Data Engineer Interview Questions

+12 More Questions with Expert Answers

Other Companies