Advanced Data Engineering interview questions

125 questions with answers (DEN-301 to DEN-425). Try each one aloud before reading the answer.

SQL Fundamentals

DEN-301 · Write a query to find users whose first purchase happened within 7 days of sign-up.

Join users to the minimum purchase date per user: SELECT u.user_id FROM users u JOIN (SELECT user_id, MIN(purchased_at) AS first_buy FROM orders GROUP BY user_id) o ON o.user_id = u.user_id WHERE o.first_buy <= u.signed_up_at + INTERVAL '7 days'; (date arithmetic syntax varies by engine).

DEN-302 · How do you compute a median in SQL on an engine without a MEDIAN function?

Use PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) where available; otherwise rank rows with ROW_NUMBER and COUNT and average the middle one or two values. Approximate functions (APPROX_PERCENTILE) are faster on big data.

DEN-303 · How would you calculate 30-day user retention by sign-up cohort in SQL?

Assign each user a cohort (sign-up month), then for each cohort count users with any activity between day 30 and day 37 (or within day 30), divide by cohort size, and pivot cohorts against periods. Use date diff functions and DISTINCT user counts.

DEN-304 · How do you find gaps in a sequence of invoice numbers?

Use LEAD: SELECT inv_no + 1 AS gap_start, next_no - 1 AS gap_end FROM (SELECT inv_no, LEAD(inv_no) OVER (ORDER BY inv_no) AS next_no FROM invoices) t WHERE next_no - inv_no > 1;.

DEN-305 · How do you write a query that sessionises clickstream events with a 30-minute inactivity rule?

Use LAG to find the previous event time per user, flag a new session when the gap exceeds 30 minutes, then take a running SUM of the flags over each user's ordered events as the session number.

DEN-306 · How do NULLs affect NOT IN subqueries, and how do you avoid the problem?

If the subquery returns any NULL, x NOT IN (subquery) returns no rows because comparisons with NULL are unknown. Use NOT EXISTS or filter NULLs out of the subquery.

DEN-307 · How would you detect and remove duplicate events that arrive with slightly different timestamps?

Define a business key (e.g. user, event type, amount) and a tolerance window, then use window functions (LAG or ROW_NUMBER within key ordered by time) to flag events within the window of a previous identical event and keep the first.

DEN-308 · How do you compute the percentage change between a row and the same day last year?

Use a self join on date − 1 year, or LAG with a calendar table to align dates; handle leap years and missing days by joining to a date dimension rather than relying on row offsets.

DEN-309 · How do you implement pagination efficiently for large tables?

Use keyset (seek) pagination: WHERE id > :last_seen_id ORDER BY id LIMIT 100 with an index, instead of OFFSET, which scans and discards all skipped rows and gets slower for deep pages.

DEN-310 · How would you write a query to find the top product per category by revenue, handling ties?

Aggregate revenue by category and product, apply RANK() or DENSE_RANK() over (PARTITION BY category ORDER BY revenue DESC), and keep rank 1; RANK keeps all tied products, ROW_NUMBER keeps one arbitrarily.

DEN-311 · How do you compare two tables to find rows that differ?

Use FULL OUTER JOIN on keys and compare columns (null-safe comparisons), or EXCEPT in both directions to find rows present in one but not the other. Hashing row values speeds comparison of wide tables.

Advanced SQL & Query Optimization

DEN-312 · A dashboard query slows from 2 seconds to 2 minutes after data grows. How do you investigate?

Read the execution plan to see scans, join types and row estimates; check for missing or unused indexes, stale statistics, non-SARGable predicates, exploding joins and spills to disk. Fix the cause (indexes, partition pruning, rewriting joins, pre-aggregation) and re-check the plan.

DEN-313 · Why might a query ignore an index you created?

The predicate may not be SARGable (functions on the column, implicit type conversion), the index column order may not match, statistics may say a full scan is cheaper, or the index doesn't cover the query and lookups would be too costly.

DEN-314 · How do stale statistics cause bad query plans?

The optimiser estimates row counts from statistics; when data changes significantly, estimates are wrong, leading to poor join orders or algorithms (nested loops instead of hash joins). Update statistics after large loads.

DEN-315 · How do you optimise a query with many OR conditions across different columns?

Rewrite as UNION ALL of simpler queries that each use an index, or use IN lists where possible; consider composite or filtered indexes; check whether the optimiser can use index union.

DEN-316 · A join produces far more rows than expected. What is happening and how do you fix it?

The join key isn't unique on one or both sides, so rows multiply (fan-out), often because of duplicate records or joining tables at different grains. Check key uniqueness with GROUP BY key HAVING COUNT(*) > 1, aggregate to the right grain before joining, or add the missing join conditions.

DEN-317 · How do you speed up COUNT(DISTINCT) on billions of rows?

Use approximate algorithms (HyperLogLog via APPROX_COUNT_DISTINCT) when small error is acceptable, pre-aggregate distinct sets per partition, or cluster data on the counted column to reduce shuffle.

DEN-318 · When would you use a materialised view versus a scheduled summary table?

Materialised views refresh automatically (sometimes incrementally) and are transparent to the optimiser, but have limits on supported SQL and refresh costs. Scheduled tables give full control over logic and timing and work with any SQL.

DEN-319 · How do you choose between hash join, merge join and nested loop join?

Hash joins suit large unsorted inputs with equality conditions; merge joins suit inputs already sorted on the join key; nested loops suit a small outer input with an indexed inner side. Optimisers choose based on statistics, so accurate statistics matter.

DEN-320 · How do you detect and handle query spills to disk?

Execution plans or profiles show spilled bytes when sorts, aggregations or joins exceed memory. Reduce data earlier (filters, projections), fix skew, increase memory or warehouse size, or break the query into stages.

DEN-321 · How do you write SQL that performs well on columnar warehouses?

Select only needed columns, filter on partition and clustering keys, avoid row-by-row UDFs, pre-aggregate before joins, and use approximate functions where acceptable.

DEN-322 · How do you optimise a slow window function query?

Partition and order by columns that allow efficient sorting, reduce rows before applying the window, avoid multiple windows with different orderings, and ensure the data is clustered appropriately.

Data Modeling

DEN-323 · How would you design a dimensional model for an e-commerce company?

Identify business processes (orders, payments, shipments, returns), declare the grain for each fact (order line, payment event), choose conformed dimensions (customer, product, date, store, promotion), and define measures. Use SCD Type 2 where history matters, such as customer address or product category.

DEN-324 · How would you model a subscription business with plan changes over time?

Use a subscription dimension with SCD Type 2 for plan history, a periodic snapshot fact (daily or monthly MRR per subscription) and a transaction fact for events like upgrades, downgrades and cancellations, enabling MRR movement analysis.

DEN-325 · How do you model hierarchies such as product category trees of varying depth?

Use a bridge table with ancestor-descendant pairs and depth (closure table), or flatten to fixed levels when depth is bounded; recursive CTEs can traverse parent-child tables but are slower for analytics.

DEN-326 · How would you model data for both historical accuracy and current reporting?

Use SCD Type 2 dimensions with surrogate keys for history, plus current flags or Type 1 overlay attributes (Type 6) so reports can show either the as-was or as-is view.

DEN-327 · How do you model multiple currencies in a sales fact table?

Store the transaction amount and currency, plus amounts converted to a reporting currency using rates from a currency rate dimension at the transaction date, and keep the rate used for auditability.

DEN-328 · When would you choose Data Vault over Kimball modelling?

Data Vault suits enterprises integrating many changing sources with strong auditability needs; it is flexible and append-only but needs a presentation layer. Kimball is simpler and faster to deliver for analytics.

DEN-329 · How do you design a model for event analytics at billions of events per day?

Use a wide, partitioned event fact table at event grain with key dimensions denormalised, store semi-structured properties in JSON or variant columns, and build aggregated tables for common queries.

DEN-330 · How do you keep data models from breaking when source schemas change?

Isolate sources behind a staging layer that maps raw columns to stable model columns, agree data contracts with producers, detect schema changes automatically (new, dropped or retyped columns) and fail fast in CI, and version models so consumers can migrate gradually.

