Python Data Transformations

What Are Python Transformations?

Python transformations in Keboola enable users to perform complex data operations that extend beyond the capabilities of SQL. While SQL is perfect for straightforward tasks such as joins, sorting, and aggregations, Python truly excels when dealing with intricate data manipulations, API integrations, and advanced data science workflows. With Python, you can effortlessly create powerful data scripts that handle demanding tasks efficiently and effectively.

When to Choose Python Over SQL

While SQL transformations handle basic data querying and manipulations excellently, there are several scenarios where Python transformations become the superior choice:

• Advanced Data Manipulation: Perform complex transformations, conditional logic, or iterative data processing that SQL can't handle efficiently.
• API Integrations: Easily connect and interact with external APIs, enabling seamless integration of third-party data.
• Data Science Workflows: Utilize Python's rich ecosystem of libraries, such as Pandas, NumPy, TensorFlow, and scikit-learn, to build sophisticated data science pipelines, machine learning models, and predictive analytics.

Running Python Scripts in Keboola: Environment and Limitations

Keboola runs your Python transformation scripts within isolated Docker environments, ensuring stable and consistent execution. Each Python transformation container is provisioned with:

• 8GB of memory - sufficient for most data processing tasks.
• A 6-hour execution limit - long enough to support extensive data tasks.
• The latest stable Python version, with options to temporarily switch to previous versions if compatibility is required.

You can easily adjust your Python version within the transformation settings, ensuring optimal compatibility and performance.

Adding Python Packages in Keboola

Enhance the capabilities of your Python transformation scripts by adding external Python packages from PyPI directly within the Keboola UI. Here's how:

• Packages Section: Specify required packages directly through the Keboola interface. Ensure correct spelling and versioning, as errors will only manifest when the transformation job runs.
• Direct Installation in Scripts: Alternatively, include package installation commands within your Python scripts. This method allows precise control over package versions and dependencies.

Common libraries such as Pandas, Requests, and NumPy are frequently added to streamline data manipulation and integrations. Always import the libraries explicitly in your scripts after installation to avoid runtime errors.

Understanding Input and Output Mapping in Keboola

Keboola’s unique mapping system simplifies data management, clearly defining how your Python scripts interact with data inputs and outputs:

• Input Mapping: Tables from Keboola Storage are converted to CSV files staged at /data/in/tables/. Python scripts must read data from these specified paths. Incorrect configuration can lead to file-not-found errors and pipeline disruptions.
• Output Mapping: Save your transformed data outputs to /data/out/tables/. Properly configured output mappings ensure that transformed data is correctly stored and readily accessible for further analysis or downstream processes.

Best Practices for Working with CSV Files

When handling CSV files, it's critical to follow certain best practices:

• Explicit Formatting: Always explicitly set formatting options when reading and writing CSVs to avoid unexpected results.
• Using index=False: When saving dataframes to CSV, setting index=False prevents unnecessary index columns, reducing potential errors and simplifying data processing.

Interactive Development with JupyterLab Workspaces

Keboola offers built-in JupyterLab workspaces, providing an interactive environment to develop, test, and optimize your Python transformation scripts:

• Interactive Coding: Develop Python scripts interactively, immediately examining results and adjusting scripts accordingly.
• Efficient Resource Management: Sessions can be paused and resumed, helping you efficiently manage billing and resource use. Workspaces auto-sleep after one hour of inactivity to optimize resource consumption.
• Data Loading and Unloading: Easily load input data and test outputs directly within the workspace environment, speeding up your development workflow.

Hands-On Example: Step-by-Step Guide

Let's walk through a practical example of performing a Python transformation in Keboola:

1. Create a Python Transformation: Start by creating a new Python transformation in Keboola. Clearly name it and add a meaningful description for future reference.
2. Configure Input Mapping: In the Input Table section, specify the tables or CSV files you want to use. For our example, we’ll use a CSV file.
3. Develop Your Script in JupyterLab: Launch a JupyterLab workspace directly from your transformation. It automatically includes the same mapped input files, streamlining your development process.
4. Test and Refine: Carefully develop, test, and refine your script within the workspace. Verify that it correctly handles the input data and produces the expected results.
5. Configure Output Mapping: Before running your transformation, clearly define your output mappings. This ensures your results are saved back to Keboola Storage at the designated output location.
6. Execute and Validate: Execute your Python transformation. Validate the results to ensure accuracy and completeness.

By following this structured approach, you’ll experience fewer errors, more efficient data transformations, and improved overall workflow productivity.

Conclusion: Optimizing Your Data Pipelines with Python

Python transformations within Keboola offer powerful capabilities beyond traditional SQL operations, enabling advanced data manipulation, integration, and sophisticated data science workflows. With intuitive input/output mappings, the flexibility of Python's extensive libraries, and interactive JupyterLab workspaces, your data pipelines become more robust, scalable, and efficient.