Pipeline & Transform Migration: Foundry to Azure¶

A technical deep-dive for data engineers migrating Palantir Foundry pipelines, transforms, and scheduling to Azure Data Factory, dbt, and Microsoft Fabric.

1. Overview of Foundry pipeline architecture¶

Palantir Foundry organizes data transformation through a layered system that couples compute, orchestration, and governance into a single proprietary surface.

The key architectural constraint is that every component above runs inside Foundry's proprietary runtime. Code, scheduling definitions, quality rules, and monitoring configuration are stored in Foundry-specific formats that cannot be exported to standard tooling without rewriting.

2. Azure pipeline architecture¶

The Azure equivalent separates concerns into purpose-built services, each best-in-class for its function.

flowchart LR
    subgraph Ingestion
        ADF[Azure Data Factory]
        EH[Event Hubs]
    end
    subgraph Storage
        ADLS[ADLS Gen2 / OneLake]
    end
    subgraph Transformation
        dbt[dbt Core / dbt Cloud]
        NB[Fabric / Databricks Notebooks]
        DBX[Databricks Spark Jobs]
    end
    subgraph Quality
        DBTT[dbt Tests]
        GE[Great Expectations]
        DC[Data Contracts]
    end
    subgraph Orchestration
        ADFT[ADF Triggers & Pipelines]
        GHA[GitHub Actions CI/CD]
    end
    subgraph Monitoring
        AM[Azure Monitor]
        AI[App Insights]
        WH[Webhook Alerts]
    end
    subgraph AI Enrichment
        AOI[Azure OpenAI]
        AIF[Azure AI Foundry]
    end

    ADF -->|Copy to Bronze| ADLS
    EH -->|Stream| ADLS
    ADLS --> dbt
    ADLS --> NB
    ADLS --> DBX
    dbt --> DBTT
    NB --> GE
    DBTT --> DC
    ADFT -->|Schedule| ADF
    ADFT -->|Trigger| dbt
    GHA -->|Deploy| dbt
    ADF -->|On failure| WH
    WH --> AM
    dbt --> AOI
    NB --> AIF

Why this is better: Each service can be replaced independently. dbt models are portable SQL. ADF pipelines are ARM/Bicep templates stored in Git. Notebooks run on any Spark-compatible engine. There is no single vendor whose removal would require rewriting everything.

3. Pipeline Builder to ADF migration¶

Foundry's Pipeline Builder provides a visual canvas for connecting data sources, applying transforms, and scheduling execution. Azure Data Factory provides an equivalent visual designer with broader connector support.

Foundry Pipeline Builder pattern¶

In Foundry, a typical ingestion pipeline is configured through the visual builder and produces a graph of dataset dependencies. Behind the scenes, it generates transform code and schedules. The pipeline is not exportable as a standard format.

Azure Data Factory equivalent¶

CSA-in-a-Box provides a reference ADF pipeline for batch ingestion at domains/shared/pipelines/adf/pl_ingest_to_bronze.json:

{
    "name": "pl_ingest_to_bronze",
    "properties": {
        "description": "Generic batch ingestion pipeline. Copies data from source to ADLS raw/bronze container with metadata logging.",
        "activities": [
            {
                "name": "SetIngestionTimestamp",
                "type": "SetVariable",
                "typeProperties": {
                    "variableName": "ingestionTimestamp",
                    "value": "@utcnow('yyyy-MM-ddTHH:mm:ssZ')"
                }
            },
            {
                "name": "CopyToRaw",
                "type": "Copy",
                "dependsOn": [
                    {
                        "activity": "SetIngestionTimestamp",
                        "dependencyConditions": ["Succeeded"]
                    }
                ],
                "typeProperties": {
                    "source": { "type": "DelimitedTextSource" },
                    "sink": {
                        "type": "ParquetSink",
                        "storeSettings": { "type": "AzureBlobFSWriteSettings" }
                    }
                }
            }
        ],
        "parameters": {
            "sourceContainer": {
                "type": "String",
                "defaultValue": "source-data"
            },
            "sourceFolderPath": { "type": "String" },
            "domainName": { "type": "String", "defaultValue": "shared" },
            "entityName": { "type": "String" }
        }
    }
}

