Introduction To Data Engineering 1 — Questions and Answers

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

Data Integration Layer
Staging Layer (Correct answer)
Semantic Layer
Access Layer

Correct answer: Staging Layer

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

Table Clustering
Table Indexing
Table Partitioning (Correct answer)
Table Compaction

Correct answer: Table Partitioning

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.

Autocompress
MVCC
Autovacuum (Correct answer)
BGcleaner

Correct answer: Autovacuum

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.

Audit Table
Dimension Table (Correct answer)
Bridge Table
Mapping Table

Correct answer: Dimension Table

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?

Atomicity, Consistency, Isolation, Database
Atomicity, Consistency, Inconsistency, Durability
Atomicity, Consistency, Isolation, Durability (Correct answer)
Automatically, Concurrency, Isolation, Durability

Correct answer: Atomicity, Consistency, Isolation, Durability

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.

Question 6: What does ETL in a data pipeline mean?

Extract-Transform-Load (Correct answer)
Execute-Transform-Load
Extract-Tweak-Load
Extract-Transform-Link

Correct answer: Extract-Transform-Load

ETL is a widely used process in data warehousing and data integration. It stands for Extract, Transform, Load, referring to the three main steps involved in moving data from source systems to a target data store. Data is first extracted from various sources, then transformed into a suitable format for analysis, and finally loaded into a data warehouse or data lake.

Question 7: A secondary database that is used to offload analytics queries from the primary instance.

Merge replica
Read replica (Correct answer)
Streaming replica
Global replica

Correct answer: Read replica

A read replica is a secondary database instance that maintains a copy of the data from a primary database. Its main purpose is to offload read-heavy operations, such as analytics queries and reporting, from the primary instance. This separation improves the performance of the primary database by reducing its workload and enhances data availability for analytical purposes.

Introduction To Data Engineering 1

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