Running Analysis Programs on Processed Data

The pipeline deliberately separates collection, transformation, and analysis. This separation is not wasted effort: it allows each stage to serve its own purpose effectively.

Programming

Why One Database Is Not Enough

After a spidering process collects email data, it is tempting to analyze the database immediately. The raw database contains the messages, so it may seem unnecessary to create another database first. The key insight is that collection and analysis require different data designs. The collection database must preserve raw details for debugging, while the analysis database must organize data for fast queries and visualization.

Key point

The pipeline deliberately separates collection, transformation, and analysis. This separation is not wasted effort: it allows each stage to serve its own purpose effectively.

How does data move from collection to an analysis-ready database?

Tracing the Three Stages

1
The spidering process collects emails and stores them in content.sqlite.
2
You inspect the raw database to verify that collection and parsing worked correctly.
3
gmodel.py reads content.sqlite and applies cleaning, modeling, compression, and mappings.
4
The program writes the transformed result to index.sqlite.
5
Analysis programs query index.sqlite to compute statistics, produce reports, or generate visualizations.

content.sqlite is the collection stage. It contains the collected messages in a raw, uncompressed form. Its inefficient data model is intentional: the raw structure makes it easier to inspect individual records, find missing fields or malformed data, and debug problems in the spidering or email-parsing process.

gmodel.py is the transformation stage. It reads the raw database and produces a separate database rather than changing the source. This program is a bridge between a format designed for inspection and a format designed for analysis.

index.sqlite is the analysis stage's data source. It contains cleaned, normalized, compressed, and indexed data. Programs such as gbasic.py use this database for statistics and visualizations instead of querying the raw collection database.

Inside the Transformation

What happens to raw records as gmodel.py prepares them for analysis?

Cleaning removes inconsistencies and duplicates.
Modeling organizes the information into a normalized schema.
Compression reduces file size by eliminating redundant storage and compressing header and body text.
Mappings provide rules for handling particular inconsistencies or for treating different values as equivalent.

In the real world

A source example of a mapping treats john.smith@example.com and jsmith@example.com as the same person. Another mapping can treat Project Alpha and Project A as the same category. These mappings let repeated runs of gmodel.py refine how the analysis database represents the collected data.

Why Raw Queries Drag

Characteristic	content.sqlite	Effect on analysis
Storage	Raw and uncompressed	Larger file size
Data model	Inefficient	More complex joins and lookups
Organization	Not normalized	Redundant data is scattered through the database
Primary purpose	Debugging collection	Useful for inspection, not efficient analysis

Why the raw collection database is not the preferred analysis source

Having all the raw data does not automatically make a database efficient for analysis. content.sqlite is larger because it is uncompressed, and its inefficient, non-normalized structure can require more complex joins and lookups. Analytical tasks such as counting senders, grouping messages by date, or computing statistics therefore experience a performance drag when they query the raw database directly.

Why does the database that is best for debugging differ from the database that is best for analysis?

Best practice

Use content.sqlite to inspect collection results and investigate problems. Use index.sqlite when running analysis programs, computing statistics, generating reports, or creating visualizations.

Measuring the Advantage

How do size and query performance differ between the two databases?

index.sqlite is typically 10 times smaller than content.sqlite. Its smaller size comes from compressing header and body text and eliminating redundant storage. Its normalized schema and indexed structure also make analysis queries much faster: the source describes queries that may take minutes against content.sqlite but only seconds against index.sqlite, with analysis queries running 10 to 100 times faster.

Key point

The benefit is not only that a query finishes sooner. Fast queries support interactive exploration, quick hypothesis testing, and repeated visualization changes. Slow queries make experimentation feel costly and can cause the exploratory process to grind to a halt.

Refining the Index

Worked example

A Mapping Refinement Cycle

You inspect collected email data and decide that two sender addresses should represent one person in the analysis database.

1
Inspect
Use content.sqlite to inspect the collected records and identify the inconsistency.
2
Map
Add or refine a mapping in gmodel.py so the two addresses are treated as the same person.
3
Transform
Run gmodel.py so it reads the unchanged raw source and creates a new index.sqlite using the updated mapping.
4
Analyze
Run analysis programs against the regenerated index.sqlite, where the sender values now follow the refined model.

