One Hour. Seven Stages.

### Background

Hi, I’m **Blair Chen**, a Senior Data Engineer with **seven years of experience in Singapore**, working on data warehousing and analytics for **global payments and e-commerce**.

### Skills

My strengths are **SQL, Python, Spark, Flink, AWS, dimensional modeling, data warehousing, and data governance**. I have experience with both **batch and real-time data warehousing**, with **more extensive experience on the batch side**. I build reliable data pipelines and reusable datasets that business teams can trust.

### Enterprise Experience

My two most recent roles were at **TC** and **Mi**.

At **TC**, I spent **four years** working across three areas:

1. **Data warehouse architecture:** Built models for cross-border payments, from collection to payout. 2. **Data governance and asset management:** Improved the **data quality score** and made our data assets more **standardized, reliable, and easier to understand**. 3. **Pipeline and SLA optimization:** Improved pipeline performance and reporting reliability.

At **Mi**, I focused on three areas:

1. **Data warehouse standards:** Defined **data domains**, helping shift development from report-specific tables to reusable models built around business processes. 2. **Reservation data mart:** Built a reusable mart that supported **CRM targeting**. 3. **AI analytics:** Built a **natural-language analytics agent** on top of the mart using **LangGraph, RAG, and controlled SQL**.

I’m interested in **Traveloka** because this role focuses on scalable warehouse architecture, trusted metrics, and reliable data pipelines for a transactional business. I would bring hands-on data engineering experience and practical AI capability where it adds value.

- **6Estates:** Built an AWS pipeline for commercial invoices using S3, Glue, PySpark, and Athena. Standardized schemas, deduplicated records, checked data quality, and identified unit-price anomalies. - **Working with analysts:** I align on the business definition, table grain, keys, and delivery time. I then build reusable models and validate completeness, uniqueness, freshness, and source reconciliation. - **Why I’m looking:** I’m seeking a hands-on data engineering role with end-to-end ownership. My AI agent work is an additional capability built on governed data. - **Career move from Mi:** My role shifted mainly toward operational support, with less engineering and development ownership than I expected. - **Career move from TC:** After four years, I wanted broader architecture challenges and room to grow. - **Gap:** My data engineering role was subject to a 180-day non-compete agreement.

I’m interested in **this role** because this role focuses on **scalable warehouse architecture, trusted metrics, and reliable data pipelines** for a transactional business.

Following a company restructuring, most of the core engineering work moved to Beijing, and the Singapore team became smaller.

My responsibilities shifted toward operational support and ad-hoc requests, with fewer opportunities to work on new development.

I chose to leave voluntarily and look for a role where I can contribute more to hands-on engineering and end-to-end delivery.

**Q1: What is the difference between OLTP and OLAP?** **OLTP (Online Transaction Processing)** supports daily transactions. **OLAP (Online Analytical Processing)** supports historical analysis and aggregation. **Q2: What are Facts, Dimensions, and Measures?** **Facts** record business events, such as payments. **Dimensions** provide context, such as users and merchants. **Measures** are numeric values, such as payment amounts. **Q3: How do you design a Fact Table?** Define the **business process**, then declare the **grain**: exactly what one row represents. Next define the **keys, dimensions, measures, source logic, loading strategy, and quality checks**. ### Schemas & Keys **Q4: What is the difference between Star Schema and Snowflake Schema?** A **Star Schema** connects a fact table directly to denormalized dimensions. It is simpler and requires fewer joins. A **Snowflake Schema** normalizes dimension hierarchies into separate tables, reducing duplication but requiring more joins. **Q5: What are the main key and dimension concepts?** A **business key** is a source-system identifier, such as `merchant_id`. A **conformed dimension** has consistent definitions across data marts. A **degenerate dimension** is an identifier, such as an order number, stored in a fact table without a separate dimension table. ### Dimension History & Loading **Q6: What is SCD, and how do Type 1 and Type 2 differ?** **SCD (Slowly Changing Dimension)** defines how dimension changes are handled. **Type 1** overwrites the old value without preserving history. **Type 2** adds a new row with effective dates to preserve history. **Q7: What is the difference between Full Snapshot and Incremental Loading?** **Full snapshot loading** captures the complete dataset for each snapshot date. **Incremental loading** processes only new or changed records. Retained daily snapshots preserve each day's state, but do not capture every change within a day. ### Warehouse Layers & DM Design **Q8: What are the main Data Warehouse layers?** **ODS (Operational Data Store)** holds raw or near-raw source data. **DWD (Data Warehouse Detail)** contains cleaned, standardized detailed records. **DIM (Dimension)** contains reusable descriptive entities. **DM (Data Mart)** contains business-oriented analytical datasets and metrics. **ADS (Application Data Service)** serves specific reports or applications. **Q9: How do you design a DM Table, and why is it reusable?** Define the **business process, grain, keys, dimensions, metrics, source logic, loading strategy, and governance controls**. **Milestone metrics** capture key timestamps or states, such as KYC approval. **Time-series metrics** capture rolling or cumulative values, such as 30-day payment amount. A DM standardizes shared business logic so reports can reuse it. ### Data Processing & Partitioning **Q10: What are ETL, ELT, and Partitioning?** **ETL (Extract, Transform, Load)** transforms data before loading it into the target. **ELT (Extract, Load, Transform)** loads data first, then transforms it inside the target. **Partitioning** divides a table by a column, such as date, so queries can skip irrelevant data. **Date partitioning** can also simplify incremental loading and data retention.

