Salesforce Data Engineer Interview Questions and Answers

A Salesforce Data Engineer usually owns the data pipelines and models that make CRM, analytics, Data Cloud, and AI features reliable.

You are not only moving rows between systems. You are responsible for making sure customer, sales, marketing, and engagement data is accurate, timely, governed, and usable.

Typical ownership includes:

• Building batch and near-real-time pipelines
• Ingesting data from Salesforce CRM, external systems, APIs, files, and event streams
• Designing bronze / silver / gold style data layers
• Modeling CRM entities like Account, Contact, Lead, Opportunity, Case, and Activity
• Handling data quality checks, SLAs, backfills, retries, and schema changes
• Supporting identity resolution and unified customer/profile views in Data Cloud
• Partnering with analytics, product, security, and platform teams
• Protecting PII with retention, masking, audit, and access controls

A strong candidate does not only say:

I build ETL pipelines.

A better answer connects pipelines to business reliability:

I build and operate trusted data pipelines that ingest CRM and engagement data, transform it into analytics-ready models, validate quality, and make it available for reporting, Data Cloud, AI, and downstream business workflows.

A strong answer is:

I see the Salesforce Data Engineer role as owning reliable CRM-centric data pipelines from ingestion to serving. That includes data modeling, quality checks, backfills, SLA monitoring, schema evolution, governance, and making data trustworthy for analytics, Data Cloud, and AI use cases.

The exact loop depends on the team, but a Salesforce Data Engineer interview commonly tests SQL, data modeling, coding, system design, production ownership, and collaboration.

A typical loop may include:

1. Recruiter screen — background, location, compensation, role fit
2. Hiring manager round — projects, ownership, communication, business impact
3. SQL round — joins, window functions, CTEs, aggregations, data quality queries
4. Coding round — Python, Java, or Scala-style data problems
5. Data modeling / system design — pipeline design, CDC, warehouse modeling, SLAs
6. Behavioral round — incidents, ambiguity, conflict, leadership, Trust mindset

For senior roles, expect deeper discussion on:

• Pipeline reliability
• Cost and performance tuning
• Data contracts
• Multi-tenant data handling
• PII/governance
• Backfills and incident recovery
• Cross-team influence

For Salesforce-specific teams, prepare examples around:

• CRM data semantics
• Account / Contact / Opportunity modeling
• Data Cloud ingestion and identity resolution
• Marketing, Sales, Service, Tableau, MuleSoft, or Slack-adjacent data flows

A strong answer is:

I would expect SQL, data modeling, coding, system design, and behavioral rounds. For senior roles, I would prepare production stories around pipeline failures, backfills, SLA misses, data quality incidents, and trade-offs in architecture.

For most Salesforce Data Engineer roles, Apex is not the main focus. The core interview usually focuses on data engineering skills such as SQL, Python, Spark, cloud data platforms, orchestration, and distributed pipeline design.

However, Salesforce-specific knowledge helps because the data often comes from Salesforce products.

Useful Salesforce knowledge includes:

Apex is useful if the role is closer to Salesforce platform development or Sales/Service Cloud customization. But for data-platform roles, interviewers usually care more about whether you can build reliable data pipelines and understand CRM data correctly.

A strong answer is:

Apex is useful for Salesforce platform roles, but for a Data Engineer interview I would focus more on SQL, Python, Spark, cloud warehouses, orchestration, data modeling, CDC, and Data Cloud concepts. I would still learn basic CRM objects and SOQL so I understand the source data.

A good 4-week plan should prepare you for SQL, data modeling, pipeline design, coding, and production stories.

For SQL, practice CRM-style problems:

• Top opportunities by region
• Month-over-month revenue
• Closed opportunities missing stage history
• Duplicate contacts by email
• Latest status per account
• SCD Type 2 overlap detection

For system design, prepare:

• CRM CDC pipeline into a warehouse
• Customer identity unification pipeline
• Data quality framework
• Reliable backfill design
• Dashboard-serving mart with SLA

For behavioral, prepare stories on:

• Production incident
• Data quality issue
• Disagreement with stakeholder
• Performance improvement
• Ambiguous requirement

A strong answer is:

I would spend the first week on SQL, the second on CRM data modeling, the third on pipeline and system design, and the fourth on coding plus behavioral stories. I would prepare real examples with volume, latency, error rate, cost, and business impact.

This is a classic window-function question. The interviewer wants to see whether you understand partitioning, ranking, and tie handling.

WITH ranked AS (
  SELECT
    employee_id,
    department,
    salary,
    DENSE_RANK() OVER (
      PARTITION BY department
      ORDER BY salary DESC
    ) AS salary_rank
  FROM employees
)
SELECT
  employee_id,
  department,
  salary
FROM ranked
WHERE salary_rank <= 3
ORDER BY department, salary DESC;

Use DENSE_RANK() when ties should receive the same rank. For example, if two employees share the highest salary, both get rank 1.

Use ROW_NUMBER() if the interviewer wants exactly three rows per department, even when salaries tie.

Clarify these points before writing the final query:

• Should ties be included?
• Should null salaries be ignored?
• Should departments with fewer than three employees return fewer rows?
• Is salary stored as annual, monthly, or effective-dated compensation?

A strong answer is:

I would use a window function partitioned by department and ordered by salary descending. I would choose DENSE_RANK() if ties should be included, or ROW_NUMBER() if the requirement is exactly three rows per department.

Use an INNER JOIN when matching records must exist on both sides.

Use a LEFT JOIN when you want to preserve all rows from the left table, even if the right table has no match.

CRM example:

Account LEFT JOIN Opportunity

This is useful when you want to show all accounts and count open opportunities. Accounts with zero opportunities should still appear.

A common mistake is putting filters on the right table in the WHERE clause after a LEFT JOIN, which accidentally turns it into an inner join.

Problem pattern:

SELECT *
FROM account a
LEFT JOIN opportunity o
  ON a.account_id = o.account_id
WHERE o.stage_name = 'Closed Won';

Better pattern:

SELECT *
FROM account a
LEFT JOIN opportunity o
  ON a.account_id = o.account_id
 AND o.stage_name = 'Closed Won';

The most important interview habit: state the grain before joining.

Example:

The output grain is one row per account.

A strong answer is:

