Tech Mahindra Gcp Data Engineer Interview Questions and Answers

Window functions in BigQuery are used to perform calculations across a set of table rows related to the current row.

• Window functions allow you to perform calculations on a set of rows related to the current row

• They are used with the OVER() clause in SQL queries

• Common window functions include ROW_NUMBER(), RANK(), and NTILE()

• They can be used to calculate moving averages, cumulative sums, and more

Types of NoSQL databases in GCP include Firestore, Bigtable, and Datastore.

• Firestore is a flexible, scalable database for mobile, web, and server development.

• Bigtable is a high-performance NoSQL database service for large analytical and operational workloads.

• Datastore is a highly scalable NoSQL database for web and mobile applications.

Code to find max number of product by customer

• Iterate through each customer's purchases

• Keep track of the count of each product for each customer

• Find the product with the maximum count for each customer

Creating a dataframe in GCP Data Engineer

• Use the pandas library to create a dataframe

• Provide data in the form of a dictionary or list of lists

• Specify column names if needed

The GCP services used in our project include BigQuery, Dataflow, Pub/Sub, and Cloud Storage.

• BigQuery for data warehousing and analytics

• Dataflow for real-time data processing

• Pub/Sub for messaging and event ingestion

• Cloud Storage for storing data and files

Cloud Functions are event-driven functions that run in response to cloud events.

• Serverless functions that automatically scale based on demand

• Can be triggered by events from various cloud services

• Supports multiple programming languages like Node.js, Python, etc.

To schedule a job to trigger every hour in Airflow, you can use the Cron schedule interval

• Define a DAG (Directed Acyclic Graph) in Airflow

• Set the schedule_interval parameter to '0 * * * *' to trigger the job every hour

• Example: schedule_interval='0 * * * *'

Use Python's slicing feature to display a string in reverse order.

• Use string slicing with a step of -1 to reverse the string.

• Example: 'hello'[::-1] will output 'olleh'.

Pub/Sub is a messaging service that allows communication between independent applications.

• Pub/Sub is used for real-time messaging and event-driven systems.

• It is commonly used for data ingestion, streaming analytics, and event-driven architectures.

• Examples of Pub/Sub services include Google Cloud Pub/Sub, Apache Kafka, and Amazon SNS/SQS.

I have experience working on projects involving data processing, transformation, and analysis using GCP services like BigQuery, Dataflow, and Dataproc.

• Utilized BigQuery for storing and querying large datasets

• Implemented data pipelines using Dataflow for real-time data processing

• Utilized Dataproc for running Apache Spark and Hadoop clusters for data processing

• Worked on data ingestion and transformation using Cloud Stora...read more

Shuffle partition is a data processing technique used to redistribute data across partitions in distributed computing.

• Shuffle partition helps in balancing the load across different nodes in a distributed system.

• It is commonly used in frameworks like Apache Spark during operations like groupBy and join.

• For example, when joining two large datasets, shuffle partition ensures that related data is processed together.

• Imprope...read more

You can identify file size in Python using the os module or pathlib for efficient file handling.

• Use os.path.getsize() to get the size of a file in bytes. Example: os.path.getsize('file.txt')

• Use pathlib.Path.stat() to retrieve file size. Example: from pathlib import Path; Path('file.txt').stat().st_size

• File size can also be checked using the built-in open() function with os.fstat(). Example: os.fstat(open('file.txt').fi...read more

Google Cloud Dataflow supports Java and Python for building data processing pipelines.

• Java: Widely used for building robust data pipelines; example: Apache Beam SDK for Java.

• Python: Popular for its simplicity and ease of use; example: Apache Beam SDK for Python.

• Both languages allow for the creation of batch and streaming data processing applications.

Check if a string is a palindrome or not

• Compare the string with its reverse to check for palindrome

• Ignore spaces and punctuation marks when comparing

• Examples: 'racecar' is a palindrome, 'hello' is not

Use Python to create a GCS bucket

• Import the necessary libraries like google.cloud.storage

• Authenticate using service account credentials

• Use the library functions to create a new bucket

Python code to trigger a dataflow job in cloud function

• Use the googleapiclient library to interact with the Dataflow API

• Authenticate using service account credentials

• Submit a job to Dataflow using the projects.locations.templates.launch endpoint

Use SQL to find keys present in table A but not in table B (old copy of A).

• Use a LEFT JOIN to combine tables A and B based on the key column

• Filter the results where the key column in table B is NULL

• This will give you the keys present in table A but not in table B

SQL query to retrieve the 4th highest salary from a salary table using various methods.

• Use the 'DISTINCT' keyword to avoid duplicate salaries.

• Utilize 'ORDER BY' to sort salaries in descending order.

• Use 'LIMIT' with 'OFFSET' to skip the first three highest salaries.

• Example SQL: 'SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 3;'

Use GCP Dataflow to transfer files between GCS buckets

• Create a Dataflow pipeline using Apache Beam to read from source bucket and write to destination bucket

• Use GCS connector to read and write files in Dataflow pipeline

• Set up appropriate permissions for Dataflow service account to access both buckets

Explaining project flow and ownership to a new IT joiner involves outlining roles, responsibilities, and collaboration.

• 1. Project Initiation: Discuss how projects start with requirements gathering and stakeholder meetings.

• 2. Role Assignment: Explain how tasks are assigned based on team members' strengths and expertise.

• 3. Collaboration Tools: Introduce tools like JIRA or Trello for tracking progress and ownership.

• 4. Reg...read more

Implemented a data pipeline using Airflow for ETL processes, enhancing workflow management and scheduling.

• Airflow's DAG (Directed Acyclic Graph) structure allows for clear visualization of task dependencies.

• It supports dynamic pipeline generation, enabling flexibility in defining workflows based on external parameters.

• Airflow has a rich user interface for monitoring and managing workflows, making it easier to troublesh...read more

Cloud Composer is another orchestration tool in GCP

• Cloud Composer is a fully managed workflow orchestration service built on Apache Airflow

• It allows you to author, schedule, and monitor workflows that span across GCP services

• Cloud Composer provides a rich set of features like DAGs, plugins, and monitoring capabilities

• It integrates seamlessly with other GCP services like BigQuery, Dataflow, and Dataproc