The master orchestration pipeline (pl_medallion_orchestration) chains ingestion with dbt transforms through the full Bronze, Silver, and Gold medallion sequence:

ForEachEntity_IngestToBronze (parallel, batch=4)
    --> RunDbt_Bronze (tag:bronze)
        --> RunDbt_Silver (tag:silver)
            --> RunDbt_Gold (tag:gold)
                --> OnFailure: WebActivity alert via webhook

CSA-in-a-Box evidence: domains/shared/pipelines/adf/pl_medallion_orchestration.json

Migration checklist¶

4. Python transform migration¶

Foundry Python transforms use PySpark wrapped in proprietary decorators that handle input/output dataset binding, dependency tracking, and incremental computation. Migrating requires removing the decorator layer and replacing it with dbt models, Fabric notebooks, or Databricks jobs.

Before: Foundry Python transform¶

# Foundry Code Repository - Python transform
from transforms.api import transform_df, Input, Output

@transform_df(
    Output("/Company/datasets/silver/customers_cleaned"),
    source=Input("/Company/datasets/bronze/raw_customers"),
)
def compute(source):
    from pyspark.sql import functions as F

    return (
        source
        .dropDuplicates(["customer_id"])
        .withColumn("first_name", F.upper(F.trim(F.col("first_name"))))
        .withColumn("last_name", F.upper(F.trim(F.col("last_name"))))
        .withColumn("email", F.lower(F.trim(F.col("email"))))
        .withColumn("country_code", F.upper(F.coalesce(F.col("country"), F.lit("US"))))
        .filter(F.col("customer_id").isNotNull())
    )

After: dbt SQL model (recommended path)¶

The same logic expressed as a dbt incremental model. This is the CSA-in-a-Box standard pattern from domains/shared/dbt/models/silver/slv_customers.sql:

{{
    config(
        materialized='incremental',
        unique_key='customer_sk',
        incremental_strategy='merge',
        file_format='delta',
        tags=['silver', 'customers'],
        on_schema_change='fail'
    )
}}

with bronze as (
    select * from {{ ref('brz_customers') }}
    {% if is_incremental() %}
    where _dbt_loaded_at > (select max(_dbt_loaded_at) from {{ this }})
    {% endif %}
),

deduped as (
    select
        *,
        row_number() over (
            partition by customer_id
            order by _source_modified_at desc, _dbt_loaded_at desc
        ) as _row_num
    from bronze
),

cleaned as (
    select
        {{ dbt_utils.generate_surrogate_key(['customer_id']) }} as customer_sk,
        cast(customer_id as string) as customer_id,
        trim(upper(coalesce(first_name, ''))) as first_name,
        trim(upper(coalesce(last_name, ''))) as last_name,
        trim(lower(coalesce(email, ''))) as email,
        trim(upper(coalesce(country, 'US'))) as country_code,
        now() as _dbt_loaded_at,
        '{{ invocation_id }}' as _dbt_run_id
    from deduped
    where _row_num = 1
)

select * from cleaned

After: Fabric/Databricks notebook (when PySpark is required)¶

For transforms that genuinely require Python (ML feature engineering, complex UDFs, API calls), use a Fabric or Databricks notebook:

# Fabric / Databricks notebook - no proprietary decorators
from pyspark.sql import functions as F

# Read from Delta Lake directly - no Foundry Input() wrapper
bronze_df = spark.read.format("delta").table("bronze.raw_customers")

cleaned = (
    bronze_df
    .dropDuplicates(["customer_id"])
    .withColumn("first_name", F.upper(F.trim(F.col("first_name"))))
    .withColumn("last_name", F.upper(F.trim(F.col("last_name"))))
    .withColumn("email", F.lower(F.trim(F.col("email"))))
    .withColumn("country_code", F.upper(F.coalesce(F.col("country"), F.lit("US"))))
    .filter(F.col("customer_id").isNotNull())
    .withColumn("_processed_at", F.current_timestamp())
)

