Summary
Bilt Rewards software engineer interview that was basically one big SQL and data pipeline problem. The whole thing revolved around a merchant CSV and a transactions table, and they pushed pretty hard on design reasoning, not just syntax.
Questions Asked(4)
Pretty mechanical but I fumbled explaining whether I'd validate the CSV before loading or after.
Suggested Approach
Frame your answer around a reliable, repeatable ETL pattern: validate the CSV schema, load raw data into a staging table with minimal transformation, then apply data quality checks before promoting to production. Emphasize that staging tables act as a safe buffer to isolate bad data from the core transactions table. Mention specific tooling choices (e.g., COPY command in PostgreSQL/Redshift, LOAD DATA INFILE in MySQL, or a Python-based pipeline) and justify your trade-offs.
Inspect & Validate the CSV Schema
Before loading, examine the CSV structure — column names, data types, encoding (UTF-8), and delimiter — and compare it against the expected merchant data contract. Flag any mismatches or missing required fields early to avoid downstream corruption.
Design the Staging Table
Create a staging table that mirrors the CSV columns with permissive data types (e.g., VARCHAR for everything initially) to absorb raw data without rejection. Include metadata columns like `loaded_at` timestamp and `source_file_name` for auditability.
Load Data Using an Efficient Bulk Method
Use a bulk-load mechanism appropriate to your database — PostgreSQL's COPY command, Redshift's COPY from S3, or a tool like pandas/SQLAlchemy for smaller files — to maximize throughput over row-by-row inserts. Handle encoding, quoting, and escape characters explicitly in the load command.
Apply Data Quality Checks
After loading, run validation queries on the staging table to check for nulls in required fields, duplicate merchant IDs, out-of-range values, and referential integrity against the transactions table. Log or quarantine bad rows rather than silently dropping them.
Promote Clean Data & Handle Idempotency
Upsert or insert validated records from staging into the production merchant table using a merge/upsert strategy to handle re-runs safely. Truncate or archive the staging table after a successful load to keep the pipeline idempotent and re-runnable.
Key Points to Mention
The LEFT JOIN plus CASE combo is not hard but I second-guessed myself on whether to put the CASE inside a subquery or inline.
Suggested Approach
Use a LEFT JOIN from transactions to merchants so that unmatched rows are preserved, then apply COALESCE to substitute 'UNKNOWN' for NULL merchant fields. Use a CASE expression to apply the 3x multiplier for restaurant category and 1x for all others, computing base_points with FLOOR(amount).
Choose the Correct Join Type
Select LEFT JOIN (transactions as the left/driving table) to ensure all transactions are returned even when no matching merchant exists. This is the key structural decision that satisfies the 'UNKNOWN' requirement.
Handle Unmatched Merchants with COALESCE
Wrap merchant_code and category in COALESCE(..., 'UNKNOWN') so that NULL values produced by the LEFT JOIN are replaced with the literal string 'UNKNOWN'. Apply this in the SELECT clause.
Compute base_points
Derive base_points as FLOOR(amount), aliasing it clearly. This establishes the integer base before the multiplier is applied.
Apply Conditional Multiplier with CASE
Use a CASE expression on the resolved category (after COALESCE) to multiply base_points by 3 when category = 'restaurant' and by 1 otherwise, aliasing the result as final_points.
Validate and Review Edge Cases
Mentally verify edge cases: transactions with no merchant match return 'UNKNOWN' for both code and category and receive 1x points; transactions with a restaurant merchant correctly receive 3x; FLOOR handles decimal amounts properly.
Key Points to Mention
I talked about network overhead and keeping logic close to the data, which landed okay.
Suggested Approach
Frame your answer around the principle of pushing computation closer to the data, emphasizing that databases are highly optimized engines purpose-built for set-based operations. Walk through concrete trade-offs across performance, scalability, and maintainability, then tie it back to real-world implications for a fintech/rewards platform like Bilt where data volume and accuracy are critical.
Establish the Core Principle
Open by stating that databases are optimized to process data where it lives, using decades of query optimization, indexing, and execution planning. This sets the intellectual foundation before diving into specifics.
Address Performance & Efficiency
Explain that SQL JOINs and CASE expressions leverage the database's query optimizer, indexes, and parallel execution, whereas application code must load raw data into memory first, increasing both latency and memory pressure.
Highlight Network & Data Transfer Costs
Point out that fetching raw, untransformed data means transferring potentially massive result sets over the network to the application layer, which is slow and expensive — especially at Bilt's scale with high transaction volumes.
Discuss Maintainability & Consistency
Note that centralizing transformation logic in SQL ensures a single source of truth — multiple services or consumers get consistent results without duplicating or diverging business logic across codebases.
Acknowledge Trade-offs & Context
Demonstrate maturity by noting when application-side transformation might be preferable — e.g., complex business logic that changes frequently, or when the database is a bottleneck — showing you can reason about trade-offs rather than applying rules dogmatically.
Key Points to Mention
The indexing part was fine.
Suggested Approach
Structure your answer by first addressing indexing strategies tied to query patterns (e.g., user lookups, merchant searches, date-range scans), then pivot to data-cleaning pipelines for normalization and deduplication. Finally, tackle fuzzy/approximate merchant name matching as a distinct sub-problem, proposing concrete algorithmic and architectural solutions.
Identify Query Patterns Before Adding Indexes
Start by asking what the most frequent and latency-sensitive queries are (e.g., 'transactions by user + date range', 'merchant aggregation for rewards'). Indexes should be driven by access patterns, not added speculatively, to avoid write amplification.
Propose Targeted Index Strategies
Recommend composite indexes on high-cardinality query fields such as (user_id, transaction_date) and (merchant_id, category), and consider partial indexes for active records or specific statuses. For text-heavy merchant name lookups, suggest a GIN/trigram index in Postgres or a dedicated search index (Elasticsearch).
Define a Data-Cleaning Pipeline
Outline an ETL/ELT pipeline that standardizes merchant names (lowercasing, stripping punctuation, expanding abbreviations like 'St.' → 'Street'), deduplicates records using deterministic keys, and flags anomalies (null amounts, future dates) for review. Emphasize idempotency so the pipeline can be safely re-run.
Handle Approximate Merchant Name Matching
Propose a layered matching strategy: exact match first, then fuzzy matching using algorithms like Jaro-Winkler or token-sort ratio (via libraries like RapidFuzz), and finally embedding-based semantic similarity for harder cases. Introduce a confidence score threshold to route low-confidence matches to a human review queue.
Discuss Trade-offs and Operationalization
Acknowledge trade-offs such as index maintenance overhead on high-write tables, the latency cost of fuzzy matching at query time vs. pre-computing canonical merchant IDs, and the risk of false positives in matching. Suggest a merchant canonical table as a golden reference that gets enriched over time via feedback loops.
Key Points to Mention
Discussion(4)
Sign in to join the discussion.
The extension is pg_trgm, for what it's worth. Blanking on the name in the moment is annoying but not fatal, and you clearly understood the concept. The more interesting design question is the one you flagged: pre-normalize vs. match at query time. My take is that matching at query time with trigram similarity is fine for a one-off audit or backfill, but it's too slow and too unpredictable to run on every transaction insert in production. The better pattern is to do a normalization pass when merchants are loaded into the staging table, strip punctuation, lowercase everything, collapse common abbreviations like LLC or Inc, and then do the fuzzy match once to produce a canonical merchant_id that gets stored. After that, transaction joins are just exact matches on that ID. The fuzzy step becomes a periodic reconciliation job rather than a hot path. Index-wise, a GIN index on the trgm-tokenized merchant name column makes the similarity search tolerable, and you'd want the usual btree indexes on transaction merchant_id and date for the join and any time-range filters.
Your instinct that leading with schema validation feels cleaner is right, but the raw-load-first approach isn't actually sloppy if you can defend it. The argument for loading raw first is that you preserve the original data exactly as received, which matters a lot when a vendor sends you a malformed file and you need to audit what actually came in versus what your pipeline rejected. At Bilt's scale, merchant data is probably coming from multiple sources with inconsistent formatting, so having that raw layer is genuinely useful. The cleaner framing is to call it a two-stage process on purpose: land everything into a raw staging table with all columns as TEXT and no constraints, then run validation and transformation into a clean staging table before any joins or downstream writes. That way you're not doing cleanup in place on your only copy of the data. Where I've seen people get burned is skipping the raw layer entirely and applying constraints at ingest, then losing records silently when a column is slightly wider than expected or a date format differs. If you frame the raw load as intentional rather than lazy, it's actually a stronger answer.
The network overhead point is solid but yeah, atomicity is the one that probably would've landed best with the interviewer. When you do the join and transformation in SQL inside a single transaction, you get a consistent read of both tables at the same point in time. If you fetch raw transactions and raw merchants separately into application memory and join them there, you can hit a race where a merchant record gets updated between your two fetches. For a rewards platform that's not hypothetical, merchant category changes mid-batch could silently miscalculate points. The other thing I'd add is that pushing the logic into SQL keeps it auditable. A data analyst can run the same query and reproduce the output; if the join logic lives in a service layer, reproducing results means running application code, which is a much higher bar.
The inline CASE is the right call. Wrapping it in a subquery just to compute base_points and then referencing that alias in the outer SELECT is tempting because it feels cleaner, but most interviewers will just see it as unnecessary indirection unless you're reusing that computed column multiple times. For the COALESCE piece, yeah, a separate CASE for the UNKNOWN fallback is the instinct but it's redundant. COALESCE(m.merchant_code, 'UNKNOWN') on the LEFT JOIN result handles it in one shot. The query structure that works cleanly here is something like SELECT t.user_id, COALESCE(m.merchant_code, 'UNKNOWN'), COALESCE(m.category, 'UNKNOWN'), FLOOR(t.amount) AS base_points, FLOOR(t.amount) * CASE WHEN m.category = 'restaurant' THEN 3 ELSE 1 END AS final_points FROM transactions t LEFT JOIN merchants m ON t.merchant_id = m.id. One thing worth noting for the Bilt context specifically: points multipliers are core business logic, so an interviewer there will probably care that you're not hardcoding the 3x in application code where it can drift out of sync.