Q: Explain different types of Slowly Changing Dimensions.

SCD Type 0 — Fixed : attribute never changes (e.g., original signup date). No action on source change. SCD Type 1 — Overwrite : update the record in place, no history kept. Use when historical value is irrelevant (e.g., correcting a typo). SCD Type 2 — Add new row (most common): expire current record, insert new one. Full history preserved. SCD Type 3 — Add column : add previous value column alongside current. Limited history (only one prior value). SCD Type 6 — Hybrid : combines Type 1 + 2 + 3 — current row always has the current value AND history rows for point in time queries.

Q: What are the differences between Data Warehouse and Data Mart?

Data Warehouse : Centralized repository for the entire enterprise Contains data from all business domains (Finance, Sales, HR, Operations) Managed by a central data engineering team Comprehensive but complex — many tables, many joins Data Mart : Subject specific subset for one domain or team Example: Marketing Data Mart — campaigns, leads, conversions, customer acquisition cost Simpler, pre joined tables optimized for the domain's queries Faster queries (less data, fewer joins) Types : Dependent data mart : sourced from the central DWH — consistent, single source of truth Independent data mart : sourced directly from operational systems — faster to build, risks inconsistency Modern equivalent : Gold layer tables in Medallion architecture serve as domain specific data marts — Finance Gold, Marketing Gold — while the DWH is the Silver/curated layer. At Accenture : large enterprise clients often have a central DWH (Snowflake/Synapse) with multiple dependent marts per business unit.

Q: What are Delta Live Tables?

Delta Live Tables (DLT) is a declarative framework in Databricks for building reliable, maintainable ETL pipelines — replacing hand crafted Spark streaming/batch code. Benefits : Automatic dependency management between tables Built in data quality expectations with @dlt.expect Automatic CDC with APPLY CHANGES INTO Lineage and observability out of the box Handles both batch and streaming in one framework

Q: How do you manage pipeline dependencies?

Within Airflow DAG (most common for batch): Cross DAG dependencies (Airflow): Data aware scheduling (Airflow 2.4+ datasets): Databricks Workflows : define job clusters with task dependencies, task level retries, and conditional execution (run task B only if task A succeeds). Best practices : make each task idempotent (safe to retry), log run metadata (run id, rows processed) to a control table for dependency tracking across teams.

Q: What is Unity Catalog?

Unity Catalog (UC) is Databricks' centralized governance layer for data and AI assets — providing unified access control, data lineage, and discovery across all Databricks workspaces. Three level namespace : catalog.schema.table (e.g., prod.finance.fact revenue ) Key capabilities : Unified access control : GRANT/REVOKE at catalog, schema, table, column, or row level — one place for all permissions Column level security : mask PII columns based on user group Row level security : users see only their region's data Data lineage : automatic column level lineage across notebooks, queries, and pipelines Audit logs : every data access logged — who, when, what query Cross workspace : share catalogs across dev/staging/prod workspaces External locations : register ADLS/S3 paths as external locations — control which catalogs can access which storage paths.

Q: How do you validate ETL pipeline results?

Pre load validation (before writing to target): Post load reconciliation : Great Expectations (automated, reusable): On failure : block downstream tasks via Airflow BranchPythonOperator ; alert data owners; log to validation runs table.

Q: What is checkpointing in Spark Streaming?

Checkpointing persists the streaming query state (Kafka offsets + aggregation state) to durable storage so a failed job can resume from where it left off — not from the beginning. Two types : 1. Metadata checkpointing : saves query progress (offsets consumed) 2. Data checkpointing : saves aggregation state for stateful operations (windowed aggregations, mapWithState ) What's saved in the checkpoint : Last committed Kafka partition offsets Watermark state for late data handling Windowed aggregation intermediate results Recovery : on restart, Spark reads the checkpoint, discovers the last committed offset, and resumes consuming from that point — guarantees exactly once processing when combined with idempotent writes. Important : checkpoint location must be unique per streaming query — two queries sharing a checkpoint will corrupt each other's state.

Question 1

Explain your approach to building cloud data pipelines.

Accepted Answer

Discovery : understand source systems, data volume, SLA requirements, and downstream consumers before writing any code. Architecture choice : Batch (hourly/daily): ADF + Databricks / Glue + Spark Near real time (<15 min): Spark Structured Streaming + Event Hub/Kafka Real time (<1 min): Flink or Kafka Streams Standard layers (Medallion): Bronze: ingest raw, append only Silver: deduplicate, validate, enrich Gold: aggregate, model for consumption Reliability practices : Idempotent writes (MERGE over INSERT) Checkpointing for streaming jobs Row count validation at each layer Dead letter queue for bad records Observability : centralized logging to Log Analytics/CloudWatch; Airflow/Databricks Workflows for scheduling; alerting on job failure and SLA breach. CI/CD : all code in Git; automated unit tests; deploy via Azure DevOps / GitHub Actions; separate dev/staging/prod environments.

