Introduction To Data Engineering

Question 1

Raw data from source systems is stored in an ELT data warehouse.

Accepted Answer

Staging Layer

Answer

In an ELT (Extract, Load, Transform) data warehouse architecture, raw data from source systems is first loaded directly into a 'staging layer' or 'raw data layer.' This layer acts as a temporary holding area where the data resides in its original, untransformed state before subsequent transformations are applied within the data warehouse itself. This allows for flexibility and reusability of raw data.

Question 2

Dividing a large table into smaller physical pieces to improve query performance, speed up bulk loads and data deletions, and cost-savings

Accepted Answer

Table Partitioning

Answer

Table partitioning is a database technique where a large table is divided into smaller, more manageable physical pieces called partitions. This strategy significantly improves query performance by allowing the database to scan only relevant partitions, speeds up data loading and deletion, and can lead to cost savings by optimizing storage and maintenance. It's crucial for managing very large datasets.

Question 3

A PostgreSQL utility process that cleans up dead tuples in the background.

Accepted Answer

Autovacuum

Answer

Autovacuum is a crucial utility process in PostgreSQL that automatically reclaims storage occupied by 'dead tuples' (rows marked for deletion but not yet physically removed) and updates statistics used by the query planner. This background process is essential for maintaining database performance, preventing transaction ID wraparound, and ensuring efficient storage utilization and data integrity.

Question 4

A _________ table in a Star Schema contains attributes that describe the events in a fact table. It provides answers to the event's who, what, where, when, and why questions.

Accepted Answer

Dimension Table

Answer

In a Star Schema, a dimension table contains descriptive attributes related to the business events recorded in the fact table. It provides context and answers the 'who, what, where, when, and why' questions about the measures in the fact table. This structure enables detailed analysis and reporting by allowing users to slice and dice data based on these descriptive attributes.

Question 5

What are database transactions' ACID properties?

Accepted Answer

Atomicity, Consistency, Isolation, Durability

Answer

ACID is an acronym representing a set of properties that guarantee valid database transactions. Atomicity ensures all operations within a transaction are completed or none are; Consistency ensures the database remains in a valid state; Isolation ensures concurrent transactions don't interfere with each other; and Durability ensures committed transactions persist even in case of system failure. These properties are fundamental for reliable database operations.

Data Engineering Practice Test

Data Engineering Practice Test

Introduction To Data Engineering