How to Turn Video Transcripts into a Clean Research Dataset (SQLite, CSV & RAG Chunking)
Target Workflow: Academic researchers, data scientists, and AI engineers building a structured text corpus or Retrieval-Augmented Generation (RAG) knowledge base from YouTube channels.
When building a research dataset or a Retrieval-Augmented Generation (RAG) index from video content, raw transcript text alone is not enough. Every passage must retain provenance metadata: video_id, title, canonical url, published_at date, transcript_source (manual vs generated), and [MM:SS] timestamps so citations can link directly to the exact moment in the video.
1. Querying channel_cache.db Directly with SQL or Python
Every output folder created by Shirube YouTube Transcript Tool contains a standard SQLite 3 database (channel_cache.db) with three relational tables: videos, transcripts, and errors. You can query it directly in Python without even parsing CSV files:
import sqlite3
import pandas as pd
conn = sqlite3.connect("./youtube_transcripts/channel_cache.db")
df = pd.read_sql_query("""
SELECT v.video_id, v.title, v.published_at, v.duration_seconds,
t.language, t.transcript_source, t.transcript_text,
t.translated_language, t.translated_text
FROM transcripts t
JOIN videos v ON t.video_id = v.video_id
WHERE v.transcript_status = 'success'
ORDER BY v.published_at DESC
""", conn)
print(f"Loaded {len(df)} verified video transcripts.")
2. Preserving Timestamp Citations via transcripts/{video_id}.txt
While transcripts.csv stores continuous normalized prose for full-text search, each file in transcripts/{video_id}.txt preserves line-by-line [MM:SS] timestamps alongside a structured header:
================================================================================ Title: How Constraints Supercharge Your Creativity | Eric Zimmerman | TED Video ID: vfbNzpd6cIw URL: https://www.youtube.com/watch?v=vfbNzpd6cIw Published: 2026-10-15 Language: en (Source: manual) ================================================================================ [00:00] Commentators... [00:14] Look at this...
When chunking these .txt files for vector databases or RAG pipelines, converting [MM:SS] into https://www.youtube.com/watch?v={video_id}&t={seconds}s lets your AI assistant return clickable timestamped video citations.