Architecture Decision Guide: Synapse vs Databricks vs Both

Datanest Digital | datanest.dev

Purpose

This guide provides a structured decision framework for determining which Azure analytics

service to use for each workload in your data platform. Most mature Azure data platforms

benefit from running both Synapse and Databricks. The question is not "which one" but

"which workload goes where."

Executive Summary

Decision Tree

Use this tree top-down. Start with your workload type and follow the branches.

Level 1: What is the primary workload?

START HERE
  │
  ├── T-SQL analytics / reporting ──────────> SYNAPSE (dedicated or serverless pool)
  │
  ├── Ad-hoc data exploration ──────────────> See Level 2A
  │
  ├── ETL / Data transformation ────────────> See Level 2B
  │
  ├── Machine Learning ─────────────────────> DATABRICKS
  │
  ├── Real-time streaming ──────────────────> DATABRICKS (Structured Streaming)
  │
  ├── Data warehousing (star schema) ───────> See Level 2C
  │
  └── Data sharing / governance ────────────> See Level 2D

Level 2A: Ad-Hoc Data Exploration

Ad-Hoc Data Exploration
  │
  ├── Data is in Parquet/CSV/JSON on ADLS?
  │  └── YES ──> SYNAPSE SERVERLESS
  │    - OPENROWSET queries, no cluster spin-up
  │    - Pay per TB scanned (~$5/TB)
  │    - Ideal for infrequent, schema-discovery queries
  │
  ├── Data is in Delta Lake?
  │  ├── Need T-SQL interface?
  │  │  └── YES ──> SYNAPSE SERVERLESS (with Delta support)
  │  └── Need full Spark/Python interface?
  │    └── YES ──> DATABRICKS SQL or notebooks
  │
  └── Data volume per query > 1 TB regularly?
    └── YES ──> DATABRICKS SQL WAREHOUSE
      - More cost-effective at high scan volumes
      - Better query optimization for Delta

Level 2B: ETL / Data Transformation

ETL / Data Transformation
  │
  ├── Transformation language?
  │  ├── T-SQL only (CTAS, views, stored procs)
  │  │  └── SYNAPSE DEDICATED POOL or SERVERLESS
  │  │
  │  ├── PySpark / Scala Spark
  │  │  ├── Workload < 2 hours/day, < 500 GB?
  │  │  │  └── EITHER (cost-compare with calculator tool)
  │  │  └── Workload > 2 hours/day or > 500 GB?
  │  │    └── DATABRICKS (Photon engine, better autoscaling)
  │  │
  │  └── Mixed T-SQL + Spark
  │    └── BOTH: Databricks for Spark, Synapse for T-SQL layers
  │
  ├── Need Delta Lake MERGE / UPSERT?
  │  └── YES ──> DATABRICKS
  │    - Native Delta engine with optimized MERGE
  │    - Synapse Spark has Delta support but less optimized
  │
  ├── Need orchestration?
  │  ├── Simple pipelines ──> Synapse Pipelines (built-in ADF)
  │  ├── Complex DAGs ──> Databricks Workflows or external orchestrator
  │  └── Enterprise orchestration ──> Azure Data Factory (works with both)
  │
  └── Need incremental processing?
    ├── Change Data Feed (Delta) ──> DATABRICKS
    └── Watermark-based ──> EITHER

Level 2C: Data Warehousing

Data Warehousing
  │
  ├── Existing T-SQL codebase to maintain?
  │  └── YES ──> SYNAPSE DEDICATED POOL
  │    - MPP engine compatible with SQL Server patterns
  │    - Distribution, indexing, materialized views
  │
  ├── Building new from scratch?
  │  ├── Team has strong SQL Server / T-SQL skills?
  │  │  └── YES ──> SYNAPSE DEDICATED POOL
  │  ├── Team prefers Spark + SQL hybrid?
  │  │  └── YES ──> DATABRICKS SQL WAREHOUSE
  │  └── Need lakehouse architecture (no data copies)?
  │    └── YES ──> DATABRICKS SQL WAREHOUSE over Delta Lake
  │
  ├── Concurrency requirements?
  │  ├── < 30 concurrent queries ──> EITHER
  │  └── > 30 concurrent queries ──> DATABRICKS SQL WAREHOUSE
  │    - Better concurrency scaling with serverless SQL warehouses
  │
  └── Data freshness requirements?
    ├── Near real-time (< 5 min) ──> DATABRICKS (streaming into Delta)
    └── Batch (hourly+) ──> EITHER

