Gartner Data Engineer Interview Questions and Answers

Code to generate a CSV file with notepad data

• Open a text file and read the data

• Parse the data and write it to a CSV file

• Use libraries like pandas in Python for easier CSV handling

Columnar storage is a data storage format that stores data in columns rather than rows, improving query performance.

• Columnar storage stores data in a column-wise manner instead of row-wise.

• It improves query performance by reducing the amount of data that needs to be read from disk.

• Parquet is a columnar storage file format that is optimized for big data workloads.

• It is used in Apache Spark and other big data processing ...read more

The project architecture involves the design and organization of data pipelines and systems for efficient data processing and storage.

• The architecture includes components such as data sources, data processing frameworks, storage systems, and data delivery mechanisms.

• It focuses on scalability, reliability, and performance to handle large volumes of data.

• Example: A project architecture may involve using Apache Kafka for ...read more

To connect SQL server to Databricks, use JDBC/ODBC drivers and configure the connection settings.

• Install the appropriate JDBC/ODBC driver for SQL server

• Configure the connection settings in Databricks

• Use the JDBC/ODBC driver to establish the connection

Optimisation techniques used in data engineering

• Partitioning data to improve query performance

• Using indexing to speed up data retrieval

• Implementing caching mechanisms to reduce data access time

• Optimizing data storage formats for efficient storage and processing

• Parallel processing and distributed computing for faster data processing

• Using compression techniques to reduce storage space and improve data transfer

• Applying qu...read more

Repartition is used to increase or decrease the number of partitions in a DataFrame, while coalesce is used to decrease the number of partitions without shuffling data.

• Repartition involves shuffling data across the network, which can be expensive in terms of performance and resources.

• Coalesce is a more efficient operation as it minimizes data movement by only creating new partitions if necessary.

• Example: Repartition(10...read more

Copy Activity in ADF is used to move data between supported data stores

• Copy Activity is a built-in activity in Azure Data Factory (ADF)

• It can be used to move data between supported data stores such as Azure Blob Storage, SQL Database, etc.

• It supports various data movement methods like copy, transform, and load (ETL)

• You can define source and sink datasets, mapping, and settings in Copy Activity

• Example: Copying data from...read more

Star schema has a single fact table connected to multiple dimension tables, while Snowflake schema has normalized dimension tables.

• Star schema denormalizes data for faster query performance

• Snowflake schema normalizes data to reduce redundancy

• Star schema is easier to understand and query

• Snowflake schema is more flexible and scalable

• Example: A star schema for a sales database would have a fact table for sales transaction...read more

Transient tables are temporary tables, fact tables contain quantitative data, and dimension tables contain descriptive data.

• Transient tables are temporary and used for intermediate processing or storing temporary data.

• Fact tables contain quantitative data such as sales, revenue, or quantities.

• Dimension tables contain descriptive data like customer names, product categories, or dates.