Went through the Mastercard data engineer interview process earlier this year, targeting their data platform team. The role was in the O'Fallon, MO office (they have a big engineering hub there). Sharing because DE-specific info on Mastercard is sparse.
What the loop looked like: Phone screen (coding/SQL), then a full day of 4 rounds: SQL, pipeline design, coding, and behavioral.
SQL round: This was the heaviest round by far. Mastercard processes billions of transactions and their SQL questions reflect that. I got: A window function problem involving sessionization of transaction events by card. Basically: given a stream of transaction rows with timestamps, define a "session" as consecutive transactions within 30 minutes, and for each session compute the total spend. A query optimization problem. They gave me a poorly performing query (multiple nested subqueries) and asked me to rewrite it. I used CTEs, rewrote the join order, and suggested adding a composite index. They asked why that specific index would help.
They're using BigQuery and Redshift depending on the team. Worth knowing both flavors. Some syntax differs.
Pipeline design round: This is their system design equivalent for DE roles. I got: design an end-to-end pipeline that ingests raw transaction events, applies fraud scoring, and makes that data available for both real-time fraud decisions and offline analytics. So: dual-path architecture.
I sketched: Kafka as the event bus, a streaming path (Flink/Spark Streaming) feeding a low-latency feature store for real-time scoring, and a batch path (Spark on EMR or Dataproc) writing to a columnar data warehouse. They asked about schema evolution, late-arriving data, and how I'd handle exactly-once semantics in the streaming path.
Coding round: Python. One problem on data transformation (flatten a nested JSON transaction object into tabular form, handle missing keys gracefully). One problem on writing an efficient deduplication function given a stream of events with a unique transaction ID. Both were medium difficulty, nothing exotic.
One thing I didn't expect: They asked about data governance and PII handling. Transaction data includes cardholder info and Mastercard is PCI-DSS compliant. They asked how I'd ensure sensitive fields are handled correctly in a pipeline. Have an answer about masking, tokenization, and access controls.
Outcome: Got an offer. Negotiated base up slightly. Comp for a senior DE in O'Fallon tracks below NYC (cost of living difference is real) but the total package with bonus and RSUs was competitive for the market there.