Level 2D: Data Sharing & Governance

Data Sharing & Governance
  │
  ├── Cross-workspace data sharing?
  │  └── DATABRICKS UNITY CATALOG
  │    - Centralized metastore across workspaces
  │    - Row/column-level security
  │    - Data lineage tracking
  │
  ├── Need to share data with T-SQL consumers?
  │  └── BOTH: Unity Catalog for governance + Synapse serverless for T-SQL access
  │
  ├── External data sharing (outside organization)?
  │  ├── Delta Sharing protocol ──> DATABRICKS
  │  └── Via SQL endpoint ──> SYNAPSE or DATABRICKS SQL
  │
  └── Audit and compliance?
    ├── Unity Catalog audit logs ──> DATABRICKS
    └── Synapse + Purview integration ──> SYNAPSE

Combined Architecture Patterns

Pattern 1: Databricks for Processing, Synapse for Serving

Best for: Organizations with strong T-SQL reporting teams and large-scale Spark ETL.

Ingestion ──> Databricks (Bronze/Silver/Gold) ──> ADLS Gen2 (Delta) ──> Synapse Serverless ──> Power BI

When to use:

• ETL is primarily PySpark
• Reporting team uses T-SQL
• Power BI connects via Synapse serverless endpoints
• Cost-sensitive on the serving layer (pay-per-query)

When to avoid:

• Low data volumes (< 100 GB) -- Synapse serverless alone may suffice
• No T-SQL requirements

Pattern 2: Synapse for DWH, Databricks for ML

Best for: Organizations with an existing Synapse dedicated pool running star-schema workloads.

Sources ──> ADF/Synapse Pipelines ──> Synapse Dedicated Pool (DWH)
                 ──> ADLS Gen2 ──> Databricks (ML training)

When to use:

• Established Synapse DWH with complex T-SQL logic
• ML team needs Spark for feature engineering and training
• Reporting runs against the dedicated pool

When to avoid:

• DWH workload is shrinking or migrating to lakehouse

Pattern 3: Full Lakehouse with Synapse SQL Access

Best for: New builds adopting the lakehouse architecture.

Ingestion ──> Databricks (medallion architecture) ──> Delta Lake on ADLS Gen2
                            ├──> Databricks SQL (primary)
                            └──> Synapse Serverless (T-SQL access)

When to use:

• Greenfield deployment
• Want single source of truth in Delta Lake
• Mixed consumer base (SQL + Python + BI)

When to avoid:

• Regulatory requirement for a traditional DWH appliance

Pattern 4: Migration Transition State

Best for: Organizations migrating from Synapse Spark pools to Databricks.

Phase 1: Synapse Spark + Synapse SQL (current)
Phase 2: Databricks Spark + Synapse SQL (transition -- use migration guide)
Phase 3: Databricks Spark + Databricks SQL + Synapse Serverless (target)

Cost Decision Matrix

Use the included tools/cost_comparison_calculator.py for detailed estimates. Here are

general guidelines:

Synapse is Cheaper When:

Databricks is Cheaper When:

Both Together Optimizes Cost When:

Workload Mapping Worksheet

Use this worksheet to map your current workloads. Fill in each row and use the

decision tree above to determine the target service.

Anti-Patterns to Avoid

1. Running Synapse Spark Pools for Large-Scale ETL

Synapse Spark pools lack Photon, have slower autoscaling, and limited cluster

