Atlassian Interview Questions

1 million events/day ≈ ~12 events/second — well within a single Kafka topic's capacity. Architecture : Key decisions : Kafka partitions : 3 6 is suffici...

• How would you ingest and process over 1 million events per day efficiently?

  1 million events/day ≈ ~12 events/second — well within a single Kafka topic's capacity. Architecture : Key decisions : Kafka partitions : 3 6 is sufficient at this scale — don't over partition Micro batch vs streaming : at 12 events/sec, micro batch every 5 minutes is simpler and more cost effective than true streaming File format : Parquet/Delta — columnar, compressed, partition by event date Deduplication : include event id in schema; use Delta MERGE or dropDuplicates to handle at least once d

• Design a data model for impressions and clicks from marketing campaigns.

  Fact tables : Attribution query — click through rate per campaign:

• How do you handle late-arriving data and deduplication?

  Late arriving data : Use watermarking in Spark Structured Streaming: withWatermark("event ts", "2 hours") — accepts events up to 2 hours late For batch pipelines: process WHERE event date BETWEEN yesterday 1 AND yesterday — includes events from the previous day that arrived late Reprocessing: Airflow backfill for dates where late data is expected Deduplication : Prevention : include event id (UUID) in every event at the producer side — makes idempotent processing possible at every stage.

• Write SQL to calculate average bug resolution time per team.

  Assuming a Jira style issues table: Note : use median (PERCENTILE CONT) in addition to average — a few very long lived bugs can inflate the mean significantly.

• SQL: Find the most frequently used Atlassian product last month.

  If "most used" means by total interactions :

• SQL: Rolling average or sum over the last 3 days.

  ROWS vs RANGE : ROWS BETWEEN 2 PRECEDING AND CURRENT ROW : always exactly 3 physical rows RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW : covers the date range (handles missing days correctly)

• Python: Search for target number position in an array.

  Binary search (sorted array) — O(log n): Linear search (unsorted array) — O(n): Find all occurrences (unsorted):

• System design: Build a real-time, scalable data pipeline.

  Requirements : ingest millions of events/day, sub minute latency, fault tolerant. Architecture : Scalability levers : Kafka: add partitions + consumer pods Spark: scale executor count; auto scaling on EMR/Kubernetes Storage: object storage (S3/ADLS) scales infinitely Reliability : Kafka replication + Spark checkpointing for exactly once Dead letter for bad records Alerting on consumer lag (CloudWatch/Datadog) Operational : Airflow for scheduling batch Gold layer; CI/CD with GitHub Actions for pi