I use INNER JOIN when both tables must match, and LEFT JOIN when I need to preserve the left-side grain and detect missing relationships. In analytics SQL, I also watch filter placement because a WHERE filter on the right table can accidentally remove unmatched rows.

First define the grain:

One row per month and product category.

Then aggregate revenue and compare it to the previous month using LAG().

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month_start,
    product_category,
    SUM(amount) AS revenue
  FROM orders
  GROUP BY 1, 2
),
growth AS (
  SELECT
    month_start,
    product_category,
    revenue,
    LAG(revenue) OVER (
      PARTITION BY product_category
      ORDER BY month_start
    ) AS prev_revenue
  FROM monthly
)
SELECT
  month_start,
  product_category,
  revenue,
  prev_revenue,
  CASE
    WHEN prev_revenue IS NULL OR prev_revenue = 0 THEN NULL
    ELSE (revenue - prev_revenue) / prev_revenue
  END AS mom_growth_rate
FROM growth
ORDER BY product_category, month_start;

Explain edge cases:

• First month has no previous month
• Previous revenue can be zero
• Current month may be partial
• Refunds or credits may reduce revenue
• Multi-currency data needs conversion before aggregation
• Timezone affects month boundaries

Salesforce-style example:

For Opportunity revenue, I would clarify whether to use created date, close date, booked date, or stage transition date.

A strong answer is:

I would aggregate revenue at month and category grain, use LAG() to get previous-month revenue, then calculate the percentage change. I would also clarify partial months, refunds, currency conversion, and which business date defines the month.

In an SCD Type 2 table, each business key should have non-overlapping validity windows.

Example grain:

One row per account version.

Typical columns:

account_id, account_name, owner_id, valid_from, valid_to, is_current

Use LEAD() to compare each row with the next row for the same account.

WITH ordered AS (
  SELECT
    account_id,
    valid_from,
    valid_to,
    LEAD(valid_from) OVER (
      PARTITION BY account_id
      ORDER BY valid_from
    ) AS next_valid_from
  FROM account_history
)
SELECT
  account_id,
  valid_from,
  valid_to,
  next_valid_from
FROM ordered
WHERE next_valid_from IS NOT NULL
  AND (
    next_valid_from < valid_to
    OR next_valid_from > valid_to
  );

This assumes valid_to is exclusive. If your model uses inclusive end dates, adjust the comparison.

For example:

Exclusive end: next_valid_from should equal valid_to
Inclusive end: next_valid_from should equal valid_to + 1 day

Also validate:

• Only one is_current = true row per account
• No null valid_from
• Current row has open-ended valid_to
• Deleted records are represented consistently
• Timezone is consistent

A strong answer is:

I would order SCD2 rows by valid_from per account and use LEAD() to compare each row with the next window. Then I would flag overlaps, gaps, multiple current rows, and inconsistent delete or open-ended records.

First separate current pipeline from pipeline-as-of. They answer different business questions.

Recommended model:

1. Opportunity dimension

   • opportunity_id
   • account/customer key
   • owner
   • region
   • amount
   • created date
   • close date
   • current status

2. Opportunity stage history fact

   • grain: one row per stage transition
   • opportunity_id
   • stage_name
   • stage_changed_at
   • optional previous stage
   • source event ID

3. Daily snapshot / as-of mart

   • grain: one row per opportunity per as-of date
   • latest stage as of that date
   • amount as of that date
   • owner/region as of that date

For as-of reporting:

• Build a date spine
• Join opportunities active on each date
• Pick the latest stage at or before the as-of timestamp
• Use SCD2 or snapshots for changing attributes like owner, region, and amount

Avoid double counting:

• Do not join raw stage history directly to opportunity totals without selecting one stage per as-of date
• Do not key customer facts only by mutable email
• Use stable surrogate keys when identity resolution can merge records

A strong answer is:

I would model stage changes as a transition fact and build a separate daily as-of mart. For each date, I would select the latest stage and attributes valid at that time, while keeping current pipeline as a separate model to avoid mixing current and historical semantics.

This is a data quality validation question. The goal is to detect Opportunities whose current state says closed, but stage history does not show a matching closed transition.

Example query:

SELECT
  DATE(o.close_date) AS close_day,
  COUNT(*) AS closed_missing_history
FROM opportunity o
LEFT JOIN opportunity_stage_history h
  ON h.opportunity_id = o.opportunity_id
 AND h.stage_name IN ('Closed Won', 'Closed Lost')
 AND h.stage_changed_at <= o.close_date
WHERE o.is_closed = TRUE
  AND h.opportunity_id IS NULL
GROUP BY 1
ORDER BY 1;

What this checks:

• Opportunity is marked closed
• No matching Closed Won / Closed Lost stage history exists
• Stage transition should have happened on or before close date

In an interview, explain possible causes:

• Missing CDC event
• Late-arriving stage history
• Backfill gap
• Source system correction
• Manual data update
• Different stage naming convention
• Timezone mismatch around close date

Production handling:

• Alert if count crosses threshold
• Quarantine bad records if they break downstream metrics
• Track the issue by source system and ingestion date
• Reprocess from raw/bronze data if possible
• Backfill corrected history safely
• Add a data quality dashboard

A strong answer is:

I would LEFT JOIN closed Opportunities to closed-stage history and flag rows where the history is missing. Then I would check for late events, source corrections, timezone issues, and backfill gaps before quarantining or fixing the affected records.

In transactional databases, indexes help find rows quickly. In warehouses and lakehouses, similar goals are achieved through physical design features such as partitioning, clustering, sorting, statistics, pruning, and file layout.

The goal is to reduce scanned data and improve query performance.

In CRM analytics, common choices are:

Example:

A dashboard filtering Opportunity snapshots by date should not scan all historical partitions.

Physical design matters when:

• Tables are very large
• Dashboards need low latency
• Joins are frequent
• Filters are selective
• Data is skewed by large customers or regions

Avoid over-optimizing too early. First check query plans, scan volume, join strategy, and dashboard SLA.

A strong answer is:

In a warehouse, the purpose is to reduce scanned data and improve query speed using indexes, partitioning, clustering, sorting, statistics, or materialized aggregates. I would design physical layout based on access patterns and validate with query plans.

For a unified customer dimension, define the grain carefully.

Recommended grain:

One row per resolved person or customer profile.