Result

The raw collection remains available for debugging, while the regenerated index.sqlite becomes cleaner and more useful for analysis.

What changes when a mapping is refined and gmodel.py is run again?

Common Pipeline Mistakes

Common mistake
Assuming that containing all the raw data makes content.sqlite ready for analysis.
What it looks like
Querying content.sqlite directly for sender counts, date groupings, or statistics.
Why it fails
The raw database is uncompressed, inefficiently modeled, and not normalized, so analytical queries can be slow and require complex joins or lookups.
Do this instead
Use content.sqlite for inspection and debugging, then run analysis programs against index.sqlite.
Common mistake
Treating gmodel.py as a one-time conversion.
What it looks like
Accepting the first index.sqlite even after discovering inconsistent categories or sender values.
Why it fails
Mappings can be added or refined, and repeated runs are intended to improve data quality.
Do this instead
Refine the mappings and rerun gmodel.py to regenerate a better index.sqlite.
Common mistake
Modifying the raw database to fix an analysis problem.
What it looks like
Changing content.sqlite directly when a mapping produces an unwanted grouping.
Why it fails
The raw database is the reliable source for debugging and rebuilding; changing it would undermine the separation between collection and transformation.
Do this instead
Keep content.sqlite unchanged and adjust the transformation mappings instead.
Common mistake
Using the databases interchangeably.
What it looks like
Using index.sqlite to investigate whether the spidering process stored malformed or missing fields.
Why it fails
index.sqlite is optimized for cleaned analysis data, while content.sqlite preserves raw details that make collection problems easier to inspect.
Do this instead
Choose the database according to the task: raw inspection with content.sqlite and analysis with index.sqlite.

Practice the Separation

MEDIUM

A team has finished collecting email data. They need to verify whether the spider stored malformed messages, then they want to group messages by sender and generate a visualization. Which database should they use for each task, and what transformation should happen between the two tasks?

Hints

Match debugging tasks with the database designed for raw inspection.
Match statistics and visualizations with the database designed for fast analysis.
Remember that gmodel.py reads the raw database and writes the analysis database.

Your attempt (not saved)

Worked example

Practice Answer

Choose the database and process for debugging collected messages and then generating a sender visualization.

1
Debug
Use content.sqlite because its raw, uncompressed records make missing fields, malformed data, duplicate entries, and collection problems easier to inspect.
2
Transform
Run gmodel.py after the collection is understood. It cleans, models, compresses, and applies mappings while leaving content.sqlite unchanged.
3
Visualize
Use index.sqlite for the sender grouping and visualization because it is normalized, compressed, indexed, smaller, and faster to query.

Result

The correct workflow is content.sqlite for debugging, gmodel.py for transformation, and index.sqlite for analysis and visualization.

The Pipeline in Practice

1
content.sqlite stores the raw, uncompressed output of the spidering process and is optimized for debugging collection.
2
gmodel.py transforms the raw source by cleaning inconsistencies and duplicates, organizing data into a normalized schema, compressing redundant content, and applying mappings.
3
gmodel.py can be run repeatedly, so improved mappings produce progressively better versions of index.sqlite without changing the raw source.
4
index.sqlite is typically 10 times smaller than content.sqlite and supports analysis queries that can run 10 to 100 times faster.
5
Analysis programs should query index.sqlite, while collection problems should be investigated in content.sqlite.

Key Takeaways

The pipeline has three distinct stages: raw collection in content.sqlite, transformation through gmodel.py, and analysis using index.sqlite.
content.sqlite preserves raw details for debugging but is slow and inefficient for analytical queries.
gmodel.py cleans, normalizes, compresses, and iteratively refines the data without modifying the raw source.
index.sqlite is typically 10 times smaller and enables analysis queries to run 10 to 100 times faster.
Separating raw collection from analysis prevents one database design from compromising both debugging and performance.

Related concepts

More concepts starting with R