DEN-331 · How would you model a many-to-many relationship between patients and diagnoses for analytics?

Use a diagnosis group bridge table linking a diagnosis group key on the fact to multiple diagnoses, with an allocation weight if measures must be split, so totals aren't double-counted when grouping by diagnosis.

DEN-332 · How do you decide the grain of a fact table when stakeholders want both detail and summaries?

Model at the most atomic grain available (e.g. order line) because it answers the widest range of questions, then add aggregate tables or materialised views for performance. Summaries can be derived from detail, but not the reverse.

DEN-333 · How would you model user identity across devices and accounts?

Create an identity resolution layer that maps device IDs, cookies, emails and account IDs to a persistent person key with match rules and confidence, track mapping history, and use the person key in facts.

Relational & NoSQL Databases

DEN-334 · How would you choose a database for an order-management system with 10,000 writes per second?

A relational database (e.g. PostgreSQL) with proper indexing, partitioning and connection pooling handles strong consistency and transactions; at higher scale use distributed SQL (CockroachDB, Spanner, Aurora) or shard by customer. Choose NoSQL only if relational guarantees aren't needed.

DEN-335 · How do you design a DynamoDB table for an access pattern-heavy application?

List access patterns first, choose partition and sort keys that serve the main queries with even partition distribution, use composite sort keys and GSIs for other patterns, and consider single-table design to fetch related items in one query.

DEN-336 · How do you avoid hot partitions in a NoSQL store?

Choose high-cardinality partition keys, add random or time-based suffixes (write sharding) for very popular keys, and spread writes evenly; monitor partition-level metrics.

DEN-337 · How would you scale a PostgreSQL database that is reaching its limits?

Optimise queries and indexes first, add read replicas for read traffic, use connection pooling (PgBouncer), partition large tables, cache hot data, and move to sharding (Citus) or a distributed database when writes exceed one node.

DEN-338 · How do you migrate a large table's schema with no downtime?

Use expand-and-contract: add new columns or tables, dual-write or backfill in batches, switch reads, then remove old structures; use online schema change tools (gh-ost, pg_repack) to avoid long locks.

DEN-339 · How do you choose an isolation level for a payment system?

Use serializable or repeatable read where anomalies like lost updates or write skew could cause financial errors, or use explicit locking for balance updates; weaker levels risk inconsistencies under concurrency.

DEN-340 · How do you implement CDC from a relational database to a data lake?

Use log-based CDC (Debezium reading the WAL or binlog) to stream changes into Kafka, then apply them to lake tables with MERGE, preserving operation type and ordering per key.

DEN-341 · How do you choose between a graph database and relational joins for relationship queries?

Graph databases excel at multi-hop traversals (friends of friends, fraud rings) with variable depth; relational databases handle fixed joins well. Choose graph when queries are dominated by deep, flexible relationships.

DEN-342 · How would you store high-volume IoT sensor data for both recent and historical queries?

Use a time-series database or columnar tables partitioned by time (and clustered by device), keep recent raw data hot for dashboards, downsample older data into minute or hourly rollups, compress aggressively, and apply retention policies that move or delete raw data after a set period.

DEN-343 · How do you keep a cache consistent with the database?

Use cache-aside: read from cache, fall back to the database, and invalidate (not update) the cache entry on every write; add TTLs as a safety net. Protect against races with versioned values or delayed double deletes, and accept short staleness where the business allows it.

DEN-344 · How do you back up a large production database so you can actually restore it?

Enable continuous backups with point-in-time recovery (WAL or binlog archiving plus periodic snapshots), copy backups to another region or account, encrypt them, and test full restores regularly against recovery time and recovery point objectives. An untested backup isn't a backup.

Data Warehousing

DEN-345 · How would you design a warehouse architecture for a company migrating from on-premise SQL Server?

Assess workloads and data volumes, choose a cloud warehouse, define layers (raw, staging, core, marts), build ingestion with CDC and batch tools, re-implement transformations in dbt or SQL, validate results against the old system in parallel runs, and migrate reports in phases.

DEN-346 · Reports show different revenue numbers from two teams. How would you resolve it?

