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

Companies asking these questions

All 12 Questions

More Interview Prep Guides

Get All Answers in PDF Format

Kaseya Data Engineer Interview Questions

Companies asking these questions

All 12 Questions

More Interview Prep Guides

Get All Answers in PDF Format