Data Engineering Flashcards

What term does not have the same meaning as ETL?

Answer: To translate

ETL stands for Extract, Transform, Load, which are the three core phases of a data integration process. 'Extract' involves gathering data from source systems, 'Transform' involves cleaning and structuring the data, and 'Load' involves writing the processed data to a target system. 'To translate' does not represent a distinct phase or a synonym for the overall ETL process, unlike the other options which are components of ETL.

Except for one, they are data technologies found in Google Cloud.

Answer: Kinesis

Kinesis is a data streaming service primarily associated with Amazon Web Services (AWS). In contrast, BigQuery (a data warehouse), GCS (Google Cloud Storage), and Dataflow (a data processing service) are all core data technologies offered within the Google Cloud Platform (GCP) ecosystem. Therefore, Kinesis is the technology not found in Google Cloud among the given options.

In a data warehouse, a ______ stores and manages both current and historical data over time. It is regarded and implemented as one of the most critical ETL tasks in tracking dimension record history.

Answer: Slowly Changing Dimension (SCD)

A Slowly Changing Dimension (SCD) is a technique used in data warehousing to manage and track changes in dimension data over time. It ensures that both current and historical versions of data attributes are stored, providing a complete historical view for analysis. This is crucial for maintaining data integrity and enabling accurate trend analysis in a data warehouse.

The unique, meaningless, and sequential numbers generated by the system and attached to data warehouse tables are referred to as:

Answer: Surrogate Keys

Surrogate keys are system-generated, unique, and meaningless identifiers assigned to rows in data warehouse tables. Unlike natural or business keys, they are not derived from the source system's data and are typically sequential numbers. Their purpose is to provide a stable, efficient, and independent primary key for dimension and fact tables, preventing issues that can arise from changes in natural keys.

You have a BigQuery table that contains information about customer purchases, such as the date of purchase, the type of product purchased, the product name, and several other descriptive attributes. There is roughly three years of data. You typically query data by month, followed by customer. You want to scan as little data as possible. How would you set the table?

Answer: Partition by purchase date and cluster by customer

In BigQuery, partitioning by purchase date significantly reduces the amount of data scanned for monthly queries, as only relevant date partitions are accessed. Clustering by customer within those partitions further optimizes queries that filter or group by customer, as data for specific customers will be physically co-located. This combination minimizes data scanned and improves query performance for the described access patterns.

Each row in a Star Schema ________ table represents an event that occurred at a specific time (such as a client opening a contract)

Answer: Fact Table

In a Star Schema, a Fact Table is the central table that stores quantitative measures and foreign keys to dimension tables. Each row in a fact table represents a specific event or transaction, such as a sale or a contract opening, along with associated metrics. Dimension tables, on the other hand, provide descriptive attributes related to these events.

You're creating a data pipeline to feed into a sales data mart. The project's sponsor has previously experienced quality control issues and has developed a set of rules for filtering out bad data before it enters the data mart. At what point in the data pipeline would you put those rules in place?

Answer: Transformation

The 'Transformation' phase of a data pipeline is where data is cleaned, validated, enriched, and structured to meet the requirements of the target system. Implementing data quality rules, such as filtering out bad data, is a critical activity performed during this phase. This ensures that only high-quality, reliable data enters the data mart for analysis.