Trace both numbers through lineage to their sources and logic, find definitional differences (gross vs net, refunds, time zones, currency, order status filters), agree a single certified metric definition with business owners, implement it once in a semantic layer or shared model, and deprecate the competing logic.

DEN-347 · How would you design a semantic layer for consistent metrics?

Define metrics, dimensions and joins centrally (dbt Semantic Layer, LookML, Cube) with owners and documentation, expose them to BI tools and APIs, and test metric outputs so every tool computes revenue or active users the same way.

DEN-348 · How do you handle late-arriving facts in a warehouse?

Load them with the correct event date into the right partition, look up dimension versions valid at the event time (SCD Type 2 effective dates), reprocess affected aggregates, and track data completeness per period.

DEN-349 · How would you design a warehouse for near-real-time dashboards?

Stream changes via CDC or event streams into the warehouse (Snowpipe Streaming, BigQuery streaming, Kafka connectors), use incremental models or dynamic tables refreshed every few minutes, and keep heavy history in batch layers.

DEN-350 · How do you choose distribution and sort keys in Redshift for a large fact table?

Distribute on the most frequently joined high-cardinality key (e.g. customer_id) to co-locate joins with a large dimension, use ALL distribution for small dimensions, and sort on the most common filter column, usually a date.

DEN-351 · How do you manage warehouse workloads so ETL doesn't slow down BI users?

Separate compute (different virtual warehouses or workload management queues), schedule heavy ETL off-peak, use resource monitors and query timeouts, and give BI warehouses autoscaling for concurrency.

DEN-352 · How would you implement data versioning for reproducible reports?

Use table snapshots or time travel, record report runs with data version identifiers, and store snapshot tables for official month-end results.

DEN-353 · How do you validate a warehouse migration?

Compare row counts, sums and distinct counts by key dimensions between old and new systems, run report reconciliation, sample record-level diffs, and run both in parallel before cutover.

DEN-354 · How would you model and load a large slowly changing dimension efficiently?

Use hash diffs to detect changed rows, MERGE only changed records, partition or cluster by key, and avoid full-table comparisons on each load.

DEN-355 · How do you design aggregate tables that stay correct?

Build them from the atomic fact with the same business logic, refresh incrementally by affected partitions, and test that totals reconcile with the detail.

ETL/ELT & Orchestration

DEN-356 · An Airflow DAG with 500 tasks runs slowly and the scheduler lags. What would you do?

Check scheduler and executor capacity (parsing time, pool and concurrency settings), reduce top-level code in DAG files, use dynamic task mapping or task groups, move heavy processing out of Airflow into Spark or the warehouse, scale workers (Celery or Kubernetes), and split very large DAGs.

DEN-357 · How would you design a pipeline that ingests data from 200 SaaS APIs?

Use a metadata-driven framework or managed connectors (Fivetran, Airbyte) with incremental syncs, per-source configs, rate-limit handling, schema change detection, raw landing in the lake, and monitoring of freshness and failures per source.

DEN-358 · How do you handle schema changes from upstream sources in ELT?

Land raw data with flexible schemas (variant or JSON columns), detect changes automatically, apply schema evolution where safe, alert owners for breaking changes, and update staging models through version-controlled changes.

DEN-359 · How do you build dependencies between DAGs owned by different teams?

Use dataset or asset-based scheduling (Airflow Datasets, Dagster assets) so downstream jobs run when upstream data is updated, or external task sensors with timeouts, and document data contracts between teams.

DEN-360 · How would you make a daily batch pipeline recoverable after partial failures?

Make each task idempotent and partition-based, record checkpoints, allow reruns of failed tasks only, and validate outputs before publishing to consumers (write-audit-publish).

DEN-361 · What is the write-audit-publish pattern?

Write new data to a staging location or branch, run data quality checks on it (audit), and only then atomically publish it to the production table. Consumers never see bad data; table-format branches (Iceberg, Nessie) and zero-copy clones make it cheap.

DEN-362 · How do you choose between Airflow, Dagster and Prefect?

