Question 1

Explain the types of triggers in ADF, including schedule, tumbling window, and event-based triggers.

Accepted Answer

Schedule: Fixed cadence (cron, every N mins). Predictable batch windows; simple ops. Tumbling window: Fixed non-overlapping intervals; fires once per window. Ideal for idempotent, exactly-once semantics—no overlap means no double-processing. Event-based: Fires on blob created, queue message, etc. Enables near real-time pipelines....

Question 2

Can you chain multiple triggers for a single pipeline?

Accepted Answer

**ADF/Synapse:** Pipeline can have multiple triggers (schedule, storage, manual). Trigger chaining: Pipeline A completion triggers B via trigger dependency. Avoid cycles.

**Best Practice:** Use parameters for flexibility. Monitor trigger runs....

Question 3

Can you provide a use case where Assert Transformations helped maintain data quality?

Accepted Answer

**Use Case:** ETL loading customer data. Assert: email NOT NULL, email regex, age 0–120. Fail pipeline if any row violates. Prevents bad data in warehouse.

**Tools:** dbt tests (unique, not_null, relationships). Great Expectations. Assert early; actionable messages. Separate assertion step for debugging.

**Result:** Caught 500 invalid emails in first run; fixed source system....

Question 4

How do partitioning strategies differ between source and sink?

Accepted Answer

**Source**: How data is stored—S3 path (dt=2024-01-01/), Kafka topic partition. **Sink**: How we write—table partition columns, file layout. **Mismatch**: Source flat, sink partitioned—repartition in pipeline. Source partitioned, sink different key—repartition. **Align when possible**: Same partition key = partition pruning on read. **Over-partitioning sink**: Many small files; coalesce....

Question 5

How do tumbling window triggers ensure data consistency in batch processing?

Accepted Answer

**Tumbling windows**: Fixed, non-overlapping intervals. Each batch processes one complete window; no overlap = each record in exactly one batch. **Trigger**: Fires when window completes (e.g., watermark passes window end). **Consistency**: Exactly-once or at-least-once per window; idempotent sink replays safely....

Question 6

How do you integrate with an on-premises SQL Server without using SHIR?

Accepted Answer

**Without SHIR**: (1) **VPN/ExpressRoute**: Connect cloud to on-prem network; use private SQL Server IP from cloud. (2) **Public endpoint**: Not recommended—security risk. (3) **Export/Load**: SSIS or script exports to blob; cloud job loads. (4) **Third-party**: Fivetran, Striim with on-prem agent. (5) **Reverse proxy**: On-prem proxy exposes limited endpoint. **Trade-off**: VPN adds latency; export is batch-only....

Question 7

What are Assert Transformations, and where are they used?

Accepted Answer

Assert transformations validate data quality within a pipeline. They check conditions (e.g., no nulls, value ranges, referential integrity) and fail the pipeline if violated. Used in: dbt (schema and data tests), Great Expectations, custom Spark/Python checks. Example—dbt: tests: - unique: user_id - not_null: email. In Spark: df.filter("amount < 0").count() and assert == 0....

Virtusa SQL Interview Questions

Companies asking these questions

All 7 Questions

More Interview Prep Guides

Get All Answers in PDF Format

Virtusa SQL Interview Questions

Companies asking these questions

All 7 Questions

More Interview Prep Guides

Get All Answers in PDF Format