Fragma Data Systems Interview Questions and Answers

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

• Window functions are used in SQL to perform calculations on a specific subset of rows related to the current row.

• They are often used with aggregate functions like SUM, AVG, and COUNT to calculate running totals, moving averages, and rankings.

• Examples of window functions include ROW_NUMBER(), RANK(), LEAD(), ...read more

Use PySpark code to connect data to ADLS with partitioning

• Use SparkSession to create a Spark application

• Set the configuration for ADLS storage account and container

• Read data from ADLS using Spark DataFrame API

• Partition the data based on a specific column while writing back to ADLS

I am a Data Engineer with experience in designing and implementing project architectures. My day-to-day responsibilities include data processing, ETL tasks, and ensuring data quality.

• Designing and implementing project architectures for data processing

• Performing ETL tasks to extract, transform, and load data into the system

• Ensuring data quality and integrity through data validation and cleansing

• Collaborating with cross-...read more

Use SQL to calculate the difference in marks for each student ID across different years.

• Use a self join on the table to compare marks for the same student ID across different years.

• Calculate the difference in marks by subtracting the marks from different years.

• Group the results by student ID to get the difference in marks for each student.

The answer to the question is that in which state which gender makes the most purchases.

• Aggregate the data by state and gender to calculate the total purchases made by each gender in each state.

• Identify the gender with the highest total purchases in each state.

• Present the results in a table or chart for easy visualization.

ADF stands for Azure Data Factory, a cloud-based data integration service that allows you to create, schedule, and manage data pipelines.

• ADF is used for building, scheduling, and monitoring data pipelines to move and transform data from various sources to destinations.

• It supports data integration between various data stores such as Azure SQL Database, Azure Blob Storage, and on-premises data sources.

• ADF provides a visu...read more

DAG stands for Directed Acyclic Graph, a data structure used to represent dependencies between tasks in a workflow.

• DAG is a collection of nodes connected by edges, where each edge has a direction and there are no cycles.

• It is commonly used in data engineering for representing data pipelines and workflows.

• DAGs help in visualizing and optimizing the order of tasks to be executed in a workflow.

• Popular tools like Apache Ai...read more

Lineage refers to the history and origin of data, including its source, transformations, and dependencies.

• Lineage helps in understanding how data is generated, processed, and transformed throughout its lifecycle.

• It tracks the flow of data from its source to its destination, including any intermediate steps or transformations.

• Lineage is important for data governance, data quality, and troubleshooting data issues.

• Example...read more

Spark handles fault tolerance through resilient distributed datasets (RDDs) and lineage tracking.

• Spark achieves fault tolerance through RDDs, which are immutable distributed collections of objects that can be rebuilt if a partition is lost.

• RDDs track the lineage of transformations applied to the data, allowing lost partitions to be recomputed based on the original data and transformations.

• Spark also replicates data par...read more

Only one job will run in parallel in Spark with four cores and four worker nodes.

• In Spark, each core can only run one task at a time, so with four cores, only four tasks can run concurrently.

• Since there are four worker nodes, each with four cores, a total of four tasks can run in parallel.

• Therefore, only one job will run in parallel in this scenario.

I have used techniques like indexing, query optimization, and parallel processing in my projects.

• Indexing: Used to improve the speed of data retrieval by creating indexes on columns frequently used in queries.

• Query optimization: Rewriting queries to improve efficiency and reduce execution time.

• Parallel processing: Distributing tasks across multiple processors to speed up data processing.

• Caching: Storing frequently acce...read more

Identify duplicate rows in a table

• Use SQL query with GROUP BY and HAVING clause to identify duplicate rows based on specific columns

• Example: SELECT column1, column2, COUNT(*) FROM table_name GROUP BY column1, column2 HAVING COUNT(*) > 1