Load Pipedrive CRM data into BigQuery with a scheduled dlt pipeline

Keep your Pipedrive CRM data continuously available in Google BigQuery for analytics, reporting, and downstream transformations. This blueprint extracts deals, contacts, activities, and related CRM objects from the Pipedrive API and loads them into BigQuery using dlt (the open source data load tool), with schema inference and evolution handled automatically. The whole pipeline runs on an hourly schedule, so your warehouse stays fresh without any manual exports or brittle glue scripts.

How it works

1. A io.kestra.plugin.core.trigger.Schedule trigger named hourly fires the flow every hour using the @hourly cron expression.
2. The single dlt_pipeline task of type io.kestra.plugin.scripts.python.Script runs on a Docker task runner (io.kestra.plugin.scripts.runner.docker.Docker) using the python:3.11 container image.
3. beforeCommands install dlt with the BigQuery extra (pip install dlt[bigquery]) and scaffold the verified source with dlt --non-interactive init pipedrive bigquery.
4. The inline Python script builds a dlt pipeline targeting the pipedrive dataset in BigQuery and calls pipeline.run(pipedrive_source()) to extract, normalize, and load the data.
5. Credentials for both Pipedrive and BigQuery are injected as environment variables that dlt reads, pulled from Kestra Secrets.

What you get

• Hourly, hands-off replication of Pipedrive CRM data into BigQuery.
• Automatic schema creation and evolution managed by dlt.
• A reproducible, containerized Python runtime so the pipeline behaves the same everywhere.
• Centralized, secret-based credential handling instead of plaintext keys.

Who it's for

• Data engineers building a CRM ingestion layer in BigQuery.
• Analytics and RevOps teams that need fresh Pipedrive data for dashboards and modeling.
• Teams standardizing dlt pipelines under a single orchestrator.

Why orchestrate this with Kestra

dlt knows how to load data, but it does not schedule, monitor, or recover itself. Kestra adds the operational layer: event and schedule triggers, automatic retries on transient API or load failures, execution history and logs for lineage and debugging, and a fully declarative YAML definition you can version control. The hourly Schedule trigger replaces ad hoc cron jobs, and every run is observable from one place, which a standalone dlt script cannot provide.

Prerequisites

• A Kestra instance with the Docker task runner available.
• A Pipedrive account with an API token.
• A Google Cloud project with BigQuery enabled and a service account that can write datasets and tables.

Secrets

Configure the following Kestra Secrets:

• PIPEDRIVE_API_KEY: Pipedrive API token used by the dlt source.
• BIGQUERY_PROJECT_ID: target Google Cloud project ID.
• BIGQUERY_CLIENT_EMAIL: service account email for the BigQuery destination.
• BIGQUERY_PRIVATE_KEY: service account private key for the BigQuery destination.

Quick start

1. Add the four secrets above to your Kestra instance.
2. Copy this blueprint into your namespace (adjust namespace from company.team as needed).
3. Confirm Docker is available for the task runner.
4. Run the flow once manually to validate connectivity and the initial load.
5. Leave the hourly trigger enabled to keep BigQuery in sync.

How to extend

• Change the trigger cron to a different cadence (for example daily or every 15 minutes).
• Restrict or expand the resources pulled from pipedrive_source() to load only the CRM objects you need.
• Switch dlt to incremental loading or merge write disposition to reduce volume and cost.
• Add downstream dbt or SQL tasks to model the raw pipedrive dataset.
• Send a Slack or email notification on failure using Kestra's notification plugins.

Links

• Python script plugin docs: https://kestra.io/plugins/plugin-script-python
• BigQuery orchestration with Kestra: https://kestra.io/orchestration/bigquery