Airflow is mature with a huge ecosystem and is task-centric; Dagster is asset-centric with strong typing, lineage and local testing; Prefect is Python-native with dynamic flows and easy deployment. Team skills, existing infrastructure and the need for data-asset awareness decide.

DEN-363 · How do you test dbt models effectively?

Use generic tests (unique, not_null, relationships, accepted_values), singular SQL tests for business rules, unit tests on small fixture inputs, source freshness checks, and run dbt build in CI against a slim, state-based selection of changed models.

DEN-364 · How do you handle a dbt incremental model when logic changes?

Run a full refresh (--full-refresh) to rebuild history with the new logic, or backfill only affected partitions; version the model if downstream consumers need time to adapt.

DEN-365 · How would you orchestrate a pipeline with strict SLAs?

Define SLAs per dataset, schedule with buffer time, add sensors and retries, alert early when tasks run late, monitor end-to-end latency and keep runbooks for incident response.

Apache Spark & Big Data

DEN-366 · A Spark join job has one task running for an hour while the others finish in minutes. How do you fix it?

This is key skew. Enable AQE skew join handling, broadcast the smaller table if possible, salt the hot keys (add a random suffix to the skewed side and replicate the other side), or process the hot keys separately and union the results.

DEN-367 · How would you size a Spark cluster for a 2 TB daily job?

Estimate data after filtering and shuffles, aim for partitions of about 128–256 MB, choose executors with about 4–5 cores and enough memory per core, size the cluster so the job meets its time window, then tune using the Spark UI (spill, GC time, shuffle sizes). Use autoscaling and spot instances where safe.

DEN-368 · How do you optimise writing output from Spark to avoid small files?

Repartition or coalesce by the partition columns before writing, set maxRecordsPerFile, enable optimized writes or auto-compaction in Delta/Iceberg, and run periodic compaction.

DEN-369 · How would you debug a Spark job that fails with "executor lost" errors?

Check executor logs for OOM kills by YARN or Kubernetes (memory overhead), long GC pauses, skewed partitions, or spot instance terminations; increase memory overhead, fix skew, reduce partition size and use reliable instances for critical jobs.

DEN-370 · When should you cache a DataFrame in Spark?

When it is reused multiple times in the same job and recomputing it is expensive; unpersist it after use. Caching data used once only wastes memory.

DEN-371 · How do you process slowly changing dimensions at scale in Spark?

Use table-format MERGE with hash comparisons on changed columns, partition by key ranges, and process only incremental changes rather than comparing entire dimensions.

DEN-372 · How would you migrate Hadoop jobs to a cloud lakehouse?

Inventory jobs and data, move storage to object storage with table formats, replace Hive and MapReduce with Spark on managed platforms, validate outputs against the old system, and decommission in phases.

DEN-373 · How do you reduce the cost of Spark jobs in the cloud?

Use autoscaling and spot instances, right-size executors, optimise code to reduce shuffles and scans, use efficient file formats and partition pruning, and schedule jobs to share clusters.

DEN-374 · How do you implement a deduplication step on billions of records in Spark?

Use window functions (ROW_NUMBER over key ordered by timestamp) or dropDuplicates on keys, partition the data by key to limit shuffles, and handle late duplicates with incremental MERGE.

DEN-375 · How would you debug slow Spark SQL queries?

Use the Spark UI SQL tab to find expensive stages, check the physical plan for scans, joins and exchanges, look at partition sizes and skew, and test optimisations like broadcast hints.

Streaming (Kafka, Flink, Spark Streaming)

DEN-376 · How would you design a real-time fraud detection pipeline?

Ingest transactions into Kafka, compute features in a stream processor (Flink) with keyed state (velocity counts, last location), call a low-latency model service or embedded model, write decisions back to Kafka for the payment system within tens of milliseconds, and persist events and features to the lake for training and audits.

DEN-377 · How do you choose the number of partitions for a Kafka topic?

Base it on target throughput divided by per-partition throughput for producers and consumers, the maximum consumer parallelism needed, and future growth; too many partitions increase broker overhead and rebalancing time. Keys should spread evenly.

DEN-378 · Consumer lag keeps growing on a Kafka topic. How do you fix it?