configuration. If your Spark workloads exceed 2 hours/day, Databricks is typically

30-50% more cost-effective due to Photon and better cluster management.

2. Using Databricks for Simple T-SQL Reporting

If your reporting team writes T-SQL and data volumes are small (< 10 TB/month scanned),

Synapse serverless SQL is simpler and cheaper. There is no need to spin up a Databricks

SQL warehouse for 20 queries per day.

3. Duplicating Data Between Services

The worst outcome is copying data into both Synapse dedicated pools AND Delta Lake.

Choose ONE storage layer (Delta Lake on ADLS Gen2 is recommended) and have both services

read from it. Synapse serverless can query Delta directly.

4. Running Dedicated Pools 24/7 at High DWU

If your dedicated pool runs at DW2000c+ but utilization is low outside business hours,

either implement pause/resume automation or consider moving to Databricks SQL serverless

which scales to zero.

5. Ignoring Unity Catalog for Governance

If you run Databricks, enable Unity Catalog from day one. Retrofitting governance is

expensive. Synapse consumers can still access Unity Catalog-managed tables via Synapse

serverless views over Delta Lake.

Migration Decision

If you are currently on Synapse Spark pools and considering Databricks, see the

companion guide: guides/migration_synapse_to_databricks.md.

Key migration triggers:

• Spark workload costs exceeding budget by > 20%
• Need for Photon engine acceleration
• Unity Catalog governance requirements
• ML/MLflow workflow needs
• Structured Streaming requirements
• Cluster startup time complaints (Synapse Spark: ~5 min, Databricks: ~2-3 min)

Decision Checklist

Before finalizing your architecture, verify:

• [ ] Each workload is mapped to a specific service with a documented rationale
• [ ] ADLS Gen2 is designated as the shared storage layer
• [ ] Cross-service authentication is planned (see guides/security_cross_service.md)
• [ ] Cost estimates are calculated for the combined architecture
• [ ] Power BI connectivity path is determined (Synapse endpoint vs Databricks SQL)
• [ ] Governance model is defined (Unity Catalog, Purview, or both)
• [ ] Disaster recovery covers both services
• [ ] Network architecture supports private endpoints for both services
• [ ] Team skill gaps are identified and training is planned

Further Reading

• guides/security_cross_service.md -- Authentication patterns
• guides/migration_synapse_to_databricks.md -- Migration playbook
• sql/serverless_views_over_delta.sql -- Synapse-to-Delta integration SQL
• tools/cost_comparison_calculator.py -- Automated cost comparison

*Datanest Digital -- datanest.dev*

Migration Guide: Synapse Spark Pools to Azure Databricks

Datanest Digital | datanest.dev

Overview

This guide covers the end-to-end migration from Azure Synapse Spark pools to Azure

Databricks. The migration preserves your existing data in ADLS Gen2 (no data movement

required) and focuses on compute, code, and orchestration changes.

Migration Phases

Phase 1: Assessment & Planning    (1-2 weeks)
Phase 2: Infrastructure Provisioning  (1 week)
Phase 3: Code Migration        (2-4 weeks)
Phase 4: Orchestration Migration    (1-2 weeks)
Phase 5: Validation & Cutover     (1-2 weeks)

Phase 1: Assessment & Planning

1.1 Inventory Current Synapse Spark Assets

Catalog every Spark asset in your Synapse workspace:

1.2 Identify Synapse-Specific APIs

Search your notebooks for Synapse-specific APIs that need translation:

1.3 Assess Spark Pool Configurations

Map each Synapse Spark pool to a Databricks cluster configuration:

Node size mapping (approximate):

Phase 2: Infrastructure Provisioning

2.1 Deploy Databricks Workspace

Use the included Terraform module at terraform/synapse-databricks/main.tf to deploy:

• Databricks workspace in the same region as your ADLS Gen2 account
• Unity Catalog metastore (if not already provisioned)
• Managed identity for ADLS Gen2 access
• Private endpoint connectivity (recommended)

