PwC Interview Questions
ADF is Azure's cloud ETL service for orchestrating data movement and transformation. Key components: Pipelines (workflow), Copy Activity (data movement)...
Explain your experience with Azure Data Factory and ETL development.
ADF is Azure's cloud ETL service for orchestrating data movement and transformation. Key components: Pipelines (workflow), Copy Activity (data movement), Mapping Data Flows (Spark based transformations), Linked Services (connections), and Triggers (scheduling). Typical usage: extract from Azure SQL/Blob → transform in Data Flow or Databricks → load into Synapse Analytics.
How do you design a data model for reporting?
Use a star schema : a central fact table (measures like revenue, quantity) linked to dimension tables (date, customer, product). Keep dimensions denormalized for query performance. Add surrogate keys, handle SCD Type 2 for historical tracking, and create aggregate fact tables for commonly queried KPIs.
Difference between OLTP and OLAP.
OLTP (Online Transaction Processing): normalized schema, optimized for high volume inserts/updates, row oriented, used for operational systems (banking, e commerce). OLAP (Online Analytical Processing): denormalized star/snowflake schema, optimized for complex analytical queries, column oriented, used for reporting and BI. ETL pipelines typically move data from OLTP → OLAP systems.
How do you handle large datasets in PySpark?
Partition data by high cardinality filter columns Use broadcast joins for small dimension tables Apply predicate pushdown and column pruning Cache DataFrames reused across multiple actions Handle skew via salting or AQE ( spark.sql.adaptive.enabled=true ) Target 128 MB–1 GB per partition
Explain caching and persistence in Spark.
cache() stores a DataFrame in memory (spills to disk if needed). persist(level) lets you choose the storage level explicitly: Use when a DataFrame is reused in 2+ actions. Always call unpersist() when done to free memory.
How do you set up CI/CD for data pipelines?
1. Store code (ADF JSON/ARM templates, PySpark scripts, notebooks) in Git 2. Use Azure DevOps CI pipeline to run unit tests and validate 3. Use release pipeline to deploy via ARM templates or Databricks REST API 4. Apply environment specific parameters for Dev/UAT/Prod 5. Require PR approvals before merging to main
Explain incremental load strategies in ADF.
Watermark based : store last loaded timestamp in a control table; query source for records newer than watermark CDC : read SQL Server change tables via Debezium or ADF's built in CDC connector Delta Lake MERGE : upsert records using a unique key Partition based : load only today's date partition Always update the watermark after a successful load.
How do you handle schema evolution in Delta Lake?
For breaking changes, use overwriteSchema=true . You can also run ALTER TABLE ADD COLUMNS . Delta's transaction log tracks all schema changes, enabling time travel to pre change versions.