Wipro Gcp Data Engineer Interview Questions and Answers

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

Developed a data pipeline to ingest, process, and analyze customer feedback data for a retail company.

• Used Google Cloud Platform services like BigQuery, Dataflow, and Pub/Sub for data processing.

• Implemented data cleansing and transformation techniques to ensure data quality.

• Created visualizations and dashboards using tools like Data Studio for stakeholders to easily interpret the data.

GCP offers different storage classes for varying performance and cost requirements.

• Standard Storage: for frequently accessed data

• Nearline Storage: for data accessed less frequently

• Coldline Storage: for data accessed very infrequently

• Archive Storage: for data stored for long-term retention

SQL optimization techniques focus on improving query performance by reducing execution time and resource usage.

• Use indexes to speed up data retrieval

• Avoid using SELECT * and instead specify only the columns needed

• Optimize joins by using appropriate join types and conditions

• Limit the use of subqueries and instead use JOINs where possible

• Use EXPLAIN to analyze query execution plans and identify bottlenecks

GCP BigQuery is a serverless, highly scalable, and cost-effective data warehouse for analyzing big data sets.

• BigQuery is a fully managed, petabyte-scale data warehouse that enables super-fast SQL queries using the processing power of Google's infrastructure.

• BigQuery's architecture includes storage, Dremel execution engine, and SQL layer.

• Cloud Composer is a managed workflow orchestration service that helps you create, s...read more

BigQuery is used for analyzing large datasets and running complex queries, while SQL is used for querying databases.

• BigQuery is used for analyzing large datasets quickly and efficiently

• SQL is used for querying databases to retrieve specific data

• BigQuery can handle petabytes of data, making it ideal for big data analysis

• SQL can be used to perform operations like filtering, sorting, and aggregating data

Bigquery is a cloud-based data warehousing tool used for analyzing large datasets quickly. Pubsub is a messaging service, Dataflow is a data processing tool, and Cloud Storage is a scalable object storage service.

• Bigquery is used for analyzing large datasets quickly

• Pubsub is a messaging service used for asynchronous communication between applications

• Dataflow is a data processing tool used for batch and stream processin...read more