Q: Your ingestion script fails intermittently on weekends but runs fine on weekdays. What steps do you take to debug this issue?

Intermittent weekend failures point to a time dependent or environment dependent root cause . A systematic debugging approach: 1. Gather evidence from logs: Review pipeline logs, Airflow task logs, and cloud logging (Cloud Logging / CloudWatch) for error messages and stack traces. Compare a successful weekday run vs a failed weekend run — look for differences in execution time, error message, row counts. 2. Identify potential weekend specific causes: Data volume differences : Weekend data might be significantly lower (or higher) — check if the script times out waiting for data or crashes on empty datasets. Source system downtime : The source database or API might have maintenance windows on weekends. Check if the source was accessible during the failure. Batch job conflicts : Other jobs run on weekends that compete for resources (slots, connections, memory). External API rate limits : Fewer requests during the week may keep you under rate limits, but weekend batches may hit them. Date/calendar logic bug : Script might use WEEKDAY or date offset logic that behaves incorrectly over weekends. 3. Add instrumentation: Add logging at each stage (source read, transform, write) with row counts and timestamps. Add a pre flight check: verify source is reachable and non empty before processing. 4. Reproduce: Run the script manually on a weekend morning with verbose logging. Add retry logic with exponential backoff to handle transient network failures. 5. Fix and monitor: Implement idempotent writes (overwrite instead of append) so reruns don't double count data.

Q: What are the methods available for offline data center migration to Google Cloud Platform (GCP)?

Offline migration moves data to GCP without relying solely on internet connectivity — critical for large datasets where online transfer would take days or weeks: 1. Transfer Appliances (Google Transfer Appliance): Google ships a physical high capacity appliance (100 TB or 480 TB) to your data center. You copy data to it locally at network speeds (not internet speeds), then ship it back to Google. Google uploads the data to GCS from the appliance at their data center. Best for multi TB to PB scale migrations. 2. BigQuery Data Transfer Service: Managed service to transfer data from SaaS sources (S3, Redshift, Teradata, other clouds) into BigQuery. Supports scheduled, recurring transfers, not just one time migrations. 3. gsutil / Storage Transfer Service (online with high bandwidth dedicated link): If a Cloud Interconnect or Partner Interconnect link is available, use gsutil m cp (parallel) or Storage Transfer Service for large scale transfers over the dedicated line. 4. Database Migration Service (DMS): Managed service for migrating databases (MySQL, PostgreSQL, SQL Server, Oracle) to Cloud SQL or AlloyDB with minimal downtime. Supports both one time and continuous replication. 5. Migrate for Compute Engine (Velostrata): Lift and shift migration of VMs from on premises VMware or other hypervisors to GCE. Choosing the right method : Evaluate by data size, available bandwidth, required downtime, and whether structured or unstructured data is being migrated.

Question 1

Explain the difference between RANK(), DENSE_RANK() and ROW_NUMBER() in SQL.

Accepted Answer

All three are SQL window functions that assign a number to rows within a result set ordered by a specific column: Function Behavior with ties Gaps after ties ROW NUMBER() Always unique — arbitrary ordering among tied rows N/A (always sequential) RANK() Tied rows get the same rank Yes — gaps exist (1, 1, 3, 4…) DENSE RANK() Tied rows get the same rank No — no gaps (1, 1, 2, 3…) Sample output (two employees with salary 90000): When to use each: ROW NUMBER() : Pagination, deduplication (pick one row per group). RANK() : Competitions where tied entries should reflect the real gap in standings. DENSE RANK() : Most common for "top N per group" queries where gaps are undesirable.

Question 2

What is a CTE (Common Table Expression)? When is it better to use a CTE over subqueries?

Accepted Answer

A CTE (Common Table Expression) is a named, temporary result set defined with the WITH keyword that exists only for the duration of a single query. CTEs vs Subqueries: CTE Subquery Readability High — named and defined at the top Lower for complex, nested queries Reuse Can be referenced multiple times in the main query Must be repeated each time Recursion Recursive CTEs (WITH RECURSIVE) are possible Not supported Debugging Easier to test in isolation Harder to isolate intermediate steps Performance Usually same as subquery; optimizer may or may not materialize Optimizer may inline or materialize similarly Prefer CTEs when: The same intermediate result is referenced more than once. The query has multiple levels of aggregation or transformation. You need a recursive query (hierarchical data like org charts). Readability and maintainability are priorities. Prefer subqueries when : The intermediate result is simple and used only once — a subquery keeps the logic co located.

Question 3

How is exception handling managed in production-grade Python pipelines?

Accepted Answer

Production Python data pipelines require robust exception handling to ensure observability, graceful degradation, and recoverability: Structured try/except with specific exceptions: Key patterns: Catch specific exceptions — avoid bare except: which catches SystemExit and KeyboardInterrupt . Re raise critical errors (network, DB connection) so Airflow/Celery retry mechanisms can kick in. Dead letter queues / quarantine files : Write failed records to a separate location instead of dropping them silently. Structured logging with logger.exception() to capture the full traceback. Circuit breaker pattern : If failure rate exceeds a threshold, stop processing and alert rather than hammering a failing downstream system. Alerts : Integrate with PagerDuty, Slack, or email via Airflow callbacks ( on failure callback ) for immediate notification.

Question 4

How would you persist intermediate data in a PySpark pipeline? Compare cache() vs persist().

Accepted Answer

In PySpark, caching and persisting store a DataFrame in memory (and optionally disk) to avoid recomputing it when it is used multiple times in the DAG. cache() is a shorthand for persist(StorageLevel.MEMORY AND DISK) : persist(StorageLevel) gives explicit control over storage: When to use: Cache when a DataFrame is used in 2+ downstream actions (avoids full recomputation each time). Use DISK ONLY for very large DataFrames that don't fit in executor memory. Always call .unpersist() when done to free memory explicitly. Caching does NOT persist across Spark sessions — it is lost when the session ends.