# Write to Delta Lake directly - no Foundry Output() wrapper
cleaned.write.format("delta").mode("overwrite").saveAsTable("silver.customers_cleaned")

Key differences¶

5. SQL transform migration to dbt¶

Foundry SQL transforms map directly to dbt models. The migration is primarily syntactic.

Before: Foundry SQL transform¶

-- Foundry SQL Transform (Code Repository)
-- Inputs and outputs declared in the Foundry UI
SELECT
    order_id,
    customer_id,
    order_date,
    SUM(line_total) AS order_total,
    COUNT(*) AS line_count
FROM @input('bronze_orders')
GROUP BY order_id, customer_id, order_date

After: dbt SQL model¶

-- domains/shared/dbt/models/silver/slv_orders.sql
{{
    config(
        materialized='incremental',
        unique_key='order_sk',
        incremental_strategy='merge',
        file_format='delta',
        tags=['silver', 'orders']
    )
}}

with source as (
    select * from {{ ref('brz_orders') }}
    {% if is_incremental() %}
    where _dbt_loaded_at > (select max(_dbt_loaded_at) from {{ this }})
    {% endif %}
),

aggregated as (
    select
        {{ dbt_utils.generate_surrogate_key(['order_id']) }} as order_sk,
        order_id,
        customer_id,
        order_date,
        sum(line_total) as order_total,
        count(*) as line_count,
        now() as _dbt_loaded_at
    from source
    group by order_id, customer_id, order_date
)

select * from aggregated

Translation rules¶

6. Incremental pipeline patterns¶

Foundry's incremental computation is one of its most valuable features. It processes only changed rows or files since the last successful run. dbt provides equivalent incremental functionality through its incremental materialization strategy.

Before: Foundry incremental Python transform¶

from transforms.api import transform, Input, Output, incremental

@incremental()
@transform(
    output=Output("/datasets/silver/transactions"),
    source=Input("/datasets/bronze/raw_transactions"),
)
def compute(source, output):
    # Foundry automatically tracks which rows/files are new
    new_rows = source.dataframe()
    output.write_dataframe(new_rows)

After: dbt incremental model¶

The CSA-in-a-Box project configures incremental models at the project level in dbt_project.yml:

models:
    csa_analytics:
        bronze:
            +materialized: incremental
            +file_format: delta
            +schema: bronze
        silver:
            +materialized: incremental
            +file_format: delta
            +schema: silver
            +incremental_strategy: merge
        gold:
            +materialized: table # Gold is full rebuild by default
            +file_format: delta
            +schema: gold

Individual models control incremental behavior:

-- Incremental merge pattern (CSA-in-a-Box standard)
{{
    config(
        materialized='incremental',
        unique_key='_surrogate_key',
        incremental_strategy='merge',
        file_format='delta',
        on_schema_change='fail'
    )
}}

with source as (
    select * from {{ source('raw_data', 'sample_customers') }}
    {% if is_incremental() %}
    {{ incremental_file_filter(this) }}
    {% endif %}
),

staged as (
    select
        {{ dbt_utils.generate_surrogate_key(['customer_id']) }} as _surrogate_key,
        *,
        now() as _dbt_loaded_at,
        {{ source_file_path_from_metadata() }} as _source_file,
        {{ source_file_modification_time() }} as _source_modified_at,
        '{{ invocation_id }}' as _dbt_run_id
    from source
)

select * from staged

CSA-in-a-Box evidence: domains/shared/dbt/models/bronze/brz_customers.sql

ADF incremental copy (for ingestion)¶

For source-system ingestion, ADF provides watermark-based and change-data-capture patterns:

{
    "name": "IncrementalCopy",
    "type": "Copy",
    "typeProperties": {
        "source": {
            "type": "SqlSource",
            "sqlReaderQuery": {
                "value": "SELECT * FROM transactions WHERE modified_date > '@{pipeline().parameters.lastWatermark}'",
                "type": "Expression"
            }
        }
    }
}

Incremental strategy comparison¶

7. Streaming migration approaches¶

Foundry uses Apache Flink for low-latency streaming pipelines. Azure provides multiple streaming options depending on latency requirements and complexity.