I mainly built data warehouses for **cross-border payments and merchant collection** at **TC**. ### How the Foundations Fit This Warehouse The source applications handled **OLTP** transactions, while the warehouse supported **OLAP** reporting and historical analysis. For each analytical model, I start with the **business process and grain**, then define its keys, dimensions, measures, source logic, loading strategy, and quality checks. Facts represent measurable business events; dimensions such as **Merchant, Store, and Order** provide reusable context. I choose a **Star Schema** when simpler reporting joins are useful, and a **Snowflake Schema** when normalized hierarchies justify the extra joins. A **business key** comes from the source; a **surrogate key** is warehouse-generated and can distinguish historical dimension versions. Shared **conformed dimensions** keep definitions consistent across marts, while an order identifier can remain a **degenerate dimension** when it needs no separate descriptive table. I also classify measures as **additive**, **semi-additive**, or **non-additive**; for example, balances should not usually be summed across time, and ratios should not be summed directly. I also separate **dimension history** from **data loading**: SCD Type 1 overwrites a value, while Type 2 preserves effective-dated versions. Full snapshots capture a complete state; incremental loads process changes, and a daily snapshot cannot show every intra-day change. Finally, **ETL vs. ELT** describes where transformations happen. I use **date partitioning** when it matches query filters and retention needs, so queries can skip irrelevant data. ### Data Warehouse Development Process 1. I defined the **business goals and requirements**. 2. I ingested raw source data into my **ODS**, the Operational Data Store (**Databricks counterpart: Bronze Delta tables in cloud object storage**). 3. I cleaned and standardized detailed data in **DWD / DIM** (**Databricks counterpart: Silver Delta tables**). 4. I built reusable subject-oriented models in **DM** (**Databricks counterpart: Gold facts and dimensions**). 5. I served downstream applications through **ADS** (**Databricks counterpart: curated Gold serving tables or views**). The architecture followed **ODS → DWD → DM → ADS**. In a lakehouse comparison, these layers are broadly **Bronze → Silver → Gold → Gold serving**, but the mapping is conceptual rather than one-to-one. | **TC DWH area** | **My design and responsibility** | **Databricks counterpart** | | --- | --- | --- | | **ODS** | Raw source landing; full snapshots and minimal transformation preserve the source of truth. | Bronze Delta tables in cloud object storage; retain raw CDC only when the source connector provides it. | | **DWD / DIM** | Clean, deduplicate, standardize, and validate detailed business records and shared dimensions. | Validated Silver Delta tables with quality rules and controlled change handling. | | **DM / DWS** | Reusable subject models such as **Merchant, Store, and Order**; shared facts, dimensions, and metrics. | Governed Gold facts and dimensions for consistent business analysis. | | **ADS** | Application-specific datasets for **BI, regulatory reporting, and AML compliance**. | Curated Gold serving tables or views for reports and applications. | | **Internal DAG scheduler** | Dependency-based scheduling, triggers, retries, SLA monitoring, and alerting. | Databricks Workflows / Jobs as a conceptual orchestration counterpart. | | **Hive tables / HDFS** | Hive tables stored on HDFS, with storage and table metadata managed in the existing platform. | Delta tables on cloud object storage; table format and storage layer are separate concepts. | | **Data governance** | Asset scoring and controls for standards, data quality, security, and cost. | Unity Catalog and related platform governance controls, subject to the implemented design. | Across all layers, we applied **data governance and SLA monitoring**, and used **DAG-based dependency scheduling**, which improved our SLA from **95% to over 99%** through dependency cleanup, trigger-based scheduling, and proactive monitoring and alerting.