Do not use raw email as the only customer key. Emails can change, be shared, be missing, or appear differently across systems.

Recommended design:

1. Unified customer dimension

   • customer_key
   • resolved profile attributes
   • preferred email
   • consent status
   • created/updated timestamps

2. Identity link table

   • customer_key
   • source system
   • source record ID
   • email
   • phone
   • external ID
   • match confidence
   • active flag

3. Source-specific dimensions

   • Sales Cloud Contact
   • Lead
   • Marketing subscriber
   • Web/app user

4. Facts

   • Join through stable keys or identity bridge
   • Avoid rewriting historical facts every time an identity merge changes

Important Salesforce/Data Cloud concepts:

• Source records can map to unified profiles
• Match rules decide which records belong together
• Reconciliation rules decide which field value wins
• Merge churn should be monitored

Example issue:

If two contacts merge into one unified customer, historical Opportunity facts should remain explainable and not double-count revenue.

A strong answer is:

I would use one row per resolved customer profile with a stable surrogate customer_key, and maintain an identity link table from source IDs, emails, phones, and external IDs to that key. Facts should not depend only on mutable email addresses.

Choose the ingestion pattern based on the freshness requirement, cost, source limits, and downstream use case.

Batch ingestion is easier to operate and good for reporting where a few hours of delay is acceptable.

Near-real-time ingestion is better when business users need fresh changes quickly, such as:

• Opportunity stage changes
• Case escalation alerts
• Marketing audience updates
• Customer profile refresh in Data Cloud
• Fraud/risk signals
• Sales pipeline coverage monitoring

A common production design is hybrid:

CDC stream for hot changes + nightly batch reconciliation for correctness

The nightly batch helps catch missed events, source corrections, late-arriving records, and drift between source and target.

A strong answer is:

I choose batch when cost and simplicity matter more than freshness, and CDC or near-real-time ingestion when downstream users need updates within minutes. For important CRM data, I prefer a hybrid design: CDC for freshness and nightly reconciliation for correctness.

An idempotent CDC pipeline produces the same final result even if the same event is processed more than once.

This is important because CDC systems can have:

• Duplicate events
• Retries
• Out-of-order delivery
• Consumer restarts
• Replays from a checkpoint
• Late-arriving updates

Key design elements:

1. Stable event key

   • Use event ID, replay ID, transaction ID, or a composite key like source object ID + sequence + timestamp.

2. Append-only bronze layer

   • Store raw events before transformation so replay and debugging are possible.

3. Deduplication logic

   • Remove duplicates before writing conformed silver tables.

4. Monotonic merge rules

   • Apply newer changes over older changes using commit timestamp or version number.

5. Checkpointing

   • Store the last processed offset/replay token per object stream.

6. Dead-letter queue

   • Send malformed or poison events to a DLQ instead of blocking the full pipeline.

7. Metrics

   • Track lag, duplicate rate, late-event rate, failed records, and merge conflicts.

For Salesforce CRM data, do not rely only on email or name for identity. Use stable Salesforce record IDs, source system metadata, and defined identity rules.

A strong answer is:

I would make the CDC pipeline idempotent by storing raw events, deduplicating by stable event keys, applying version-aware merge logic, checkpointing per stream, and sending bad events to a DLQ. Reprocessing the same event should not change the final result incorrectly.

First protect the pipeline and downstream users. Then fix the schema properly.

Operational response:

1. Confirm the change

   • Check source metadata, pipeline logs, schema validation error, and affected object.

2. Protect ingestion

   • If possible, land the raw payload in bronze as JSON or semi-structured data so ingestion does not fully stop.

3. Assess downstream impact

   • Find which silver/gold tables, dashboards, Data Cloud mappings, or ML features depend on the Opportunity schema.

4. Version the schema

   • Add the new field as optional first, with a default null value where needed.

5. Update mappings and contracts

   • Update Data Stream mapping, DLO/DMO mapping, dbt model, schema registry, or data contract.

6. Backfill if needed

   • Backfill historical values if the source exposes field history or historical extracts.

7. Test before promotion

   • Run compatibility tests, row-count checks, null checks, and downstream dashboard validation.

8. Communicate

   • Notify consumers if the new field changes business logic or reporting definitions.

The key interview point is that schema evolution should not silently break gold tables or dashboards.

A strong answer is:

I would isolate the schema change, keep raw ingestion running if possible, add the new field in a backward-compatible way, update mappings and contracts, validate downstream models, and backfill historical values only if the business needs them.

Data quality checks should run at multiple stages, not only at the final dashboard layer.

Common checks:

Example Salesforce checks:

• Opportunities marked closed must have Closed Won or Closed Lost history
• Contacts must have valid Account relationship when required
• Duplicate Leads by email should be tracked
• Opportunity amount should not be negative unless credits are allowed
• Account hierarchy should not contain cycles

Failed checks should not be silently ignored. Depending on severity:

• Warn and continue
• Quarantine bad records
• Block promotion to gold
• Alert the data owner
• Open an incident if SLA or business reporting is affected

A strong answer is:

I would add checks for completeness, uniqueness, referential integrity, freshness, valid values, business rules, and anomalies. Critical failures should quarantine or block bad data, while alerts and lineage help owners understand which reports or consumers are affected.

Missing and late-arriving data should be handled explicitly because CRM data often arrives out of order or gets corrected after the fact.

For late-arriving events:

• Use event time, not only ingestion time
• Define a watermark and allowed lateness window
• Reprocess affected partitions
• Update as-of marts safely
• Mark metrics as provisional if they may change

For missing batches:

• Compare source manifests with landed files
• Validate row counts and checksums
• Retry extraction with backoff
• Alert when SLA is at risk
• Backfill from the last successful checkpoint

For downstream reporting:

• Use is_final or data_status flags when metrics can revise
• Publish incident notes if dashboards are stale
• Show freshness timestamps in important reports

Salesforce-style example:

An Opportunity may be marked closed before the matching stage-history event arrives. The pipeline should detect this semantic gap, allow late correction, and avoid publishing misleading close-rate metrics.

A strong answer is:

I handle late data with watermarks, allowed lateness, partition reprocessing, and provisional metrics. For missing data, I compare manifests, retry safely, backfill from checkpoints, and communicate freshness or SLA impact to downstream users.

