Running Analysis Programs on Processed Data
The three-database pipeline consists of content.sqlite (raw, uncompressed), gmodel.py (transformation program), and index.sqlite (cleaned, normalized, compressed).
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.
The pipeline deliberately separates collection, transformation, and analysis. This separation is not wasted effort: it allows each stage to serve its own purpose effectively.
Tracing the Three Stages
- The spidering process collects emails and stores them in content.sqlite.
- You inspect the raw database to verify that collection and parsing worked correctly.
- gmodel.py reads content.sqlite and applies cleaning, modeling, compression, and mappings.
- The program writes the transformed result to index.sqlite.
- 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
- 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.
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 |
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.
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
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.
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
A Mapping Refinement Cycle
You inspect collected email data and decide that two sender addresses should represent one person in the analysis database.
Inspect: Use content.sqlite to inspect the collected records and identify the inconsistency.
Map: Add or refine a mapping in gmodel.py so the two addresses are treated as the same person.
Transform: Run gmodel.py so it reads the unchanged raw source and creates a new index.sqlite using the updated mapping.
Analyze: Run analysis programs against the regenerated index.sqlite, where the sender values now follow the refined model.
The raw collection remains available for debugging, while the regenerated index.sqlite becomes cleaner and more useful for analysis.
Common Pipeline Mistakes
Assuming that containing all the raw data makes content.sqlite ready for analysis.
The raw database is uncompressed, inefficiently modeled, and not normalized, so analytical queries can be slow and require complex joins or lookups.
Fix:
Use content.sqlite for inspection and debugging, then run analysis programs against index.sqlite.Treating gmodel.py as a one-time conversion.
Mappings can be added or refined, and repeated runs are intended to improve data quality.
Fix:
Refine the mappings and rerun gmodel.py to regenerate a better index.sqlite.Modifying the raw database to fix an analysis problem.
The raw database is the reliable source for debugging and rebuilding; changing it would undermine the separation between collection and transformation.
Fix:
Keep content.sqlite unchanged and adjust the transformation mappings instead.Using the databases interchangeably.
index.sqlite is optimized for cleaned analysis data, while content.sqlite preserves raw details that make collection problems easier to inspect.
Fix:
Choose the database according to the task: raw inspection with content.sqlite and analysis with index.sqlite.
Practice the Separation
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.
Practice Answer
Choose the database and process for debugging collected messages and then generating a sender visualization.
Debug: Use content.sqlite because its raw, uncompressed records make missing fields, malformed data, duplicate entries, and collection problems easier to inspect.
Transform: Run gmodel.py after the collection is understood. It cleans, models, compresses, and applies mappings while leaving content.sqlite unchanged.
Visualize: Use index.sqlite for the sender grouping and visualization because it is normalized, compressed, indexed, smaller, and faster to query.
The correct workflow is content.sqlite for debugging, gmodel.py for transformation, and index.sqlite for analysis and visualization.
The Pipeline in Practice
- content.sqlite stores the raw, uncompressed output of the spidering process and is optimized for debugging collection.
- gmodel.py transforms the raw source by cleaning inconsistencies and duplicates, organizing data into a normalized schema, compressing redundant content, and applying mappings.
- gmodel.py can be run repeatedly, so improved mappings produce progressively better versions of index.sqlite without changing the raw source.
- index.sqlite is typically 10 times smaller than content.sqlite and supports analysis queries that can run 10 to 100 times faster.
- 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.