Project 01 · Data Engineering
Metadata-Driven Ingestion:Salesforce → Azure SQL
A configuration-driven Azure Data Factory framework moving hundreds of thousands of Salesforce records into Azure SQL daily. Object names, schemas, load type and column mappings live in metadata, driven by a master/child pipeline pattern over medallion layers in ADLS Gen2. Onboarding a new object is a config file drop — no new pipeline, no redeploy.
- Records synced
- ~100K+/dayRecords synced
- Refresh cadence
- DailyRefresh cadence
- New pipelines per object
- 0New pipelines per object
- Default load pattern
- IncrementalDefault load pattern
- Azure Data Factory
- ADLS Gen2
- Azure SQL Database
- Azure Key Vault
- Azure DevOps CI/CD
- Medallion Architecture
- Salesforce API
Select any component to see how it works.
Source
System of record
Auth: credentials resolved at runtime from Key Vault — never stored in the pipeline.
Control Plane
Metadata decides everything
Ingestion
Salesforce child pipeline
Medallion Layers
ADLS Gen2
Serving
Consumption layer
Outcome: CRM data is queryable in SQL — no API limits, no manual exports.
Cross-cutting platform concerns
Architecture overview · Project 01 · Select any block for detail
Read every component in fullComponent reference
Every component in the architecture above, in detail — each with a plain-language summary and the technical implementation.
SourceSalesforceThe CRM system of record. Objects are pulled through the ADF Salesforce connector.
In plain terms
Salesforce is excellent at running a sales process. It's far less good at answering questions across one — especially when the question spans years of history or needs to sit next to data from elsewhere.
The problem
- Getting real answers out means exporting spreadsheets by hand, every time
- Salesforce limits how much data you can pull per day, so you can't just grab everything
- Records get edited in place — yesterday's export is already out of date
Technical detail
What gets pulled
- Standard objects — Account, Contact, Lead, Opportunity and related entities
- Custom objects specific to the business process
- Each object is one entry in the metadata config, not one new pipeline
Why it's a demanding source
- API limits — daily call caps make full reloads unviable at scale
- Wide objects — standard objects carry far more fields than are needed downstream
- In-place updates — records change rather than append, so the target must be merged, not stacked
SourceSalesforce Linked ServiceThe ADF connection to Salesforce — configured once, reused by every object.
In plain terms
The connection to Salesforce is built once. Every record type that gets added later reuses the exact same doorway.
Why that matters
- The login details are pulled from a secure vault at the moment they're needed — never written into the system
- When a password changes, it's updated in one place and everything keeps working
- Adding a new record type needs no new connection and no new setup
Technical detail
How the connection is defined
- A single ADF linked service using the Salesforce connector
- Username, password and security token are Key Vault references, not stored values
- One dataset, fully parameterised — object name passed in at runtime
Query generation
- SOQL is built dynamically from metadata: object name, selected columns, watermark filter
- Nothing object-specific is baked into the connection or the dataset
Control PlaneMetadata ConfigurationStored in Blob / ADLS Gen2. Every pipeline decision is read from here at runtime.
In plain terms
Instead of writing a separate program for every type of record, the system reads a simple settings file that tells it what to do.
What the settings file says
- Which record type to copy — customers, deals, contacts
- Where to put it in the destination database
- Everything, or just the changes?
- Which field goes where between the two systems
- When it last ran, so it knows where to pick up from
Why this is the whole point: when the business asks for a new type of data, nobody writes new software. Someone adds a few lines to a settings file. What would normally be a multi-week project becomes an afternoon.
Technical detail
What the config carries
- object_name — the Salesforce object to pull
- schema_name / table_name — target schema and table in SQL
- load_type —
fullorincremental - mapping_file — pointer to the column mapping JSON
- watermark column & last-run value — the incremental boundary
Shape
{
"object_name": "Opportunity",
"schema_name": "sfdc",
"table_name": "Opportunity",
"load_type": "incremental",
"watermark_column": "LastModifiedDate",
"mapping_file": "maps/opportunity.json"
}
The payoff: onboarding a new object is a config drop and a pull request — no pipeline development, no regression testing of existing feeds.
Control PlaneMaster PipelineThe single entry point — reads metadata, decides what runs, drives the worker.
In plain terms
Think of it as a shift supervisor. It reads the instruction sheet, works out what's due today, and hands each item to the worker one at a time.
What it does each run
- Reads the settings file
- Works out which record types are due
- Hands each one to the worker, with its instructions attached
- Keeps going if one item fails — the rest of the day's work still completes
Because the supervisor's logic lives in one place, a change to how everything runs is made once, not repeated dozens of times.
Technical detail
Execution flow
- Lookup — read the metadata config from Blob storage
- Filter — keep only objects marked active and due to run
- ForEach — iterate that list
- Execute Pipeline — invoke the child, passing every value as a parameter
Why a master/child split
- Orchestration logic lives in one place and changes once
- The child pipeline stays small, testable and independently deployable
- A failure on one object doesn't stop the rest of the run
- Parallelism and batch size are controlled centrally at the ForEach
Control PlaneForEach → Execute PipelineThe hand-off — one iteration per Salesforce object, each fully parameterised.
In plain terms
The list from the instruction sheet gets worked through one item at a time — customers, then contacts, then deals — each handed to the same worker with different instructions.
Why this is deliberate
- Several items can be handled at once to save time, but not so many that Salesforce starts refusing requests
- If one record type has a problem, it's recorded and the rest carry on
- Adding an item to the list needs no change here at all
Technical detail
How it works
- ForEach iterates the object list returned by the metadata lookup
- Each iteration calls the same child pipeline with a different parameter set
- Batch count controls parallelism without overloading the source API
- Failures are captured per iteration, so one bad object doesn't fail the run
Parameters passed down
object_nameschema_nametable_name
load_typemapping_filewatermark_column
Nothing is hard-coded in the child. The same pipeline handles Account on one iteration and Opportunity on the next.
IngestionSalesforce Child PipelineOne parameterised pipeline serving every Salesforce object in the config.
In plain terms
This is the part that does the actual copying. Crucially, there is only one of it — the same worker handles customers, deals and contacts, just with different instructions each time.
What it does, in order
- Checks when it last ran for this record type
- Asks Salesforce only for what's changed since then
- Saves an exact copy, then cleans it up
- Updates the database — changing existing records rather than duplicating them
- Records the new timestamp, but only if everything worked
Why that last point matters: if something fails halfway, the system doesn't pretend it succeeded. The next run picks up exactly where it left off, and nothing is silently lost.
Technical detail
Inside the pipeline
- Get watermark — read the last successful load boundary
- Build query — construct SOQL with the incremental filter applied
- Copy activity — Salesforce → ADLS Gen2 Bronze, using the dynamic mapping
- Load to SQL — staged, then merged into the target table
- Update watermark — committed only after a successful load
Design principles
- Idempotent — a re-run produces the same result, no duplicate rows
- Fail-safe watermark — updated last, so failure means the next run retries the same window
- Zero-code onboarding — new object, new config entry, done
IngestionIncremental Load StrategyThe core efficiency win — reload only what actually changed.
In plain terms
Imagine backing up your photos by re-copying every photo you've ever taken, every single night. That's what most systems do. This one copies only the pictures you took today.
How it knows
- Salesforce stamps every record with when it was last edited
- The system remembers when it last ran
- It asks only for records touched since that moment
- Changed records get updated in place, not duplicated
What this saves
- The daily run finishes in a fraction of the time
- Salesforce isn't hammered with requests it doesn't need to answer
- Cloud costs stay low, because you pay for what you move
A full copy is still available on demand — useful for a first load, or for rebuilding after a problem. It's a single setting, not a different system.
Technical detail
The problem it solves
- A full daily reload of every object is slow, expensive, and burns API quota on unchanged data.
How it works
- Each object declares a watermark column (typically
LastModifiedDate) - The pipeline reads the last successful watermark before the pull
- Source query filters to
WHERE LastModifiedDate > @lastWatermark - New and changed rows are merged into the target on the business key
- The watermark advances only after the load commits
Full load still available
load_type: "full"switches the same pipeline to truncate-and-reload- Used for small reference objects, backfills and recovery
IngestionDynamic Column MappingSource-to-target mapping lives in a config file, not inside the pipeline.
In plain terms
Salesforce and the database don't name things the same way. This is the translation list that connects the two.
What it handles
- Matching each Salesforce field to the right database column
- Bringing across only the fields the business needs, not all several hundred
- Making sure a date arrives as a date, and a number as a number
Why it lives outside the code: when someone adds a field in Salesforce, capturing it is a small edit to a list — not a software change that needs testing and a release.
Technical detail
What it handles
- Source column → target column translation
- Data type alignment between Salesforce and SQL
- Column selection — only what's needed downstream
Why externalise it
- A source schema change is a config edit, not a pipeline redeploy
- Mapping is reviewable in Git alongside the rest of the metadata
- The same copy activity serves every object because mapping is injected at runtime
Medallion · Layer 1Bronze — Raw LandingData lands exactly as the source gave it, in ADLS Gen2.
In plain terms
The first thing the system does is take a photograph of the data exactly as Salesforce handed it over — before anyone touches it.
Why keep an untouched copy
- If a mistake is found later in the clean-up rules, everything can be rebuilt from here — without asking Salesforce for the data again
- It's a record of exactly what the source said on any given day, which matters if numbers are ever questioned
- Each day is filed separately, so any single day can be found and re-run
This is the cheap insurance policy that stops a small logic error from becoming a week of lost work.
Technical detail
Rules of the layer
- No transformation. Raw fidelity is the entire point
- Partitioned by load date so any run can be located and replayed
- Append-only — history preserved even when the source overwrites it
Why it earns its place
- Downstream bugs are fixed by rebuilding Silver and Gold without re-hitting the Salesforce API
- Provides an audit trail of exactly what the source returned on any given day
Medallion · Layer 2Silver — Cleansed & ConformedRaw data becomes trustworthy, queryable data.
In plain terms
Real data is messy. The same customer entered three different ways, dates in two formats, empty fields where there shouldn't be. This stage sorts that out.
What gets fixed
- Duplicates removed — one record per customer, keeping the most recent version
- Formats standardised — dates look like dates, numbers behave like numbers
- Inconsistencies tidied — stray spaces, mixed capitalisation, blank-versus-missing
- Problem records flagged, not quietly deleted — so someone can look at them
That last point matters more than it sounds. Systems that silently drop bad records produce reports that look perfectly fine and are quietly wrong.
Technical detail
What happens here
- Type casting — strings become dates, decimals, booleans
- De-duplication — one row per business key, latest version wins
- Standardisation — consistent naming, null handling, trimmed values
- Validation — bad records flagged rather than silently dropped
This is the layer that makes data comparable and safe to build on.
Medallion · Layer 3Gold — Business ReadyCurated tables shaped around business questions rather than source structures.
In plain terms
Clean data still isn't useful data. This stage reshapes it around the questions people actually ask.
What happens here
- Data is arranged by how the business thinks — customers, deals, time periods
- Calculations everyone relies on are worked out once, in one place
- Structured so reports open quickly instead of grinding
The quiet benefit: because definitions live in one place, two reports can't disagree about what "active customer" means. Anyone who has sat in a meeting where two teams present different numbers for the same thing knows why that's worth having.
Technical detail
What lives here
- Conformed dimensions and fact tables
- Business logic and derived measures applied consistently, once
- Models shaped for reporting performance, not source convenience
The contract
- Business users and reports consume Gold — never Bronze or Silver
- Definitions live in one place, so two reports can't disagree on a metric
ServingAzure SQL DatabaseThe consumption target — the warehouse the business queries.
In plain terms
The destination. Reporting tools and analysts read from here, not from Salesforce.
How updates are handled safely
- New data is prepared off to the side first, then swapped in
- Existing records are updated, new ones added — nothing is wiped and rebuilt
- The database stays available the whole time — nobody sees it go down mid-refresh
- If an update fails, the existing data is left untouched rather than left half-finished
That last one is the difference between "the report is a day old" and "the report is wrong and nobody noticed."
Technical detail
Load pattern
- Data written to a staging table first
- A MERGE upserts on the business key — updates existing rows, inserts new ones
- No truncate-and-reload on incremental feeds, so the table stays available
- Wrapped in a transaction — a failed load leaves the target untouched
Access
- Connection strings resolved from Key Vault at runtime
ServingDownstream ConsumersWhat the pipeline ultimately exists to serve.
In plain terms
None of the engineering matters if nobody can use the result. This is the part the business actually touches.
What changes day to day
- Dashboards are current every morning, with nobody preparing them
- Questions get answered in seconds instead of "I'll export it and get back to you"
- Years of history are available — Salesforce reports struggle with that
- No Salesforce licence needed just to look at the numbers
The measure of success here is boring: the data is simply correct and current every morning, and nobody has to think about it.
Technical detail
Who reads this data
- Reporting and BI dashboards
- Business applications needing a consolidated view
- Analysts running ad-hoc SQL
The business outcome
- CRM data is queryable in SQL instead of via Salesforce reports and manual exports
- No API limits or licence seats between an analyst and the data
- Refreshed daily without anyone touching a pipeline
PlatformAzure Key VaultEvery secret lives here and is fetched at runtime.
In plain terms
The system needs logins for Salesforce and the database. Those are never written into the system itself — they're kept in a locked vault and requested at the moment they're needed.
Why this is non-negotiable
- No password ever appears in the project files, so a copy of the code is worthless to an attacker
- Changing a password is done in the vault — nothing needs rebuilding
- Access is granted to the system itself, not to a person, so there's no shared password floating around
- Every access is logged
Hard-coded passwords are one of the most common causes of real-world breaches. This design removes the possibility rather than relying on people to be careful.
Technical detail
What's stored
- Salesforce credentials and security tokens
- SQL Database connection strings
- Storage account keys for the ADLS Gen2 landing zone
How it's used
- Linked services reference Key Vault secrets rather than storing values
- Access via managed identity — no credentials in code or config
- Secret rotation happens in the vault with no pipeline change
- Nothing sensitive lands in Git or an ARM template
PlatformAzure DevOps CI/CDPipelines are versioned, reviewed and promoted like application code.
In plain terms
Nobody edits the live system directly. Changes are made in a practice copy first, tested, reviewed, and only then promoted.
The route every change takes
- Built and tried in a development copy
- Checked in a test copy using realistic data
- Reviewed by someone else before it can go further
- Released to live only after passing all of that
What this buys you
- A full history of every change and who made it
- If something does go wrong, it can be reversed in one step
- The live system can't be broken by an accidental edit
Technical detail
The flow
- ADF is Git-integrated — every change is a branch and a pull request
- Publishing generates ARM templates from the collaboration branch
- Release pipeline deploys through Dev → Test → Prod
- Environment-specific values injected via parameter overrides per stage
What it prevents
- No manual changes in the production Data Factory
- Full change history and a one-step rollback path
- Peer review before anything reaches production
PlatformMonitoring, Logging & AuditKnowing a load succeeded matters as much as running it.
In plain terms
An automated system that fails silently is worse than no system at all — people keep trusting numbers that stopped updating weeks ago.
What gets recorded every run
- When it started, when it finished, how long it took
- How many records came across
- Whether it succeeded — and if not, exactly what went wrong
When something breaks
- Temporary glitches are retried automatically — most resolve themselves
- Real failures raise an alert, so it's caught before the business notices
- One failing record type doesn't stop the others from updating
Technical detail
Captured per run
- Pipeline run ID, object, start and end time
- Rows read and rows written
- Load status and full error detail on failure
- Watermark value before and after
Operational handling
- Retry policy on transient source and network failures
- Failure alerts so problems surface before the business notices stale data
- Per-object isolation — one failing feed doesn't block the run
Design RationaleWhy Metadata-Driven?The engineering decision that defines this project.
In plain terms
Most data projects are built one piece at a time. Need customer data? Build something. Need order data too? Build another. Each one is separate, and each one has to be maintained forever.
What that costs you
- Every new request is a fresh project with a fresh timeline and a fresh bill
- A change to how things work has to be repeated across every piece
- The more you build, the slower and more fragile it gets
What was built instead
- One system, driven by a settings file
- New data request → update the settings. No new software
- An improvement made once applies everywhere immediately
- The system gets cheaper per addition over time, not more expensive
The honest trade-off
- It takes longer to build the first one this way
- The investment pays back within the first handful of additions — and keeps paying after that
In one line: this was built as a reusable system rather than a one-off job, so the second, fifth and twentieth request cost a fraction of the first.
Technical detail
The conventional approach
- One pipeline per object. Dozens of objects means dozens of near-identical pipelines
- Every new object is a development ticket, a test cycle and a release
- Changing shared logic — retry behaviour, logging, load pattern — means editing every pipeline by hand
This approach
- One master pipeline, one child pipeline. Everything else is configuration
- New object → add a metadata entry. No development, no new pipeline
- Shared logic changes once and applies to every object
- A source schema change is a mapping file edit, not a redeploy
Trade-offs, honestly
- Higher upfront design effort — the framework has to be right before it pays off
- Debugging is more abstract; strong logging is not optional
- An object with genuinely unusual behaviour may still need special handling
Verdict: the upfront cost is repaid within the first handful of objects — and keeps paying with every one added after.
The capability behind it
Start a conversation
Tell us what you are trying to build
Send the problem rather than a spec. We will tell you what it takes, who would work on it, and whether we are the right people for it.
