← Freddie Mac Interview Insights
This one took me a while to untangle mentally.
Structure the answer as a single CTE-based SQL statement that applies each cleaning transformation in a logical sequence, then deduplicates using a window function. Explain the order of operations and how each function handles edge cases, emphasizing that the final SELECT returns the cleaned, deduplicated rows.
Pro tip: Mention that in production you would materialize the cleaned data into a new table or view rather than running a massive single statement repeatedly, and highlight the importance of testing each transformation on a sample before full deployment.
Use LOWER(TRIM(email)) to normalize, then validate with a simple pattern like email LIKE '%_@_%.__%' and flag or exclude invalid ones.
Collapse multiple spaces in names using REGEXP_REPLACE(name, '\s+', ' '), and map country strings to ISO codes via a CASE expression or lookup join.
Use COALESCE with multiple TO_TIMESTAMP or TRY_CAST attempts to handle various date formats, falling back to NULL if unparseable.
Apply ROW_NUMBER() OVER (PARTITION BY normalized_email ORDER BY date DESC, id ASC) and filter to row_number = 1 to keep the most recent record.
Combine all transformations in a single CTE chain and output the cleaned, deduplicated rows.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Pretty standard mapping exercise but the updated_at column is a small gotcha.
Start by clarifying the source staging schema and target customers table, then present a mapping table with source column, target column, data type, transformation rule, and nullability. Walk through how updated_at is populated (e.g., via ETL timestamp or trigger) and provide a concrete before/after row example to illustrate the transformation.
Pro tip: Emphasize data quality and auditability: mention how you handle nulls, deduplication, and late-arriving data, and show that updated_at is consistently set to the load time or source change time to support downstream analytics and compliance.
Review the staging table columns and the customers table DDL to identify data types, constraints, and nullability. Confirm any business keys and expected transformations.
Create a mapping table listing each source column, target column, data type conversion, transformation logic (e.g., trim, cast, default), and nullability. Include rules for handling missing or invalid data.
Decide how updated_at is set: use the ETL load timestamp, a source system change timestamp, or a database trigger. Explain how you ensure it reflects the latest change and supports incremental loads.
Show a sample row from staging with raw values and the resulting row in customers after applying transformations, highlighting data type changes and updated_at value.
Discuss handling of nulls, duplicates, and late-arriving records, and how you would validate the load (e.g., row counts, checksums).
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Start by defining a normalized key using LOWER(TRIM(email)) and use it to find duplicates within the customers table via GROUP BY/HAVING or window functions. Then apply the same normalization to staging records and perform an anti-join or inner join to identify collisions, returning both customer_id and staging_id. Emphasize data quality, edge cases, and business impact.
Pro tip: Mention that you would first profile the data to understand email formats and null rates, and propose a deduplication strategy that includes a survivorship rule (e.g., keep most recent) to avoid arbitrary record loss.
Create a consistent key by applying LOWER(TRIM(email)) to both customers and staging tables. Consider handling NULLs and empty strings explicitly.
Use GROUP BY normalized_email HAVING COUNT(*) > 1 to list duplicate groups, or use window functions to assign row numbers and filter for duplicates. Return all customer identifiers in each group.
Join staging to customers on the normalized key to find staging records that match existing customers. Use an inner join to show both sides' identifiers (customer_id and staging_id).
Check for NULLs, empty strings, and non-standard formats. Validate results by sampling and comparing counts. Consider performance implications of functions on large tables.
Propose a deduplication strategy (e.g., survivorship rules) and a process to prevent future duplicates, such as adding a unique constraint on the normalized key.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
I knew DDL vs DML cold so that wasn't the issue.
First classify each operation as DDL or DML, then write the exact SQL with attention to syntax and constraints. For the foreign key, justify the deletion rule based on business logic and data integrity. For indexes, explain how each supports the specific queries (duplicate-finding and upsert).
Pro tip: Mention that adding a unique constraint on an expression requires a unique index, and that for upserts, a unique index on the conflict target is essential. Also, consider the performance impact of foreign key deletion rules.
Determine whether each operation is DDL (Data Definition Language) or DML (Data Manipulation Language). Adding constraints and indexes are DDL, while upsert (INSERT ... ON CONFLICT) is DML.
Use CREATE UNIQUE INDEX on lower(trim(email)) to enforce uniqueness on the normalized email. Alternatively, add a unique constraint using a computed column if supported.
Add a foreign key from orders to customers with a deletion rule. Justify ON DELETE CASCADE or ON DELETE RESTRICT based on whether orders should be deleted when a customer is removed.
For duplicate-finding, create an index on the columns used in GROUP BY or the expression. For upsert, create a unique index on the conflict target columns.
Discuss the performance implications of indexes and foreign key constraints, and how they support data integrity and query efficiency.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Self-joins always look cleaner than they feel to write.
Write a self-join on the orders table where the same customer has orders one day apart, using a date function to compare dates. To avoid duplicate pairs, enforce that the first order's date is earlier than the second's. For performance, ensure an index on (customer_id, order_date) and consider filtering by date range if possible.
Pro tip: Mention that using an inequality join (e.g., o1.order_date < o2.order_date) prevents duplicate pairs and that adding a computed column or using a function-based index can speed up date arithmetic. Also, consider that on very large tables, a self-join can be expensive, so partitioning or clustering by customer_id can help.
Clarify that we need pairs of orders from the same customer where one order date is exactly one day after the other, and we must avoid duplicate pairs (e.g., (A,B) and (B,A)).
Join the orders table to itself on customer_id, and filter where the second order's date equals the first order's date plus one day. Use an inequality (o1.order_date < o2.order_date) to ensure each pair appears only once.
Select customer_id, o1.order_id, o2.order_id, o1.order_date, o2.order_date. Ensure column names are clear and aliases are used to avoid ambiguity.
Suggest indexing on (customer_id, order_date) to speed up the join. If the table is huge, consider filtering by a date range or using partitioning. Avoid functions on the join columns if possible.
Mention testing with sample data to ensure correctness, especially edge cases like multiple orders on the same day or consecutive days. Check that no duplicate pairs are returned.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Start by outlining the INSERT ... ON CONFLICT DO UPDATE statement, using GREATEST with COALESCE to pick the longest non-null name and COALESCE to conditionally update country_code. Then explain how PostgreSQL's atomic upsert and row-level locking prevent race conditions, and discuss trade-offs like deadlocks and performance under concurrency.
Pro tip: Mention that you would test the upsert under concurrent load using pgbench or a custom script to validate correctness and performance, and consider using advisory locks or partitioning if contention becomes a bottleneck.
Clarify the conflict target (likely customer_id), the name selection logic (longest non-null normalized name), and the conditional update for country_code.
Write the INSERT ... ON CONFLICT DO UPDATE with expressions: GREATEST(COALESCE(customers.name, ''), COALESCE(excluded.name, '')) for name, and COALESCE(customers.country_code, excluded.country_code) for country_code, and set updated_at = now().
Describe how PostgreSQL's ON CONFLICT ensures atomicity: concurrent inserts/updates on the same key are serialized via row-level locks, preventing lost updates.
Address potential deadlocks when multiple rows are updated in different orders, and suggest ordering or retry logic. Mention performance implications and alternatives like MERGE or application-level locking.
Propose testing under concurrent load and monitoring for deadlocks or contention, with possible mitigations like partitioning or batching.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.