In Salesforce Data Cloud / Data 360, DLOs and DMOs are part of the ingestion and harmonization flow.

Simple flow:

Source data → Data Stream → DLO → DMO → Identity Resolution → Unified Profile → Activation

Example:

• A marketing subscriber file lands as a DLO
• Fields from that DLO are mapped to standard objects such as Individual or Contact Point DMOs
• Identity resolution links records into unified profiles
• The unified data can be used for segmentation, analytics, activation, or AI use cases

Key interview point:

DLO is closer to source-shaped data.
DMO is closer to business/model-shaped data.

Zero-copy federation can also allow Data Cloud/Data 360 to access external data without physically copying all of it into Salesforce, depending on the connector and use case.

A strong answer is:

A DLO stores or references source-aligned data after ingestion, while a DMO is the harmonized model object used in the Customer 360 model. The usual flow is source to DLO, mapping to DMO, identity resolution, unified profile, and activation.

Identity resolution links multiple source records that represent the same real-world person, account, or customer.

High-level flow:

1. Ingest data from sources
2. Map source data to DMOs
3. Define match rules
4. Define reconciliation rules
5. Run identity resolution
6. Create unified profiles and link records
7. Monitor match quality and merge behavior

Important concepts:

Example match signals:

• Exact email
• Phone number
• CRM Contact ID
• External customer ID
• Name + address
• Loyalty ID

Be careful with over-merging. A false positive can combine two different people into one profile, which is worse than leaving two duplicate profiles in many compliance and personalization scenarios.

Also explain that source records are not erased. Good identity systems preserve lineage so teams can trace a unified profile back to original CRM, marketing, or service records.

A strong answer is:

Identity resolution maps source records into a common model, applies match rules to link records, uses reconciliation rules to choose winning attribute values, and creates unified profiles while preserving source lineage. I would monitor false positives, merge churn, and compliance risk carefully.

This is an incident-management question. The interviewer wants to see calm ownership, not panic debugging.

Step-by-step response:

1. Acknowledge the alert

   • Check orchestrator status, failed task, logs, error message, and last successful run.

2. Assess impact

   • Which tables, dashboards, ML jobs, or business users are affected?
   • Is this a freshness issue or bad-data issue?

3. Stabilize

   • Retry only if the failure is safe and likely transient.
   • Pause downstream jobs if bad data may be published.

4. Find root cause

   • Schema change
   • Source API issue
   • Credential expiry
   • File missing
   • Data skew
   • Warehouse capacity issue
   • Code deployment bug

5. Recover

   • Restart from checkpoint
   • Backfill missing partitions
   • Re-run reconciliation checks
   • Validate row counts and business metrics

6. Communicate

   • Notify stakeholders about impact, ETA, and workaround.
   • Update dashboard freshness notes if needed.

7. Prevent recurrence

   • Add tests, alerts, schema contracts, retry logic, or runbook updates.

A strong answer is:

I would first assess whether users are seeing stale data or wrong data. Then I would stabilize the pipeline, protect downstream consumers, recover from the last good checkpoint, validate reconciliation checks, communicate impact, and add a guardrail to prevent the same failure.

Start by clarifying requirements:

• Views per second
• Exact vs approximate counts
• Unique views vs total views
• Per-video, per-user, per-region, or per-time-window metrics
• Latency target
• Fraud/bot filtering needs

High-level design:

Client/App → Event collector → Kafka/Kinesis/PubSub → Stream processor → Hot store + warehouse → API/dashboard

Components:

1. Event collector

   • Receives view events with event_id, video_id, user_id, and timestamp.

2. Message queue

   • Buffers traffic spikes and decouples producers from consumers.

3. Stream processor

   • Aggregates counts by video_id and time window.

4. Hot store

   • Redis or similar store for current counters.

5. Analytics store

   • ClickHouse, BigQuery, Druid, Snowflake, or lakehouse tables for historical reporting.

6. Serving API

   • Reads hot counters for live views and historical store for older windows.

Reliability concerns:

• Deduplicate by event_id
• Handle late events with watermarks
• Mitigate hot keys for viral videos
• Use idempotent writes
• Apply bot/fraud filtering
• Reconcile stream counts with raw events

For unique viewers, use exact distinct counts if scale allows, or approximate structures like HyperLogLog when approximate accuracy is acceptable.

A strong answer is:

I would ingest view events into a durable stream, aggregate them by video and time window, store live counters in a hot store, persist raw and aggregated data for analytics, and handle deduplication, late events, hot keys, and bot filtering.

A 5-minute SLA means the design must focus on low-latency ingestion, idempotent processing, and strong observability.

High-level design:

Salesforce CRM CDC → Event bus → Bronze raw lake → Silver conformed models → Synapse gold marts + Data Cloud ingestion

Suggested flow:

1. CDC capture

   • Subscribe to Salesforce CDC events for objects such as Account, Contact, Opportunity, and Case.

2. Streaming buffer

   • Use Kafka, Azure Event Hubs, or another durable bus to absorb spikes and support replay.

3. Bronze layer

   • Store raw CDC events in ADLS with metadata: source object, event ID, replay ID, schema version, and ingestion time.

4. Silver layer

   • Deduplicate, validate, apply ordering rules, and upsert into conformed CRM entities.

5. Gold layer

   • Build BI-ready tables in Synapse for dashboards and SLA-based reporting.

6. Data Cloud path

   • Send or expose harmonized data to Data Cloud/Data 360 through Data Streams, connectors, or federation depending on the source and architecture.

7. Observability

   • Monitor end-to-end lag, event backlog, failed events, DLQ count, freshness, and row-count reconciliation.

Key design points:

• Use idempotent upserts
• Keep raw events for replay
• Separate BI serving from profile activation
• Use DLQ for poison events
• Add schema evolution handling
• Reconcile CDC with scheduled batch extracts

A strong answer is:

I would capture Salesforce CDC events into a durable stream, land raw events in bronze, dedupe and merge into silver CRM models, publish gold marts to Synapse, and feed Data Cloud through the right ingestion or federation path. I would monitor lag, DLQ, freshness, and reconciliation against source.

For petabyte-scale migration, use a phased approach instead of a single cutover.

Migration plan:

1. Discovery

   • Inventory datasets, owners, size, dependencies, SLAs, PII, and retention rules.