My ODS was the raw landing layer, with source data stored as Hive tables backed by HDFS (**Databricks counterpart: Bronze Delta tables in cloud object storage, which can retain raw CDC events and the operation and source metadata provided by the connector**).

I cleaned and standardized detailed records in DWD / DIM (**Databricks counterpart: Silver Delta tables, where CDC changes can be validated, deduplicated, applied in source order, and handled for deletes and approved schema changes**).

My DM contained reusable business models such as Merchant, Store, and Order (**Databricks counterpart: Gold facts and dimensions**).

My ADS served BI dashboards, regulatory reports, and AML compliance (**Databricks counterpart: curated Gold serving tables or views for downstream reporting and applications**).

Your experience: InLong + Kafka and warehouse layers. Databricks Bronze/Silver/Gold and managed CDC connectors are reference design choices unless you have used them in production.

One example was a **critical Spark job** for cross-border remittance analytics. It usually took around **30 minutes**, but at peak times ran for as long as **75 minutes** and occasionally failed with **Executor OOM**. ### 1. Background & Problem The job calculated unique senders with `COUNT(DISTINCT sender_id)` and summed remittance amounts by provider, destination country, and currency. It supported transaction monitoring and operational reporting. ### 2. First Attempt — Configuration Tuning Before I knew the cause was data skew, I tried configuration changes: increasing shuffle partitions and executor memory, enabling **AQE**, and testing a sort-based aggregation by disabling the hash aggregation path where supported. The sort-based attempt stopped the OOM, but its sorting overhead pushed runtime beyond **one hour**. More partitions and executor memory did not remove the long-running tasks. That showed configuration tuning alone was not enough. ### 3. Investigation — Spark UI & Hot-Key Skew I then checked the **Spark UI**, comparing task durations, shuffle volumes, and key distribution. Most tasks completed normally, but a few reducers ran much longer. The distribution showed two extreme hot keys — **Wise-US-USD** and **PandaRemit-KR-KRW** — with more than **7 million transactions** in total. ### 4. Root Cause — Distinct Aggregation Under those hot keys, `COUNT(DISTINCT sender_id)` created a very large in-memory distinct set in **HashAggregate**, driving heavy GC and Executor OOM. AQE helped with milder skew, but could not make these two groups small enough. ### 5. SQL Rewrite — Two-Stage Aggregation & Targeted Salting After the Spark UI showed a few unusually slow reducers, I counted rows by **provider, destination country, and currency** and compared the key volumes. **Wise-US-USD** and **PandaRemit-KR-KRW** stood out, with more than **7 million transactions combined**; those were the hot keys I targeted. I reduced the data first at **business key + sender_id** grain, then aggregated back to the original business grain. I applied deterministic **8-way salting only to the two extreme keys**, hashing `sender_id` so each sender stayed in one bucket and the distinct count remained exact. ### 6. Results The final job completed in **13 minutes**, down from a peak of **75 minutes**. The OOM failures stopped, and the run became more stable. ### 6. Likely Follow-up Questions **Why did `COUNT(DISTINCT)` cause OOM?** A skewed key forced one task to hold millions of sender IDs in memory, causing heavy GC and executor OOM. **Why did AQE not solve it completely?** AQE helped with moderate skew, but the two extreme keys still needed a query-level fix. **What is two-stage aggregation?** Deduplicate by business key and sender first, then count at the original business grain. **Why salt only the hot keys?** It spreads oversized groups across tasks without adding shuffle overhead to normal keys. **Why use deterministic eight-way salting?** Hashing `sender_id` keeps each sender in one bucket; eight buckets gave these hot keys more parallelism. ### SQL example — Two-stage aggregation An exact distinct count can be reduced in two stages: ```sql WITH unique_senders AS ( SELECT DISTINCT provider, destination_country, currency, sender_id FROM remittance_events ), sender_counts AS ( SELECT provider, destination_country, currency, COUNT(*) AS unique_senders FROM unique_senders GROUP BY provider, destination_country, currency ) SELECT provider, destination_country, currency, unique_senders FROM sender_counts; ``` For the extreme hot keys, I would salt only those keys during the first stage, then combine partial results at the original business grain. For exact distinct counts, each sender must map consistently to one salt bucket. ### 7. One-minute Interview Summary **Spark UI → find skew → HashAggregate OOM → config first: SortAggregate + AQE → still slow → two-stage aggregation → 8-way targeted salting → 75 min to 13 min**

