Practice: Data Loading

Working through a few common scenarios will provide a better understanding of how to choose the right loading strategy and anticipate potential challenges. The goal of the loading stage is to reliably get transformed data into the target system where it can be used.

Scenario 1: The First Big Load

Imagine your team is building a brand-new data warehouse to analyze sales performance. You have several years' worth of historical sales data extracted from an old system and transformed into clean CSV files. The target table in the data warehouse, let's call it FactSales, is currently completely empty.

• Question: Which loading strategy makes the most sense here: a Full Load ($L_{full}$) or an Incremental Load ($L_{incremental}$)? Why?
• Consideration: Before you start loading, what must you confirm about the FactSales table and your transformed CSV data?

Think it through: Since the FactSales table is empty and you want to populate it with all the historical data you have prepared, completely filling the table is the objective. An incremental load wouldn't make sense because there's nothing existing to update or add to.

Answer: A Full Load ($L_{full}$) is the appropriate strategy. It's designed to load an entire dataset, often involving truncating (emptying) the target table first (though in this case, it's already empty) and then inserting all the transformed records.

Before loading, it's essential to:

1. Verify the Target Schema: Ensure the FactSales table structure (columns, data types, constraints) matches the structure of your transformed data.
2. Confirm Schema Mapping: Double-check that each column in your CSV files correctly maps to the intended column in the FactSales table.

Scenario 2: Daily Sales Updates

The initial load was successful! Now, your data warehouse contains historical sales. Going forward, each morning, your ETL process extracts only the sales transactions from the previous day. This new data needs to be added to the FactSales table without affecting the existing historical records.

• Question: Should you use a Full Load or an Incremental Load for these daily updates?
• Consideration: If using an incremental approach, are you primarily adding new data or changing existing data? What piece of information is essential to ensure you don't accidentally load the same day's sales twice?

Think it through: Performing a full load every day would be highly inefficient. It would involve deleting all existing data and reloading everything, including the history that hasn't changed, just to add one day's worth of transactions. You only want to add the new records.

Answer: An Incremental Load ($L_{incremental}$) is the correct approach. Specifically, since these are entirely new transactions for the previous day, an Append strategy (a type of incremental load) is most suitable. You are simply adding new rows to the FactSales table.

To prevent duplicates, you need a way to identify the records already loaded. This typically involves tracking the load date or using the transaction dates themselves. Your ETL process needs logic to ensure it only processes records for the specific previous day it's meant to handle.

Scenario 3: Updating Product Information

Your data warehouse also includes a DimProduct table containing details about each product sold (ID, name, category, price). Occasionally, information about existing products changes in the source system (e.g., a price update, a category change). Your ETL process extracts a file containing only the products with changes, identified by their unique ProductID.

• Question: What loading strategy is required here?
• Consideration: What should happen in the DimProduct table if a ProductID from the incoming file matches an existing product? What if the ProductID is entirely new (which might indicate a data quality issue or a genuinely new product)?

Think it through: Like the daily sales, you don't want to reload the entire product dimension table every time a few prices change. However, unlike the daily sales, you aren't just adding new records; you need to modify existing ones.

Answer: This calls for an Incremental Load ($L_{incremental}$), but specifically one that handles Updates (often called an "Upsert" or "Merge" operation).

• If an incoming ProductID matches an existing row in DimProduct, the loading process should update that row with the new information (e.g., the new price).
• If an incoming ProductID does not exist in DimProduct, you need a defined business rule. Common approaches are:
  • Insert: Add the new product as a new row (Append).
  • Reject/Log: Discard the record and log an error, as unexpected new products might indicate a problem upstream.

This update/insert logic is a common pattern in dimension table loading.

Scenario 4: Handling Loading Errors

Let's revisit the daily sales load (Scenario 2). The FactSales table requires dates in YYYY-MM-DD format. However, during today's load, a few rows in the source file mistakenly have dates formatted as MM/DD/YYYY. Your transformation logic didn't catch this.

• Question: What is likely to happen when the loading process tries to insert a row with '05/20/2024' into a date column expecting '2024-05-20'?
• Consideration: What are a couple of basic ways the loading process could be configured to handle such errors?

Think it through: Databases and data warehouses enforce data types. Trying to insert text that doesn't conform to the target column's expected format (like putting 'MM/DD/YYYY' into a strict DATE type) will usually cause a problem.

Answer: The loading process will likely fail for those specific rows. The database will reject the insert because the provided string '05/20/2024' cannot be implicitly converted to the required YYYY-MM-DD date format.

How the overall load behaves depends on its configuration:

1. Stop on First Error: The entire load process might halt as soon as the first bad record is encountered.
2. Reject and Continue: The process could be configured to skip the rows causing errors, log them to an error file or table, and continue loading the valid records.
3. Load All, Validate Later: Less commonly for type errors, some systems might load data into staging tables with more flexible types, relying on post-load validation to catch issues (though type mismatches are often caught earlier).

For beginner pipelines, logging errors and deciding whether to stop or continue loading good records is a common requirement. Ideally, data formatting issues like this should be caught and corrected during the Transformation stage, before attempting the load.

Practice: Mapping Source to Target

Imagine your transformed data looks like this (simplified):

• Source Fields: transaction_id, sale_timestamp, product_identifier, units_sold, revenue_usd

Your target table FactSales has this structure:

• Target Columns: SalesKey (auto-incrementing integer), TransactionID (varchar), DateKey (integer, links to DimDate table), ProductKey (integer, links to DimProduct table), QuantitySold (integer), Revenue (decimal)

Assume your transformation stage already handled converting sale_timestamp to a DateKey and product_identifier to a ProductKey through lookups.

• Task: How would you map the source fields to the target columns during the load?

A diagram showing the mapping from source fields to target table columns. SalesKey is generated by the database, while DateKey and ProductKey are assumed to be derived during transformation before loading.

Mapping:

• transaction_id -> TransactionID
• sale_timestamp (transformed) -> DateKey
• product_identifier (transformed) -> ProductKey
• units_sold -> QuantitySold
• revenue_usd -> Revenue
• SalesKey is typically generated automatically by the database upon insertion, so it doesn't require a direct mapping from the source file.

By working through these scenarios, you can see how understanding the nature of your data (initial vs. ongoing, new vs. updates) and your target system is fundamental to choosing the correct loading strategy and anticipating the necessary steps like schema mapping and error handling.