2. Target design

   • Choose storage layout, partitioning, encryption, access model, and compute engine.

3. Initial bulk copy

   • Run parallel transfer to object storage.
   • Validate using checksums, row counts, and sample comparisons.

4. Incremental sync

   • Use CDC, logs, timestamp-based extracts, or replication to capture changes after the initial copy.

5. Dual-run period

   • Run old and new systems in parallel.
   • Compare business metrics and critical queries.

6. Cutover

   • Use feature flags, DNS/connection string changes, or application configuration.

7. Rollback plan

   • Keep old system readable for a defined period.
   • Define clear rollback triggers.

8. Post-cutover validation

   • Compare counts, hashes, aggregates, and business invariants.

Special considerations:

• Historical field history
• Attachments and files
• Data retention policies
• PII masking/tokenization
• Network bandwidth
• Transfer cost
• Partition skew
• Query compatibility

A strong answer is:

I would do an initial bulk load, validate it, keep systems in sync with CDC or incremental replication, run both systems in parallel, reconcile business metrics, cut over with a rollback plan, and continue validation after migration.

A distributed scheduler coordinates workflows, dependencies, retries, and execution across many workers.

Core components:

High-level flow:

DAG definition → Scheduler → Queue → Worker → Metadata DB update → Logs/metrics

Design concerns:

• Task dependencies
• Retries and backoff
• Idempotent task execution
• Backfills
• Catchup behavior
• SLA monitoring
• Worker autoscaling
• Priority queues
• Dead task detection
• Secure secrets handling

For data platforms, tasks should be idempotent because retries are normal. For example, a load task should safely overwrite or merge a partition without creating duplicates.

A strong answer is:

I would design a scheduler with a metadata database, scheduler, executor, distributed workers, logs, retries, backfill support, and alerting. The most important reliability rule is that tasks should be idempotent because retries and reruns are expected.

A 10x ingestion spike should be handled with buffering, autoscaling, prioritization, and backpressure.

Immediate response:

1. Check queue lag

   • Is the system dropping data or only falling behind?

2. Protect critical pipelines

   • Prioritize revenue, CRM CDC, security, or customer-impacting data over batch reprocessing.

3. Scale consumers

   • Add workers if partitions and downstream systems can handle it.

4. Apply backpressure

   • Throttle non-critical producers or consumers.

5. Use durable buffering

   • Let Kafka/Event Hubs/Kinesis absorb the burst within retention limits.

6. Protect downstream systems

   • Avoid overwhelming the warehouse, database, Data Cloud ingestion, or APIs.

7. Monitor cost

   • Autoscaling should have limits and alerts.

After the spike:

• Replay DLQ
• Validate missed records
• Reconcile counts
• Review partition strategy
• Check hot keys
• Update capacity planning
• Add traffic forecasts if spike was business-driven

A strong answer is:

I would first confirm whether data is being lost or only delayed. Then I would use queue buffering, scale consumers safely, apply backpressure to non-critical flows, protect downstream systems, prioritize critical SLAs, and reconcile data after the spike.

A multi-tenant lakehouse must isolate data, enforce access controls, and provide auditability.

Clarify first:

• Number of business units
• Regulatory requirements
• Whether tenants can share any data
• Whether isolation must be logical or physical
• Who can access PII
• Retention and deletion requirements

Design options:

High-level design:

Data Cloud / source systems → ingestion layer → tenant-aware bronze/silver/gold → governed access layer → BI/ML/activation

Important controls:

• Add tenant_id to shared tables where logical isolation is used
• Prevent broad service principals
• Encrypt data at rest and in transit
• Mask or tokenize PII columns
• Log who accessed what data and when
• Separate dev/test/prod data access
• Apply retention and deletion policies per tenant

A strong answer is:

I would design tenant isolation using separate schemas or storage for regulated tenants, enforce IAM and row/column-level controls, protect PII with masking or tokenization, and maintain immutable audit logs, lineage, and freshness metrics per tenant.

A data quality framework should make checks reusable, visible, and enforceable across pipelines.

Core components:

1. Rule definition

   • Rules written in SQL, YAML, dbt tests, Great Expectations, or a custom framework.

2. Rule types

   • Null checks
   • Uniqueness
   • Referential integrity
   • Freshness
   • Accepted values
   • Reconciliation
   • Anomaly detection
   • Business rules

3. Execution layer

   • Run checks after bronze, silver, and gold stages.

4. Severity levels

   • Warn
   • Quarantine
   • Block promotion
   • Incident

5. Metadata and ownership

   • Every rule has owner, table, column, SLA, severity, and linked dashboard.

6. Alerting

   • Notify the right owner through Slack, email, PagerDuty, or ticketing.

7. Observability

   • Track pass rate, failure trend, freshness, and data quality score.

8. Lineage integration

   • Show which dashboards, marts, or consumers are affected.

Example pilot:

Start with Account and Opportunity because they are high-value CRM entities.

Example checks:

• Opportunity must have valid Account
• Closed Opportunity must have terminal stage
• Account ID must be unique
• Required owner fields must not be null
• Daily Opportunity count should not drop suddenly

A strong answer is:

I would build a declarative data quality framework with reusable rules, severity levels, alerts, ownership, lineage, and trend reporting. Critical checks should block or quarantine bad data, while lower-severity checks warn owners before issues become incidents.

This tests whether you can process large data without loading everything into memory.

Approach:

1. Read the file line by line
2. Parse timestamp and error message
3. Keep only records from the last one hour
4. Count messages using a hash map
5. Return the top five using Counter.most_common() or a min-heap

Simple Python approach:

from collections import Counter
from datetime import timedelta

def top_errors(lines, now, window=timedelta(hours=1)):
    cutoff = now - window
    counts = Counter()

    for line in lines:
        ts, msg = parse_log_line(line)

        if ts >= cutoff:
            counts[msg] += 1

    return counts.most_common(5)

For very large or streaming logs, improve the design:

• Use a min-heap of size 5 for top-K
• Use event time instead of processing time
• Handle out-of-order logs with a watermark
• Normalize error messages so IDs do not create fake unique errors
• Store rolling counts in Redis or a stream processor for real-time dashboards

Complexity:

A Salesforce-style example:

Find the top failing ingestion errors in the last hour for Account and Opportunity CDC events.

A strong answer is:

I would stream the log file line by line, filter records to the last hour, count error messages with a hash map, and return the top five. For production-scale logs, I would handle event-time ordering, message normalization, and rolling-window aggregation.

This is a classic stack problem. It checks whether you understand last-in, first-out matching.

Use a stack:

def is_balanced(s: str) -> bool:
    stack = []
    pairs = {
        ')': '(',
        ']': '[',
        '}': '{'
    }

    for ch in s:
        if ch in '([{':
            stack.append(ch)
        elif ch in ')]}':
            if not stack or stack.pop() != pairs[ch]:
                return False

    return not stack

How it works:

• Push opening brackets to the stack
• When a closing bracket appears, check if it matches the latest opening bracket
• If anything mismatches, return False
• At the end, stack must be empty

Examples:

Complexity:

Possible follow-ups:

• Ignore non-bracket characters
• Return the position of the first invalid bracket
• Validate brackets while streaming a file
• Track line and column number for parser errors

A strong answer is:

I would use a stack. Opening brackets are pushed, closing brackets must match the most recent opening bracket, and the stack must be empty at the end. This gives O(n) time and O(n) worst-case space.

First clarify whether the interviewer wants:

• One pair or all pairs
• Indexes or values
• Duplicate pairs allowed or not
• Same element reuse allowed or not

For all unique value pairs, use a hash set.

def two_sum_pairs(nums, target):
    seen = set()
    pairs = set()

    for x in nums:
        need = target - x

        if need in seen:
            pairs.add(tuple(sorted((need, x))))

        seen.add(x)

    return list(pairs)

Example:

nums = [1, 2, 3, 4, 5, 3]
target = 6

Possible output:

[(1, 5), (2, 4), (3, 3)]

Complexity:

If the array is already sorted, you can use the two-pointer approach:

left at start, right at end
move left/right based on current sum

Two-pointer approach uses O(1) extra space but requires sorted input.

Salesforce/data example:

Find pairs of adjustment records whose amounts cancel each other out.

A strong answer is:

I would clarify duplicates and whether indexes or values are required. For unsorted input, I would use a hash set for O(n) time. For sorted input, I would use two pointers to reduce extra space.

Flattening a nested dictionary is common when semi-structured JSON lands in a bronze layer and needs to be converted into column-style paths.

Example input:

{
    "account": {
        "id": "A1",
        "owner": {
            "id": "U1"
        }
    }
}

Expected output:

{
    "account.id": "A1",
    "account.owner.id": "U1"
}

Recursive solution:

def flatten(obj, prefix=""):
    out = {}

    for key, value in obj.items():
        path = f"{prefix}.{key}" if prefix else key

        if isinstance(value, dict):
            out.update(flatten(value, path))
        else:
            out[path] = value

    return out

Complexity:

Important follow-ups:

• How should lists be handled?
• What if keys already contain dots?
• Should null values be kept?
• Should deeply nested JSON use iterative traversal to avoid recursion depth issues?
• Should output preserve data types?

For CDC pipelines, this helps when raw Salesforce or external events land as JSON and you need predictable field paths for validation or schema inference.

A strong answer is:

I would recursively traverse the dictionary, build a dotted path for each nested key, and store leaf values in the output map. I would also clarify how to handle arrays, nulls, dotted keys, and very deep JSON.

This is a good data-engineering coding question because it resembles merging sorted CDC shards, partitioned files, or ordered event streams.

Use a min-heap of size k.

import heapq

def merge_k_lists(lists):
    heap = []

    for list_index, records in enumerate(lists):
        if records:
            heapq.heappush(
                heap,
                (records[0][0], list_index, 0)
            )

    result = []

    while heap:
        ts, list_index, record_index = heapq.heappop(heap)
        records = lists[list_index]

        result.append(records[record_index])

        next_index = record_index + 1

        if next_index < len(records):
            heapq.heappush(
                heap,
                (records[next_index][0], list_index, next_index)
            )

    return result

Assumption:

Each record is sorted by timestamp, and timestamp is at index 0.

Example record:

("2026-06-27T10:00:00Z", "event_1")

Complexity:

Where:

• N = total records across all lists
• k = number of sorted lists

Important follow-ups:

• What if timestamps are equal?
• Do we need stable ordering?
• Are records already sorted by event time or ingestion time?
• Can lists be streams instead of in-memory arrays?
• How do we handle late events?

A strong answer is:

I would use a min-heap containing the current record from each sorted list. Each pop gives the next earliest timestamp, and I push the next record from the same list. This gives O(N log k) time and works well for merging ordered CDC shards.

To deduplicate a CRM event stream, use a stable event identifier and keep track of processed events.

Simple logic:

seen = set()

def process(event):
    if event.id in seen:
        return None

    seen.add(event.id)
    return transform(event)

This works for small in-memory examples, but production streams need bounded and durable state.

Production design options:

Important considerations:

• Event ID must be stable
• Dedup state should survive restarts
• State must be bounded using TTL or compaction
• Replays should not produce duplicate target rows
• Sink writes should be idempotent
• Checkpointing should align with successful writes

For Salesforce CDC, possible keys include:

event_id, replay_id, source_object_id, commit_timestamp, change_sequence

Do not deduplicate only by business fields like email or account name because those can change and may not uniquely identify events.

A strong answer is:

I would deduplicate by a stable event ID, store seen IDs in a bounded or durable state store, and make the sink idempotent with upserts or unique constraints. In production, the dedup state must survive retries and restarts.

A strong answer should connect Salesforce's values to your actual data engineering work, not generic praise.

Good values to connect with data work:

A weak answer sounds like:

Salesforce is a big company and I want to work on interesting products.

A stronger answer connects your experience:

Trust resonates with me because data engineering directly affects customer confidence. If CRM, consent, or PII pipelines are wrong, downstream analytics and AI decisions can be wrong. In my past work, I handled access controls, audit logs, data quality checks, and incident communication, so I see Trust as a daily engineering responsibility.

You can also mention interest in Data Cloud / Data 360 and Agentforce if it is genuine:

I am interested in how Salesforce uses unified customer data to power analytics, automation, and AI agents. That kind of system needs strong data engineering foundations: identity resolution, freshness, lineage, governance, and quality checks.

A strong answer is:

Trust resonates most with me because data engineering is directly tied to reliability, privacy, and customer confidence. I want to work on systems where clean, governed, and timely data powers customer-facing analytics, Data Cloud, and AI use cases.

Use a STAR structure and show that you can disagree without making it personal.

A good example can come from:

• Streaming vs batch ingestion
• SCD2 vs daily snapshot
• SQL model vs denormalized mart
• Redis cache vs warehouse optimization
• Custom pipeline vs managed connector
• Strict data quality gate vs warning-only check

Example structure:

What interviewers want to hear:

• You listened first
• You used data, not ego
• You considered maintainability
• You aligned the decision with customer or business impact
• You preserved the relationship

Avoid saying:

The senior engineer was wrong, so I proved my approach was better.

Say instead:

We had different assumptions, so I made the trade-offs visible and helped the team choose based on SLA, cost, and maintainability.

A strong answer is:

I handled the disagreement by clarifying the requirement, documenting trade-offs, validating assumptions with a small test, and aligning the final decision with SLA, maintenance cost, and business impact rather than personal preference.

Choose a story where you identified a real risk, acted responsibly, and improved the system.

Good examples:

• PII copied into a sandbox without masking
• Overly broad service principal access to storage
• API key committed to Git
• Data export shared with too many users
• Logs containing tokens, emails, or customer identifiers
• No audit trail for sensitive table access
• Production data available from a non-production notebook

Answer structure:

1. Risk found

   • What did you notice?

2. Impact

   • What could have gone wrong?

3. Action

   • Who did you inform?
   • Did you rotate credentials?
   • Did you restrict access?
   • Did you mask or delete exposed data?

4. Prevention

   • What control did you add?

Example:

I found that a shared service principal had write access to multiple storage containers, including PII datasets that the pipeline did not need. I raised it with the platform and security teams, helped reduce permissions to least privilege, rotated credentials, and added a review step for new data access requests.

Strong prevention actions:

• Secret scanning in CI
• IAM least-privilege review
• Token rotation
• Column masking
• Audit logging
• Access review workflow
• Policy-as-code checks

A strong answer is:

I identified the risk, assessed the blast radius, involved the right owners, fixed the exposure, and added a preventive control such as secret scanning, least-privilege IAM, masking, or audit logging so the issue would not repeat.

Pick a failure where you learned something meaningful and changed your behavior.

Good data-engineering examples:

• Pipeline launched without enough data quality gates
• Dashboard users lost trust after metric definitions changed
• Identity merge churn caused duplicate or missing customer counts
• Backfill took longer than expected because dependencies were not mapped
• Streaming pipeline missed late-arriving data
• Gold table design did not match actual business questions

Avoid blaming:

The stakeholder kept changing requirements.

Better framing:

We did not define the data contract and success criteria clearly enough before building.

Example answer:

In one project, we built a gold sales mart quickly, but we did not define the exact grain and metric ownership early. After launch, different teams interpreted pipeline value differently, and users lost confidence in the dashboard. I helped pause further rollout, documented metric definitions, added reconciliation checks, and created a sign-off process with business owners. The lesson was that trusted data products need contracts, ownership, and validation before wide release.

What the interviewer wants:

• You take ownership
• You learned a practical lesson
• You changed your process
• You do not blame others
• You understand how data trust is lost and rebuilt

A strong answer is:

The project failed because we did not define ownership, grain, and data quality gates early enough. I learned to align on data contracts, success metrics, validation rules, and stakeholder sign-off before promoting data to trusted reporting layers.

This tests whether you can operate in a large enterprise without silently choosing favorites.

A good framework:

1. Clarify business impact

   • Revenue, compliance, customer-facing impact, executive reporting, operational risk.

2. Clarify deadline

   • Is it truly urgent or just important?

3. Estimate effort and dependencies

   • Can one request unblock the other?
   • Is there a shared data model or pipeline component?

4. Identify risk

   • What happens if each request is delayed?

5. Offer phased delivery

   • MVP metric first, full mart later.
   • Temporary extract first, governed pipeline later.

6. Escalate transparently

   • Present trade-offs to the decision maker with facts.

7. Document the decision

   • Capture scope, timeline, owner, and follow-up.

Example trade-off matrix:

Avoid:

I would work on whichever executive is louder.

Better:

I would make the trade-off visible and ask for prioritization based on business impact and risk.

A strong answer is:

I would compare business impact, urgency, effort, dependencies, and risk, then present a transparent trade-off matrix. If both cannot be done, I would propose phased delivery and get explicit prioritization rather than silently moving work around.

Choose a story where the problem was unclear, requirements changed, or multiple teams were blocked.

Good examples:

• Acquisition data had to be integrated quickly
• New CRM source had undocumented fields
• A dashboard broke before quarter-end reporting
• Data Cloud identity rules caused unexpected merge behavior
• Source system changed schema without notice
• Pipeline ownership was unclear during an incident

Strong answer structure:

1. Stabilize

   • Identify immediate user impact and protect critical outputs.

2. Clarify known vs unknown

   • Separate facts, assumptions, and open questions.

3. Create a short plan

   • Time-box investigation.
   • Assign owners.
   • Define decision points.

4. Communicate frequently

   • Explain what is affected, what is safe, and what is still unknown.

5. Deliver incrementally

   • Provide a partial fix or MVP while deeper correction continues.

6. Close with learning

   • Update runbooks, tests, contracts, or escalation paths.

Example:

During a CRM migration, we discovered late that several account hierarchy fields had inconsistent meanings across regions. I paused promotion to gold tables, created a data profiling report, met with regional owners, defined a temporary mapping for critical dashboards, and tracked unresolved fields in a risk register. We delivered the quarter-end dashboard on time with documented limitations, then followed up with a permanent contract.

A strong answer is:

I lead ambiguous crises by separating facts from assumptions, stabilizing the highest-impact users first, assigning clear owners, communicating known risks, and delivering incremental fixes while building the longer-term solution.

A lead-level answer should cover architecture, operations, and team practices.

Key areas:

Salesforce-specific scaling concerns:

• Account hierarchies can create skew
• Opportunity history can grow quickly
• CDC events can arrive in bursts
• Identity resolution can change profile counts
• Multi-tenant or business-unit data may require isolation
• PII controls can affect how data is joined and served

A lead candidate should also mention people and process:

• Establish design reviews
• Mentor engineers on data modeling and performance
• Create reusable pipeline templates
• Set performance budgets
• Run capacity reviews
• Practice incident game days

A strong answer is:

I would scale the platform through correct data grain, partitioning, skew handling, bulk processing, idempotent CDC design, pre-aggregated serving layers, governance standards, and strong observability. As a lead, I would also create reusable patterns and mentor teams to apply them consistently.

Technical debt in data platforms is dangerous because it often appears later as wrong metrics, slow pipelines, high cost, or poor trust.

Common data-platform debt:

• Unowned tables
• No data contracts
• No tests for critical transformations
• Duplicated pipeline logic
• Hardcoded business rules
• Manual backfills
• Poor naming conventions
• No lineage or documentation
• Expensive queries nobody owns
• Legacy pipelines no one wants to touch

Management framework:

1. Make debt visible

   • Create a debt register with owner, impact, risk, and effort.

2. Prioritize by customer impact

   • Fix debt that affects correctness, trust, SLA, cost, or security first.

3. Reserve capacity

   • Allocate regular sprint capacity for debt reduction.

4. Prevent new debt

   • Use PR templates, naming standards, tests, and data contracts.

5. Refactor safely

   • Use a strangler pattern: run old and new pipelines in parallel until reconciliation passes.

6. Measure improvement

   • Track pipeline runtime, failure rate, cost, test coverage, and incident count.

Example:

We replaced a legacy Opportunity mart by building a new model in parallel, validating row counts and business metrics for two weeks, then switching dashboards gradually.

A strong answer is:

I manage technical debt by making it visible, ranking it by business impact and risk, reserving capacity to reduce it, and preventing new debt with standards, tests, contracts, and design reviews. For risky refactors, I run old and new pipelines in parallel until reconciliation passes.

The decision should be based on reliability, maintenance cost, flexibility, and business differentiation.

Prefer managed or out-of-the-box options when:

• Standard connector supports the source well
• Required SLA is covered
• Transformations are simple
• Security and compliance controls are built in
• Long-term maintenance should be low
• The integration is not a competitive differentiator

Build custom logic when:

• You need complex CDC handling
• Identity matching rules are custom
• Transformations are highly business-specific
• You need advanced deduplication or ordering logic
• Multi-cloud or hybrid architecture is required
• Existing connector cannot meet latency, schema, or governance needs

Decision rubric:

Avoid building custom pipelines only because it feels more flexible. Custom code also brings monitoring, alerting, testing, security, and upgrade responsibility.

A strong answer is:

I prefer managed connectors or configuration when they meet SLA, security, and transformation needs. I build custom logic only when the business requirements, latency, identity rules, or integration complexity cannot be handled safely by standard tools.

MuleSoft fits in enterprise integration by providing reusable APIs and integration flows between Salesforce and other systems.

A common MuleSoft pattern is API-led connectivity:

Where MuleSoft helps:

• Connect Salesforce CRM with ERP, billing, support, marketing, or data platforms
• Transform payloads between systems
• Apply routing and orchestration
• Enforce authentication and rate limits
• Provide reusable integration contracts
• Improve observability of enterprise APIs
• Reduce point-to-point integration sprawl

For data engineers, MuleSoft may be relevant in two ways:

1. You consume APIs exposed by MuleSoft as pipeline sources.
2. You coordinate event contracts and payload formats with integration teams.

When MuleSoft is better than point-to-point integration:

• Multiple consumers need the same business data
• Enterprise governance matters
• APIs need versioning and monitoring
• Direct source access is risky or inconsistent
• Integrations should be reusable across teams

A strong answer is:

MuleSoft provides API-led connectivity between Salesforce and enterprise systems. As a data engineer, I may consume MuleSoft APIs, coordinate event contracts, and rely on reusable System, Process, and Experience APIs instead of building fragile point-to-point integrations.

Use this as a hybrid behavioral and technical answer. Show leadership, planning, validation, and measurable results.

Good structure:

1. Scope

   • What was migrated?
   • From which system to which target?
   • How much data?
   • Which users or business processes were affected?

2. Challenges

   • Duplicate contacts/accounts
   • Bad account hierarchy
   • Missing required fields
   • Attachment migration
   • Historical field history
   • Downtime window
   • Data ownership conflicts
   • Consent and PII requirements

3. Plan

   • Profiling
   • Mapping
   • Cleansing
   • Test loads
   • Reconciliation
   • Parallel run
   • Cutover
   • Rollback

4. Execution

   • Built migration pipelines
   • Created validation queries
   • Coordinated with business owners
   • Ran mock cutovers
   • Tracked defects and sign-offs

5. Result

   • Match rate
   • Downtime
   • Error reduction
   • Adoption
   • Reporting accuracy
   • Business outcome

Example:

I led a CRM migration where duplicate Accounts and inconsistent region mappings were breaking sales reporting. We profiled the data, created match rules, ran test loads, reconciled key metrics with business owners, and used a phased cutover. The final migration completed within the planned downtime window, and post-cutover reconciliation passed for the critical revenue and pipeline dashboards.

A strong answer is:

I would describe the migration scope, data quality challenges, cutover plan, validation strategy, stakeholder coordination, and measurable result. The key is showing that I handled both the technical migration and the business trust needed for adoption.

Agentforce and AI features depend on trusted, governed, and timely data. Raw CRM dumps are not enough.

Data engineering supports AI by providing:

Example:

If an AI agent recommends the next best action for a customer, it needs accurate account status, recent service cases, consent preferences, purchase history, and current engagement signals.

Important risks:

• Stale data can cause wrong recommendations
• Poor identity resolution can mix customers incorrectly
• Missing consent can create compliance issues
• Bad lineage makes AI behavior hard to explain
• Unchecked feedback loops can reinforce bad decisions

A Salesforce Data Engineer should connect Agentforce/Data Cloud work to fundamentals:

• DLO/DMO mapping
• Unified profiles
• Identity resolution
• Data quality checks
• Real-time ingestion
• Secure access
• Audit and lineage
• Monitoring and feedback metrics

A strong answer is:

Data engineering supports Agentforce by providing trusted, fresh, unified, and governed customer data. I would focus on identity resolution, data quality, lineage, PII controls, real-time signals, and feedback loops so AI agents act on accurate and compliant context.