Question 1

What is a CTE (Common Table Expression)? What are its uses?

Accepted Answer

**Architectural Logic:** A CTE is a named temporary result set (WITH ... AS) scoped to a single statement. **Why use CTEs:** Readability, modular logic, recursion (hierarchies), and avoiding repeated subquery evaluation in some engines. **Scalability:** CTEs may be inlined or materialized—behavior varies by engine. In BigQuery/Snowflake, CTEs are typically not materialized unless hinted; repeated references can re-execute. **Cost:** Materialized CTEs (e.g., WITH ......

Question 2

Explain the Medallion Architecture (Bronze, Silver, Gold layers).

Accepted Answer

**Section 1 — The Context (The 'Why')**
Medallion addresses tension between raw preservation (audit) and curated analytics. Bronze retains everything; Silver/Gold add structure.

**Section 2 — The Diagram**
```
[Sources] --> [Bronze] --> [Silver] --> [Gold]
  Append    Dedup/Merge   Marts
```

**Section 3 — Component Logic**
**Bronze** raw append-only, immutable. **Silver** schema, dedup, Delta MERGE. **Gold** star schemas. **TTL policies** move Bronze to Glacier....

Question 3

Explain the components of ADF: Pipelines, Activities, Linked Services, Datasets, Triggers, and Integration Runtimes

Accepted Answer

Architecture: Linked Services (connections) → Datasets (data refs) → Pipelines (orchestration) → Activities (Copy, Lookup, etc.) → Triggers (schedule/event) → IR (Azure/SHIR/SSIS) executes. Flow: Trigger → Pipeline → Activities use Datasets via Linked Services; IR runs. Why each: Separation of connection, data, and orchestration....

Question 4

Explain the differences between Azure IR, Self-hosted IR, and Azure-SSIS IR

Accepted Answer

Architectural difference: Azure IR = managed, cloud-only. SHIR = on-prem/VNet, self-maintained. SSIS IR = runs SSIS packages. Use Azure IR for cloud-to-cloud. SHIR for on-prem, private network. SSIS for legacy package migration. Trade-off: Azure IR = no ops; SHIR = maintenance, network access....

Question 5

APPLY Operator - CROSS APPLY and OUTER APPLY

Accepted Answer

**CROSS APPLY**: Invokes table-valued function/subquery per row; like INNER JOIN—excludes rows where function returns nothing. **OUTER APPLY**: Like LEFT JOIN—keeps all left rows, NULL where function returns nothing. **Use**: Correlated subqueries, JSON parsing, string splitting. **PostgreSQL**: LATERAL JOIN (CROSS JOIN LATERAL, LEFT JOIN LATERAL). **Performance**: Can be expensive—row-by-row. Prefer set-based when possible....

Question 6

Error Handling in T-SQL - TRY...CATCH, THROW, RAISEERROR

Accepted Answer

**Architectural Logic**: Error handling affects debuggability and caller experience. **TRY-CATCH**: Captures errors in block; enables cleanup or logging. **THROW** (SQL 2012+): Raises error; preserves call stack; use in CATCH to rethrow. **RAISERROR**: Legacy; custom messages; doesn't preserve stack. **Example**: `BEGIN TRY; INSERT ...; END TRY BEGIN CATCH; THROW; END CATCH`. **Why THROW**: Better diagnostics; RAISERROR is deprecated for new code....

Question 7

Normalization: 1NF, 2NF, 3NF with examples

Accepted Answer

1NF: atomic values, no repeating groups. 2NF: 1NF + no partial dependencies (all non-key attributes depend on full key). 3NF: 2NF + no transitive dependencies (non-key depends only on key). Example: Orders(order_id, customer_id, customer_name) violates 3NF—customer_name depends on customer_id; split to Orders, Customers. BCNF: every determinant is candidate key. **Why it matters**: Design choices compound at scale—wrong approach can cause 100× overhead....

Question 8

PIVOT Operator in T-SQL

Accepted Answer

T-SQL PIVOT: rotates rows to columns. SELECT * FROM (SELECT product, region, sales FROM raw) t PIVOT (SUM(sales) FOR region IN ([North],[South],[East],[West])) pvt. Requires aggregation. UNPIVOT does reverse. Best: ensure known column set or use dynamic SQL for variable columns. **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....

Kaseya Data Engineer Interview Questions

Reading isn't practice. Get AI feedback on your answers.

Other Companies

Kaseya Data Engineer Interview Questions

Reading isn't practice. Get AI feedback on your answers.

Other Companies