{"id":9133,"date":"2026-09-21T05:19:33","date_gmt":"2026-09-21T05:19:33","guid":{"rendered":"https:\/\/www.coffee.ai\/articles\/ingest-crm-into-data-lake"},"modified":"2026-09-21T05:19:33","modified_gmt":"2026-09-21T05:19:33","slug":"ingest-crm-into-data-lake","status":"publish","type":"post","link":"https:\/\/www.coffee.ai\/articles\/ingest-crm-into-data-lake","title":{"rendered":"How To Ingest CRM Data Into a Data Lake: A Guide"},"content":{"rendered":"<p><em>Written by: Doug Camplejohn, CEO &amp; Co-Founder, Coffee<\/em><\/p>\n<h2 id=\"key-takeaways\">Key Takeaways<\/h2>\n<ul>\n<li>CRM data in Salesforce, HubSpot, and Dynamics 365 stays siloed by default, so you need explicit pipelines to feed your data lake.<\/li>\n<li>Manual CSV exports create stale, error-prone data. Automated pipelines using Bulk API, CDC, or managed connectors keep data fresh and reliable.<\/li>\n<li>Raw landing zones should store untouched source data with Hive-style partitioning and metadata fields such as ingested_at, batch_id, and source_system.<\/li>\n<li>Incremental loads with watermark columns and SCD Type 2 handling keep pipelines efficient while preserving history for objects like Accounts and Contacts.<\/li>\n<li>Coffee improves CRM data quality at the source, which reduces the need for complex extraction workarounds and keeps clean data flowing into your lake.<\/li>\n<\/ul>\n<p><a href=\"https:\/\/www.coffee.ai\/pricing\" class=\"solid-button\" target=\"_blank\">Explore Coffee Pricing<\/a><\/p>\n<h2>Why Reliable CRM Ingestion Pipelines Matter<\/h2>\n<p><a href=\"https:\/\/www.coffee.ai\" target=\"_blank\" rel=\"noindex nofollow\">Coffee&#8217;s Market Data<\/a> shows that 71% of sales reps spend too much time on data entry, leaving only 35% of their time for actual selling. That manual burden degrades CRM data quality at the source. A well-designed ingestion pipeline enforces good data in and good data out. Structured extraction, schema-aware landing, and incremental loading keep the lake aligned with real-world activity instead of partial notes. Complex CRM object models, careful rate limit handling, and SCD Type 2 history logic together explain why a production-grade pipeline often takes days to weeks to build and test.<\/p>\n<h2>Readiness And Preconditions<\/h2>\n<p>Confirm a few basics before you start writing extraction code:<\/p>\n<ul>\n<li>API access credentials for your CRM: a Connected App with JWT Bearer OAuth 2.0 for Salesforce, a Private App access token for HubSpot (legacy API keys were <a href=\"https:\/\/clonepartner.com\/blog\/how-to-export-data-from-hubspot-service-hub-methods-api-limits\" target=\"_blank\" rel=\"noindex nofollow\">sunset in November 2022<\/a>), or a service principal for Dynamics 365<\/li>\n<li>A target data lake such as S3, ADLS Gen2, or GCS with appropriate IAM roles and bucket policies<\/li>\n<li>An orchestration tool such as Apache Airflow, Azure Data Factory, or Databricks Lakeflow Jobs<\/li>\n<li>A basic understanding of ELT patterns: load raw data first, then transform inside the warehouse or lake<\/li>\n<\/ul>\n<h2>Step 1: Map The CRM Object Model And Initial Scope<\/h2>\n<p>Every major CRM organizes data around a common set of core objects. Start with these before moving to custom objects:<\/p>\n<ul>\n<li><strong>Accounts<\/strong>: the company or organization record; primary key is <code>AccountId<\/code> in Salesforce<\/li>\n<li><strong>Contacts<\/strong>: individual people associated with an Account<\/li>\n<li><strong>Leads<\/strong>: unqualified prospects not yet linked to an Account<\/li>\n<li><strong>Opportunities<\/strong>: active deals with stage, amount, and close date<\/li>\n<li><strong>Cases<\/strong>: support tickets linked to Contacts or Accounts<\/li>\n<\/ul>\n<p>Map the foreign key relationships before extraction. Contacts have an <code>AccountId<\/code> foreign key. Opportunities have both <code>AccountId<\/code> and <code>ContactId<\/code>. Cases link back to both. These relationships determine join order in the silver layer and which objects must land first so others can be enriched.<\/p>\n<h2>Step 2: Choose Your Extraction Method<\/h2>\n<p>The extraction method sets your throughput, latency, and operational complexity. Use the table below to compare the four main options by ideal use case and the primary limit you will hit first, then match the method to your volume and freshness needs.<\/p>\n<table>\n<thead>\n<tr>\n<th>Method<\/th>\n<th>Best For<\/th>\n<th>Key Limit<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>REST API<\/td>\n<td>Low-volume, real-time lookups<\/td>\n<td><a href=\"https:\/\/salesforcedictionary.com\/blogs\/salesforce-integration-patterns-rest-vs-soap-vs-bulk-vs-composite\" target=\"_blank\" rel=\"noindex nofollow\">100,000 + (1,000 \u00d7 license count) calls\/24 hrs (Salesforce Enterprise)<\/a><\/td>\n<\/tr>\n<tr>\n<td>Bulk API 2.0<\/td>\n<td>High-volume batch extraction<\/td>\n<td><a href=\"https:\/\/salesforcedictionary.com\/blogs\/salesforce-integration-patterns-rest-vs-soap-vs-bulk-vs-composite\" target=\"_blank\" rel=\"noindex nofollow\">150 MB CSV per upload chunk; 150M records\/24 hrs<\/a><\/td>\n<\/tr>\n<tr>\n<td>CDC \/ Streaming<\/td>\n<td>Near-real-time delta capture<\/td>\n<td><a href=\"https:\/\/salesforcedictionary.com\/blogs\/salesforce-integration-patterns-rest-vs-soap-vs-bulk-vs-composite\" target=\"_blank\" rel=\"noindex nofollow\">3-day (72-hour) event replay window (Salesforce Pub\/Sub API)<\/a><\/td>\n<\/tr>\n<tr>\n<td>Managed Connector<\/td>\n<td>Speed to value, low maintenance<\/td>\n<td>Vendor-managed; schema drift handled automatically<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>HubSpot rate limits vary by tier. <a href=\"https:\/\/clonepartner.com\/blog\/how-to-export-data-from-hubspot-service-hub-methods-api-limits\" target=\"_blank\" rel=\"noindex nofollow\">Free\/Starter allows 100 requests per 10 seconds and 250,000 per day; Professional allows 190 requests per 10 seconds and 650,000 per day; Enterprise allows 190 requests per 10 seconds and 1,000,000 per day.<\/a> HubSpot&#8217;s CRM Search API often becomes the bottleneck. It is capped at <a href=\"https:\/\/clonepartner.com\/blog\/how-to-export-data-from-hubspot-service-hub-methods-api-limits\" target=\"_blank\" rel=\"noindex nofollow\">4 requests per second and 10,000 total results per query<\/a>. Prefer list endpoints with the <code>after<\/code> cursor token for full exports.<\/p>\n<p>The <a href=\"https:\/\/learn.microsoft.com\/en-us\/power-apps\/developer\/data-platform\/webapi\/query-data-web-api\" target=\"_blank\" rel=\"noindex nofollow\">Dataverse Web API for Dynamics 365 returns up to 5,000 rows per request<\/a> and uses server-driven paging via <code>@odata.nextLink<\/code>. Offset-based pagination does not work here, so plan for cursor-based loops.<\/p>\n<p>The following Python snippet submits a Salesforce Bulk API 2.0 query job and polls for results:<\/p>\n<pre><code>import requests, time SF_INSTANCE = \"https:\/\/yourorg.my.salesforce.com\" ACCESS_TOKEN = \"YOUR_ACCESS_TOKEN\" headers = { \"Authorization\": f\"Bearer {ACCESS_TOKEN}\", \"Content-Type\": \"application\/json\", } # Create query job job_payload = { \"operation\": \"query\", \"query\": \"SELECT Id, Name, LastModifiedDate FROM Account\", \"contentType\": \"CSV\", } job_resp = requests.post( f\"{SF_INSTANCE}\/services\/data\/v60.0\/jobs\/query\", json=job_payload, headers=headers, ) job_id = job_resp.json()[\"id\"] # Poll until complete while True: status = requests.get( f\"{SF_INSTANCE}\/services\/data\/v60.0\/jobs\/query\/{job_id}\", headers=headers, ).json()[\"state\"] if status == \"JobComplete\": break time.sleep(10) # Download results (paginate via locator) locator = None while True: url = f\"{SF_INSTANCE}\/services\/data\/v60.0\/jobs\/query\/{job_id}\/results\" if locator: url += f\"?locator={locator}\" result = requests.get(url, headers=headers) # Write result.text (CSV) to landing zone locator = result.headers.get(\"Sforce-Locator\") if not locator or locator == \"null\": break <\/code><\/pre>\n<h2>Step 3: Design The Raw Landing Zone<\/h2>\n<p>The raw landing zone acts as the source of truth for your pipeline. <a href=\"https:\/\/techinterview.org\/post\/3233468987\/lld-data-lake\" target=\"_blank\" rel=\"noindex nofollow\">Raw data must remain untouched so later jobs can replay from the original bytes.<\/a> Keep this layer focused on faithful storage of the source payload plus ingestion metadata.<\/p>\n<p>Use this folder partitioning convention:<\/p>\n<pre><code>s3:\/\/your-bucket\/crm\/salesforce\/accounts\/year=2026\/month=09\/day=20\/ s3:\/\/your-bucket\/crm\/hubspot\/contacts\/year=2026\/month=09\/day=20\/ <\/code><\/pre>\n<p><a href=\"https:\/\/segmenthub.com\/posts\/warehouse-export-partitions\" target=\"_blank\" rel=\"noindex nofollow\">Hive-style <code>key=value<\/code> partition directories are recognized automatically by Athena, Redshift Spectrum, Spark, and DuckDB<\/a>. This pattern enables partition pruning without extra catalog configuration. Plain date folders like <code>2026-09-20<\/code> require manual partition declaration.<\/p>\n<p>Each file in the landing zone should carry three metadata fields alongside the source payload:<\/p>\n<ul>\n<li><code>ingested_at<\/code>: UTC timestamp of when the batch landed<\/li>\n<li><code>batch_id<\/code>: a UUID identifying the pipeline run for lineage and replay<\/li>\n<li><code>source_system<\/code>: for example, <code>salesforce<\/code>, <code>hubspot<\/code>, or <code>dynamics365<\/code><\/li>\n<\/ul>\n<p>Store raw files as compressed JSON or the source&#8217;s native CSV format. <a href=\"https:\/\/prophecy.ai\/guides\/what-is-data-extraction\" target=\"_blank\" rel=\"noindex nofollow\">Apply schema-on-read for raw landing zones<\/a>. Projecting structure during processing instead of enforcing it at landing prevents an unexpected source field from blocking ingestion.<\/p>\n<p>Convert data to Parquet in the silver layer. <a href=\"https:\/\/cloudtech.com\/resources\/aws-data-lake-best-practices\" target=\"_blank\" rel=\"noindex nofollow\">Converting JSON to Parquet can cut query costs by more than 70% for regular reports.<\/a><\/p>\n<p>Write a commit marker file (<code>_manifest.json<\/code>) last, after all data parts are written. <a href=\"https:\/\/segmenthub.com\/posts\/warehouse-export-partitions\" target=\"_blank\" rel=\"noindex nofollow\">If the manifest exists, the partition is complete and safe to load. If it does not exist, the next sweep will overwrite it.<\/a><\/p>\n<h2>Step 4: Implement Incremental Loads And SCD Type 2<\/h2>\n<p>Incremental loads keep CRM pipelines efficient. Full reloads are expensive and rarely necessary for most CRM objects. Use watermark columns to extract only changed records. <a href=\"https:\/\/startdataengineering.com\/post\/incremental-load-strategy\" target=\"_blank\" rel=\"noindex nofollow\">When a source table exposes an <code>updated_at<\/code> or <code>LastModifiedDate<\/code> column, use it as the incremental watermark with a half-open interval filter.<\/a><\/p>\n<pre><code>-- Salesforce incremental extract filter WHERE LastModifiedDate &gt;= '2026-09-19T00:00:00Z' AND LastModifiedDate &lt; '2026-09-20T00:00:00Z' <\/code><\/pre>\n<p>Dimension tables such as Accounts and Contacts often need full history. Implement SCD Type 2 for these tables. <a href=\"https:\/\/conduktor.io\/glossary\/cdc-for-real-time-data-warehousing\" target=\"_blank\" rel=\"noindex nofollow\">Type 2 Slowly Changing Dimensions create a new row for each change with validity timestamps, which preserves historical analysis while keeping a clear current state.<\/a><\/p>\n<pre><code>-- SCD Type 2 MERGE for Accounts (Databricks \/ Spark SQL) MERGE INTO silver.accounts AS target USING ( SELECT id, name, industry, annual_revenue, last_modified_date, ingested_at, batch_id FROM bronze.accounts_staging ) AS source ON target.id = source.id AND target.is_current = TRUE WHEN MATCHED AND ( target.name &lt;&gt; source.name OR target.industry &lt;&gt; source.industry OR target.annual_revenue &lt;&gt; source.annual_revenue ) THEN UPDATE SET target.valid_to = source.ingested_at, target.is_current = FALSE WHEN NOT MATCHED THEN INSERT ( id, name, industry, annual_revenue, valid_from, valid_to, is_current, batch_id ) VALUES ( source.id, source.name, source.industry, source.annual_revenue, source.ingested_at, NULL, TRUE, source.batch_id ); -- Insert new version for updated rows INSERT INTO silver.accounts SELECT id, name, industry, annual_revenue, ingested_at AS valid_from, NULL AS valid_to, TRUE AS is_current, batch_id FROM bronze.accounts_staging s WHERE EXISTS ( SELECT 1 FROM silver.accounts t WHERE t.id = s.id AND t.is_current = FALSE AND t.valid_to = s.ingested_at ); <\/code><\/pre>\n<p><a href=\"https:\/\/startuphub.ai\/ai-news\/artificial-intelligence\/2026\/databricks-simplifies-sql-etl-in-lakehouse\" target=\"_blank\" rel=\"noindex nofollow\">Databricks&#8217; AUTO CDC feature lets users declaratively define change data capture logic, including keys, sequencing, delete handling, and SCD Type 1 or Type 2 storage, with minimal code. This approach replaces many complex MERGE INTO statements.<\/a><\/p>\n<h2>Step 5: Orchestrate And Schedule<\/h2>\n<p>Orchestration keeps incremental CRM ingestion reliable over time. The following Airflow DAG skeleton handles incremental loads with retry logic:<\/p>\n<pre><code>from airflow import DAG from airflow.operators.python import PythonOperator from datetime import datetime, timedelta default_args = { \"owner\": \"data-engineering\", \"retries\": 3, \"retry_delay\": timedelta(minutes=5), \"retry_exponential_backoff\": True, } with DAG( dag_id=\"crm_salesforce_incremental\", default_args=default_args, schedule_interval=\"0 2 * * *\", # 02:00 UTC daily start_date=datetime(2026, 1, 1), catchup=True, # enables backfill max_active_runs=1, ) as dag: extract = PythonOperator( task_id=\"extract_accounts\", python_callable=run_bulk_api_extract, op_kwargs={\"object\": \"Account\", \"watermark_col\": \"LastModifiedDate\"}, ) land = PythonOperator( task_id=\"land_to_s3\", python_callable=write_to_landing_zone, ) merge = PythonOperator( task_id=\"scd2_merge\", python_callable=run_scd2_merge, op_kwargs={\"object\": \"accounts\"}, ) extract &gt;&gt; land &gt;&gt; merge <\/code><\/pre>\n<p>For Azure-based stacks, Azure Data Factory&#8217;s Copy Activity with the Dynamics 365 connector handles entity export natively. <a href=\"https:\/\/go-cloud.io\/azure-data-lake-architecture\" target=\"_blank\" rel=\"noindex nofollow\">Azure Data Factory is recommended for connector-based movement and pipeline orchestration into ADLS Gen2.<\/a> Enable retry policies on each activity and set a pipeline-level timeout so long-running jobs do not consume your entire API quota.<\/p>\n<h2>Step 6: Evaluate Build Versus Buy For Connectors<\/h2>\n<p>The build-versus-buy decision depends on connector coverage, engineering capacity, and time-to-value expectations.<\/p>\n<p><a href=\"https:\/\/trackraptor.com\/blog\/data-pipeline-build-vs-buy-cost\" target=\"_blank\" rel=\"noindex nofollow\">A 12-month TCO model for a mid-size SaaS team running 5\u201310 ingestion sources estimates managed platform costs at $25,000\u2013$90,000 fully loaded annually versus $90,000\u2013$220,000+ for a custom pipeline.<\/a> Ongoing maintenance usually lands around 0.1\u20130.2 FTE for managed tools and 0.5\u20131.5 FTE for custom connector upkeep.<\/p>\n<p><a href=\"https:\/\/techconcepts.org\/blog\/data-pipeline-development-cost\" target=\"_blank\" rel=\"noindex nofollow\">Building a custom batch ETL pipeline for 3\u20135 sources often costs $10,000\u2013$30,000 over 4\u20138 weeks with $500\u2013$2,000 per month of ongoing maintenance.<\/a> For 10 or more sources, costs typically rise to $30,000\u2013$60,000 over 8\u201316 weeks with $1,000\u2013$3,000 per month of maintenance.<\/p>\n<p>Some situations still point to custom builds. These include cases where the primary data source has no connector, the use case needs sub-minute latency, or compliance rules require that raw data always stay inside the company&#8217;s own VPC. Outside those constraints, managed connectors usually win on speed and maintenance burden for standard CRM sources such as Salesforce and HubSpot.<\/p>\n<p>Coffee complements whichever path you choose by improving CRM data quality at the point of capture. Coffee&#8217;s AI agent keeps records clean inside the CRM, which reduces downstream pipeline complexity and makes both managed and custom connectors easier to operate.<\/p>\n<p><a href=\"https:\/\/www.coffee.ai\/pricing\" class=\"solid-button\" target=\"_blank\">Compare Coffee To Custom Pipelines<\/a><\/p>\n<h2>Step 7: Implement PII Governance<\/h2>\n<p>CRM objects contain direct identifiers such as email, phone, and full name, along with indirect identifiers such as IP address, device ID, and financial fields. <a href=\"https:\/\/dataworkers.io\/resources\/how-to-handle-pii-in-data-pipelines\" target=\"_blank\" rel=\"noindex nofollow\">GDPR, CCPA, HIPAA, and SOC 2 all require organizations to know where PII lives, who accessed it, and how to delete it on request.<\/a><\/p>\n<p>Implement the following controls at the lake layer:<\/p>\n<ul>\n<li><strong>Column-level masking<\/strong>: <a href=\"https:\/\/datalakehouse101.com\/knowledge\/column-level-security.html\" target=\"_blank\" rel=\"noindex nofollow\">Apply masking policies at the catalog layer so the same rule enforces across notebooks, SQL endpoints, and BI tools.<\/a> In Databricks Unity Catalog, AWS Lake Formation, or Snowflake, tag PII columns with a sensitivity label and bind masking expressions to those tags.<\/li>\n<li><strong>Row-level security<\/strong>: Restrict which rows a role can see. For example, a regional analyst sees only records where <code>region = 'US-West'<\/code>.<\/li>\n<li><strong>Retention and deletion<\/strong>: <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/databricks\/ldp\/gdpr\" target=\"_blank\" rel=\"noindex nofollow\">GDPR and CCPA require permanent deletion of PII upon request. Delete in the bronze layer first, then propagate to silver and gold. Run <code>VACUUM<\/code> on Delta tables to purge underlying files<\/a>. Delta retains time-travel history for 30 days by default, so deleted rows remain accessible without explicit cleanup.<\/li>\n<li><strong>Audit logging<\/strong>: <a href=\"https:\/\/dataexpert.io\/blog\/lakehouse-governance-pii-best-practices\" target=\"_blank\" rel=\"noindex nofollow\">Track column-level lineage and access logs so teams can trace where sensitive fields move and who accessed them, when, and from which tool.<\/a><\/li>\n<\/ul>\n<p>For deep dives on GDPR right-to-erasure implementation in Delta Lake, see <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/databricks\/ldp\/gdpr\" target=\"_blank\" rel=\"noindex nofollow\">Microsoft&#8217;s Azure Databricks GDPR guidance<\/a>. For AWS Lake Formation column and row filtering, see <a href=\"https:\/\/hidekazu-konishi.com\/entry\/lake_formation_fine_grained_access_control_for_ai_data.html\" target=\"_blank\" rel=\"noindex nofollow\">Hidekazu Konishi&#8217;s Lake Formation fine-grained access control guide<\/a>.<\/p>\n<h2>Common Mistakes And Troubleshooting<\/h2>\n<p>Most CRM ingestion projects run into the same recurring issues:<\/p>\n<ul>\n<li><strong>API rate limiting<\/strong>: HubSpot returns <a href=\"https:\/\/clonepartner.com\/blog\/how-to-export-data-from-hubspot-service-hub-methods-api-limits\" target=\"_blank\" rel=\"noindex nofollow\">HTTP 429 with a <code>Retry-After<\/code> header<\/a> when limits are exceeded. Read the header and implement exponential backoff. Avoid fixed sleep intervals.<\/li>\n<li><strong>Pagination mishandling<\/strong>: Salesforce Bulk API 2.0 uses a job and result-set workflow with an <code>Sforce-Locator<\/code> header, not page numbers. HubSpot uses an <code>after<\/code> cursor token. <a href=\"https:\/\/learn.microsoft.com\/en-us\/power-apps\/developer\/data-platform\/webapi\/query-data-web-api\" target=\"_blank\" rel=\"noindex nofollow\">Dynamics 365 uses <code>@odata.nextLink<\/code> and does not support <code>$skip<\/code>.<\/a><\/li>\n<li><strong>Schema drift<\/strong>: CRM admins add custom fields without notifying data engineering. <a href=\"https:\/\/prophecy.ai\/guides\/what-is-data-extraction\" target=\"_blank\" rel=\"noindex nofollow\">Use schema-on-read in the raw zone<\/a> and detect new columns in the silver merge step before they break downstream models.<\/li>\n<li><strong>Missing watermark columns<\/strong>: Some custom objects lack <code>LastModifiedDate<\/code>. Fall back to comparing source and destination rows by primary key hash, or trigger a full reload for that object only.<\/li>\n<li><strong>SCD Type 2 merge failures<\/strong>: A MERGE that matches on <code>id AND is_current = TRUE<\/code> will silently skip rows if <code>is_current<\/code> was not set correctly on the previous run. Add a row-count reconciliation check after every merge.<\/li>\n<li><strong>Orchestration timeouts<\/strong>: <a href=\"https:\/\/salesforcedictionary.com\/blogs\/salesforce-integration-patterns-rest-vs-soap-vs-bulk-vs-composite\" target=\"_blank\" rel=\"noindex nofollow\">Salesforce REST and SOAP API calls time out after 10 minutes.<\/a> For large Bulk API jobs, poll asynchronously instead of blocking a synchronous task.<\/li>\n<\/ul>\n<h2>Validation And Success Criteria<\/h2>\n<p>Healthy pipelines produce correct data, not just error-free runs. Validate on four dimensions:<\/p>\n<ul>\n<li><strong>Row counts<\/strong>: Compare source record counts (via CRM reporting API) against landed row counts in the bronze table after each run.<\/li>\n<li><strong>Null checks<\/strong>: Assert that primary key columns (<code>Id<\/code>, <code>AccountId<\/code>) contain no nulls in the silver layer.<\/li>\n<li><strong>Watermark progression<\/strong>: Confirm that the maximum <code>LastModifiedDate<\/code> in the bronze table advances with each incremental run. A stale watermark indicates a broken extraction filter.<\/li>\n<li><strong>SCD Type 2 history correctness<\/strong>: For a known record that changed, verify exactly one row with <code>is_current = TRUE<\/code> and at least one row with <code>is_current = FALSE<\/code> and a non-null <code>valid_to<\/code>.<\/li>\n<\/ul>\n<p>Coffee strengthens this validation process by providing ground-truth CRM data from real interactions. Coffee captures activity from emails, calendars, and call transcripts automatically, so teams can compare pipeline output against a trusted behavioral record when they debug discrepancies.<\/p>\n<h2>Variations And Scaling Considerations<\/h2>\n<p>Pipeline design should match team size, connector count, and data volume:<\/p>\n<ul>\n<li><strong>Small teams (1\u20135 engineers, under 10 sources)<\/strong>: Start with a managed connector such as Fivetran or Airbyte for standard CRM objects. Add Airflow only when scheduling complexity justifies it. <a href=\"https:\/\/techconcepts.org\/blog\/data-pipeline-development-cost\" target=\"_blank\" rel=\"noindex nofollow\">A recommended small-team stack of Fivetran + BigQuery + dbt Core + Looker Studio costs roughly $550\u2013$2,200 per month in total.<\/a><\/li>\n<li><strong>Mid-market teams (5\u201320 engineers, 10+ sources)<\/strong>: Use custom incremental pipelines with Airflow or Databricks Lakeflow Jobs. Implement CDC for high-velocity objects such as Activities and Events. Use Delta Lake or Apache Iceberg for ACID upserts in the silver layer.<\/li>\n<li><strong>Multi-CRM ingestion<\/strong>: Normalize object schemas to a canonical model in the silver layer, such as a unified <code>contact<\/code> table with a <code>source_system<\/code> column. Maintain separate bronze prefixes per CRM to preserve source fidelity.<\/li>\n<li><strong>Real-time streaming<\/strong>: <a href=\"https:\/\/salesforcedictionary.com\/blogs\/salesforce-integration-patterns-rest-vs-soap-vs-bulk-vs-composite\" target=\"_blank\" rel=\"noindex nofollow\">Use the Salesforce Pub\/Sub API (gRPC-based) for Change Data Capture events, which provides a 3-day (72-hour) replay window.<\/a> Buffer events in Kafka or Kinesis before applying them to the lake. <a href=\"https:\/\/conduktor.io\/glossary\/cdc-for-real-time-data-warehousing\" target=\"_blank\" rel=\"noindex nofollow\">Apache Flink provides exactly-once processing semantics that are essential for CDC transformations.<\/a><\/li>\n<\/ul>\n<p><a href=\"https:\/\/www.coffee.ai\/pricing\" class=\"solid-button\" target=\"_blank\">Scale Clean CRM Data With Coffee<\/a><\/p>\n<h2>Frequently Asked Questions<\/h2>\n<h3>What Are The Two Main Types Of Data Ingestion?<\/h3>\n<p>The two main types are batch ingestion and streaming ingestion. Batch ingestion extracts data on a schedule such as hourly, daily, or weekly, and loads it in discrete chunks. It is simpler to build, easier to reprocess, and sufficient for most CRM analytics use cases where data that is one to several hours old is acceptable. Streaming ingestion captures and delivers data continuously as events occur, which suits fraud detection, real-time dashboards, and operational AI applications that require data within seconds or minutes. For CRM pipelines, most teams start with batch ingestion using incremental watermarks and add streaming CDC only when business requirements clearly demand sub-minute freshness.<\/p>\n<h3>Is Data Ingestion The Same As ETL?<\/h3>\n<p>Data ingestion and ETL overlap, but they describe different scopes. ETL (Extract, Transform, Load) describes a three-phase process where data is transformed before it reaches the destination. Modern CRM pipelines more commonly follow ELT (Extract, Load, Transform). Raw data lands in the lake first, and transformations run in-warehouse afterward using tools like dbt. This pattern decouples ingestion from analytical logic so teams can revise business rules without re-extracting from the source. Data ingestion refers specifically to the extraction and landing phases, regardless of whether transformation happens before or after landing.<\/p>\n<h3>How Do I Export Data From A CRM?<\/h3>\n<p>The right export method depends on volume and freshness requirements. For Salesforce, the Bulk API 2.0 is the correct choice for any extraction exceeding a few thousand records. It <a href=\"https:\/\/salesforcedictionary.com\/blogs\/salesforce-integration-patterns-rest-vs-soap-vs-bulk-vs-composite\" target=\"_blank\" rel=\"noindex nofollow\">handles up to 150 million records per 24-hour rolling window<\/a> and abstracts batch management automatically. For HubSpot, use list endpoints with cursor-based pagination for full object exports, and <a href=\"https:\/\/clonepartner.com\/blog\/how-to-export-data-from-hubspot-service-hub-methods-api-limits\" target=\"_blank\" rel=\"noindex nofollow\">batch read endpoints (up to 100 object IDs per request)<\/a> to reduce call volume. Avoid the CRM&#8217;s native UI export for pipeline use. <a href=\"https:\/\/clonepartner.com\/blog\/how-to-export-data-from-hubspot-service-hub-methods-api-limits\" target=\"_blank\" rel=\"noindex nofollow\">HubSpot&#8217;s UI export silently omits engagement bodies, attachment binary content, and the full association graph.<\/a> For Dynamics 365, use the Dataverse Web API with server-driven paging via <code>@odata.nextLink<\/code>. In all cases, authenticate using OAuth 2.0 or JWT Bearer flows and avoid hard-coded API keys.<\/p>\n<h3>How Do I Ingest Dynamics 365 Entities Into A Data Lake With Data Factory?<\/h3>\n<p>Azure Data Factory&#8217;s native Dynamics 365 (Microsoft Dataverse) connector <a href=\"https:\/\/learn.microsoft.com\/en-us\/azure\/data-factory\/connector-dynamics-crm-office-365\" target=\"_blank\" rel=\"noindex nofollow\">supports Microsoft Entra service principal authentication (authenticationType &#8220;AADServicePrincipal&#8221;) and enables incremental extraction by manually filtering on the <code>modifiedon<\/code> attribute in a FetchXML query, rather than through a built-in watermark mechanism<\/a>. The general pattern is straightforward. Create a Linked Service pointing to your Dynamics 365 environment with a service principal credential. Create a Dataset referencing the target entity, such as <code>account<\/code>, <code>contact<\/code>, or <code>opportunity<\/code>. Configure a Copy Activity with a source filter on <code>modifiedon &gt;= @pipeline().parameters.watermark_start<\/code>. Write the output to ADLS Gen2 in Parquet format, partitioned by ingestion date. Store the high-water mark in an Azure SQL table or pipeline parameter and update it at the end of each successful run. For large entities, enable parallel copy and set the degree of copy parallelism to match your Dataverse API throughput. The <a href=\"https:\/\/learn.microsoft.com\/en-us\/power-apps\/developer\/data-platform\/webapi\/query-data-web-api\" target=\"_blank\" rel=\"noindex nofollow\">Dataverse Web API returns up to 5,000 rows per page<\/a>, and Data Factory&#8217;s built-in pagination handles the <code>@odata.nextLink<\/code> loop automatically.<\/p>\n<h2>Conclusion<\/h2>\n<p>Building a reliable CRM data ingestion pipeline means navigating API rate limits, pagination patterns, schema drift, and incremental watermarks. Then come the harder problems: SCD Type 2 merge logic and PII governance. All of this work happens before the first analyst query runs. The engineering burden is real and ongoing because APIs change, schemas drift, and watermarks can break silently.<\/p>\n<p>Coffee reduces that burden by improving CRM data quality at the source. Coffee&#8217;s AI agent captures CRM data automatically from emails, calendars, and call transcripts so Salesforce or HubSpot hold accurate ground-truth data from the start. Your lake then receives high-quality records without as many manual fixes or fragile extraction scripts.<\/p>\n<p>Use this guide when you need to design and run the pipeline itself. When you want to reduce pipeline complexity by fixing CRM data at the point of capture, Coffee provides a practical path forward.<\/p>\n<p><a href=\"https:\/\/www.coffee.ai\/pricing\" class=\"solid-button\" target=\"_blank\">Start Capturing Clean CRM Data<\/a><\/p>\n<section data-read-next=\"true\">\n<h2>Read Next<\/h2>\n<ul>\n<li><a href=\"https:\/\/coffee.ai\/articles\/data-lake-architecture-for-crm\" target=\"_blank\">CRM Data Lake Architecture: Salesforce &amp; Data 360<\/a><\/li>\n<li><a href=\"https:\/\/coffee.ai\/articles\/crm-data-lake-integration\" target=\"_blank\">CRM Data Lake Integration: Batch vs CDC vs Federation<\/a><\/li>\n<li><a href=\"https:\/\/coffee.ai\/articles\/crm-data-warehouse-architecture\" target=\"_blank\">CRM Data Warehouse Architecture: A Practical Blueprint<\/a><\/li>\n<li><a href=\"https:\/\/coffee.ai\/articles\/hubspot-crm-data-warehouse\" target=\"_blank\">HubSpot CRM Data Warehouse: The Complete Guide<\/a><\/li>\n<li><a href=\"https:\/\/coffee.ai\/articles\/improve-sales-pipeline-visibility-crm\" target=\"_blank\">How to Improve Sales Pipeline Visibility With CRM Data<\/a><\/li>\n<\/ul>\n<\/section>\n","protected":false},"excerpt":{"rendered":"<p>Learn how to ingest CRM data into a data lake with Coffee&#8217;s step-by-step technical guide. Build reliable pipelines with confidence. Start today!<\/p>\n","protected":false},"author":11,"featured_media":9132,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-9133","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/www.coffee.ai\/articles\/wp-json\/wp\/v2\/posts\/9133","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.coffee.ai\/articles\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.coffee.ai\/articles\/wp-json\/wp\/v2\/types\/post"}],"replies":[{"embeddable":true,"href":"https:\/\/www.coffee.ai\/articles\/wp-json\/wp\/v2\/comments?post=9133"}],"version-history":[{"count":0,"href":"https:\/\/www.coffee.ai\/articles\/wp-json\/wp\/v2\/posts\/9133\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.coffee.ai\/articles\/wp-json\/wp\/v2\/media\/9132"}],"wp:attachment":[{"href":"https:\/\/www.coffee.ai\/articles\/wp-json\/wp\/v2\/media?parent=9133"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.coffee.ai\/articles\/wp-json\/wp\/v2\/categories?post=9133"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.coffee.ai\/articles\/wp-json\/wp\/v2\/tags?post=9133"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}