Find the bottleneck: slow processing per message, external calls, rebalances or skewed partitions. Scale consumers up to the partition count, increase partitions if needed, batch work, make external calls async, and fix hot keys.

DEN-379 · How do you achieve end-to-end exactly-once from Kafka to a database?

Use idempotent writes keyed by event ID (upserts) or transactional sinks that commit offsets and output together (two-phase commit in Flink), so retries don't create duplicates.

DEN-380 · How would you build a streaming pipeline that joins two event streams?

Use interval or window joins keyed on a shared ID with a time bound, watermarks to limit state, and handling for unmatched events (outer joins with timeouts), sizing state retention to the maximum expected delay.

DEN-381 · How do you handle schema evolution in a streaming system without downtime?

Use a schema registry with backward or full compatibility, add fields with defaults, deploy consumers that tolerate new fields before producers start sending them, and avoid removing or renaming fields abruptly.

DEN-382 · How do you reprocess historical data in a streaming architecture?

Replay from Kafka if retention allows (Kappa architecture), or run a batch job over lake data with the same logic, writing to a new table and swapping when complete.

DEN-383 · How would you monitor a production streaming pipeline?

Track consumer lag, throughput, processing latency, error and dead-letter rates, checkpoint duration and failures, state size, and data quality metrics, with alerts on SLA breaches.

DEN-384 · How do you design dead-letter handling in streaming?

Route records that fail parsing or processing to a dead-letter topic with error metadata, alert on rates, and provide tooling to fix and replay them.

DEN-385 · How do you choose between Kafka Streams, Flink and Spark Structured Streaming?

Kafka Streams is a lightweight library for Kafka-to-Kafka apps; Flink offers true event-at-a-time processing with strong state handling for complex low-latency jobs; Spark Structured Streaming suits micro-batch workloads and teams already using Spark.

Data Lakes & Lakehouse (Delta, Iceberg, Hudi)

DEN-386 · How would you design a lakehouse for a company with batch, streaming and ML workloads?

Store data in an open table format on object storage with bronze, silver and gold layers, ingest batch and streaming into bronze, apply quality checks and MERGE in silver, build gold marts and features, govern with a central catalog, and let SQL engines, Spark and ML tools read the same tables.

DEN-387 · Queries on a Delta table slowed down over months. What would you check?

Look for small files and fragmented data (run OPTIMIZE and compaction), a growing transaction log without checkpoints, missing clustering on filter columns, stale statistics and accumulated deleted files; vacuum old files and review partitioning.

DEN-388 · How do you choose between Delta Lake, Iceberg and Hudi?

Consider engine support (Iceberg has broad multi-engine support, Delta is deeply integrated with Databricks, Hudi excels at upsert-heavy CDC workloads), features like hidden partitioning and branching, governance integrations and team ecosystem.

DEN-389 · How do you implement CDC upserts into a lakehouse table efficiently?

Deduplicate changes per key within each batch (keep latest by sequence number), MERGE into the target on the key with update, insert and delete actions, cluster by the key to limit rewritten files, and compact regularly.

DEN-390 · How do you manage concurrent writers to the same lake table?

Table formats use optimistic concurrency: writers commit if no conflicting changes happened; design jobs to write different partitions, retry on conflicts, and avoid many small concurrent commits.

DEN-391 · How would you migrate a raw Parquet data lake to an open table format?

Convert existing Parquet tables in place where supported (CONVERT TO DELTA, Iceberg migrate/snapshot procedures) or rewrite them, register them in the catalog, switch writers to the table-format APIs, validate row counts and query results, and then enable compaction and retention policies.

DEN-392 · How do you use table-format time travel for debugging and recovery?

Query the table as of a previous version or timestamp to compare data before and after a bad load, and restore or roll back to the last good version if needed, within the retained history.

DEN-393 · How do you secure a data lake with many teams?

Use a central catalog with table-, column- and row-level permissions (Unity Catalog, Lake Formation, Ranger), restrict direct bucket access, separate zones by sensitivity, encrypt data, and audit access.

DEN-394 · How do you design partitioning for a table queried by date and customer?