Option A: Event Hubs + Stream Analytics (managed, lowest effort)¶

Best for: simple transformations, windowed aggregations, real-time dashboards.

-- Azure Stream Analytics query
SELECT
    System.Timestamp() AS window_end,
    device_id,
    AVG(temperature) AS avg_temp,
    MAX(temperature) AS max_temp,
    COUNT(*) AS reading_count
INTO [output-power-bi]
FROM [input-event-hub] TIMESTAMP BY event_time
GROUP BY
    device_id,
    TumblingWindow(minute, 5)

Option B: Spark Structured Streaming (Databricks/Fabric)¶

Best for: complex transformations, ML scoring, multi-source joins.

# Databricks or Fabric notebook
from pyspark.sql import functions as F

stream = (
    spark.readStream
    .format("kafka")
    .option("kafka.bootstrap.servers", broker)
    .option("subscribe", "raw-events")
    .load()
    .select(F.from_json(F.col("value").cast("string"), schema).alias("data"))
    .select("data.*")
)

# Write to Delta Lake with checkpoint for exactly-once semantics
(stream
    .writeStream
    .format("delta")
    .outputMode("append")
    .option("checkpointLocation", "/checkpoints/events")
    .toTable("silver.events_streaming")
)

Option C: Fabric Real-Time Intelligence¶

Best for: organizations standardizing on Microsoft Fabric, Kusto-based analytics.

// Fabric Eventhouse KQL query
RawEvents
| where ingestion_time() > ago(5m)
| summarize avg_temp = avg(temperature),
            max_temp = max(temperature),
            count = count()
  by device_id, bin(event_time, 5m)
| where avg_temp > 80.0

Streaming migration decision matrix¶

8. Scheduling migration¶

Foundry supports cron-based, dependency-based, and event-based scheduling. ADF provides direct equivalents through its trigger system.

Before: Foundry scheduling¶

Foundry schedules are configured through the UI or API:

• Cron schedules: Run at fixed intervals (e.g., daily at 06:00 UTC)
• Dependency triggers: Run when upstream datasets are updated
• Event triggers: Run when external events occur (e.g., file arrival)

After: ADF trigger types¶

CSA-in-a-Box provides reference triggers at domains/shared/pipelines/adf/triggers/.

Schedule trigger (replaces Foundry cron schedules):

{
    "name": "tr_daily_medallion",
    "properties": {
        "type": "ScheduleTrigger",
        "typeProperties": {
            "recurrence": {
                "frequency": "Day",
                "interval": 1,
                "startTime": "2026-01-01T06:00:00Z",
                "timeZone": "UTC",
                "schedule": {
                    "hours": [6],
                    "minutes": [0]
                }
            }
        },
        "pipelines": [
            {
                "pipelineReference": {
                    "referenceName": "pl_medallion_orchestration",
                    "type": "PipelineReference"
                },
                "parameters": {
                    "environment": "prod",
                    "fullRefresh": false
                }
            }
        ]
    }
}

CSA-in-a-Box evidence: domains/shared/pipelines/adf/triggers/tr_daily_medallion.json

Tumbling window trigger (replaces Foundry dependency-based scheduling):

{
    "name": "tr_hourly_ingest",
    "properties": {
        "type": "TumblingWindowTrigger",
        "typeProperties": {
            "frequency": "Hour",
            "interval": 1,
            "startTime": "2026-01-01T00:00:00Z",
            "retryPolicy": { "count": 3, "intervalInSeconds": 30 },
            "dependsOn": []
        }
    }
}

Event-based trigger (replaces Foundry event triggers):

{
    "name": "tr_file_arrival",
    "properties": {
        "type": "BlobEventsTrigger",
        "typeProperties": {
            "blobPathBeginsWith": "/landing/sales/",
            "blobPathEndsWith": ".csv",
            "events": ["Microsoft.Storage.BlobCreated"],
            "scope": "/subscriptions/{sub}/resourceGroups/{rg}/providers/Microsoft.Storage/storageAccounts/{sa}"
        }
    }
}

Schedule mapping reference¶

9. Data quality migration¶

