SQL for Analytics and Pipelines
SQL is the backbone of the process. You will write multi-step transformations, often with chained questions where each step feeds the next. Strong performance looks like correct answers first, then clear structure, edge case handling, and an ability to reason about performance (indexes, partitions, window function costs).
Be ready to go over:
- Joins and filtering correctness – Inner vs. left joins, semi/anti joins, deduplication patterns, null semantics.
- Window functions – Ranking, partitioned aggregates, gaps-and-islands, sessionization.
- Time-series and incremental logic – Slowly changing patterns, late-arriving data, watermarking, validity intervals.
Advanced concepts (less common):
- Performance tuning and exploitation of partitions/clustering
- MERGE/UPSERT semantics and idempotent backfills
- Data quality constraints and anomaly detection in SQL
Example questions or scenarios:
- “Given events(user_id, event_time, event_type), compute daily active users and a 7-day rolling retention metric.”
- “You have orders and refunds. Produce net revenue by week with late refunds applied correctly.”
- “Three-step task: build a sessions table, compute per-session conversion, then join to user attributes for a cohort analysis.”
Python for Data Engineering
Expect Python tasks that manipulate records, parse logs, or implement transformation logic you might otherwise write in SQL. Strong solutions demonstrate clean structure (functions), testable logic, and linear-time reasoning with attention to memory.
Be ready to go over:
- Parsing and transformation – Reading line-oriented logs/JSON, normalizing fields, handling malformed records.
- Aggregation and grouping – Rolling and windowed computations, map-reduce style grouping.
- Data validation – Asserting schema, filtering bad data, simple unit checks.
Advanced concepts (less common):
- Iterators and generators for streaming
- Pandas vs. pure-Python trade-offs in production
- Type hints, docstrings, and basic test scaffolding
Example questions or scenarios:
- “Parse web server logs and compute the top N endpoints per user in the last 24 hours.”
- “Given a list of events with timestamps, deduplicate by key keeping the most recent, then output time-bucketed counts.”
- “Transform nested JSON payloads into a normalized structure suitable for warehousing.”
Data Warehousing and Dimensional Modeling
Round 2 for many candidates explores modeling. Interviewers assess if you can translate business processes into resilient schemas and explain choices. Strong answers show business understanding, naming rigor, and a plan for change management.
Be ready to go over:
- Star vs. snowflake – When to denormalize for analytics and performance.
- Slowly Changing Dimensions (SCD) – Type 1 vs. Type 2 trade-offs and how to implement them.
- Grain definition – Choosing the right grain for facts, surrogate keys, and surrogate vs. natural keys.
Advanced concepts (less common):
- Bridge tables for many-to-many
- Data privacy and PII handling in models
- Data vault or ELT layering with dbt-like patterns
Example questions or scenarios:
- “Model product usage for Jira issues, users, and projects to support daily active metrics and retention.”
- “Design a subscription and invoicing model handling upgrades, proration, and refunds.”
- “Explain how you would implement SCD Type 2 for account attributes and query ‘as-of’ states.”
Big Data and Distributed Processing
Some interviews include architecture or Spark/Kafka topics. You will be evaluated on knowing when to use distributed systems, how to partition data, and how to make pipelines resilient and cost-aware.
Be ready to go over:
- Spark fundamentals – Wide vs. narrow transformations, shuffles, joins, and checkpointing.
- Streaming vs. batch – Latency vs. correctness trade-offs, exactly-once semantics.
- Storage and file layout – Parquet/ORC, partitioning strategies, small files problem.
Advanced concepts (less common):
- Stateful streaming with watermarks
- Skew handling (salting, broadcast joins)
- Schema evolution and compatibility
Example questions or scenarios:
- “Design a pipeline to process clickstream events in real time for feature flags exposure and conversions.”
- “Given skewed keys in Spark, how would you optimize a join to avoid OOM errors?”
- “Outline a backfill strategy for a historical table with late-arriving events.”
Pipeline and Platform Design
You may be asked to design an end-to-end architecture for a specific Atlassian-style analytics use case. Strong performance includes clear component boundaries, orchestration, observability, and failure recovery.
Be ready to go over:
- Orchestration and lineage – Airflow-style DAGs, retries, idempotency, data contracts.
- Monitoring – SLAs, SLOs, data quality checks, alerting thresholds.
- Cost and reliability – Storage tiering, cluster autoscaling, partition pruning.
Advanced concepts (less common):
- Data mesh patterns for domain ownership
- Row vs. columnar store trade-offs for workloads
- Multi-region replication and disaster recovery
Example questions or scenarios:
- “Propose a data platform to support A/B testing metrics with trustworthy guardrails and reproducibility.”
- “How would you implement data quality checks that block downstream jobs on critical failures?”
- “Design a CDC-based pipeline from OLTP to a cloud data warehouse with late-arrival handling.”
Values, Collaboration, and Delivery
Atlassian values open communication, customer-centric decisions, and teamwork. Interviewers evaluate how you document, review, and iterate with peers, and how you keep customer trust top of mind.
Be ready to go over:
- Stakeholder alignment – Translating ambiguous asks into specific metrics and tables.
- Documentation and reviews – RFCs, ADRs, and PR etiquette.
- Incident response – Communicating outages, root cause analysis, and prevention.
Advanced concepts (less common):
- Balancing speed vs. quality under deadlines
- Navigating trade-offs with product and infra constraints
- Mentoring and uplifting team standards
Example questions or scenarios:
- “Tell us about a time you protected data quality under pressure.”
- “Describe a difficult stakeholder request and how you clarified scope and success metrics.”
- “How do you handle a breaking change your pipeline caused downstream?”
