[1col]Why Integrate Pipedrive with BigQuery or Snowflake?
Moving Pipedrive data into a cloud data warehouse like Google BigQuery or Snowflake helps unify your revenue data, combining sales pipeline insights with marketing, finance, support, and product usage. With Keboola, you can automate this process, enabling advanced analytics, machine learning, and reporting, all while maintaining reliability and governance.
- [360]Unified view: Bring together deals, activities, and contacts with other business data for a single source of truth.
- [analytics]Analytics & ML: Leverage SQL, built-in ML, and governed schemas for forecasting, attribution, and more.
- [automation]Automation: Keboola Flows orchestrate extraction, transformation, loading, scheduling, and notifications.
[2col]What Does the Keboola Pipedrive Connector Extract?
Keboola’s official Pipedrive connector pulls data incrementally using Pipedrive’s API. Two templates are available:
- [data-source]Basic: Organizations, persons, users, pipelines, activities, stages, and more.
- [advanced]Extended: Everything in Basic, plus deals and additional fields.
All data is loaded incrementally, ensuring up-to-date data without reloading everything. Custom fields are mapped using corresponding *Fields endpoints for full context and usability.
High-Level Process Architecture
- [extract]Extract: Pipedrive data is brought into Keboola Storage tables.
- [analytics](Optional) Transform: SQL transformations in BigQuery or Snowflake workspaces for type hygiene, deduplication, and custom-field mapping.
- [load]Load: Data is published to BigQuery or Snowflake using dedicated writer components.
- [automation]Orchestrate: Keboola Flows manage and automate the process with phases, parallel steps, schedules, and notifications.
Prerequisites
- [services]Keboola project with Flows and component access.
- [company]Pipedrive company domain and API token.
- [cloud]BigQuery: Service Account JSON key with Data Editor & Job User roles.
- [database]Snowflake: Your own credentials or a Keboola-provisioned database.
Step-by-Step: Loading Pipedrive into BigQuery
- Configure the Pipedrive Connector:
- Create a configuration in Data Source Connectors → Pipedrive.
- Enter API token and company domain.
- Select Extended for deals, then save and run once.
- (Optional) Transform in BigQuery Workspace:
- Cast data types (e.g.,
DATETIME vs TIMESTAMP). - Deduplicate incremental appends (latest by
id and updated_time). - Map custom fields by joining with
*Fields tables: CREATE OR REPLACE TABLE analytics.stg_deals AS SELECT AS VALUE t FROM ( SELECT d.*, ROW_NUMBER() OVER (PARTITION BY d.id ORDER BY d.update_time DESC) AS rn FROM raw.pipedrive_deals d ) WHERE rn = 1; - Configure BigQuery Writer:
- In Data Destination Connectors, add BigQuery, paste Service Account JSON, set the dataset.
- Add tables from Storage (raw or transformed), set destination table names, and load type (Full or Incremental).
- Define at least one column’s data type per table.
- Orchestrate with Keboola Flows:
- Create a Flow with Extract (Pipedrive) and Load (BigQuery) steps.
- Run steps in parallel where possible for speed.
- Schedule the Flow and enable notifications for success, warnings, or errors.
Step-by-Step: Loading Pipedrive into Snowflake
- Configure the Pipedrive Connector: Same as above; run once to land raw tables in Storage.
- (Optional) Transform in Snowflake Workspace:
- Handle quoting/case sensitivity for identifiers.
- Normalize types and map custom fields.
- Create marts or views as needed: MERGE INTO ANALYTICS.DEALS AS T USING STAGE.DEALS_INCR AS S ON T.ID = S.ID WHEN MATCHED THEN UPDATE SET T.UPDATE_TIME = S.UPDATE_TIME, T.STAGE_ID = S.STAGE_ID, T.VALUE = S.VALUE WHEN NOT MATCHED THEN INSERT (...columns...) VALUES (...);
- Configure Snowflake Writer:
- Choose between your own Snowflake DB or a Keboola-provisioned one.
- Add tables, define PK for upserts on incremental loads, select Full Load to rebuild as needed.
- Set column types, nullable status, and defaults.
- Orchestrate with Keboola Flows:
- Extract → (Optional Transform) → Load.
- Schedule and enable notifications.
Data Modeling & Quality Best Practices
- [data-source]Custom Fields Mapping: Use lookup tables (e.g.,
dealfields) to translate hashed keys into readable names. - [clean]Type Hygiene: Explicitly select
TIMESTAMP vs DATETIME in BigQuery, quote identifiers in Snowflake. - [efficient]Incremental Loads: BigQuery appends on incremental, so dedupe/merge in SQL; Snowflake supports upsert if PK is set.
- [cloud]API Rate Limits: Pipedrive enforces token-based daily budgets; stagger schedules and limit parallelism. API v2 is more efficient.
- [reliable]Flow Robustness: Don’t enable “continue on failure” unless required. Limit concurrency to about 10 parallel jobs.
- [ops]Ops & Governance: Enable notifications (email/webhook), monitor run history, and version changes in Flows.
Common Use Cases
- [analytics]Pipeline Health & Velocity: Monitor stage conversions, aging, SLAs, and bottlenecks.
- [forecasting]Forecasting & Quota: Use historical win rates and open pipeline for forecasting.
- [decision]Marketing-to-Revenue Attribution: Join campaign and web event data with Pipedrive conversions for CAC and MQL→SQL→Won analysis.
- [person]Rep Productivity & Capacity: Analyze activities for coaching and staffing decisions.
- [product]Product & Pricing Insights: Link deals to products/plans for attach rates, discounting, and churn analysis.
Detailed Checklists
Pipedrive → BigQuery
- [connection]Pipedrive connector: token + domain → Extended → run once
- [clean]Transform (optional): cast timestamps, dedupe, map custom fields
- [database]BigQuery writer: add tables, set at least one column type, choose load type
- [automation]Flow: Extract → Load, schedule, notifications
Pipedrive → Snowflake
- [connection]Pipedrive connector: as above
- [clean]Transform (optional): quote identifiers, normalize types, enrich
- [database]Snowflake writer: credentials or provisioned DB, define PK for upsert, choose load type
- [automation]Flow: Extract → (Transform) → Load, schedule, notifications
Optional: AI-Assisted Setup
Keboola’s MCP Server enables AI-driven configuration. Use chat-based prompts to assemble extractors, writers, flows, and scheduling for Pipedrive to BigQuery or Snowflake pipelines.
Troubleshooting
- [incomplete]Deals missing: Use the Extended template.
- [low-quality]BigQuery incremental duplicates: Expected. Add dedupe/merge transformation.
- [incomplete]Snowflake incremental didn’t update rows: Define a Primary Key for upsert.
- [exclamation]Type mismatch errors: Enforce types in the writer and cast upstream. Mind case/quoting in Snowflake.
- [clock]API limits: Prefer API v2, stagger schedules, reduce parallelism.
Typical Tables & Join Keys
- [database]
deals (PK id, FKs org_id, person_id, stage_id, pipeline_id) - [person]
persons (PK id, FK org_id) - [company]
organizations (PK id) - [analytics]
activities (PK id, FKs deal_id, person_id, org_id, user_id) - [flow]
pipelines, stages (PK id); join deals on pipeline_id/stage_id - [tool]
*fields lookup tables map custom field keys to names
References & Further Reading
