Question 1

Write a Python function to determine if a given string is a palindrome.

Accepted Answer

A palindrome reads the same forwards and backwards. Several approaches: Edge cases to handle: Empty string → True (vacuously palindrome). Single character → True . Mixed case → normalize with .lower() . Spaces and punctuation → strip before comparing (for natural language palindromes). Time complexity : O(n) for both methods. The two pointer method avoids creating a reversed copy, using O(1) extra space versus O(n) for the slice approach.

Question 2

Develop a Python script to create a Google Cloud Storage (GCS) bucket.

Accepted Answer

Use the google cloud storage library: Prerequisites: Install: pip install google cloud storage Authenticate: Set GOOGLE APPLICATION CREDENTIALS env var pointing to a service account JSON, or use Application Default Credentials ( gcloud auth application default login ). Storage classes: STANDARD , NEARLINE (30 day min), COLDLINE (90 day min), ARCHIVE (365 day min) — choose based on access frequency and cost requirements. Bucket naming rules : Globally unique, 3–63 characters, lowercase letters, numbers, and hyphens only.

Question 3

You have a production table with duplicate data. While you cannot delete existing rows due to business rules, you need to prevent further duplication. How would you achieve this?

Accepted Answer

When existing duplicate rows must stay but no new duplicates should be inserted, several prevention strategies apply: 1. Add a UNIQUE constraint on the natural key (if supported by the DB): 2. Create a UNIQUE index while ignoring existing duplicates: 3. Use an INSERT guard at the application/pipeline layer: 4. Implement a staging → merge pattern: Land new data in a staging table first. Use a MERGE statement that only inserts rows not already present in the target. 5. Add a CHECK constraint or trigger: A database trigger that runs BEFORE INSERT can query for existing rows and raise an error if a duplicate is detected. Best practice : Combine a UNIQUE constraint (for enforcement) with INSERT ... ON CONFLICT DO NOTHING (for graceful handling at the application layer).

Question 4

Explain what a stored procedure is in SQL and write the syntax for creating one.

Accepted Answer

A stored procedure is a named, precompiled set of SQL statements stored in the database that can be executed by calling the procedure name. It encapsulates logic, improves performance (execution plan is cached), and promotes code reuse. Generic SQL syntax: PostgreSQL example: Key benefits: Performance : Execution plan is cached; reduces repeated parsing overhead. Security : Grant EXECUTE permission without exposing underlying table permissions. Maintainability : Business logic lives in one place, not scattered across application code. Note : BigQuery does not support traditional stored procedures in the same way — it supports scripting blocks and CREATE PROCEDURE with limited functionality.

Question 5

How can you optimize performance in BigQuery?

Accepted Answer

BigQuery performance optimization operates across several dimensions: 1. Reduce data scanned (most impactful): Partition tables by date/timestamp and always filter on the partition column. Cluster tables on frequently filtered/joined columns (up to 4). Select only needed columns — avoid SELECT on wide tables. 2. Optimize query structure: Filter early — apply WHERE clauses before joins. Avoid functions on partition columns in WHERE (e.g., DATE(timestamp col) can disable partition pruning; use timestamp col BETWEEN ... AND ... ). Use approximate aggregate functions ( APPROX COUNT DISTINCT ) where exact values aren't needed. 3. Control data skew in joins: Pre aggregate before joining large tables. Use LIMIT in subqueries only with caution — BQ doesn't guarantee predicate pushdown. 4. Use caching and materialized views: BQ caches query results for 24 hours; identical queries reuse cached results at no cost. Materialized views precompute and incrementally maintain aggregations. 5. Slot management: Use reservations (flat rate) for consistent high volume workloads. Monitor slot utilization in Cloud Monitoring to identify contention. 6. Table design: Denormalize appropriately — BQ is optimized for wide, flat tables with nested/repeated fields rather than heavily normalized schemas.

Question 6

How can I change the executor in Google Cloud Composer?

Accepted Answer

Google Cloud Composer runs Airflow and supports switching the executor via environment configuration: Default executor : Composer 2 uses CeleryExecutor by default (distributed across workers). Composer 1 also supports LocalExecutor for small environments. Changing the executor in Composer: 1. Via the Console (Composer 2): Navigate to Composer → your environment → Edit . Under Airflow configuration overrides , add: Section: core Key: executor Value: LocalExecutor (or CeleryExecutor , KubernetesExecutor ) Save and wait for the environment to update. 2. Via gcloud CLI: 3. Via Terraform (for IaC managed environments): When to change: Use LocalExecutor for small, low concurrency workloads (cheaper, simpler). Use CeleryExecutor for high concurrency parallel task execution. Use KubernetesExecutor for complete task isolation and dynamic resource scaling per task.

Question 7

Calculate the cumulative sum of revenue for each product over time. Display the product name, order date, revenue and cumulative revenue.

Accepted Answer

Use a SQL window function with SUM() OVER (PARTITION BY ... ORDER BY ...) : Sample output: Key window frame clause: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — includes all rows from the start of the partition up to and including the current row. This is the standard cumulative sum frame. PARTITION BY product id — resets the running total for each product. ORDER BY order date — defines the order in which revenue is accumulated. In PySpark:

Question 8

How many jobs, stages, and tasks are created during a Spark job execution?

Accepted Answer

Understanding Spark's execution hierarchy (Job → Stages → Tasks) is fundamental: Jobs: One job is created per action called on a DataFrame or RDD (e.g., collect() , count() , save() , show() ). A single application can have many jobs. Stages: A job is divided into stages at every shuffle boundary (wide transformation: groupBy , join , repartition , distinct ). Between shuffles, operations are pipelined into a single stage. Typical job: 2 stages for a single groupBy (1 map stage + 1 reduce stage). Tasks: Each stage is divided into tasks — one task per partition . If a DataFrame has 200 partitions, a stage over it spawns 200 tasks. Tasks run in parallel across executor cores. Example for a groupBy aggregation on 100 partition DataFrame: 1 Job (triggered by .show() ) 2 Stages: Stage 1 (partial map — 100 tasks), Stage 2 (reduce/shuffle — tasks = spark.sql.shuffle.partitions , default 200) Total tasks: 100 + 200 = 300 Formula: Jobs = number of actions Stages = number of shuffle boundaries + 1 per job Tasks per stage = number of partitions in that stage's input data Use the Spark UI → Jobs tab → Stage details to inspect this hierarchy for any running application.

TCS Interview Questions