Maximizing ETL Efficiency and Cost-Effectiveness with Modal

ETL (Extract, Transform, Load) refers to the methodology of transferring data from one system to another, most commonly moving data from source systems such as production databases or APIs into analytical data warehouses like Snowflake. This process allows data teams to analyze and combine data more effectively using platforms like Fivetran or orchestration tools such as Airflow. However, Modal presents a compelling alternative for teams seeking cost-efficient, flexible ETL solutions.

Advantages of Using Modal for ETL

Modal offers significant advantages in the ETL space that could resonate with many data teams:

• Cost Savings: Modal operates on a usage-based pricing model, allowing users to pay based solely on compute usage rather than the volume of data transferred. This structure is particularly beneficial when handling large datasets, translating into considerable cost reductions.
• Flexibility and Ease of Use: Compared to orchestration platforms like Airflow, which can be complex and overly comprehensive for simple data jobs, Modal simplifies the creation and management of ETL tasks. Its design caters to the majority of ETL needs without the overhead.

Example 1: Cost-Effective Data Transfer to Snowflake

An insightful use case is the transfer of 12 million rows from ClickHouse — a database utilized for tracking metrics related to resource usage — to Snowflake for further analysis, such as determining conversion rates. The initial step involves extracting data from ClickHouse using its Python connector:

def extract_from_clickhouse(date):
    import clickhouse_connect
    # Extract data with a provided query

After extraction, the data is loaded into Snowflake using a batch insert mechanism that handles 10,000 rows at a time to accommodate Snowflake's capabilities:

def load_to_snowflake(data: list[tuple], date):
    # Load data to Snowflake in batches

By utilizing Modal, the ETL function is executed in the cloud efficiently:

@app.function(...)
def run_etl(date):
    results = extract_from_clickhouse(date)
    load_to_snowflake(results, date)

For instance, running this ETL job successfully transferred 12 million rows within 16 minutes with the following resource expenditure:

• 5 CPU hours costing $0.26
• 4.4 GiB of memory costing $0.03

The total cost for this operation was a mere $0.29, contrasting sharply with Fivetran's estimated cost of $3,300 for the same dataset transfer, reflecting Modal's pricing advantages.

Example 2: Enriching Data with the GitHub API

Another scenario involves enhancing user data by integrating information from the GitHub API. When users sign up, while their usernames are collected, additional data like their associated companies can offer valuable insights:

def get_usernames():
    # Connect to Snowflake to fetch usernames and IDs

Next, a function queries the GitHub API to retrieve user company information:

def get_company(user):
    # Fetch user company from GitHub

The data enrichment function aggregates this information into a more comprehensive dataset:

def get_user_companies(df):
    # Apply company retrieval logic on data

The entire process can even be scheduled automatically to run daily, employing Modal's cron capabilities to ensure consistent updates:

@app.function(..., schedule=modal.Cron("0 6 * * *"))

When Traditional ETL Solutions May Excel

While Modal provides exceptional flexibility and cost efficiency, there are contexts where traditional ETL solutions might still be preferred:

• Common Connectors: Solutions like Fivetran excel in user-friendly integrations for standard data sources with small to medium volumes.
• Complex Pipelines: For extensive, business-critical ETL workflows requiring intricate orchestration — such as function caching and partial retries — using a specialized tool like Airflow remains advantageous.

Conclusion: Reimagining ETL with Custom Solutions

The current data ecosystem is evolving, with a growing recognition that custom coding can be a strategic asset rather than a liability. Modal’s approach reduces vendor lock-in and broadens the range of actionable data strategies. By leveraging Modal for ETL processes, organizations can enhance productivity, unlock substantial cost savings, and embrace the flexibility defined by modern data needs.

Additional Resources

For further exploration of Modal's capabilities in data analytics, check out additional examples such as:

• Deploying a dbt project that transforms S3 data using the DuckDB adapter.
• Hosting Streamlit apps for interactive data visualizations.
• Sending daily reports to Google Sheets for streamlined reporting.