2.2 Configure Storage Access

The most critical step. Your ADLS Gen2 accounts must be accessible from Databricks.

Option A: Unity Catalog External Locations (recommended)

-- In Databricks SQL
CREATE STORAGE CREDENTIAL adls_credential
WITH (AZURE_MANAGED_IDENTITY = '<managed-identity-id>');

CREATE EXTERNAL LOCATION bronze_lake
URL 'abfss://bronze@<storage>.dfs.core.windows.net/'
WITH (STORAGE CREDENTIAL adls_credential);

Option B: Cluster-Level ADLS Access

# Spark config on cluster or in notebook
spark.conf.set(
  "fs.azure.account.key.<storage>.dfs.core.windows.net",
  dbutils.secrets.get(scope="storage", key="account-key")
)

2.3 Migrate Spark SQL Metadata

If you used Synapse Spark SQL databases and tables, recreate them in Databricks:

-- Create Unity Catalog schema matching Synapse Spark database
CREATE SCHEMA IF NOT EXISTS catalog_name.schema_name;

-- Register existing Delta tables (no data copy needed)
CREATE TABLE catalog_name.schema_name.table_name
USING DELTA
LOCATION 'abfss://container@storage.dfs.core.windows.net/path/to/table';

2.4 Migrate Custom Libraries

Upload custom .whl and .jar files to:

• Unity Catalog Volumes (recommended): CREATE VOLUME catalog.schema.libs
• DBFS: /FileStore/jars/ (legacy approach)
• Workspace files: /Workspace/Shared/libs/

Phase 3: Code Migration

3.1 Notebook Translation

Step 1: Export from Synapse

Export notebooks from Synapse Studio as .ipynb or .py files. Use the Synapse REST API

for bulk export:

# List all notebooks
az synapse notebook list \
  --workspace-name <workspace> \
  --query "[].name" -o tsv

# Export each notebook
az synapse notebook export \
  --workspace-name <workspace> \
  --name <notebook_name> \
  --output-folder ./exported_notebooks/

Step 2: API Translation

Apply these find-and-replace patterns across all exported notebooks:

# mssparkutils.fs -> dbutils.fs
# Direct replacement -- API is nearly identical
# mssparkutils.fs.ls()    -> dbutils.fs.ls()
# mssparkutils.fs.cp()    -> dbutils.fs.cp()
# mssparkutils.fs.mv()    -> dbutils.fs.mv()
# mssparkutils.fs.rm()    -> dbutils.fs.rm()
# mssparkutils.fs.mkdirs()  -> dbutils.fs.mkdirs()
# mssparkutils.fs.head()   -> dbutils.fs.head()

# Secrets
# mssparkutils.credentials.getSecret("kv-name", "secret-name")
# -> dbutils.secrets.get(scope="kv-name", key="secret-name")

# Notebook orchestration
# mssparkutils.notebook.run("path", timeout, {"param": "val"})
# -> dbutils.notebook.run("path", timeout, {"param": "val"})

# Exit values
# mssparkutils.notebook.exit("value")
# -> dbutils.notebook.exit("value")

Step 3: Handle Synapse SQL Connector

Replace synapsesql connector calls with JDBC or lakehouse patterns:

# BEFORE (Synapse)
df = spark.read.synapsesql("pool.schema.table")
df.write.synapsesql("pool.schema.table", mode="overwrite")

# AFTER (Databricks) -- Option 1: JDBC
jdbc_url = "jdbc:sqlserver://<synapse-endpoint>:1433;database=<db>"
df = spark.read.jdbc(url=jdbc_url, table="schema.table",
           properties={"accessToken": token})

# AFTER (Databricks) -- Option 2: Unity Catalog lakehouse table
df = spark.read.table("catalog.schema.table")
df.write.mode("overwrite").saveAsTable("catalog.schema.table")