Foundry Data Expectations allow pipeline authors to define assertions on output datasets. Azure provides richer alternatives through dbt tests, Great Expectations, and CSA-in-a-Box data contracts.

Before: Foundry Data Expectations¶

# Foundry Data Expectations
from transforms.api import transform_df, Input, Output
from transforms.expectations import expect_column_values_to_not_be_null
from transforms.expectations import expect_column_values_to_be_unique

@expect_column_values_to_not_be_null("customer_id")
@expect_column_values_to_be_unique("customer_id")
@transform_df(
    Output("/datasets/silver/customers"),
    source=Input("/datasets/bronze/raw_customers"),
)
def compute(source):
    return source.dropDuplicates(["customer_id"])

After: dbt tests in schema.yml¶

CSA-in-a-Box defines quality tests in domains/shared/dbt/models/silver/schema.yml:

version: 2

models:
    - name: slv_customers
      description: >
          Silver layer: Cleansed customer records. Deduped, standardized
          casing, validated email format, surrogate-keyed.
      columns:
          - name: customer_sk
            description: Surrogate key (md5 of customer_id).
            tests:
                - unique
                - not_null
          - name: customer_id
            description: Natural customer identifier from Bronze.
          - name: email
            description: Customer email (lowercased)
          - name: is_valid
            description: True when the row passes every quality check.
            tests:
                - not_null
          - name: category
            tests:
                - accepted_values:
                      values:
                          ["ELECTRONICS", "CLOTHING", "HOME", "BOOKS", "SPORTS"]
                      severity: warn

After: CSA-in-a-Box data contracts¶

For cross-domain quality enforcement, CSA-in-a-Box uses data product contracts (domains/finance/data-products/invoices/contract.yaml):

apiVersion: csa.microsoft.com/v1
kind: DataProductContract

metadata:
    name: finance.invoices
    domain: finance
    owner: finance-data-engineering@contoso.com
    version: "1.0.0"

schema:
    primary_key: [invoice_sk]
    columns:
        - name: invoice_sk
          type: string
          nullable: false
        - name: status
          type: string
          nullable: false
          allowed_values: [DRAFT, SENT, PAID, OVERDUE, CANCELLED, PARTIAL]

sla:
    freshness_minutes: 60
    valid_row_ratio: 0.97

quality_rules:
    - rule: expect_column_values_to_not_be_null
      column: invoice_sk
    - rule: expect_column_values_to_be_unique
      column: invoice_sk
    - rule: expect_column_values_to_be_in_set
      column: status
      value_set: [DRAFT, SENT, PAID, OVERDUE, CANCELLED, PARTIAL]
    - rule: expect_column_values_to_be_between
      column: total_amount
      min_value: 0
      mostly: 0.97

Contracts are validated in CI via .github/workflows/validate-contracts.yml.

After: Inline validation flags (CSA-in-a-Box pattern)¶

Rather than filtering bad data, Silver models flag invalid records so Gold models can decide:

-- From slv_customers.sql - validation pattern
validated as (
    select
        *,
        case when customer_id is null or customer_id = ''
             then true else false end as _is_missing_id,
        {{ flag_invalid_email('email') }} as _is_invalid_email,
        case when first_name = '' and last_name = ''
             then true else false end as _is_missing_name
    from cleaned
)

select
    *,
    not (_is_missing_id or _is_invalid_email or _is_missing_name) as is_valid,
    concat_ws('; ',
        case when _is_missing_id then 'customer_id missing' end,
        case when _is_invalid_email then 'email failed regex validation' end,
        case when _is_missing_name then 'first_name and last_name both empty' end
    ) as validation_errors
from validated

Quality migration comparison¶

10. LLM enrichment pipelines on Azure¶

Foundry's LLM pipeline transforms provide built-in classification, sentiment analysis, summarization, entity extraction, and translation. On Azure, these capabilities are delivered through Azure OpenAI and Azure AI Foundry, integrated into data pipelines via ADF custom activities or notebooks.

Before: Foundry LLM transform¶

# Foundry LLM Transform (AIP-enabled pipeline)
from transforms.api import transform_df, Input, Output
from palantir_models.transforms import LlmTransform