Question 5

Explain the role of DAG (Directed Acyclic Graph) in Spark job execution.

Accepted Answer

In Apache Spark, a DAG (Directed Acyclic Graph) represents the logical execution plan of a job — a graph of transformations where each node is an RDD/DataFrame operation and edges represent data dependencies. How Spark builds and uses the DAG: 1. Lazy evaluation : When you call transformations ( filter , map , join ), Spark does not execute them immediately. It builds a DAG representing what needs to be done . 2. Action triggers execution : When an action ( count , collect , save ) is called, the DAG Scheduler analyzes the DAG and creates an execution plan. 3. Stage division : The DAG Scheduler splits the DAG into stages at shuffle boundaries (wide transformations). Operations within a stage are pipelined (no network I/O). 4. Task creation : Each stage is divided into tasks (one per partition), which the Task Scheduler distributes to executor cores. Benefits of DAG based execution: Optimization : Catalyst optimizer can reorder, fuse, and push down operations by analyzing the full DAG before execution. Fault tolerance : If a partition is lost, Spark recomputes only the lost partition by replaying the relevant DAG path — no full recomputation needed. Pipelining : Multiple narrow transformations (map, filter) are combined into a single stage, minimizing data movement. Visualizing the DAG : Use the Spark UI → Jobs → DAG Visualization to inspect stage boundaries and transformation chains.

Question 6

How does Spark handle data shuffles? What are broadcast joins and when would you use them?

Accepted Answer

Shuffles occur during wide transformations ( groupBy , join , distinct , repartition ) and involve redistributing data across partitions over the network — the most expensive Spark operation. How shuffles work: 1. Each executor writes shuffle output (map output files) to local disk, partitioned by the target key's hash. 2. In the reduce phase, each executor reads its assigned partitions from all other executors over the network. 3. The number of reduce partitions is controlled by spark.sql.shuffle.partitions (default: 200). Minimizing shuffle impact: Tune spark.sql.shuffle.partitions to cluster size. Pre partition data by join keys using bucketBy . Use broadcast joins to eliminate shuffle entirely for small tables. Broadcast joins: A broadcast join sends a copy of the smaller DataFrame to every executor so the join can be performed locally without any network shuffle: When to use broadcast joins: One table is small (< threshold set by spark.sql.autoBroadcastJoinThreshold , default 10 MB). The large table is extremely wide — shuffling it would be prohibitively expensive. The join is performed repeatedly in a loop — broadcasting once and reusing avoids repeated shuffles. When NOT to use : If the small table is not actually small ( 100 MB), broadcasting causes OOM on executors.

Question 7

Your ingestion script fails intermittently on weekends but runs fine on weekdays. What steps do you take to debug this issue?

Accepted Answer

Intermittent weekend failures point to a time dependent or environment dependent root cause . A systematic debugging approach: 1. Gather evidence from logs: Review pipeline logs, Airflow task logs, and cloud logging (Cloud Logging / CloudWatch) for error messages and stack traces. Compare a successful weekday run vs a failed weekend run — look for differences in execution time, error message, row counts. 2. Identify potential weekend specific causes: Data volume differences : Weekend data might be significantly lower (or higher) — check if the script times out waiting for data or crashes on empty datasets. Source system downtime : The source database or API might have maintenance windows on weekends. Check if the source was accessible during the failure. Batch job conflicts : Other jobs run on weekends that compete for resources (slots, connections, memory). External API rate limits : Fewer requests during the week may keep you under rate limits, but weekend batches may hit them. Date/calendar logic bug : Script might use WEEKDAY or date offset logic that behaves incorrectly over weekends. 3. Add instrumentation: Add logging at each stage (source read, transform, write) with row counts and timestamps. Add a pre flight check: verify source is reachable and non empty before processing. 4. Reproduce: Run the script manually on a weekend morning with verbose logging. Add retry logic with exponential backoff to handle transient network failures. 5. Fix and monitor: Implement idempotent writes (overwrite instead of append) so reruns don't double count data.

Question 8

What are the methods available for offline data center migration to Google Cloud Platform (GCP)?

Accepted Answer

Offline migration moves data to GCP without relying solely on internet connectivity — critical for large datasets where online transfer would take days or weeks: 1. Transfer Appliances (Google Transfer Appliance): Google ships a physical high capacity appliance (100 TB or 480 TB) to your data center. You copy data to it locally at network speeds (not internet speeds), then ship it back to Google. Google uploads the data to GCS from the appliance at their data center. Best for multi TB to PB scale migrations. 2. BigQuery Data Transfer Service: Managed service to transfer data from SaaS sources (S3, Redshift, Teradata, other clouds) into BigQuery. Supports scheduled, recurring transfers, not just one time migrations. 3. gsutil / Storage Transfer Service (online with high bandwidth dedicated link): If a Cloud Interconnect or Partner Interconnect link is available, use gsutil m cp (parallel) or Storage Transfer Service for large scale transfers over the dedicated line. 4. Database Migration Service (DMS): Managed service for migrating databases (MySQL, PostgreSQL, SQL Server, Oracle) to Cloud SQL or AlloyDB with minimal downtime. Supports both one time and continuous replication. 5. Migrate for Compute Engine (Velostrata): Lift and shift migration of VMs from on premises VMware or other hypervisors to GCE. Choosing the right method : Evaluate by data size, available bandwidth, required downtime, and whether structured or unstructured data is being migrated.

Tata Digital Interview Questions