UST Senior Developer Interview Questions and Answers

ETL stands for Extract, Transform, Load and DWH stands for Data Warehouse.

• ETL is a process of extracting data from various sources, transforming it into a format suitable for analysis, and loading it into a target system.

• DWH is a system used for storing and managing data from various sources for business intelligence purposes.

• ETL is a crucial step in populating a DWH with data.

• ETL involves data extraction, data transfo...read more

Outer join returns all records from one table and matching records from another, while inner join returns only matching records.

• Outer join uses the (+) symbol in Oracle and LEFT/RIGHT OUTER JOIN in SQL Server

• Inner join uses INNER JOIN keyword in SQL

• Sample data: Table A has 5 records, Table B has 3 records, Outer join returns 8 records while Inner join returns 3 records

SCDs are used to track changes in data over time. There are three types: Type 1, Type 2, and Type 3.

• Type 1 SCDs overwrite old data with new data.

• Type 2 SCDs add a new row for each change, with a start and end date.

• Type 3 SCDs add columns to the existing row to track changes.

• SCDs are commonly used in data warehousing and business intelligence.

• The choice of SCD type depends on the specific use case and data requirements.

To delete duplicate rows based on key in a table, use the DELETE statement with a subquery.

• Identify the key column(s) that define the uniqueness of a row

• Use the GROUP BY clause to group the rows by the key column(s)

• Use the HAVING clause to filter out the groups that have more than one row

• Use the subquery to select the duplicate rows to be deleted

• Use the DELETE statement with the subquery to delete the duplicate rows

Fact and dimension are two types of data in a data warehouse.

• Fact is a measurable event that can be analyzed, while dimension provides context to the fact.

• Fact is quantitative, while dimension is qualitative.

• Fact is stored in a fact table, while dimension is stored in a dimension table.

• Examples of fact include sales, revenue, and profit, while examples of dimension include time, location, and product.

• Fact and dimension...read more

Yes, fact tables can be joined together in a data warehouse to combine related information.

• Fact tables contain quantitative data and are typically joined using common dimensions.

• Joining fact tables allows for more comprehensive analysis and reporting.

• For example, joining a sales fact table with a customer fact table can provide insights on customer behavior and purchasing patterns.

Degenerate dimension refers to a dimension with only one value.

• A degenerate dimension is a dimension table with only one column and one row.

• It is used to join fact tables with different granularity levels.

• For example, a time dimension with only one value can be used to join a sales fact table with daily granularity and a returns fact table with hourly granularity.

OLTP is a transactional system for day-to-day operations, while OLAP is analytical system for decision-making.

• OLTP deals with real-time data processing, while OLAP deals with historical data analysis.

• OLTP is optimized for write operations, while OLAP is optimized for read operations.

• OLTP is used for operational tasks like order processing, while OLAP is used for strategic tasks like sales forecasting.

• OLTP databases are...read more