Question 1

What is Z-ordering in Spark?

Accepted Answer

Z ordering (also called Z order clustering) is a data layout optimization technique in Delta Lake that collocates related data in the same set of files, dramatically reducing the amount of data read for selective queries. How it works: Z ordering uses a space filling curve to map multi dimensional data into a single dimension while preserving locality. Records with similar values in the Z ordered columns are stored physically close together in the same files. Or in PySpark: Benefits: Queries filtering on Z ordered columns skip entire files (data skipping) 10 100x reduction in data scanned for selective queries Works best with low to medium cardinality columns that appear in WHERE clauses When to use: Columns frequently used in filters or joins Columns NOT suitable as partition keys (too high cardinality) Large Delta tables where query performance matters Limitation: Z ordering is not free — it rewrites files and costs compute time during OPTIMIZE runs.

Question 2

Explain the difference between Spark SQL and PySpark DataFrame APIs.

Accepted Answer

Both execute on the same Spark engine with the same Catalyst optimizer, but differ in syntax and use case: Spark SQL: Write queries as SQL strings Familiar to analysts who know SQL Register DataFrames as temp views first PySpark DataFrame API: Programmatic, chainable method calls Type hints, IDE autocomplete, easier refactoring Better for dynamic query construction Key similarities: Both produce the same execution plan (Catalyst optimizer processes both) Same performance for equivalent queries Interchangeable — results can be mixed in the same notebook When to use each: Scenario Preferred Ad hoc analysis by analysts Spark SQL Dynamic query building DataFrame API Complex business logic DataFrame API Reporting/dashboards Spark SQL Unit testing transformations DataFrame API Best practice: Use the DataFrame API in production code for testability; use Spark SQL for readability in notebooks and data exploration.

Question 3

How do you implement incremental load in ADF?

Accepted Answer

Incremental load in Azure Data Factory copies only new or modified records since the last run, rather than reloading the full dataset. Method 1: Watermark based incremental load 1. Store the last successful load timestamp in a control table (Azure SQL) 2. ADF pipeline reads the watermark, queries source for records newer than watermark, loads them, then updates the watermark ADF pipeline steps: 1. Lookup Activity — get last watermark from control table 2. Copy Activity — copy data using watermark range as filter 3. Stored Procedure Activity — update watermark to current run timestamp Method 2: Change Data Capture (CDC) with Datastream / SQL CDC Enable CDC on the source SQL Server table ADF reads change tables ( cdc.change tables ) which log inserts, updates, deletes Only net changes are loaded Method 3: Partition based For date partitioned sources, load only today's partition: Best practice: Always validate row counts after incremental load and alert on anomalies (0 rows may indicate watermark drift).

Question 4

How do you handle large-scale data ingestion into ADLS?

Accepted Answer

Azure Data Lake Storage (ADLS Gen2) ingestion at scale requires careful design across parallelism, partitioning, and reliability. Ingestion patterns: 1. ADF parallel copy with partitioning: For database sources, use physical/logical partitioning in ADF Copy Activity to parallelize reads. 2. Event Hubs / Kafka → Stream Analytics / Databricks → ADLS: For streaming ingestion, use micro batch writes (5 10 min intervals) to avoid small file proliferation. 3. Databricks Auto Loader: Scalable incremental file ingestion from ADLS: Performance tips: Use Parquet or Delta format (not CSV) for efficient columnar storage Partition by date: year=YYYY/month=MM/day=DD Target file sizes of 128 MB–1 GB (avoid small file problem) Enable hierarchical namespace (HNS) for POSIX compliant operations Use service principal + Azure Key Vault for secure credential management Reliability: Use checkpointing in streaming jobs for exactly once semantics Implement dead letter containers for malformed records Monitor with Azure Monitor + custom latency alerts

Question 5

Write Python code to split a name column into first name and last name.

Accepted Answer

Output: Handle missing last names: In PySpark: Using regex for complex names (e.g., 'Dr. John Smith III'):

Question 6

What are fact and dimension tables in data modeling?

Accepted Answer

Star schema — the foundational data modeling pattern for analytical workloads — uses two types of tables: Fact Table: Stores quantitative, measurable events (transactions, clicks, orders, sensor readings) Each row = one business event Contains: foreign keys to dimensions + measures (numeric values like amount, quantity, duration) High row count (millions to billions) Example: fact sales(sale id, date key, customer key, product key, store key, quantity, amount) Dimension Table: Stores descriptive context for the facts Contains attributes used for filtering and grouping (name, category, region, status) Relatively small (thousands to millions of rows) Examples: dim customer(customer key, name, email, segment, region) dim product(product key, name, category, brand, price tier) dim date(date key, date, year, quarter, month, day of week, is holiday) Why star schema: Simple joins: fact joins to dimensions directly (one hop) Query performance: BI tools generate efficient SQL Intuitive for business users Slowly Changing Dimensions (SCD): SCD Type 1: Overwrite — no history kept SCD Type 2: Add new row with valid from / valid to — full history preserved SCD Type 3: Add new column — limited history Snowflake schema: Dimension tables are further normalized into sub dimensions. More normalized but more joins required.

Question 7

How do you design and implement data pipelines using Azure Data Factory?

Accepted Answer

ADF is Azure's cloud ETL service for orchestrating data movement and transformation. Core ADF components: Pipelines — workflow containers for activities Activities — individual steps: Copy, Data Flow, Lookup, ForEach, If Condition, Stored Procedure Datasets — define data structure and location Linked Services — connection definitions (Azure SQL, ADLS, Blob, REST APIs) Triggers — Schedule, Tumbling Window, Event, Manual Integration Runtime — compute for data movement (Azure IR, Self hosted IR for on prem) Example pipeline design — daily ETL: Parameterization: Use pipeline parameters for dates, table names, environment Store secrets in Azure Key Vault, reference via @linkedService().secretName Error handling: Set On Failure paths for each activity Use Execute Pipeline with retry for transient failures Log errors to an Azure SQL pipeline errors table via Stored Procedure CI/CD: ADF Git integration (Azure DevOps / GitHub) Publish branch → ARM templates → deploy via Azure Pipelines

Question 8

Explain the concept of PolyBase in Azure SQL Data Warehouse.

Accepted Answer

PolyBase is a technology in Microsoft SQL Server and Azure Synapse Analytics that allows you to query external data (stored in ADLS, Azure Blob, Hadoop) using standard T SQL, without moving the data into the database. How PolyBase works: 1. Define an External Data Source (ADLS, Blob, Hadoop) 2. Define an External File Format (Parquet, CSV, ORC) 3. Create an External Table pointing to the external data 4. Query the external table with T SQL — PolyBase pushes compute to the data source PolyBase for bulk loading (CTAS): This is the fastest way to load data into Synapse — PolyBase uses parallel loading across compute nodes.

Deloitte Interview Questions