Partition by date (moderate cardinality) and cluster or Z-order by customer, rather than partitioning by both, which would create too many small partitions.

DEN-395 · How would you reduce storage costs in a large lake?

Apply lifecycle policies to move cold data to cheaper storage tiers, expire old snapshots and vacuum unreferenced files, compress with efficient codecs, and delete unused datasets.

Cloud Data Platforms (AWS, Azure, GCP, Snowflake, Databricks)

DEN-396 · How would you design a cost-efficient Snowflake setup for 50 analysts and nightly ETL?

Use separate warehouses for ETL, BI and ad-hoc work sized to their needs, enable auto-suspend (e.g. 60 s) and auto-resume, use multi-cluster warehouses for BI concurrency, set resource monitors and budgets, and review query history for expensive patterns.

DEN-397 · How would you build a serverless analytics stack on AWS?

Land data in S3, catalogue it with Glue, transform with Glue jobs or EMR Serverless, query with Athena, orchestrate with Step Functions or MWAA, and govern with Lake Formation; costs scale with usage.

DEN-398 · How do you choose between BigQuery and Snowflake for a GCP-based company?

BigQuery is native to GCP, serverless, with tight integration to GCP services; Snowflake is multi-cloud with strong data sharing and workload isolation. Compare pricing model fit, ecosystem, performance on your workloads and skills.

DEN-399 · How would you implement disaster recovery for a cloud data warehouse?

Use cross-region replication or failover features (Snowflake replication, BigQuery cross-region datasets), back up critical data and configurations, define RPO and RTO, and test failover.

DEN-400 · How do you control cloud data costs across many teams?

Tag resources by team, publish cost dashboards, set budgets and alerts, enforce warehouse sizes and auto-suspend policies, and review expensive queries regularly (FinOps).

DEN-401 · How would you migrate data between cloud providers?

Plan data transfer (egress costs, transfer services), convert formats if needed, validate data, re-implement pipelines on the target platform, and run in parallel before cutover.

DEN-402 · How do you meet data residency requirements on a cloud data platform?

Keep regulated data in approved regions (separate accounts, projects or warehouses per region), disable or restrict cross-region replication and sharing, make sure backups, logs and BI extracts stay in-region too, and document data flows so auditors can verify where data is stored and processed.

DEN-403 · How do you design network security for a cloud data platform?

Use private endpoints and VPC/VNet connectivity so traffic doesn't cross the public internet, restrict access with network policies and IP allow-lists, separate production and development networks, route egress through controlled gateways and log network access.

DEN-404 · Why manage data platform infrastructure as code, and how?

Define warehouses, storage, IAM roles, networking and grants in Terraform (or similar) so changes are reviewed, versioned and reproducible across environments; run plans in CI, apply through pipelines, and detect drift between code and the real configuration.

DEN-405 · How do you choose between Databricks and Snowflake for a new platform?

Databricks is strongest for Spark-heavy engineering, streaming, ML and open table formats on your own storage; Snowflake is strongest for SQL analytics with little tuning, concurrency isolation and data sharing. Compare on your real workloads, skills and costs; many organisations use both, with clear boundaries.

Data Quality, Governance & Security

DEN-406 · A broken upstream feed silently loaded zeros for revenue yesterday. How would you prevent this happening again?

Add checks at ingestion and transformation: volume and freshness monitors, distribution and anomaly checks on key metrics (a sudden drop to zero), not-null and range tests, and a write-audit-publish step that blocks bad data from reaching dashboards. Agree a data contract with the source team and alert owners on failures.

DEN-407 · How would you roll out a data governance programme in a company with no governance today?

Start with the most critical data domains and use cases, assign owners and stewards, define key metrics and glossary terms, deploy a catalog with lineage and ownership, classify sensitive data and apply access policies, and measure adoption and data quality improvements over time.

DEN-408 · How do you implement data contracts in practice?

Define schemas, semantics, quality rules and SLAs in version-controlled files owned by producers, validate producer output in CI and at runtime (schema registry, contract tests), notify consumers about planned breaking changes, and track contract violations.

DEN-409 · How would you handle a data breach involving a data warehouse?

