Welcome to the World of Data "Plumbing"!

Welcome! Before we can build fancy predictive models or perform complex actuarial calculations, we have to talk about where the data actually comes from and how it gets to us. In this chapter, we focus on Database Management and ETL Operations. Think of this as the "plumbing" of predictive analytics. If the pipes are leaky or the water is dirty, it doesn't matter how great your faucet is—the result won't be good!

By the end of these notes, you’ll understand how data is stored, how we move it from place to place, and how we clean it up so it’s ready for your models. Don’t worry if you’ve never touched a database before; we’ll take this step-by-step!

1. What is a Database?

At its simplest, a database is an organized collection of structured information. In the actuarial world, we primarily deal with Relational Databases.

Analogy: Imagine a giant digital filing cabinet. Each drawer is a Table. Inside each drawer are folders (Rows or Records), and inside each folder are specific pieces of information like name, policy number, or claim amount (Columns or Fields).

Key Terms to Remember:
- Table: A collection of data organized in rows and columns.
- Record (Row): A single entry in a table (e.g., one specific policyholder).
- Field (Column): A specific category of information (e.g., "Date of Birth").
- Schema: The "blueprint" or structure of the database.

Quick Review: A database is the storage unit, a table is the structure within it, and rows/columns are the actual data points.

What makes a database "relational" is the ability to link tables together using Keys. This is crucial for Exam ATPA because you will often have to combine data from different sources (like "Claims Data" and "Policyholder Demographics").

Primary Keys vs. Foreign Keys

Primary Key (PK): A unique identifier for every record in a table. It must be unique and cannot be empty (null). Example: A Social Security Number or a unique Policy ID.

Foreign Key (FK): A column in one table that points to the Primary Key in another table. This is the "bridge" that connects them.

Memory Aid: Think of the Primary Key as your Passport Number (it identifies YOU uniquely). A Foreign Key is like your Passport Number written on a hotel check-in form; it links the hotel's record back to your official identity.

Common Mistake to Avoid: Don't assume every table has a single-column primary key. Sometimes, you need a Composite Key, which uses two or more columns together to create a unique identifier (e.g., Policy Number + Sequence Number).

3. Understanding ETL (Extract, Transform, Load)

ETL is the process used to move data from a source (like a legacy mainframe) to a destination (like a data warehouse where you can run your models).

Memory Aid: "Eat Tasty Lunch" (ETL)
1. Extract: Taking the food out of the fridge (Getting data from the source).
2. Transform: Chopping, seasoning, and cooking (Cleaning and formatting the data).
3. Load: Putting it on the plate (Saving the data into the final system).

Step 1: Extract

In this phase, we pull data from various sources. These sources could be SQL databases, Excel spreadsheets, or even "unstructured" data like PDF claim forms. The goal is to get the data into a staging area without disrupting the original source.

Step 2: Transform

This is where the hard work happens. Data in the "wild" is often messy. Transformations include:
- Cleaning: Fixing typos or removing duplicate records.
- Filtering: Keeping only the records we need (e.g., only policies from the last 5 years).
- Joining: Combining multiple tables into one.
- Aggregating: Summarizing data (e.g., calculating Total Claims per Year).
- Deriving: Creating new variables (e.g., \( \text{Age} = \text{Current Date} - \text{Birth Date} \)).

Step 3: Load

Finally, the "clean" data is loaded into the target system (usually a Data Warehouse). This data is now ready for you to use in R or Python for your predictive modeling.

Key Takeaway: ETL ensures that the data used for modeling is consistent, accurate, and properly formatted.

4. Data Warehousing vs. Data Lakes

As an actuary, you might hear these terms tossed around. Here is the simple breakdown:

Data Warehouse: Highly structured. Think of it like a library where every book is labeled and put in a specific spot. You use this for "clean" data and reporting.

Data Lake: Less structured. Think of it like a giant bucket of water where everything is poured in its raw form. It’s cheaper to store data here, but it’s "messier" to work with.

Did you know? Most modern companies use a "Lakehouse" approach, trying to get the flexibility of a lake with the organization of a warehouse!

5. Common SQL Operations for ETL

While Exam ATPA may not require you to write complex SQL from scratch, you must understand the logic of how data is manipulated. The most important concept is the JOIN.

Imagine you have Table A (Customers) and Table B (Claims):

1. Inner Join: Returns only the records where there is a match in BOTH tables. (Customers who have made a claim).
2. Left Join: Returns ALL records from the left table, and the matched records from the right table. If there is no match, you get a Null. (All customers, with claim info if they have any).
3. Full Outer Join: Returns everything from both tables, matching where possible.

Encouraging Phrase: Joins can be confusing! Just remember: The "Left" table is usually your starting point (your "Master" list), and you are "attaching" info from the "Right" table to it.

6. Data Integrity and Validation

During the ETL process, we must ensure Data Integrity. This means the data remains accurate and consistent.

Validation Checks to perform:
- Record Counts: Did I start with 1,000 rows and end with 1,000 rows?
- Null Checks: Are there missing values where there shouldn't be (like in a Primary Key)?
- Range Checks: Does the "Age" column have impossible values like -5 or 200?
- Uniqueness Checks: Are there duplicate IDs?

Summary/Key Takeaway:
Database Management is about how we store and relate data using tables and keys. ETL is the process of moving and refining that data. As an ATPA student, your goal is to ensure the data you pull into your model has gone through a rigorous ETL process so your predictions are based on "clean" water, not "muddy" water!

Quick Review Box:
- Extract: Get raw data.
- Transform: Clean, join, and calculate.
- Load: Save to the final destination.
- Primary Key: Unique ID for a row.
- Foreign Key: Links to a Primary Key in another table.