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....

Question 9

SQL Query Optimization techniques

Accepted Answer

Optimization: indexes on filter/join columns; avoid SELECT *; use EXPLAIN; replace subqueries with JOINs; partition large tables; materialized views; update statistics; avoid functions on columns; use IN instead of multiple OR; limit result sets; consider query rewrite. **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 10

Snowflake: Types of Caching, Time Travel vs. Fail-safe, Snowpipe, Materialized Views

Accepted Answer

Snowflake caching: metadata, query result (per warehouse), local disk. Time Travel: historical data, default 1 day (configurable up to 90). Fail-safe: 7-day backup after Time Travel. Snowpipe: continuous load from S3/GCS/Blob. Materialized views: pre-computed, auto-refresh. Use: cache for repeated queries; Time Travel for recovery; Snowpipe for streaming load. **Why it matters**: Design choices compound at scale—wrong approach can cause 100× overhead....

Kaseya Data Engineer Interview Questions

Difficulty Breakdown

Key Topics Covered

How to Use This Guide

Companies asking these questions

All 12 Questions

More Interview Prep Guides

Unlock All Expert Answers