Datametica Interview Questions

The data has gender values swapped — male names are marked Female and vice versa. The fix uses a CASE expression in an UPDATE: After the update: In BigQ...

• Can you write a query to correct this table? Table1 - id, name, salary, gender Data: 100, Anil, 2000, Female 101, Hari, 2500, Female 102, Abhishikta, 3000, Male 103, Meghna, 3500, Male

  The data has gender values swapped — male names are marked Female and vice versa. The fix uses a CASE expression in an UPDATE: After the update: In BigQuery (no direct UPDATE without a full table replace): Production note : Always run a SELECT with the CASE before executing the UPDATE to validate the result set. Wrap in a transaction where supported.

• Find the nth highest salary from an employee table without using a window function.

  To find the nth highest salary without window functions, use a correlated subquery or the LIMIT/OFFSET approach: Method 1 — Correlated subquery (ANSI SQL): The inner query counts how many distinct salaries are greater than or equal to the outer row's salary. When that count equals n , we've found the nth highest. Method 2 — LIMIT/OFFSET (MySQL / PostgreSQL): Method 3 — Double subquery: Note : The window function approach ( DENSE RANK() ) is cleaner and more scalable in practice; the non window a

• Table_1 id, customer.id, customer.address, metric.price, metric.qty, perclick.views, perclick.buys Find the total number of perclick buys for a particular customer. id:{ customer:{ id, address }, product:{ price, qty }, perclick:{ views, buys } }

  The schema is nested/repeated (JSON style with structs), typical of BigQuery or similar columnar stores. To query total perclick.buys for a specific customer: If the table has a flat structure with dot notation columns: If the table uses BigQuery STRUCT (nested schema): If perclick is a REPEATED STRUCT (array of click events per row): Key concept : UNNEST() flattens a repeated/array column into rows, which allows standard SUM() aggregation. This pattern is fundamental in BigQuery when working wi

• How to handle the situation when the partition reaches its threshold of 4000 partitions?

  In BigQuery , a table can have at most 4,000 partitions (for date/timestamp partitioned tables) or 10,000 range partitions . Hitting the limit breaks new writes. Strategies to handle this: 1. Increase partition granularity (coarser partitioning): If currently partitioned by DAY, switch to MONTH or YEAR partitioning to reduce the number of partitions: 2. Archive or expire old partitions: Set a partition expiration policy to automatically drop old partitions: 3. Cluster instead of partition (or cl

• What is the maximum number of partitions we can create in BigQuery?

  BigQuery partition limits: Partition Type Maximum Partitions Ingestion time / date / timestamp partitioning (DAY) 4,000 Ingestion time / date / timestamp partitioning (HOUR) 4,000 Integer range partitioning 10,000 Important notes: The 4,000 partition limit for date partitioned tables means you can store roughly 10 years of daily partitions (365 × 11 ≈ 4,015) before hitting the cap. Each partition stores data files; BQ enforces the limit to keep metadata management efficient. You can check curren

• How to choose between partitions and bucketing?

  Partitioning and bucketing (clustering in BigQuery) serve different performance optimization goals: Partitioning Bucketing / Clustering How it works Divides data into separate physical storage segments by a column value Sorts/groups data within partitions (or the whole table) by column values Best for Filtering on the partition key — skips entire partitions not matching the WHERE clause Joins and aggregations on the clustered column — reduces data scanned within a partition Column type Date, tim

• How to handle the situation in Airflow when task2 should not be dependent on task1?

  By default, Airflow tasks run sequentially based on the dependency chain you define. If task2 must run independently of task1 (no dependency between them), you have several options: 1. Simply do not define a dependency between them: 2. Use TriggerRule to decouple execution from upstream state: 3. Split into separate DAGs: If the tasks truly have no logical relationship, put them in separate DAGs. Each runs on its own schedule independently. Common trigger rules: ALL SUCCESS (default), ALL DONE ,

• How to extract different types of data using Spark? How to connect it with BigQuery?

  Reading different data types in Spark: Connecting to BigQuery from Dataproc/Spark: Requires the spark bigquery connector JAR and appropriate GCP IAM permissions ( BigQuery Data Editor , BigQuery Job User ).