Question 2

Explain different types of Slowly Changing Dimensions.

Accepted Answer

SCD Type 0 — Fixed : attribute never changes (e.g., original signup date). No action on source change. SCD Type 1 — Overwrite : update the record in place, no history kept. Use when historical value is irrelevant (e.g., correcting a typo). SCD Type 2 — Add new row (most common): expire current record, insert new one. Full history preserved. SCD Type 3 — Add column : add previous value column alongside current. Limited history (only one prior value). SCD Type 6 — Hybrid : combines Type 1 + 2 + 3 — current row always has the current value AND history rows for point in time queries.

Question 3

What are the differences between Data Warehouse and Data Mart?

Accepted Answer

Data Warehouse : Centralized repository for the entire enterprise Contains data from all business domains (Finance, Sales, HR, Operations) Managed by a central data engineering team Comprehensive but complex — many tables, many joins Data Mart : Subject specific subset for one domain or team Example: Marketing Data Mart — campaigns, leads, conversions, customer acquisition cost Simpler, pre joined tables optimized for the domain's queries Faster queries (less data, fewer joins) Types : Dependent data mart : sourced from the central DWH — consistent, single source of truth Independent data mart : sourced directly from operational systems — faster to build, risks inconsistency Modern equivalent : Gold layer tables in Medallion architecture serve as domain specific data marts — Finance Gold, Marketing Gold — while the DWH is the Silver/curated layer. At Accenture : large enterprise clients often have a central DWH (Snowflake/Synapse) with multiple dependent marts per business unit.

Question 4

What are Delta Live Tables?

Accepted Answer

Delta Live Tables (DLT) is a declarative framework in Databricks for building reliable, maintainable ETL pipelines — replacing hand crafted Spark streaming/batch code. Benefits : Automatic dependency management between tables Built in data quality expectations with @dlt.expect Automatic CDC with APPLY CHANGES INTO Lineage and observability out of the box Handles both batch and streaming in one framework

Question 5

How do you manage pipeline dependencies?

Accepted Answer

Within Airflow DAG (most common for batch): Cross DAG dependencies (Airflow): Data aware scheduling (Airflow 2.4+ datasets): Databricks Workflows : define job clusters with task dependencies, task level retries, and conditional execution (run task B only if task A succeeds). Best practices : make each task idempotent (safe to retry), log run metadata (run id, rows processed) to a control table for dependency tracking across teams.

Question 6

What is Unity Catalog?

Accepted Answer

Unity Catalog (UC) is Databricks' centralized governance layer for data and AI assets — providing unified access control, data lineage, and discovery across all Databricks workspaces. Three level namespace : catalog.schema.table (e.g., prod.finance.fact revenue ) Key capabilities : Unified access control : GRANT/REVOKE at catalog, schema, table, column, or row level — one place for all permissions Column level security : mask PII columns based on user group Row level security : users see only their region's data Data lineage : automatic column level lineage across notebooks, queries, and pipelines Audit logs : every data access logged — who, when, what query Cross workspace : share catalogs across dev/staging/prod workspaces External locations : register ADLS/S3 paths as external locations — control which catalogs can access which storage paths.

Question 7

How do you validate ETL pipeline results?

Accepted Answer

Pre load validation (before writing to target): Post load reconciliation : Great Expectations (automated, reusable): On failure : block downstream tasks via Airflow BranchPythonOperator ; alert data owners; log to validation runs table.

Question 8

What is checkpointing in Spark Streaming?

Accepted Answer

Checkpointing persists the streaming query state (Kafka offsets + aggregation state) to durable storage so a failed job can resume from where it left off — not from the beginning. Two types : 1. Metadata checkpointing : saves query progress (offsets consumed) 2. Data checkpointing : saves aggregation state for stateful operations (windowed aggregations, mapWithState ) What's saved in the checkpoint : Last committed Kafka partition offsets Watermark state for late data handling Windowed aggregation intermediate results Recovery : on restart, Spark reads the checkpoint, discovers the last committed offset, and resumes consuming from that point — guarantees exactly once processing when combined with idempotent writes. Important : checkpoint location must be unique per streaming query — two queries sharing a checkpoint will corrupt each other's state.

Accenture Interview Questions

Explain your approach to building cloud data pipelines.

Explain different types of Slowly Changing Dimensions.

What are the differences between Data Warehouse and Data Mart?

What are Delta Live Tables?

How do you manage pipeline dependencies?

What is Unity Catalog?

How do you validate ETL pipeline results?

What is checkpointing in Spark Streaming?