tmlsn

Question 1

What is a data warehouse

Answer 1

Centralized system designed to store process and analyze large volumes of data from multiple sources
Optimized for queries and reporting rather than transactions.

Question 2

OLTP

Answer 2

Fast reads / writes
Day to day transactions
Real-time, current data

Question 3

OLAP

Answer 3

Complex queries
Analytical queries and reporting
Historical, aggregated data
Read optimized for querying

Question 4

Database

Answer 4

OLTP online transaction processing
-structured system for storing and managing real time transactions handling read/writes for day to day
-sql server
-maintaining inventory records in a store

Question 5

Data warehouse

Answer 5

OLAP online analytical processing
-central repo optimized for storing historical data and performing complex analytical queries. Holds structured data
-red shift, snowflake
-trends on last 5 years, hr report across departments

Question 6

Data lake

Answer 6

-massive storage holding raw unstructed semi struct and structured data.
-used for data processing, ml
-s3, azure data lake, Hadoop
-storing raw sensor data from IoT devices
-unfiltered ocean of information

Question 7

Data mart

Answer 7

Subset of warehouse focused on specific department or business unit (sales, hr, marketing)
-provides faster access to relevant insights
-structured data
-hr analyzing employee retention

Question 8

• Star schema

Answer 8

o One fact connected to multiple dimension tables
o Optimized for fast querying
o Sales fact table

Question 9

• Snowflake schema

Answer 9

o Normalizes dimensions into sub-dimensions
o Reduces data redundancy but increases query complexity

Question 10

• Galaxy schema – fact constellation

Answer 10

o Multiple fact tables share common dimension tables
o Complex business scenarios (sales, returns data)

Question 10

Slowly changing dimensions (SCD) type 1

Answer 10

o Overwrites old data

Question 11

Slowly changing dimensions (SCD) type 2

Answer 11

o New row is added for every address change with start and end date

Question 12

Slowly changing dimensions (SCD) type 3

Answer 12

o Limited history (prev_addr is added to track only the last change)

Question 13

FACT table

Answer 13

• Stores measurable business events

Question 13

DIM table

Answer 13

• Stores descriptive attributes

Question 14

Landing vs staging

Answer 14

Landing is data as is staging is modified, multiple layers, structured

Question 15

How do you approach ETL design

Answer 15

Gathering stakeholder needs.
* Analyzing data sources.
* Researching architecture and processes.
* Proposing a solution.
* Fine-tuning the solution based on feedback.
* Launching the solution and user onboarding.

Question 16

TYPICAL ETL SOURCES?

Answer 16

-databases
-cloud storage like s3, blob storage
-flat files (csv, Json)
-APIs, like for web scraping

Question 17

What data validation techniques would you use?

Answer 17

• Schema validation
  o Pyspark schema enforcement and SQL constraints to detect missing or incorrect fields
• Data type & format checks
  o Date formats, numerical precision, text standardization
• Business rule validation
  o Custom business logic (mortgage balance cant be negative)
• Duplicate detection
  o Windows functions and deduplication rules
• Range and anomaly detection
  o Flagged outliers using aggregate and statistical checks

Question 18

How have you optimized etl workflows in the past?

Answer 18

• Refactored SQL queries for performance
  o Explain plan
  o Optimized joins and aggregations
  o Removed unnecessary sub queries and used CTEs instead
• Partitioning and indexing (faster data retrieval)
  o Table partitioning by date, location to improve query speeds
  o Created indexes on frequently queried columns reducing scan time
• Incremental loading
  o Change Data Capture (CDC) – only process new or updated records
• Parallel processing and workflow automation
  o Automated error handling and retry mechanisms to prevent workflow failure.

Question 19

Normal view

Answer 19

dynamically fetches data on each query

Question 19

Materialized view

Answer 19

precomputed stored result of a query

Question 20

OLAP cubes and structures

Answer 20

An OLAP cube is a multi-dimensional data structure that enables fast analytical queries by pre-aggregating data into different dimensions.

Question 20

types of OLAP

Answer 20

MOLAP (Multidimensional OLAP)
ROLAP (Relational OLAP)
HOLAP (Hybrid OLAP)

Question 21

MOLAP (Multidimensional OLAP)

Answer 21

* Stores precomputed OLAP cubes in a specialized database

Question 22

ROLAP (Relational OLAP)

Answer 22

* Stores data in relational databases (e.g., SQL, Snowflake, Redshift)

Question 23

HOLAP (Hybrid OLAP)

Answer 23

* Combines MOLAP & ROLAP – Some data is precomputed, and some is queried dynamically

Question 24

What is a cluster

Answer 24

A pool of computers working together but viewed as a single system

Question 25

What is a container?

Answer 25

- container is an isolated virtual runtime environment Comes with some CPU and memory allocation

Question 26

First normal form (1NF)

Answer 26

each row in the table should have a unique identifier, and each value in the table should be indivisible (atomic value).

Question 27

Second Normal Form (2NF)

Answer 27

that each non-key column in a table is dependent on the primary key. In other words, there should be no partial dependencies in the table.

Question 28

Third Normal Form (3NF)

Answer 28

requiring that all non-key attributes are independent of each other. This means that each column should be directly related to the primary key, and not to any other columns in the same table

Question 29

BCNF — Boyce-Codd Normal Form

Answer 29

ensures that each determinant in a table is a candidate key. In other words, BCNF ensures that each non-key attribute is dependent only on the candidate key.

Question 30

Fourth Normal Form (4NF)

Answer 30

used to eliminate the possibility of multi-valued dependencies in a table. A multi-valued dependency occurs when one or more attributes are dependent on a part of the primary key, but not on the entire primary key.

Question 31

Fifth normal form (5NF)

Answer 31

Project-Join Normal Form (PJNF). It is used to handle complex many-to-many relationships in a database.

Question 32

Star Schema: Pros:

Answer 32

1. Simplicity: With denormalized dimension tables, the structure is easier to understand and navigate. 2. Query Performance: Fewer joins are required, which can improve the speed of querying large datasets. 3. Adaptability: It’s relatively easy to add new dimensions without altering existing structure.

Question 33

Star Schema: Cons:

Answer 33

1. Redundancy: Denormalization can lead to data redundancy in dimension tables. 2. Storage: The redundant data might require more storage space. 3. Maintenance: Redundancy can make updates and inserts more complex, potentially leading to inconsistencies.

Question 34

Snowflake Schema: Pros:

Answer 34

1. Normalization: Reduced data redundancy due to the normalization of dimension tables. 2. Storage Efficiency: Uses less storage space compared to the star schema because of reduced redundancy. 3. Clear Structure: The normalized structure can be clearer for those familiar with relational database design.

Question 35

Snowflake Schema: Cons: