Question 1

What is the difference between ETL and ELT?

Accepted Answer

ETL (Extract, Transform, Load) : Transform data before loading into the target Transformation happens in a separate compute layer (Spark, Informatica, SSIS) Traditional approach — used when target systems have limited compute ELT (Extract, Load, Transform) : Load raw data first , transform inside the target Modern cloud warehouses (Snowflake, BigQuery, Redshift, Synapse) have enough compute to transform in place Enables re transformation without re ingestion ETL ELT Transform location Staging/middleware Target warehouse Raw data kept No Yes (in lake) Flexibility Lower (fixed transform) Higher (re run transforms) Tools Informatica, SSIS, Spark dbt, Spark SQL, BigQuery Modern pattern : ELT + Medallion architecture — load raw to Bronze, transform to Silver/Gold inside Databricks/Synapse.

Question 2

How do you design a data pipeline for near real-time analytics?

Accepted Answer

Near real time means latency of 1–15 minutes (not milliseconds). Architecture : Key choices : Micro batch : simpler than true streaming, sufficient for 5 min latency. Use trigger(processingTime="5 minutes") Delta Lake MERGE : idempotent writes — safe to replay on failure Autoloader : monitors new files in Bronze and incrementally processes them for Silver — no manual backfill Power BI DirectQuery : queries live Gold table — no import refresh delay Guardrails : set maxOffsetsPerTrigger to prevent micro batches from growing too large; checkpoint to ADLS/S3 for restart recovery.

Question 3

What is data lineage and why is it important?

Accepted Answer

Data lineage tracks the complete journey of data — where it came from, what transformations were applied, and where it landed. Column level lineage example : report.total revenue ← gold.orders.net amount ← silver.orders.quantity silver.orders.unit price ← raw.orders v2.quantity, raw.orders v2.price . Why it matters : Debugging : when a dashboard number is wrong, trace it back to the root cause in seconds rather than hours Impact analysis : if I rename orders.amount to orders.net amount , which 47 reports and pipelines break? Compliance (GDPR/HIPAA) : prove to auditors exactly how PII data flows through the system Trust : data consumers understand what transformations were applied before trusting the output Tools : Databricks Unity Catalog : column level lineage automatically captured dbt : DAG based transformation lineage Apache Atlas : lineage for Hadoop/Hive ecosystems Microsoft Purview : Azure native data catalog + lineage

Question 4

Explain the concept of surrogate keys in data warehouses.

Accepted Answer

A surrogate key is a system generated, meaningless integer used as the primary key in a dimension table — distinct from the business/natural key from the source system. Why use surrogate keys : SCD Type 2 : same customer id can have multiple rows (historical versions); surrogate key uniquely identifies each version Performance : integer joins are faster than string joins Independence : isolates DWH from source system changes (e.g., source renumbers customer IDs) Cross system merges : when merging data from multiple source systems with overlapping natural keys Hash keys (Data Vault approach): deterministic hash of business key — enables parallel loading without a central sequence generator.

Question 5

What are PolyBase external tables in Synapse?

Accepted Answer

PolyBase allows Synapse to query data stored externally (ADLS, Blob Storage, Hadoop) as if it were a regular SQL table — without physically loading the data into Synapse. Setup : Use cases : Query raw files without loading into dedicated SQL pool (cheaper) CETAS (Create External Table As Select): write query results back to ADLS as Parquet Bridge between Spark written Delta/Parquet files and SQL analytics

Question 6

How do you manage secrets securely in cloud pipelines?

Accepted Answer

Never hardcode credentials — store them in a secret management service: Azure Key Vault + ADF : Databricks + Key Vault : AWS Secrets Manager (similar pattern): Best practices : rotate secrets regularly, audit access logs, use Managed Identity where possible (no credentials at all), grant least privilege access.

Question 7

Explain Medallion architecture.

Accepted Answer

Medallion architecture organizes a data lakehouse into three progressive quality layers: Bronze (Raw) : Exact copy of source data — no transformation Append only; never delete historical data Enables full reprocessing if downstream logic changes Formats: JSON, CSV, Avro, or Delta with minimal schema Silver (Cleaned/Validated) : Deduplicated, parsed, type cast, null handled Joined with reference data where necessary Schema enforced; bad records routed to dead letter Target: one curated table per source entity Gold (Business ready) : Pre aggregated KPIs, dimensional models (star schema) Domain specific — separate gold tables for Finance, Marketing, Operations Optimized for BI tools (Z ordered, compacted, materialized) Lowest latency for end user queries Data flow : Bronze → Silver (Spark batch/streaming) → Gold (Airflow orchestrated aggregations) Benefits : separation of concerns, incremental reprocessing possible at any layer, clear data quality SLAs per layer.

Question 8

What is event-driven data ingestion?

Accepted Answer

Event driven ingestion triggers pipeline processing in response to events rather than on a fixed schedule. Examples : New file uploaded to S3 → S3 Event Notification → Lambda → trigger Glue job Kafka message arrives → Spark Streaming processes it immediately New blob in ADLS → Event Grid notification → ADF trigger → pipeline starts ADF Storage Event Trigger : vs schedule based : Event driven : lower latency, efficient (runs only when there's data), scales naturally Schedule based : simpler, predictable, better for batch aggregations Best for : irregular data arrival (partner feeds, IoT bursts), near real time requirements, or when you want to avoid polling on an empty source.

Coforge Interview Questions