Data Engineering Interview Questions

Following is a difference between structured and unstructured data:

Parameter | Structured Data | Unstructured Data Storage | DBMS | Unmanaged file structures Standard | ADO.net, ODBC, and SQL | STMP, XML, CSV, and SMS Integration Tool | ELT (Extract, Transform, Load) | Manual data entry or batch processing that includes codes scaling | Schema scaling is difficult | Scaling is very easy.

It is a distributed, partitioned and replicated log service.

Your interviewer wants to see what your specific definition of data engineering is, which also makes it clear that you know what the work entails. So, what is it? In a nutshell, it is the act of transforming, cleansing, profiling, and aggregating large data sets. You can also take it a step further and discuss the daily duties of a data engineer, such as ad-hoc data query building and extracting, owning an organization’s data stewardship, and so on.

• ROW_NUMBER() assigns a unique sequential number to rows within a partition, without regard for ties.
• RANK() gives rows the same rank if they have the same value, but the next rank number skips the number of rows with the same rank.
• DENSE_RANK() also gives the same rank for identical values but does not skip rank numbers.
• Use Cases: ROW_NUMBER() for unique numbering, RANK() for scenarios where gaps in ranking are acceptable, DENSE_RANK() when you want continuous ranking.

Blocks are the smallest unit of a data file. Hadoop automatically splits huge files into small pieces.

Block Scanner verifies the list of blocks that are presented on a DataNode.

Apache Airflow is an open-source workflow management platform. It began in October 2014 at Airbnb as a solution for managing the company's increasingly complex workflows. Airbnb's creation of Airflow enabled them to programmatically author, schedule, and monitor their workflows via the built-in Airflow user interface. Airflow is a data transformation pipeline ETL (Extract, Transform, Load) workflow orchestration tool.

Data quality means the data is fit for its intended use. In practice, it means data is correct, complete, consistent, timely, and well-defined so downstream consumers can trust metrics. Data quality is both a technical problem (pipelines) and a product problem (definitions and contracts).

Following are the components of Hadoop application :

• Hadoop Common: It is a common set of utilities and libraries that are utilized by Hadoop.
• HDFS: This Hadoop application relates to the file system in which the Hadoop data is stored. It is a distributed file system having high bandwidth.
• Hadoop MapReduce: It is based according to the algorithm for the provision of large-scale data processing.
• Hadoop YARN: It is used for resource management within the Hadoop cluster. It can also be used for task scheduling for users.

This data engineer interview question may be more geared toward those on the intermediate level, but in some positions, it may also be considered an entry-level question. You’ll want to answer by stating that databases using Delete SQL statements , Insert, and Update is standard operational databases that focus on speed and efficiency. As a result, analyzing data can be a little more complicated. With a data warehouse, on the other hand, aggregations, calculations, and select statements are the primary focus. These make data warehouses an ideal choice for data analysis.

Replication factor is a total number of replicas of a file in the system.

Normalization is the process of table design to minimize the data redundancy. There are different types of Noramalization forms in SQL:-

• First Normal Form (1NF): It removes all duplicate columns from the table. Creates table for related data and identifies unique column values
• First Normal Form (2NF): Follows 1NF and creates and places data subsets in an individual table and defines relationship between tables using primary key
• Third Normal Form (3NF): Follows 2NF and removes those columns which are not related through primary key
• Fourth Normal Form (4NF): Follows 3NF and do not define multi-valued dependencies. 4NF also known as BCNF

CDC is a technique to capture row-level changes (inserts, updates, deletes) from a source system and stream them downstream. In databases, CDC is commonly log-based (reading WAL/binlog) so it can capture changes without repeatedly scanning full tables. CDC pipelines often feed event logs (append-only) and/or materialized current-state tables.

A Star Schema is basically a multi-dimensional data model that is used to arrange data in a database so that it may be easily understood and analysed. Data marts, Data Warehouses , databases, and other technologies can all benefit from star schemas. The star schema style is ideal for querying massive amounts of data.

A snowflake schema is basically a multidimensional database schema that divides subdimensions into dimension tables. Engineers convert every dimension table into logical subdimensions while designing a snowflake schema. As a result, the data model turns out to be more complicated, but it might also make it straightforward for analysts in dealing with it, particularly for certain data kinds. Because its ERD (entity-relationship diagram) resembles a snowflake, it is known as the \"snowflake schema.\"

Big data refers to huge, complicated data sets that are created and sent in real-time from a wide range of sources. Big data collections can be organised, semi-structured, or unstructured, and they are regularly examined to uncover relevant patterns and insights regarding user and machine behaviour.

You can calculate a running total using the SUM() function with an OVER() clause. This query calculates a running total of sales_amount ordered by sales_date.

A data warehouse is a central store that integrates data from multiple operational systems and keeps full history, structured for analytical queries rather than transactions. Inmon's classic definition captures it: subject-oriented, integrated, time-variant, and non-volatile. Reporting directly on a production OLTP database fails for four reasons. First, heavy analytical scans compete with live transactions for CPU, I/O, and locks, so a month-end report can slow down checkout or order placement. Second, OLTP schemas are normalized for write correctness, so a simple business question needs ten-plus joins. Third, OLTP keeps current state — when a customer's address is updated, the old value is gone, so historical analysis is impossible. Fourth, real questions span systems (CRM plus billing plus support), and only an integrated store can join them consistently.

The PARTITION BY clause divides the result set into partitions to which the window function is applied. For example, SUM(sales) OVER (PARTITION BY department) calculates the total sales for each department. Each department is treated as a separate group for the calculation.

A data warehouse is a centralized repository that allows for data consolidation from a variety of sources. It is specifically designed for query and analysis rather than transaction processing. ### Key Characteristics

1. Subject-Oriented: The data warehouse is organized to deliver information on specific subject areas, or domains, such as sales, inventory, or marketing.

1. Integrated: It ensures that data from multiple sources is consistently formatted and standardized. This guarantees that the data is reliable and can be used for meaningful analysis.

1. Time-Variant: The data warehouse records all changes to data, which makes it possible to construct an understanding of historical trends and behavior over time.

1. Non-Volatile: Data within the warehouse is static, meaning that once it's in the warehouse, it doesn't change. Even if the original source of the data is updated, the data within the warehouse remains as it was when it was first loaded.

1. Optimized for Querying and Analysis: Data in a data warehouse is denormalized to improve query performance. This means that redundant data is allowed, and tables are often flattened to reduce the need for complex joins.

1. Data Modeling Emphasis: The focus is on a dimensional modeling with star or snowflake schemas for easy navigation and reporting.

1. Data Loading and Transformation: The ETL (Extract, Transform, Load) process is used to populate the data warehouse, and data is cleansed, de-duplicated, and transformed for reporting and analysis.

It is the centerpiece of HDFS. It stores data of HDFS and tracks various files across the clusters. Here, the actual data is not stored. The data is stored in DataNodes.