@transform_df(
    Output("/datasets/enriched/classified_tickets"),
    source=Input("/datasets/silver/support_tickets"),
)
def compute(source):
    llm = LlmTransform(model="palantir-llm-v2")
    return llm.classify(
        source,
        input_col="ticket_text",
        output_col="category",
        labels=["billing", "technical", "account", "general"],
    )

After: Azure OpenAI in a Fabric/Databricks notebook¶

# Fabric or Databricks notebook with Azure OpenAI
import openai
from pyspark.sql import functions as F
from pyspark.sql.types import StringType

# Configure Azure OpenAI
client = openai.AzureOpenAI(
    azure_endpoint="https://<resource>.openai.azure.com/",
    api_version="2024-06-01",
    azure_ad_token_provider=get_bearer_token  # Managed Identity
)

def classify_ticket(text: str) -> str:
    response = client.chat.completions.create(
        model="gpt-4o",
        messages=[
            {"role": "system", "content": (
                "Classify the support ticket into exactly one category: "
                "billing, technical, account, general. "
                "Respond with only the category name."
            )},
            {"role": "user", "content": text}
        ],
        max_tokens=10,
        temperature=0.0
    )
    return response.choices[0].message.content.strip().lower()

# Register as Spark UDF
classify_udf = F.udf(classify_ticket, StringType())

# Read source data
tickets = spark.read.format("delta").table("silver.support_tickets")

# Apply classification with rate limiting via repartitioning
classified = (
    tickets
    .repartition(10)  # Control parallelism for API rate limits
    .withColumn("category", classify_udf(F.col("ticket_text")))
    .withColumn("_classified_at", F.current_timestamp())
)

classified.write.format("delta").mode("overwrite").saveAsTable("enriched.classified_tickets")

After: ADF pipeline with Azure OpenAI custom activity¶

For batch LLM enrichment orchestrated through ADF:

{
    "name": "LLM_Classify_Tickets",
    "type": "DatabricksNotebook",
    "typeProperties": {
        "notebookPath": "/notebooks/enrich/classify_support_tickets",
        "baseParameters": {
            "source_table": "silver.support_tickets",
            "target_table": "enriched.classified_tickets",
            "model_deployment": "gpt-4o",
            "batch_size": "100"
        }
    },
    "linkedServiceName": {
        "referenceName": "ls_databricks",
        "type": "LinkedServiceReference"
    }
}

LLM use-case mapping¶

11. CI/CD for pipelines¶

Foundry Code Repositories provide a built-in web IDE with Git branching, pull requests, and CI checks. On Azure, pipeline and transform code lives in standard Git repositories with GitHub Actions (or Azure DevOps Pipelines) for CI/CD.

CSA-in-a-Box CI/CD architecture¶

The reference CI/CD workflow deploys dbt models to Databricks across multiple verticals. From .github/workflows/deploy-dbt.yml:

name: Deploy dbt Models

on:
    push:
        branches: [main]
        paths:
            - "domains/*/dbt/**"
    workflow_dispatch:
        inputs:
            vertical:
                description: 'Vertical to deploy (or "all")'
                required: true
                type: choice
                options: [all, usda, dot, usps, noaa, epa, commerce, interior]
            environment:
                description: "Target environment"
                type: choice
                options: [dev, staging, prod]
            full_refresh:
                description: "Run full refresh (drop and recreate)"
                type: boolean
                default: false

jobs:
    deploy:
        runs-on: ubuntu-latest
        steps:
            - uses: actions/checkout@v4
            - uses: actions/setup-python@v5
              with:
                  python-version: "3.12"
            - run: pip install dbt-databricks==1.8.* dbt-core==1.8.*
            - run: dbt deps
            - run: dbt seed --target ${{ inputs.environment }}
            - run: dbt run --target ${{ inputs.environment }}
            - run: dbt test --target ${{ inputs.environment }}
            - uses: actions/upload-artifact@v4
              with:
                  name: dbt-artifacts
                  path: |
                      target/manifest.json
                      target/run_results.json

Data contract validation in CI¶

Quality gates enforce contracts before code reaches production:

