Azure Data Platforms (Synapse, Data Lake Storage, Data Factory) Questions
Understanding Azure's data ecosystem: Synapse for data warehousing with both dedicated and serverless SQL pools, Data Lake Storage Gen2 for enterprise data lakes, Data Factory for orchestration. Understanding how components integrate and when to use each for different workloads.
HardSystem Design
38 practiced
You're migrating a 500TB Hadoop/Hive data lake and scheduled Hive ETL jobs to Azure. Outline a migration plan to move data to ADLS Gen2 and workloads to Synapse/Databricks. Cover data transfer, metadata/catalog migration, job rewrite strategy, validation, cutover plan, and rollback considerations.
Sample Answer
Requirements & constraints:- Move 500 TB from HDFS/Hive to ADLS Gen2 with minimal downtime, preserve schema/partitions/ACLS, migrate scheduled Hive ETL to Synapse (serverless/Apache Spark pools) or Databricks (preferred for Spark parity), keep lineage and Hive metastore/catalog, and allow rollback.High-level plan (phases):1. Assessment & design (2–4 weeks)- Inventory datasets (size, partitions, file formats, compression), job catalogue (schedules, dependencies, UDFs), access patterns, SLAs.- Decide target formats (Parquet/Delta), partitioning scheme, zone layout (raw/curated/served), security mapping (AD groups, POSIX ACLs -> Azure RBAC + ACLs).2. Data transfer (bulk + incremental)- Bulk copy: use DistCp or Azure Data Box if network constrained; otherwise use Azure Data Factory (ADF) or AzCopy with parallelism to ingest HDFS -> ADLS Gen2 into raw zone preserving path and timestamps.- Convert formats in-flight where feasible (DistCp + follow-up job to convert ORC->Parquet/Delta using Spark).- Set up continuous incremental sync: use CDC or HDFS WAL parsing, or schedule incremental Export jobs that copy new partitions daily until cutover.3. Metadata/catalog migration- Export Hive metastore metadata (schemas, partitions, statistics) using HMS dump or custom script.- Populate Azure Hive Metastore (Azure Databricks Unity Catalog or Azure Synapse Catalog / Azure Data Catalog) mapping table locations to ADLS paths; recreate partitions or run MSCK REPAIR AS TABLE only after data available.- Migrate ACLs: map HDFS ACLs to Azure RBAC + POSIX ACLs on ADLS; validate with sample users.4. Workload migration & rewrite strategy- Prioritize jobs by criticality & complexity. Three tracks: a) Lift-and-shift: run existing Spark/Hive queries on Databricks or Synapse with minimal change using Hive compatibility (Spark SQL, HiveContext) for quick validation. b) Re-implement: rewrite complex HiveQL with UDFs into optimized Spark or use Delta Lake features (MERGE, OPTIMIZE, ZORDER). c) Replace by pipeline: where appropriate rebuild as streaming/batch pipelines in ADF/DataBricks Jobs.- Containerize job logic, store in repo, add CI/CD (Azure DevOps/GitHub Actions) to deploy notebooks/jobs, parameterize environments.5. Validation & testing- Data correctness: row counts, checksums (parquet/ORC), sample data comparisons, partition counts.- Query parity: run sample queries and compare results and performance. Use data diff tools or Spark jobs computing hash per partition.- Performance testing: run scaled queries, tune file sizes, partitions, cluster sizing.- Security tests: access control, encryption, service principals.6. Cutover plan- Freeze writes or enter dual-write period: start sync to ADLS and run read-only consumers against ADLS for validation.- Final incremental sync window: run delta transfer, ensure no outstanding jobs on HDFS writes.- Switch job scheduler DNS/CRON to trigger Databricks/Synapse jobs; flip readers to new endpoints.- Monitor closely for 48–72 hours; keep telemetry dashboards.7. Rollback considerations- Keep original HDFS cluster intact until rollback window closes.- Maintain two-way sync capability during cutover if feasible.- Rollback steps: pause new jobs on cloud, re-enable consumers against HDFS, restore metadata pointers if needed.- Rehearse rollback in staging.Operational notes & trade-offs:- Use Delta Lake to gain ACID/efficient upserts but requires conversion cost.- Databricks offers faster developer velocity and Delta support; Synapse may be lower cost for SQL workloads.- Plan for metadata consistency—partitions must be refreshed after copy.- Budget for network egress, Data Box, and testing clusters.This approach balances low-risk bulk transfer, staged validation, and clear rollback to limit business impact.
MediumSystem Design
53 practiced
Design an end-to-end Azure Data Factory pipeline to ingest daily CSV files from an SFTP server into ADLS Gen2, transform the data, and load it into a Synapse dedicated SQL pool for reporting. Include choices for triggers, handling late files, schema drift, error handling, idempotency, and cost considerations.
Sample Answer
Requirements (clarify):- Daily CSVs on SFTP (partitioned by date), land into ADLS Gen2, transform, load into Synapse dedicated SQL pool for reporting.- SLAs: data available by X hours after day-end; handle late arrivals up to N days.- Support schema drift, retries, idempotency, monitoring, and cost-control.High-level architecture:SFTP → Azure Data Factory (ADF) pipelines → ADLS Gen2 raw zone → ADF mapping/Databricks transformations → ADLS curated zone (Parquet) → PolyBase COPY INTO / Synapse Spark / COPY for loading → Synapse dedicated SQL pool (CTAS/merge).Components & choices:1. Ingestion- ADF Copy Activity with SFTP connector to copy CSVs to ADLS raw. Use Binary copy for raw archive plus delimited copy for parsed files.- Trigger: Schedule trigger (daily) + Event-based trigger (Storage Event) for near-real-time when files arrive. Also support tumbling window trigger for deterministic runs and watermarking.2. Handling late files- Keep a "late-files" partition policy: allow arrival window (e.g., 3 days). Use metadata table in ADLS/Synapse to track file ingestion date, source filename, checksum, and processed flag.- Run an hourly reconciliation pipeline (ADF) that scans SFTP or ADLS for unprocessed/late files and processes them into historical partitions. Use watermark + max allowed lateness.3. Transformations & Schema drift- Prefer Spark (Azure Databricks or Synapse Spark) to read raw CSVs and write Parquet. Use schema merging (infer schema) with explicit rules: whitelist columns, map unknown columns to JSON column, or use dynamic mapping stored in a schema registry table.- For ADF mapping data flow: enable schema drift option and column mapping with default handling for extra/missing columns.4. Load into Synapse dedicated SQL pool- Load Parquet files using PolyBase or COPY INTO for performance. Use staging external tables or CTAS for efficient batch loads.- For incremental loads: use Merge into target table using business key + ingestion_date; maintain change tracking columns.5. Idempotency & deduplication- Use deterministic file-level checksums and metadata table to mark processed files. Processing job checks metadata to avoid reprocessing.- Use Merge with source file batch identifier to prevent duplicate inserts. Implement dedupe logic (ROW_NUMBER partitioning) if needed.6. Error handling & retries- ADF activity-level retry policies and exponential backoff. For transient SFTP failures, set retry 3-5 times.- On fatal errors, route file to quarantine folder and write error details to monitoring table + send alert (Azure Monitor/Logic Apps/Teams).- Implement dead-letter table for records failing schema/validation rules with reason and original payload.7. Monitoring & observability- Use ADF monitoring, Log Analytics, and custom telemetry: ingestion metrics, latency, row counts, schema changes. Generate alerts for SLA breaches, high error rates, or failed pipelines.8. Cost considerations- Minimize Synapse DW usage: load in large micro-batches (daily), pause DW outside business hours to save compute; use COPY/PolyBase to minimize compute-time.- Store raw as compressed Parquet to reduce storage and egress. Use serverless Synapse SQL for ad-hoc queries if possible (cost vs performance trade-off).- Use spot/auto-terminate clusters for Databricks; tune file sizes (128MB-256MB) to optimize I/O and throughput.Data flow summary:1. ADF scheduled trigger runs; checks metadata for new SFTP files.2. Copy Activity downloads to ADLS raw (archive). Record metadata (checksum, size, modified time).3. Transformation job (ADF mapping flow or Spark) reads raw CSVs, validates, handles schema drift, writes Parquet to curated zone.4. Load job uses COPY INTO/PolyBase to bulk load into Synapse; perform Merge to apply incremental changes.5. Update metadata with processed flag; emit success metrics. Quarantine or dead-letter problematic files/rows.Trade-offs:- ADF mapping flows are lower ops but less flexible for complex schema drift — choose Spark for complex logic.- PolyBase/COPY gives fast bulk loads but requires Parquet/external tables; using dedicated SQL pool compute costs vs serverless alternatives must be balanced.This design provides reliable, idempotent ingestion with late-file handling, schema drift strategies, robust error handling, and cost controls appropriate for daily batch reporting in Synapse.
MediumTechnical
76 practiced
Describe how you'd integrate Azure Purview (or a metadata/cataloging tool) with Synapse and ADLS Gen2 to provide data lineage, classification, and a discoverable data catalog for analysts. Explain how scanning, lineage extraction, and access metadata would work end-to-end.
Sample Answer
Situation/Goal: Provide analysts a discoverable catalog with classification and end-to-end lineage for data stored in ADLS Gen2 and processed in Synapse.Approach (high-level):- Register ADLS Gen2 and Synapse workspace in Azure Purview as sources.- Configure Purview scanning (credentialed) to crawl file systems, Hive/Parquet/Delta schemas, Synapse SQL pools, serverless SQL endpoints, and Spark metastore.- Enable classification rules and custom classifiers to tag PII, financial, and business domains during scanning.Scanning & metadata extraction:- Purview uses a managed identity/key to connect to ADLS Gen2 and Synapse. Schedule incremental scans (daily/real-time) to read folder/file metadata, schema, and sample data.- For Synapse, scan workspace artifacts: dedicated SQL pools, serverless views, pipelines, notebooks, and linked services to capture dataset definitions and SQL text.Lineage extraction:- Extract technical lineage by parsing SQL (SELECT/INSERT/CTAS), Spark jobs, and Synapse pipeline activities. Purview’s built-in lineage extractor (or Microsoft Purview Lineage) reads activity definitions and stores mapping between source assets, transforms, and sinks.- Capture physical lineage from ADLS paths -> tables -> views -> reports; capture process lineage from Synapse pipelines and Databricks/Spark notebooks via job logs or integration with Azure Data Factory/Purview connectors.Access & operational metadata:- Pull access control metadata from Azure RBAC and ACLs on ADLS Gen2, plus Synapse role assignments. Purview ingests and surfaces who can read/write each asset.- Ingest activity/usage metrics (read/write counts, last accessed) from Azure Monitor / Synapse workspace logs into Purview or linked monitoring store to show popularity and freshness.End-to-end flow:1. Register sources and set scanning schedules; configure credentials and path filters.2. Run initial full scan → Purview builds asset catalog, classifications, and sample schemas.3. Enable lineage extraction connectors for Synapse/ADF/Databricks to parse jobs and SQL, producing directed lineage graphs.4. Map access controls from ADLS and Synapse into Purview; surface policies and sensitive data labels.5. Provide analysts a searchable catalog (business glossary, tags), lineage visualizations, and API for programmatic discovery. Automate incremental scans and alert on classification/lineage changes.Trade-offs & considerations:- SQL parsing covers common patterns but complex dynamic SQL or external UDFs may require custom parsers or manual lineage annotation.- Scanning frequency balances freshness vs. cost.- Ensure least-privilege scanning identity and encrypt credentials; document PII handling policy.Result: Analysts get searchable datasets with sensitivity tags, ownership, access information, and a lineage graph from raw ADLS files through Synapse transformations to curated tables — enabling trust, discoverability, and faster onboarding.
EasyTechnical
49 practiced
Explain the primary responsibilities and ideal use-cases for each of these Azure services in a modern analytics platform: Azure Synapse Analytics (both dedicated SQL pools and serverless SQL), Azure Data Lake Storage Gen2 (hierarchical namespace), and Azure Data Factory. For a small-to-medium company that runs daily reporting and occasional ad-hoc queries, give concrete examples of when you'd choose each service and why.
Sample Answer
Azure Synapse (overview)- Primary responsibility: unified analytics — query, transform, and serve data at scale. It exposes two SQL models with different cost/performance tradeoffs.Dedicated SQL pool (formerly SQL DW)- Use-case: predictable, high-concurrency reporting or fast BI dashboards where you need low-latency, consistent performance.- Example (SMB): If you run nightly ETL that builds a 1TB star schema consumed by Power BI with lots of users at 9am, provision a dedicated pool so queries hit pre-computed, distributed storage and deliver predictable SLAs.- Why: provisioned compute and distribution/columnstore optimizations give stable performance; good for repeated heavy workloads.Serverless SQL pool- Use-case: ad-hoc analytics, exploratory queries over files in the data lake, cost-sensitive intermittent queries.- Example: data analyst runs occasional ad-hoc joins across CSV/Parquet files in ADLS to prototype a report. Use serverless to avoid provisioning and pay only per query.- Why: zero infra to manage, reads files directly from ADLS, great for discovery and lightweight reporting.Azure Data Lake Storage Gen2 (with hierarchical namespace)- Primary responsibility: scalable, cost-effective storage for raw/curated data; supports POSIX-like filesystem semantics.- Example: land raw ingestion (logs, CSVs, Parquet), manage zones (raw/clean/curated), enable efficient directory-level ACLs and fast file operations (rename/atomic move) during ETL.- Why: hierarchical namespace speeds up directory operations and enables fine-grained security useful for GDPR/compliance.Azure Data Factory (ADF)- Primary responsibility: orchestration and ETL/ELT — move, transform, and schedule data pipelines across services.- Example: schedule nightly pipeline that copies source DB incremental data into ADLS raw, triggers a Spark job in Synapse to transform and load the dedicated SQL curated tables, then refreshes Power BI datasets.- Why: built-in connectors, monitoring, parameterization, and integration with Synapse/ADLS make it the glue for pipelines.Practical recommendations for a small-to-medium company- ADLS Gen2 as the primary landing zone and canonical store.- Use ADF for scheduled ingestion, incremental loads, and orchestrating transformations.- Start with serverless SQL for ad-hoc exploration and small reports to minimize cost.- Add a small dedicated SQL pool only when report concurrency/latency demands justify the predictable performance and reserved cost.- Keep transformations in Spark (Synapse Spark pools) when you need flexible ETL; push curated, performance-critical datasets into dedicated SQL for BI.
EasyTechnical
48 practiced
Describe the ADLS Gen2 hierarchical namespace and how it differs from classic Blob storage. Explain implications for file-level ACLs, performance (rename/atomic operations), and common data engineering patterns (e.g., folder partitioning, atomic writes).
Sample Answer
ADLS Gen2 hierarchical namespace (HNS) adds true filesystem semantics on top of Azure Blob Storage: objects are organized into directories and files with a tree structure, rather than a flat namespace of blobs with "/" in names. Key differences vs classic Blob storage:- Namespace & metadata - HNS: directories are first-class; operations like list, delete, and set ACL can target directories. - Classic Blob: “folders” are virtual (string prefixes), managed by client-side logic.- File-level ACLs & security - HNS supports POSIX-like Access Control Lists (owner, group, others + ACL entries) at file and directory granularity via Azure RBAC + ACLs. You can grant/read permissions per folder/file without complex container-level workarounds. - Classic Blob relies on container-level ACLs, SAS tokens, or blob-level ACLs (limited), making fine-grained permissions harder.- Performance & atomic operations - Rename/move: HNS supports server-side rename/move as a metadata operation within the same filesystem — cheap and near-atomic (fast directory renames). Classic Blob requires copying then deleting blobs (expensive, non-atomic). - Delete directory: HNS can delete a directory tree server-side efficiently; classic needs iterative deletes. - Note: Large recursive renames can still be longer; concurrency semantics depend on ADLS implementation, so test for your workload.- Common data engineering patterns - Folder partitioning: With HNS you can store partitions as real directories (e.g., /events/year=2025/month=11/), and list partitions cheaply. This improves discovery and ACL scoping. - Atomic writes: Pattern — write to a temp directory or use a staging file name (e.g., _inprogress or .part), then server-side rename to final path. HNS rename makes this efficient and effectively atomic for readers that check filename convention. - Compaction and housekeeping: Efficient directory-level operations simplify compaction, vacuum, and retention tasks. - Snapshots/versioning: ADLS Gen2 supports soft-delete and snapshots separately; design pipelines to account for eventual consistency in metadata operations if you rely on high-concurrency updates.Best practices:- Use directory ACLs to limit scope of permissions instead of many container-level containers.- Implement write-then-rename pattern for atomic commits.- Keep partition sizes balanced to avoid millions of tiny files; leverage server-side listing for partition discovery.- Test rename/delete semantics under your concurrency and scale to understand latency and consistency.In short: HNS gives filesystem semantics (fine-grained ACLs, cheap renames/deletes) that simplify common data engineering patterns and improve performance for metadata-heavy operations compared to classic blob storage.
Unlock Full Question Bank
Get access to hundreds of Azure Data Platforms (Synapse, Data Lake Storage, Data Factory) interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.