Follow the incident response plan: contain access (revoke credentials, block endpoints), assess scope with audit logs, preserve evidence, notify security and legal teams, meet regulatory notification deadlines, fix the root cause and review controls.

DEN-410 · How do you implement column-level encryption for highly sensitive fields?

Encrypt sensitive columns with keys managed in a KMS, give decryption rights only to specific roles or services, use tokenisation for fields needed in joins, and audit key usage.

DEN-411 · How would you build lineage across Airflow, dbt and BI tools?

Use OpenLineage-compatible integrations to emit lineage events, ingest dbt manifests and BI metadata into a catalog (DataHub, OpenMetadata), and connect them by dataset identifiers.

DEN-412 · How do you measure data quality improvement over time?

Track quality scores per dataset (test pass rates, incidents, freshness SLA adherence, time to resolve), report trends to owners, and prioritise datasets with frequent issues.

DEN-413 · How would you anonymise data for analytics while keeping it useful?

Remove direct identifiers, generalise or bucket quasi-identifiers, apply k-anonymity or differential privacy where needed, and test re-identification risk.

DEN-414 · How do you manage access requests at scale?

Use self-service access workflows with approvals by data owners, role-based or attribute-based policies, and automatic expiry of temporary access.

DEN-415 · How do you handle data quality issues that originate in source systems?

Report them to source owners with examples, fix at the source where possible, document known issues, and apply clearly labelled corrections in the pipeline when necessary.

Python, Pipeline Design & Cost Optimization

DEN-416 · How would you design a scalable ingestion framework for hundreds of sources?

Make it metadata-driven: each source has a config (connection, tables, incremental column, schedule, schema rules), a generic engine handles extraction, landing, schema evolution and logging, and monitoring tracks freshness and errors per source. Adding a source becomes a config change rather than new code.

DEN-417 · How would you cut a pipeline's runtime from 6 hours to 1 hour?

Profile each stage, process incrementally instead of full reloads, parallelise independent tasks, push heavy work to Spark or the warehouse, optimise joins and file formats, remove redundant steps, and size compute appropriately.

DEN-418 · How do you design pipelines to be cost-aware?

Process only changed data, choose appropriate compute sizes and auto-suspend, use spot or serverless compute, store data in efficient formats, and track cost per pipeline.

DEN-419 · How would you structure a Python codebase for data pipelines?

Separate reusable libraries (connectors, transformations) from pipeline definitions, use typed configuration, unit tests, linting and packaging, and run code through CI/CD.

DEN-420 · How do you handle secrets rotation without breaking pipelines?

Fetch secrets at runtime from a secret manager, support overlapping old and new credentials during rotation, and monitor authentication failures.

DEN-421 · How do you design one pipeline that serves both daily runs and historical backfills?

Parameterise every job by a date or partition range, keep each run idempotent (overwrite or MERGE the target partition), avoid using 'today' inside logic, and let the orchestrator trigger the same code for one day or a range with controlled concurrency.

DEN-422 · How do you decide whether to build or buy a data tool?

Compare total cost of ownership (licences vs engineering and maintenance time), time to value, how well the product fits your requirements, integration with your stack, security and compliance, vendor lock-in and exit cost, and whether the capability is a differentiator worth owning.

DEN-423 · How do you reduce cloud data egress costs?

Keep compute in the same region and cloud as the data, avoid repeatedly copying data across regions or clouds, compress transfers, cache query results, use private interconnects for large steady flows and share data in place (data sharing) instead of exporting it.

DEN-424 · How would you monitor the reliability of all data pipelines centrally?

Collect run metadata (status, duration, retries), data freshness, row counts and quality results from orchestrators and tests into one observability store, show SLA dashboards per domain, alert owners on failures and late data, and track incidents and recovery times.

DEN-425 · How do you plan capacity and budget for a growing data platform?

Track growth in data volume, query volume, pipeline runtime and cost per workload over time, forecast from business plans (new products, users), set scaling plans and budgets with headroom, and revisit them quarterly against actual usage.

Other levels: Basic · Intermediate · Tricky · All Data Engineering questions