Thomson Reuters Interview Questions and Answers

Query to get the customer with the highest total order value for each year, month.

• Join the Order and Customer tables on the foreign key

• Group the results by year, month, and customer

• Calculate the total order value for each group

• Find the maximum total order value for each year, month

• If there are multiple customers with the same highest total order value, select the one with the lower Customer_ID

To fetch the modified information without ModifiedDate column from a table with 10 million records.

• Create a trigger to capture the modified information and insert it into a separate table with ModifiedDate column.

• Use a tool like Change Data Capture (CDC) to track changes in the table and extract the modified information.

• Use a query to compare the current table with a backup copy taken the previous day to identify the modified information.

Handle changing schema by using schema evolution techniques and version control.

• Use schema evolution techniques like adding new fields, renaming fields, and changing data types.

• Implement version control to track changes and ensure backward compatibility.

• Use tools like Apache Avro or Apache Parquet to store data in a self-describing format.

• Implement automated testing to ensure data quality and consistency.

• Collaborate with data producers to establish clear communication and doc...read more

Normalisation is the process of organizing data in a database to reduce redundancy and improve data integrity. Denormalisation is the opposite process.

• Normalisation is used to eliminate data redundancy and improve data integrity.

• Denormalisation is used to improve query performance by reducing the number of joins required.

• A real-time example of normalisation is breaking down a customer's information into separate tables such as customer details, order details, and payment deta...read more

Parquet and ORC are columnar storage formats. Parquet is preferred due to its cross-platform support and better compression. ORC supports schema evolution.

• Parquet is a columnar storage format that is widely used in the industry due to its cross-platform support and better compression.

• ORC is another columnar storage format that supports schema evolution.

• Parquet is preferred over ORC due to its better compression and support for a wider range of programming languages.

• ORC is pre...read more

There are three types of schema in Data Warehousing: Star Schema, Snowflake Schema, and Fact Constellation Schema.

• Star Schema: central fact table connected to dimension tables in a star shape

• Snowflake Schema: extension of star schema with normalized dimension tables

• Fact Constellation Schema: multiple fact tables connected to dimension tables in a complex structure

Broadcast variables are read-only variables that are cached on each worker node while accumulator variables are write-only variables that are used to accumulate values across multiple tasks.

• Broadcast variables are used to give every node a copy of a large input dataset or a small lookup table.

• Accumulator variables are used to keep a running total of values across multiple tasks.

• Broadcast variables are used for read-only operations while accumulator variables are used for writ...read more