One example was an **hourly cross-border remittance dashboard** built with **Kafka, Flink, ClickHouse, and Spark SQL**. Flink processed payment events using **Event Time + 1-hour TUMBLE windows**, and the dashboard had a **30-minute freshness SLA**, measured from the end of each window. ### 1. Problem The **10:00–11:00 result was still missing after 11:35**, although the Flink job was still running. ### 2. Troubleshooting I first ruled out common performance issues: **Kafka lag was small, backpressure was LOW, checkpoints were healthy, and the ClickHouse sink had no latency spike**. Then I checked Watermarks by Source subtask. I found that **one Source subtask’s output Watermark was stuck at 10:48**, while the **other active inputs had progressed beyond 11:00**. Because the downstream window operator follows the **minimum Watermark of its active input channels**, the stalled input prevented the window from firing. ### 3. Root Cause **The Source subtask had stopped receiving new events**, but its input was still considered **ACTIVE**. With no new events to advance its Watermark, it stayed at 10:48 and blocked the whole window. ### 4. Fix I configured a **5-minute source idle timeout**. After five minutes without new events, the quiet input was marked **IDLE**, so it no longer participated in the minimum Watermark calculation. The other active inputs had already advanced beyond **11:00**, allowing the window to fire. I did not need to change the business SQL or TUMBLE logic. ### 5. Result & Validation After the fix, the Watermark progressed normally and the **delayed window produced its result**. **Subsequent hourly windows were available within the 30-minute SLA**. I also validated **Kafka lag, backpressure, checkpoints, ClickHouse output, and the final business result**. ### Likely follow-up questions **Why didn't you increase parallelism?** Because **lag and backpressure were already healthy**. This was an Event-Time progress issue, not a processing-capacity issue. **Why could one quiet input block the window?** The quiet Source input’s stalled Watermark held back the **minimum Watermark of the window operator’s active input channels**, preventing the window from firing. **How did the idle timeout fix it?** After five quiet minutes, the quiet input was marked **IDLE** and excluded from the minimum Watermark calculation. The other active inputs had already progressed beyond the window boundary, allowing the window to fire. **What about very late events?** The real-time layer prioritized freshness, while a **T+1 Spark SQL batch calibration job** corrected very late data for final accuracy. ### Interview memory line **SLA miss → rule out lag / backpressure / checkpoint / sink → find Watermark outlier → quiet input still ACTIVE → 5-min idle timeout → Watermark advances → window fires → subsequent windows meet SLA**

I improved data governance by expanding a **DQC-only score into a four-dimensional data-asset framework**. The score covered **standards, data quality, security, and cost**, with daily Spark SQL jobs using **Hive metadata, lineage, security scans, and stale-table checks**. I also introduced **whitelist governance** for approved exceptions. This raised the average score across more than **600 tables from 76 to 86**. ### Governance Framework 1. **Standards — 35%:** naming, comments, dependency rules, ownership, and lineage. 2. **Data Quality — 35%:** timeliness, DQC coverage, row fluctuation, uniqueness / accuracy rules, and alert follow-up. 3. **Security — 15%:** table classification, sensitive-field protection, and owner compliance. 4. **Cost — 15%:** compute and storage efficiency, including stale assets. ### Daily Scoring & Remediation A daily **Spark SQL** job consolidated the control results into **one score per table per day**. We ranked weak assets, assigned owners, tracked remediation, and used a **whitelist only for reviewed and approved exceptions**. The key point is that governance became **measurable and operational** rather than only a DQC checklist.

I use a practical, layered approach: 1. **Agree on the data contract.** Confirm the business definition, table grain, keys, owner, and freshness expectations. 2. **Validate the data.** Check required fields, valid values, uniqueness, referential integrity, and freshness. 3. **Reconcile results.** Compare source and target counts, key coverage, and key totals by date, site, or status. 4. **Monitor and resolve issues.** Set alerts for business rules, such as checking that country and currency fields use valid three-letter codes. Then investigate affected data, fix the issue, rerun the pipeline, and verify the result. 5. **Improve through governance.** I helped assess more than **600 tables** across standards, quality, security, and cost. The average score rose from **76 to 86**, and table owners addressed the identified gaps.

