Airflow ETL Pipeline Generator

Generate production-ready Apache Airflow ETL pipelines that automatically discover, download, and transform documents from government websites and other data sources into structured markdown files.

Workflow

Phase 1: Website Exploration and Discovery

Initial Analysis:
- Use WebFetch to explore the provided website URL
- Identify document sections (downloads, archives, publications, meetings, etc.)
- Look for API endpoints, RSS feeds, or structured data sources
- Note pagination patterns and document organization
License Verification:
- Search for license information (Creative Commons, Open Government License, etc.)
- Look for terms of use or copyright notices
- Check for explicit commercial use permissions
- If unclear, ask user about license status
Document Inventory:
- Identify document types (PDF, DOC, DOCX, etc.)
- Understand the URL patterns for documents
- Determine how to detect new documents
- Note any metadata available (dates, categories, titles)
User Confirmation:
- Present findings in a clear summary
- Show example document URLs
- Describe the discovered structure
- Ask user to confirm this is the correct data source

Phase 2: Generate Airflow Pipeline Assets

Create a complete, production-ready Airflow project structure:

airflow_pipelines/
├── dags/
│   └── [source_name]_etl_dag.py
├── operators/
│   ├── __init__.py
│   ├── document_scraper.py
│   └── document_converter.py
├── utils/
│   ├── __init__.py
│   ├── license_checker.py
│   └── file_manager.py
├── config/
│   └── [source_name]_config.yaml
├── requirements.txt
└── README.md

File Generation Requirements:

1. DAG File (dags/[source_name]_etl_dag.py):

Daily schedule (adjustable)
Clear task dependencies
Error handling and retries
Sensor for checking new documents
Download task
Conversion task to markdown
File organization task
Use Airflow best practices (XComs, task groups, dynamic task generation)

2. Document Scraper (operators/document_scraper.py):

BeautifulSoup or Scrapy for web scraping
Request handling with retries
Respect robots.txt
User-agent configuration
Rate limiting
Checksum/hash tracking to avoid re-downloading
State management for incremental updates

3. Document Converter (operators/document_converter.py):

Support for PDF, DOC, DOCX conversion to markdown
Use libraries like pypandoc, pdfplumber, or python-docx
Preserve document structure (headings, lists, tables)
Extract metadata
Handle encoding issues
Clean and normalize output

4. License Checker (utils/license_checker.py):

Validate license information
Check for commercial use permission
Log license status
Skip non-compliant documents

5. File Manager (utils/file_manager.py):

Create meaningful directory structure
Organize by date, category, or document type
Generate consistent filenames
Handle duplicates
Maintain index of processed documents

6. Configuration (config/[source_name]_config.yaml):

source:
  name: "Source Name"
  url: "https://example.com"
  document_section: "/documents"

schedule:
  interval: "0 0 * * *"  # Daily at midnight

storage:
  base_path: "/data/documents"
  structure: "year/month/category"

scraping:
  rate_limit: 1  # requests per second
  user_agent: "ETL Pipeline Bot"
  retry_attempts: 3

conversion:
  format: "markdown"
  preserve_structure: true
  extract_metadata: true

7. Requirements (requirements.txt):

apache-airflow>=2.7.0
beautifulsoup4>=4.12.0
requests>=2.31.0
pypandoc>=1.12
pdfplumber>=0.10.0
python-docx>=1.0.0
pyyaml>=6.0
lxml>=4.9.0

8. Documentation (README.md):

Pipeline overview
Setup instructions
Configuration guide
Airflow connection requirements
Monitoring and troubleshooting
Example usage

Phase 3: Implementation Notes

Important Considerations:

Include comprehensive error handling
Log all operations for debugging
Add data quality checks
Implement idempotency (safe to re-run)
Use Airflow variables for sensitive config
Add email/Slack alerts for failures
Document the directory structure created

Code Quality:

Follow PEP 8 style guidelines
Add docstrings to all functions
Include type hints
Write modular, reusable code
Add comments for complex logic

Testing Recommendations (optional):

Suggest basic unit tests for utilities
Recommend integration testing approach
Provide example test cases

Phase 4: Delivery

Generate all files using Write tool
Provide summary of created assets
Explain how to deploy to Airflow:
- Copy files to Airflow home directory
- Install requirements
- Enable the DAG in Airflow UI
- Configure connections if needed
Suggest next steps (testing, scheduling, monitoring)

Examples

Example 1: German Bundestag Documents

User: "Create an ETL pipeline for https://www.bundestag.de/digitales to collect committee meeting documents"

Skill Response:
- Explores the digital committee section
- Finds document sections (agendas, protocols, reports)
- Checks copyright notice
- Confirms findings with user
- Generates complete Airflow pipeline
- Creates scraper for committee documents
- Sets up markdown conversion
- Organizes by committee and date

Example 2: EU Open Data Portal

User: "Build an Airflow pipeline for EU legislation documents from data.europa.eu"

Skill Response:
- Discovers API endpoints
- Verifies open data license
- Generates API-based scraper
- Creates pipeline with API operators
- Includes rate limiting
- Organizes by document type and year

Key Success Criteria

Pipeline runs successfully in Airflow
Documents are correctly downloaded
Markdown conversion preserves structure
File organization is logical and scalable
License compliance is enforced
New documents are detected automatically
Pipeline is idempotent and fault-tolerant

Tips for Users

Provide the main URL of the data source
Mention any specific document types needed
Specify preferred organization structure
Note any special requirements (date ranges, categories)
Test with a small sample before full deployment