Step 4: Import to Databricks

# Use Databricks CLI for bulk import
databricks workspace import-dir \
  ./exported_notebooks/ \
  /Workspace/Shared/migrated/ \
  --overwrite

3.2 Spark Job Definition Migration

Convert Synapse Spark job definitions to Databricks job definitions:

// Databricks Job JSON equivalent
{
  "name": "etl_daily_pipeline",
  "tasks": [
    {
      "task_key": "ingest",
      "notebook_task": {
        "notebook_path": "/Workspace/Shared/migrated/ingest",
        "base_parameters": {
          "date": "{{job.trigger_time.iso_date}}"
        }
      },
      "job_cluster_key": "etl_cluster"
    }
  ],
  "job_clusters": [
    {
      "job_cluster_key": "etl_cluster",
      "new_cluster": {
        "spark_version": "14.3.x-scala2.12",
        "node_type_id": "Standard_DS4_v2",
        "autoscale": {
          "min_workers": 2,
          "max_workers": 8
        }
      }
    }
  ]
}

Phase 4: Orchestration Migration

4.1 Synapse Pipeline Spark Activities

Replace Synapse pipeline Spark activities with Databricks activities:

If using Azure Data Factory, add a Databricks linked service and replace activity

references. The pipeline structure can remain identical.

4.2 Moving to Databricks Workflows (Optional)

If you want to consolidate orchestration into Databricks:

1. Map each pipeline to a Databricks multi-task job

2. Convert pipeline parameters to job parameters

3. Map pipeline triggers to Databricks job triggers

4. Replace ADF data flow activities with Spark notebooks

Phase 5: Validation & Cutover

5.1 Parallel Run Validation

Run both Synapse Spark and Databricks pipelines in parallel for 1-2 weeks:

# Validation notebook -- compare outputs
synapse_output = spark.read.delta("abfss://...synapse_output_path...")
databricks_output = spark.read.delta("abfss://...databricks_output_path...")

# Row count comparison
assert synapse_output.count() == databricks_output.count(), \
  f"Row count mismatch: {synapse_output.count()} vs {databricks_output.count()}"

# Schema comparison
assert synapse_output.schema == databricks_output.schema, \
  "Schema mismatch detected"

# Data comparison (sample-based for large datasets)
diff = synapse_output.exceptAll(databricks_output)
assert diff.count() == 0, f"Data differences found: {diff.count()} rows"

5.2 Performance Baseline

Compare execution metrics:

5.3 Cutover Checklist

• [ ] All notebooks migrated and tested
• [ ] All job definitions recreated in Databricks
• [ ] Orchestration pipelines updated
• [ ] Storage access verified from Databricks
• [ ] Secrets migrated to Databricks secret scopes
• [ ] Custom libraries uploaded and tested
• [ ] Unity Catalog tables registered
• [ ] Monitoring and alerting configured
• [ ] Synapse Spark pools paused (do not delete yet)
• [ ] Parallel run validation passed for all pipelines
• [ ] Downstream consumers (Synapse SQL, Power BI) verified
• [ ] Runbook updated for operations team

5.4 Decommission Synapse Spark Pools

After 2-4 weeks of stable Databricks operation:

1. Delete Synapse Spark pool configurations

2. Remove Spark-related linked services

3. Archive exported Synapse notebooks

4. Update documentation and runbooks

5. Retain Synapse workspace if still using SQL pools or pipelines

Common Migration Issues

Post-Migration Optimization

Once migrated, take advantage of Databricks features not available in Synapse Spark:

1. Enable Photon -- 2-8x speedup on Delta Lake queries

2. Use Unity Catalog -- Centralized governance

3. Delta Live Tables -- Declarative ETL pipelines

4. Structured Streaming -- Replace batch with streaming where applicable

5. MLflow -- Integrated ML experiment tracking

6. Databricks Asset Bundles -- CI/CD for notebooks and jobs

*Datanest Digital -- datanest.dev*