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.
MediumTechnical
46 practiced
Define an observability plan for Data Factory ingestion, ADLS file landing, and Synapse query activity for BI workloads. Which telemetry and metrics would you capture (for example, pipeline run duration, row counts, bytes scanned, failed activity counts), how would you store and visualize them, and what alerts would you create for analysts and engineers?
Sample Answer
Observability plan — goal: ensure data freshness, completeness, performance and troubleshootability across Data Factory ingestion, ADLS file landing, and Synapse query activity for BI consumers.Key telemetry to capture- Data Factory: pipeline run status, run duration, activity-level duration, input/output row counts, bytes transferred, retry counts, failed activity counts, start/finish timestamps, pipeline parameters, lineage IDs.- ADLS: file creation/modification time, file size (bytes), file count per path, checksum/hash, partition keys, latency from expected landing time, ACL/permission failures.- Synapse: query text/id, user/service principal, duration, CPU/IO/Memory, bytes scanned, rows returned, distribution of long-running queries, DWU/Compute utilization, concurrency, job failures and retries.- Business metrics: record counts per business table, late-arrival counts, null/quality rule failures.Storage and enrichment- Ship diagnostics to Azure Monitor -> Log Analytics workspace. Ingest ADF diagnostics, ADLS audit logs, Synapse workspace diagnostics. Enrich with pipeline/job metadata (environment, owners, SLAs) and correlate via correlationId/pipelineRunId.- Persist aggregated daily/hourly metrics in a purpose-built telemetry table in a curated ADLS zone or SQL DB for historical trends and retention.Visualization- Power BI operational dashboard(s): - Real-time health (last 24h): failing pipelines, files late, top slow queries. - SLA/trend views: ingestion latency, row counts vs expected, compute usage. - Root-cause drilldowns: pipeline -> activity -> logs, file list, sample records. - Business-facing freshness dashboard showing dataset-level “ready” flags.- Use workbook alerts in Azure Monitor for quick debugging links.Alerts and routing- For analysts (informational/mitigation): - Data freshness breach per dataset (e.g., > SLA threshold): notify analysts + link to last successful run and last available rows. - Row-count delta anomaly (e.g., >20% drop vs rolling baseline): email/Teams to analysts and product owner.- For engineers (actionable/urgent): - Pipeline failure count > 0 or repeated retries: page on-call engineer. - ADLS landing failure / permission denied: page + block downstream processing. - Synapse query failures or spike in bytes-scanned causing cost spike: alert engineering and infra. - Compute saturation or concurrency queue > threshold: auto-scale or ops alert.- Alert details: include correlationId, runId, sample logs, affected datasets/tables, last good timestamp, link to Power BI and Log Analytics query.SLOs and runbook- Define SLAs (e.g., 99% success within X minutes), define alert thresholds and actions, and maintain runbooks per alert with triage steps and rollback guidance. Regularly review false positives and tune thresholds.Why this works- Correlating telemetry by pipelineRunId enables fast root cause. Storing aggregates supports trend detection and cost controls. Separating analyst vs engineer alerts reduces noise while ensuring timely remediation.
EasyTechnical
42 practiced
Compare Synapse dedicated SQL pool and Synapse serverless SQL pool for BI workloads. Describe architecture differences, pricing and cost models, compute and concurrency behavior, typical performance characteristics, and concrete scenarios when you would pick one over the other for dashboards and ad-hoc analysis.
Sample Answer
Architecture:- Dedicated SQL pool (formerly SQL DW) is a provisioned, MPP (distributed Massively Parallel Processing) engine: data is distributed across compute nodes (distributions) and uses dedicated compute/storage resources. Good for large, predictable, modeled datasets.- Serverless SQL pool is on-demand, query-on-read over data in ADLS/Delta/Parquet; no dedicated compute to manage—queries spin up resources transparently and read files directly.Pricing & cost model:- Dedicated: fixed hourly cost based on DWUs (reserved compute tiers). You pay while the pool is provisioned (can pause to save compute). Storage billed separately.- Serverless: pay-per-query based on data processed (per TB scanned). No upfront compute cost; cheaper for infrequent queries or small scanned volumes.Compute & concurrency:- Dedicated: predictable throughput, high concurrency when scaled (more DWUs), low latency for complex joins/aggregations on large tables. Concurrency limits exist per service tier but are configurable by scaling.- Serverless: concurrency is elastic but subject to service limits and per-query resource caps; complex, long-running joins on huge raw files can be slower or fail due to resource/time limits.Performance characteristics:- Dedicated: best for large, repeated dashboard workloads where data is pre-modeled/partitioned/indexed—fast, consistent performance.- Serverless: best for exploratory, ad-hoc analysis, quick joins over files, prototyping, or infrequent reports; performance depends on file layout (Parquet/columnar, partitioning) and amount of data scanned.When to pick which (concrete scenarios):- Choose Dedicated for production dashboards with high concurrency, SLAs, large fact tables, complex transforms done in the warehouse, predictable daily refreshes and need for low latency visuals (Power BI live connections).- Choose Serverless for ad-hoc queries, analyst exploration, data discovery, lightweight dashboards where data lives in lake files, cost-sensitive or low-frequency reporting, and fast proof-of-concept work before modeling into dedicated pool.Best practices:- Use serverless for initial exploration; move hot, frequently queried datasets into dedicated pool and pre-compute aggregates/materialized views to optimize dashboard performance and control costs.
EasyTechnical
40 practiced
Compare Parquet, CSV, and Avro as storage formats for analytics on ADLS Gen2. Discuss differences in schema handling, compression and storage efficiency, columnar vs row layout, support for schema evolution, typical read patterns for BI queries, and how format choice impacts Synapse query performance and cost.
Sample Answer
High-level summary: For BI on ADLS Gen2, Parquet and Avro are binary formats; CSV is plain-text. Choice affects query performance, storage, and cost in Synapse.Schema handling- CSV: no embedded schema — consumers must infer columns/types → brittle, more ETL.- Avro: schema is embedded (JSON) and stored with data files — good for row-based ingestion.- Parquet: schema stored in file footer with rich types and partitioning metadata — ideal for analytics.Compression & storage efficiency- CSV: largest size (no native column compression); compressible with gzip but still inefficient.- Avro: compact and supports codecs (snappy) — good for storage but row-oriented limits compression gains.- Parquet: columnar compression (dictionary, run-length) yields best storage savings for repeated values.Columnar vs row layout- CSV/Avro: row-oriented — efficient for single-row writes/reads.- Parquet: columnar — efficient for read-heavy BI queries that select subsets of columns.Schema evolution- CSV: manual handling.- Avro: built-in support for schema evolution (forward/backward compatible).- Parquet: supports evolution (adding nullable columns, type promotion) but requires care.Typical BI read patterns- BI queries read few columns across many rows → columnar Parquet excels (less IO).- CSV/Avro cause higher IO for wide tables.Impact on Synapse performance & cost- Parquet reduces read bytes, speeds up serverless and dedicated SQL pools, and lowers query costs (less data scanned).- Avro okay for ETL or streaming ingestion into Synapse but slower BI reads.- CSV is cheapest to produce but most expensive to query repeatedly (higher compute and scan costs).Recommendation: Store analytics-ready tables as partitioned Parquet on ADLS Gen2; use Avro/CSV for ingestion landing zones, then convert to Parquet for reporting.
HardSystem Design
52 practiced
Design a system to enforce data contracts between upstream producers and downstream BI consumers. Include how to version schemas, implement contract testing in CI, validate ingested data in Data Factory, and reject incompatible writes. Describe the process for rolling schema changes without breaking consumers.
Sample Answer
Requirements & constraints:- Producers and BI consumers must agree on schema (fields, types, nullability, semantics).- Low-latency validation at ingest; safe rolling schema evolution; CI contract tests; clear rollback/communication.High-level architecture:- Schema Registry (e.g., Confluent Schema Registry / Azure Schema Registry) storing schemas (Avro/Protobuf/JSON-Schema) with compatibility settings.- Producer side: registers schemas, writes to message bus / staging lake (Kafka / ADLS) with schema id.- Ingest/Data Factory: validates incoming messages against registry before landing into curated zone.- CI: consumer-driven contract tests + integration tests that run on PRs.- Enforcement layer (gateway or sink validator) that rejects incompatible writes.Schema versioning & compatibility:- Use semantic versioning + registry compatibility modes: - BACKWARD for producers adding fields usable by old consumers; - FORWARD for producers removing fields (rare); - FULL for strict guarantees.- Policies: only allow additive nullable fields by default. Non-additive changes require major version and migration plan.Contract testing in CI:- Consumers (BI datasets, BI data models) declare expectations as contracts (subset of schema: required fields/types, business-level constraints).- Producer and consumer repos run unit tests that validate generated/expected schema via registry API.- Use consumer-driven contract framework (e.g., Pact-like or custom tests) that: - On producer PR: run consumer contracts to ensure new schema satisfies consumers’ contracts (backward compatibility). - On consumer PR: validate consumer expectations against latest registered schema.- Include property tests for constraints (e.g., field ranges, enums).Validate in Data Factory (Azure example):- Ingest pipeline stages: 1. Raw landing (immutable) with schema id metadata. 2. Pre-validate activity: call schema registry to fetch schema and run JSON-Avro validation (Data Flow or Azure Function). 3. Business validation: apply data quality rules (nullability, foreign keys, metrics). 4. If validation passes → curated zone; else route to quarantine with error metadata, alerting, and replay capability.- Use mapping data flows or custom validation functions for complex rules.Reject incompatible writes:- At source: producer SDKs integrate registry client to serialize and fail fast on incompatible schema registration.- On message bus: enable schema enforcement (Schema Registry + broker plugins) so messages failing validation are rejected.- At Data Factory: final safeguard rejects or quarantines records that don't validate; emit metrics and block downstream publish until fixed.Rolling schema changes process:1. Design change: producers create new schema version following compatibility rules; update semantic version and publish to registry.2. Deploy producer to write with new schema id while still producing old schema stream if immediate migration needed (dual-write or topic-versioning).3. Run CI consumer-contract tests automatically; notify failing consumers.4. Consumers adopt changes: update BI ETL/mappings, run integration tests against sample of new data (shadow mode).5. Switch reading logic: consumers point to new schema id or handle new optional fields gracefully.6. Deprecation window: monitor metrics and data quality; after confirmation, remove old fields or enforce stricter constraints in next major version.7. Communication: automated change notifications (changelog, schema diff), required migration deadlines, and rollback plan (stop producer version or revert schema).Example:- Add new optional field "customer_segment" (nullable string). Registry accepts it under BACKWARD compatibility. Producer publishes new version v1.1. CI runs consumer contracts — all pass. Data Factory validation accepts new field (nullable). BI dashboards ignore until they update visuals. After 90 days, producers mark field required and release v2.0 → consumer updates required before enforcement.Key trade-offs:- Strict compatibility prevents downstream breakage but slows innovation.- Dual-write and shadow-testing increase complexity but provide safe rollouts.Operational best practices:- Automate schema diff, compatibility checks, and alerts.- Maintain contract tests close to consumer code (BI models).- Keep clear SLAs for consumer migration and a quarantine+replay pipeline for fixes.
HardTechnical
66 practiced
Users report inconsistent KPI values between two dashboards that both read from Synapse, but one dashboard queries serverless CTAS Parquet outputs and the other reads materialized views in a dedicated pool. Describe a step-by-step investigation plan covering lineage verification, caching issues, transaction isolation, refresh schedules, eventual consistency, and how to reconcile and fix discrepancies.
Sample Answer
1) Clarify scope & gather evidence- Confirm which KPIs differ, exact values, timestamps, dashboard queries, and sample user reports.- Capture SQL used by both dashboards, query timestamps, and preview file paths for CTAS Parquet and MV definitions.2) Verify lineage- Map data flow: source tables → any ETL/stream jobs → serverless CTAS outputs (Parquet path) → materialized views (dedicated SQL pool) → dashboards.- Check if CTAS is reading raw source or transformed staging; see if MVs are built from same source or from transformed CTAS outputs.3) Reproduce discrepancy- Run both queries manually at the same time, using the same snapshot time/date parameters. Export results for row-level diff to identify which records differ (missing, duplicated, aggregated).4) Investigate caching & refresh schedules- Check dedicated SQL pool MV refresh schedule and last refresh timestamp.- Check any BI tool caching (Power BI model cache, dashboard tile cache) and serverless CTAS job schedules that write Parquet.- Invalidate caches and re-run both queries.5) Transaction isolation & eventual consistency- Look for upstream writes in progress: long-running transactions, late-arriving data, or streaming inserts. Serverless CTAS reading blob storage may observe eventual consistency; MVs in dedicated pool may reflect only committed snapshots at refresh time.- If source is append-only or CDC, confirm watermark logic and whether CTAS picks up in-flight files.6) Compare transformations & business logic- Verify identical SQL logic: joins, filters, time zone handling, date truncation, null handling, dedup rules, and aggregation windows.- Ensure both dashboards use same definitions for KPIs (numerator/denominator, excluded categories).7) Fix and reconcile- Short-term: communicate which view is authoritative and document reason. Force MV refresh or rerun CTAS pipeline to sync snapshots; clear BI cache.- Medium-term: align pipelines — either have MVs be built directly from canonical source or have CTAS outputs be the single source and rebuild MVs from them. Add atomic write patterns (write to temp path then atomic rename) to avoid partial reads.- Add provenance columns (data_timestamp, source_version) to outputs so queries can filter consistent snapshots.8) Prevent recurrence- Implement monitoring and alerting: data freshness metrics, row-count checks, and automated diffs between sources.- Standardize KPI SQL in a single shared library or semantic layer (Power BI dataset / LookML) to avoid divergent logic.- Document refresh SLAs and communicate to stakeholders.9) Follow-up- Validate with users after fixes, keep audit logs of changes, and schedule a retrospective to update runbooks.This approach isolates lineage, surface-level differences, timing/caching issues, transactional/consistency causes, and ensures both technical and process fixes to prevent future discrepancies.
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.