I would work through it step by step: 1. **Clarify the issue.** Confirm the metric, affected period, and reports, then align the definition and filters. 2. **Check the pipeline.** Review source arrival times, the latest successful run, row counts, alerts, and recent changes. 3. **Trace the data layer by layer.** Start with the DM (Gold) output, then check the DWD (Silver) data. If needed, ask the business or technical team to verify the records in the source system. 4. **Fix and verify.** Correct the issue at the responsible layer, rerun the pipeline, and confirm that the output and downstream reports are accurate.

### Batch / offline pipelines

I would first check the DAG run and upstream arrival times to locate the delay, then inspect Spark task duration, skew, shuffle, and failures.

At **TC**, I helped improve the on-time SLA from 95% to over 99% through dependency cleanup, trigger-based scheduling, proactive monitoring, and alerts. On a critical Spark job, I diagnosed hot-key skew and expensive distinct aggregation; targeted query changes reduced runtime from 75 minutes to 13 minutes and resolved executor OOM.

If the SLA were already missed, I would tell stakeholders what data is delayed and when I will update them, perform a safe rerun or backfill, reconcile source and target counts and business totals before publishing, then document the root cause and permanent fix.

### Real-time pipelines

For Kafka and Flink, I would check consumer lag, event-time watermarks by source, backpressure, checkpoints, and sink latency.

On my hourly remittance dashboard, a quiet Flink source subtask stayed active with a lagging watermark and blocked the window. A five-minute idleness timeout let the watermark advance; the delayed window recovered and subsequent windows met the 30-minute SLA. A T+1 Spark SQL calibration handled very late data.

My main production experience is with **Spark, Hive, and AWS-based pipelines**.

I am building hands-on familiarity with Databricks. My transferable skills include **Spark processing, SQL, data modeling, and troubleshooting**.

I am focusing on **Delta tables, Unity Catalog, and job orchestration**. I would not describe my experience as extensive Databricks production experience yet.

I would preserve the **separation of responsibilities**. Bronze would retain source data. Silver would **standardize** (STAN-der-dyze), **validate** (VAL-uh-dayt), and **deduplicate** (dee-DOO-pli-kayt) details. Gold would contain **business-ready models and metrics**.

I would use **Delta tables** for reliable updates, **Unity Catalog** for governance, and **Jobs** for execution and dependencies. **Parquet** is the underlying file format; Delta adds a **transaction log** that tracks committed table versions and supports consistent updates, `MERGE`, and time travel while the required history and data files are retained.

Delta does not automatically prevent **business-key duplicates** or make a whole multi-table pipeline one transaction, so I would still validate keys and business rules. I would design **validation and replay behaviour** from the start.

I reduce the source to one **latest valid row per business key**, then use **MERGE**: update a matched row only when the incoming version is newer, insert new keys, and apply deletes as agreed.

I validate counts and freshness, then confirm the job can be rerun safely.

For history, **table history** records technical snapshots; **SCD Type 2** keeps effective-dated business changes. A table snapshot does not replace SCD2.

**IDMC** is Informatica’s cloud data-management platform.

For integration, I understand **connections, mappings, mapping tasks, and taskflows**. A **Secure Agent** provides a runtime for supported tasks and connectivity, depending on deployment.

I haven’t used IDMC in production yet, but **incremental loading, validation, scheduling, and recovery** are familiar engineering requirements.

In this reference design, IDMC connects data sources and supports integration and quality services around the lakehouse.

Databricks with Delta Lake provides the Bronze, Silver, and Gold data layers; Kafka and stream processing support real-time paths, while cloud data warehouses and analytics tools serve consumers.

I would confirm which connectors, Secure Agent runtimes, governance services, and transformation steps the team actually uses. This diagram is a design reference, not a claim of my production IDMC experience.

I first select the **latest version of each transaction**. Then I keep **successful transactions** and aggregate by **user and payment date**.

I filter status **after deduplication** because a newer record may cancel an earlier successful transaction.

I test **normal cases and edge cases**, then validate the **schema, uniqueness, and business totals**.

In a test environment, I check the complete flow, including **reruns and recovery** where relevant.

Before release, I review **downstream impact** and prepare a **rollback or recovery plan**. After deployment, I **monitor the first runs and reconcile the output**.

I treat a data pipeline change like a software change: **review the code, test transformations and data rules, and validate the output** before release.

I follow **controlled promotion between environments**, keep configuration separate, and include **reruns and recovery** in the release plan.

After deployment, I **monitor the first runs and reconcile key results**. I would describe the specific CI/CD tools I have used and be clear about which parts were owned by the platform team.