← Freddie Mac Interview Insights
Start by profiling the data to quantify issues, then execute a prioritized cleaning plan: deduplicate customers, remove duplicate orders, standardize types and time zones, validate referential integrity, and flag impossible values. For each step, justify tie-breakers and tolerances with business logic, and provide SQL/Python code to demonstrate implementation.
Pro tip: Always create a backup or snapshot before cleaning, and log every transformation with row counts to enable auditing and rollback. This shows production maturity and is crucial in regulated environments like Freddie Mac.
Run exploratory queries to measure duplicates, nulls, type mismatches, and referential gaps. This informs the cleaning strategy and prioritization.
Use window functions to rank records per email, applying tie-breakers like most recent update or most complete record. Justify tie-breakers based on business rules.
Identify duplicates using a natural key (e.g., order_id) and time/amount tolerance. Keep the earliest or most reliable record, and document the tolerance rationale.
Convert columns to correct types (e.g., dates, numerics) and normalize timestamps to UTC. Use SQL CAST or Python pandas functions.
Check foreign key relationships and flag negative quantities or other impossible values. Provide queries to identify and optionally correct them.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Start by outlining the structure of a formal data mapping specification, including source-to-target mappings, data types, transformations, and validation rules. Then walk through each CSV column, explaining how it maps to the Orders or OrderItems tables, and describe any necessary transformations such as splitting, type conversion, or key generation. Emphasize data quality checks and referential integrity to ensure a robust onboarding process.
Pro tip: Highlight the importance of handling edge cases like missing values, duplicate order IDs, and currency conversion (if applicable), and mention that you would document assumptions and get stakeholder sign-off to avoid downstream issues.
Outline the sections of the spec: source fields, target tables/columns, data types, transformations, validation rules, and error handling. This sets a clear framework for the mapping.
For each CSV column, identify the corresponding target column in Orders or OrderItems. Explain how order-level fields (order ID, timestamp, email) go to Orders, and line-item fields (SKU, quantity, price) go to OrderItems, with appropriate foreign key relationships.
Detail any required transformations: converting timestamp to a standard format, splitting full name into first/last (if applicable), converting price from cents to dollars (if the target expects dollars), and generating surrogate keys if needed.
List validation rules such as non-null constraints, data type checks, referential integrity (e.g., order ID exists in Orders before inserting into OrderItems), and business rules (e.g., quantity > 0).
Describe how to handle errors: reject invalid rows, log issues, or apply default values. Mention handling duplicates, missing values, and potential currency conversion if prices are in cents but target expects another unit.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Start by clarifying the business rules for each deduplication scenario, such as which record to keep and how to handle ties. Then write SQL queries using window functions (ROW_NUMBER, RANK) to identify duplicates and select survivors deterministically. For the order clustering, use a self-join or window function with time and amount conditions to group likely duplicates.
Pro tip: Always consider data quality and edge cases: for example, what if multiple rows tie on the deterministic criteria? Use additional tie-breakers like primary key to ensure a single survivor. Also, mention that these queries should be tested on sample data and validated with business stakeholders.
Ask clarifying questions about what constitutes a duplicate, which record to keep (e.g., most recent, highest ID), and how to handle ties. Define reason codes for dropped rows.
Use ROW_NUMBER() partitioned by email, ordered by a deterministic criterion (e.g., customer_id). Select rows where row_number = 1 as 'keep', and others as 'drop' with a reason code like 'duplicate_email'.
Use a self-join or window function to find orders for the same customer where order dates are within 30 seconds and total amounts match to two decimal places. Assign a cluster ID or flag duplicates.
Use ROW_NUMBER() partitioned by SKU, ordered by a deterministic criterion (e.g., product_id or last_updated). Select the survivor (row_number = 1) and optionally flag others as duplicates.
Check for performance considerations (indexes on email, SKU, customer_id, order_date) and ensure queries are deterministic and handle ties. Discuss potential edge cases and validation.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Pretty conceptual compared to the rest of the questions.
Start by defining DDL and DML in the context of a data warehouse, emphasizing how DDL shapes the schema and enforces integrity while DML manipulates data. Then discuss transactional and locking implications, contrasting the heavy locks and auto-commit behavior of DDL with the finer-grained, transaction-controlled locks of DML. Finally, provide concrete examples: a DDL statement like adding a foreign key constraint and a DML statement like an INSERT or UPDATE, explaining their practical impact on the warehouse.
Pro tip: Mention that in many warehouses, DDL operations like adding constraints can be expensive and may require downtime, so it's common to enforce integrity during ETL rather than via database constraints. Also, highlight that DML operations should be batched to minimize lock contention and transaction log growth.
Briefly explain that DDL defines and modifies the structure of database objects (tables, constraints, indexes) while DML manages the data within those objects (insert, update, delete). Relate this to the warehouse schema, noting that DDL sets up the star schema and DML populates it.
Explain that DDL statements are often auto-committed and cannot be rolled back in many databases, whereas DML statements are transactional and can be rolled back. In a warehouse, this means schema changes are permanent and require careful planning, while data loads can be managed in transactions.
Describe how DDL typically acquires exclusive locks on objects, blocking concurrent DML and queries, while DML acquires row-level or page-level locks, allowing more concurrency. In a warehouse, this affects ETL jobs and query performance during schema changes.
Give a specific DDL statement, such as ALTER TABLE fact_loan ADD CONSTRAINT fk_borrower FOREIGN KEY (borrower_id) REFERENCES dim_borrower(borrower_id);, and explain how it enforces referential integrity in the warehouse.
Give a specific DML statement, such as INSERT INTO fact_loan (loan_id, borrower_id, amount) VALUES (123, 456, 100000);, and explain how it adds data to the warehouse, noting transaction and locking considerations.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Standard left join on referrer_id to customer_id.
Clarify the table schema and the referrer relationship, then write a LEFT JOIN from the Customers table to itself on the referrer ID. Ensure the join condition is correct and that customers without a referrer still appear once with NULL for the referrer's email.
Pro tip: Mention that you would verify the query handles edge cases like self-referrals or circular references, and that you'd test with sample data to confirm no duplicates or missing rows.
Identify the primary key (customer ID) and the foreign key that points to the referrer (e.g., referrer_id). Confirm that the referrer is also a customer in the same table.
Use a LEFT JOIN to ensure all customers appear, even those without a referrer. The join condition should match the customer's referrer_id to the referrer's customer ID.
Select the customer's ID and email, and the referrer's email from the joined table. Use table aliases (e.g., c and r) to distinguish the two instances of the Customers table.
Ensure that customers with no referrer return NULL for the referrer's email. Check that the query does not produce duplicate rows for any customer.
Run the query on sample data to confirm it returns the expected results, including edge cases like customers without referrers and potential self-referrals.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Start by clarifying the data model and constraints (e.g., unique SKU, primary key) and the desired upsert semantics. Then propose a concrete SQL implementation using INSERT ... ON CONFLICT (or MERGE) that deduplicates within the batch and handles conflicts. Finally, address concurrency with transaction isolation levels, locking strategies, and idempotency to ensure correctness under concurrent writes.
Pro tip: Mention that you would test the upsert logic with concurrent transactions using a tool like pgbench or a simple script, and that you'd monitor for deadlocks and retry with exponential backoff. This shows you think about production reliability, not just the happy path.
Ask about the database system (e.g., PostgreSQL, SQL Server), the table schema, unique constraints on SKU, and whether the operation should be insert-only, update-only, or upsert. Confirm the definition of 'duplicate SKUs' (e.g., within the same batch or across concurrent transactions).
Write a single SQL statement that inserts new SKUs and updates existing ones, using the database's native upsert syntax (e.g., INSERT ... ON CONFLICT (sku) DO UPDATE). Ensure that if multiple rows with the same SKU are in the input, they are deduplicated first (e.g., using DISTINCT ON or a subquery that picks the latest).
Explain how the upsert behaves under concurrent writes: use row-level locking (e.g., ON CONFLICT DO UPDATE takes a lock on the conflicting row) and choose an appropriate isolation level (e.g., READ COMMITTED). Discuss potential race conditions and how to avoid lost updates.
Make the operation idempotent so that retries are safe. Describe how to handle unique violation errors and deadlocks with retry logic. Mention the importance of using transactions to group multiple upserts if needed.
Propose testing the solution with concurrent sessions to verify correctness and performance. Suggest monitoring for lock contention and tuning batch sizes if necessary.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
OrderItems is the interesting one since neither order_id nor line_no alone is unique, you need the composite.
Start by examining the sample data to identify candidate keys and relationships, then propose primary keys (natural or surrogate) for each table, foreign keys to enforce referential integrity, and unique constraints on alternate keys. Justify composite keys where a single column is insufficient, and discuss trade-offs between surrogate and natural keys for Customers and Orders, considering performance, stability, and business meaning.
Pro tip: Emphasize that surrogate keys are often preferred in data warehousing for stability and performance, but natural keys can be valuable for integration and business logic; align your recommendation with Freddie Mac's data governance and scalability needs.
Examine each table's columns and data to determine which columns or combinations uniquely identify rows. Note any natural keys (e.g., email, order number) and potential composite keys.
Choose a primary key for each table, either natural or surrogate. For composite keys, explain why a single column is insufficient (e.g., order line items).
Identify relationships between tables and specify foreign keys to enforce referential integrity. Add unique constraints on alternate keys (e.g., email) to prevent duplicates.
Explain the rationale for any composite keys, and compare surrogate vs. natural keys for Customers and Orders, covering pros and cons like stability, performance, and business meaning.
Conclude with a recommendation that balances technical best practices with Freddie Mac's data needs, such as scalability, integration, and governance.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Give a calibrated rating (e.g., 8/10) that reflects strong proficiency without claiming perfection, then justify it with two concrete examples: one using window functions for deduplication and one implementing a concurrency-safe upsert. For each example, briefly describe the query logic, how you tested it (e.g., unit tests, edge cases, concurrency simulations), and how you optimized it (e.g., indexing, partitioning, query plan analysis).
Pro tip: Avoid claiming a 10; instead, show self-awareness by mentioning areas for growth (e.g., advanced query tuning for distributed systems) and how you stay current. This demonstrates humility and a learning mindset, which is highly valued in data science roles.
Give a specific number (e.g., 8/10) and briefly explain why—highlighting strengths in complex queries, optimization, and testing, while acknowledging any gaps.
Describe a scenario where you used ROW_NUMBER() or RANK() to deduplicate records, explain the query structure, and mention how you tested it with duplicate data and optimized it using indexes or partitioning.
Explain how you implemented an upsert (e.g., using INSERT ... ON CONFLICT or MERGE) that handles concurrent writes safely, and discuss testing with concurrent transactions and optimizing with proper locking or isolation levels.
Summarize your approach to testing SQL queries: unit tests for logic, edge cases (nulls, duplicates), and concurrency tests using tools like pgbench or custom scripts.
Highlight specific optimizations: analyzing query plans, adding indexes, partitioning large tables, and rewriting subqueries as joins or CTEs for performance.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.