Query icon
Script icon
Docker icon

Query data from Dremio and process it in Polars with a Python script

Query a Dremio Cloud lakehouse with SQL in Kestra, then transform the Amazon Ion results into a typed Polars DataFrame with a Python task.

Categories
Data

Run SQL against a Dremio Cloud lakehouse and hand the result set straight to a Python task that reshapes it with Polars, all in one orchestrated flow. Dremio returns query results as Amazon Ion, and this blueprint shows the full path from a JDBC query over your lakehouse to a clean, typed Polars DataFrame ready for analytics, validation, or downstream loading. It removes the glue work of exporting Dremio output, converting Ion types, and wiring up a Python environment by hand.

How it works

  • The query task (io.kestra.plugin.jdbc.dremio.Query) connects to Dremio Cloud over JDBC using the sql.dremio.cloud:443 endpoint with SSL, your PROJECT_ID (set from vars.project_id), and the postgres.public schema. It authenticates with the $token username and a Dremio personal access token, runs SELECT first_name, last_name, hire_date, salary FROM postgres.public.employees LIMIT 100, and uses fetchType: STORE to persist results to Kestra internal storage.
  • The python task (io.kestra.plugin.scripts.python.Script) runs on the io.kestra.plugin.scripts.runner.docker.Docker task runner with polars[sqlalchemy,adbc] and amazon-ion installed as dependencies. It reads the stored result via {{outputs.query.uri}}, parses the Amazon Ion payload, converts Ion-specific types (decimals to floats, nulls to None), builds a polars.DataFrame, and prints glimpse().

What you get

  • A working SQL-to-Python pipeline over a Dremio lakehouse with zero manual file handling.
  • Correct Ion type conversion so Dremio decimals and nulls land cleanly in Polars.
  • A reproducible Docker-based Python environment with pinned data dependencies.
  • Results stored in Kestra internal storage and passed by reference between tasks.

Who it's for

  • Data engineers building lakehouse pipelines on top of Dremio.
  • Analytics engineers who prefer Polars for fast, in-memory transformation.
  • Platform teams standardizing how Dremio queries feed downstream Python jobs.

Why orchestrate this with Kestra

Dremio is a query engine, not a scheduler. Kestra adds event triggers, retries, full execution lineage, and pass-by-reference outputs ({{outputs.query.uri}}) so the SQL and Python steps stay decoupled yet connected. Everything is declarative YAML you can version, review, and reuse, with isolated Docker runtimes per task. This is the orchestration layer Dremio's own console cannot provide: scheduling on data events, automatic recovery, and observability across the SQL and Python boundary.

Prerequisites

  • A Dremio Cloud account and project (the project_id comes from your Dremio URL, for example the last segment of https://app.dremio.cloud/sonar/<project_id>).
  • A Docker-enabled Kestra worker for the Python script task runner.
  • Network access to sql.dremio.cloud:443.

Secrets

  • DREMIO_TOKEN: a Dremio personal access token used as the JDBC password. Create it under your Dremio account settings in the "Personal Access Token" section and store it as a Kestra secret rather than inline.

Quick start

  1. Add the DREMIO_TOKEN secret to your Kestra instance.
  2. Set vars.project_id to your Dremio project ID.
  3. Adjust the JDBC url schema and the sql query to match your tables.
  4. Execute the flow and inspect the Polars glimpse() output in the python task logs.

How to extend

  • Swap the sample employees query for your own SQL or parameterize it with flow inputs.
  • Add a Schedule or webhook trigger to refresh the data on a cadence or on demand.
  • Replace glimpse() with Polars transformations, joins, or filters and write the output back to object storage or a warehouse.
  • Add retries and alerting on the query task for resilient production runs.

Links

Share this Blueprint
See How

New to Kestra?

Use blueprints to kickstart your first workflows.