# .github/workflows/validate-contracts.yml (simplified)
name: Validate Data Contracts

on:
    pull_request:
        paths:
            - "domains/*/data-products/*/contract.yaml"

jobs:
    validate:
        runs-on: ubuntu-latest
        steps:
            - uses: actions/checkout@v4
            - run: pip install pyyaml jsonschema
            - run: python -m governance.contracts.contract_validator

CI/CD comparison¶

12. Performance comparison¶

The following benchmarks are representative of typical mid-sized federal deployments (20 TB, 50-100 pipelines, 500 analytic users). Actual performance varies by workload, data volume, and SKU selection.

ETL throughput¶

Cost efficiency¶

Operational advantages¶

13. Common pitfalls¶

Pitfall 1: Attempting a 1:1 port of Foundry transforms¶

Problem: Teams try to replicate every Foundry Python transform as an identical PySpark notebook. This preserves the most complex, hardest-to-maintain pattern.

Solution: Audit each transform. If the logic is expressible in SQL (and 80%+ of transforms are), convert to a dbt model. Reserve notebooks for genuine Python requirements: ML features, API integrations, complex UDFs.

Pitfall 2: Ignoring dbt incremental strategies¶

Problem: Teams materialize everything as table (full rebuild), causing pipeline runtimes to grow linearly with data volume.

Solution: Use materialized='incremental' with incremental_strategy='merge' for Silver and Bronze models. Use timestamp watermarks (_dbt_loaded_at) to process only new data. CSA-in-a-Box configures this at the project level in dbt_project.yml.

Pitfall 3: Replicating Foundry's monolithic scheduling¶

Problem: Foundry's dependency-based scheduling rebuilds the entire DAG on any upstream change. Teams replicate this with a single ADF trigger that runs everything.

Solution: Use ADF's composable trigger system. Schedule ingestion hourly (tr_hourly_ingest), run the full medallion pipeline daily (tr_daily_medallion), and use event-based triggers for latency-sensitive sources. Let dbt's is_incremental() logic handle partial runs efficiently.

Pitfall 4: Not externalizing data quality¶

Problem: Teams embed quality checks inside transform code (Python assertions, try/except blocks) instead of using declarative quality frameworks.

Solution: Use dbt tests in schema.yml for column-level assertions. Use data contracts (contract.yaml) for cross-domain SLAs. Use inline validation flags (is_valid, validation_errors) instead of silently dropping bad records. This approach is auditable, version-controlled, and enforceable in CI.

Pitfall 5: Underestimating streaming migration complexity¶

Problem: Teams assume Foundry Flink pipelines can be directly ported to Spark Structured Streaming.

Solution: Evaluate latency requirements honestly. Many "streaming" pipelines in Foundry are actually micro-batch with 5-15 minute windows. If true sub-second latency is not required, use ADF scheduled triggers with short intervals or dbt incremental models. If sub-second latency is required, evaluate Stream Analytics (simplest), Spark Structured Streaming (most flexible), or Fabric Real-Time Intelligence (best for KQL-centric teams).

Pitfall 6: Skipping the local development workflow¶

Problem: Teams develop dbt models only in cloud environments, losing the fast iteration loop.

Solution: Use dbt with DuckDB locally for development and testing. The CSA-in-a-Box dbt project supports DuckDB as a development target (note the if target.type != 'duckdb' conditionals in model configs). Run dbt build locally in seconds, then deploy to Databricks via CI/CD.

Pitfall 7: Not mapping Foundry Health Checks to Azure Monitor¶

Problem: After migration, teams lose visibility into pipeline health because they did not set up equivalent alerting.

Solution: Configure Azure Monitor alerts for ADF pipeline failures. Use dbt source freshness tests for data-arrival monitoring. Set up webhook activities in ADF for failure notifications (see OnPipelineFailure in pl_medallion_orchestration). Route alerts to Teams, Slack, PagerDuty, or email through Azure Monitor action groups.

Summary: migration path by component¶

Last updated: 2026-04-30 Maintainers: CSA-in-a-Box core team See also: Data Integration Migration | DevOps Migration | AI & AIP Migration | Migration Playbook