Interview prep

WHERE filters individual rows before grouping. HAVING filters groups after aggregation, so it can use functions like COUNT or SUM. Example: WHERE status = 'paid' keeps paid orders; HAVING COUNT(*) > 5 keeps customers with more than five of them.

An INNER JOIN returns only rows with a match in both tables. A LEFT JOIN returns every row from the left table, with NULLs where the right table has no match. Use a LEFT JOIN when missing matches matter, for example to find customers with no orders.

All three number rows within a window. ROW_NUMBER always gives unique numbers (1, 2, 3). RANK gives ties the same number and skips the next ones (1, 1, 3). DENSE_RANK gives ties the same number without gaps (1, 1, 2).

Both stack the results of two queries. UNION removes duplicate rows, which costs a sort or hash. UNION ALL keeps everything and is faster, so use it whenever duplicates are impossible or wanted.

Group by the columns that should be unique and keep groups with more than one row: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1. To see or delete the extra copies, use ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) and keep row 1.

A Common Table Expression (WITH name AS (...)) is a named, temporary result you can reference in the rest of the query. CTEs make complex logic readable step by step, can be referenced more than once, and support recursion for hierarchies.

If the subquery returns even one NULL, x NOT IN (...) evaluates to unknown for every row, so nothing comes back. NOT EXISTS doesn't have this problem, which is why it's the safer way to write anti joins.

Rank salaries and pick rank 2: SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM employees) t WHERE r = 2. DENSE_RANK handles ties correctly. Alternatives use MAX with a subquery, or ORDER BY with OFFSET.

COUNT(*) counts all rows. COUNT(column) counts rows where that column isn't NULL. COUNT(DISTINCT column) counts unique non-NULL values. Mixing them up is a common source of wrong metrics.

A list is ordered and changeable, for sequences. A tuple is ordered and immutable, good for fixed records and dictionary keys. A set holds unique values with fast membership checks. A dictionary maps keys to values with fast lookup by key.

Default arguments are created once, when the function is defined, so the same list is shared between calls and keeps growing. Use items=None and create a new list inside the function.

A generator produces values one at a time with yield, instead of building a whole list in memory. It's ideal for large files or streams: you can process millions of rows with constant memory.

merge combines DataFrames on columns, like a SQL join. join is a shortcut that joins on the index. concat stacks DataFrames vertically or side by side without matching keys. Use merge with validate='one_to_one' or 'many_to_one' to catch duplicate keys.

apply runs a Python function row by row, which is slow. Vectorised operations such as df['a'] * df['b'], .str methods or numpy.where work on whole columns at once in optimised code, often 10 to 100 times faster.

First measure it with df.isna().sum(). Then decide per column: drop rows (dropna) when few and random, fill with a sensible value (fillna) such as 0 or the median, or flag it in a new column. Never fill blindly: missing values often carry meaning.

A shallow copy creates a new outer object but shares nested objects, so changing a nested list changes both. A deep copy (copy.deepcopy) duplicates everything. In pandas, df.copy() is deep by default, which avoids the SettingWithCopy confusion.

Fact tables record events or measurements, such as sales or sessions: long, narrow and numeric, with keys to dimensions. Dimension tables describe the context, such as customer, product, date or store: shorter and wide, full of attributes you filter and group by.

The grain is what one row represents, for example 'one row per order line'. Defining it first prevents double counting, keeps every measure consistent and makes joins predictable. Choose the lowest useful grain.

In a star schema each dimension is a single denormalised table joined directly to the fact. In a snowflake schema dimensions are normalised into several related tables. Stars are simpler and faster for BI; snowflakes reduce duplication at the cost of more joins.

Type 1 overwrites the old value, so history is lost; good for corrections. Type 2 adds a new row with valid_from, valid_to and an is_current flag, preserving history so reports show values as they were at the time. dbt snapshots implement Type 2.

A natural key comes from the business or source system, such as an email or product code. A surrogate key is a meaningless identifier created in the warehouse. Surrogates protect you when source IDs change, collide across systems or need history tracking.

Operational systems normalise to avoid update anomalies. Analytics usually denormalises into star schemas because reads vastly outnumber writes, and fewer joins make queries faster and easier for people to understand.

OLTP systems handle many small, fast transactions, such as placing orders, and usually store data row by row. OLAP systems handle large analytical queries across history, and usually store data by column. PostgreSQL is typical OLTP; Snowflake and BigQuery are OLAP.

They turn raw data into clean, tested, documented models that analysts, dashboards and data science can trust. The role sits between data engineering and analysis, and brings software practices such as version control, testing and CI to analytics.

ETL transforms data before loading it into the warehouse. ELT loads raw data first and transforms it inside the warehouse with SQL. Cheap, scalable cloud warehouses made ELT the modern default, and it's how tools like dbt work.

ref() tells dbt how models depend on each other, so it can build them in the right order, draw the lineage graph, and point to the correct schema in each environment, such as development or production.

view: always fresh, no storage, slower to query. table: rebuilt each run, fast to query. incremental: only processes new or changed rows, for large tables. ephemeral: inlined as a CTE into downstream models and never built on its own.

Generic tests in YAML, such as unique, not_null, accepted_values and relationships, cover most needs. Singular tests are SQL files returning failing rows. Source freshness checks catch stale data. Run them all with dbt build, ideally in CI on every pull request.

Late-arriving or updated records can be missed if you only load rows newer than the latest timestamp. Fix it with a lookback window, a unique_key with merge, and a periodic full refresh. Always test the incremental result matches a full build.

A single place where metrics like revenue or active users are defined once, with their logic and dimensions, so every tool and person gets the same answer. It reduces conflicting numbers and makes self-service and AI assistants trustworthy.

Data is stored once in cloud storage, while independent virtual warehouses provide compute. Teams can query the same data without slowing each other down, and you only pay for compute while a warehouse is running.

Snowflake stores tables in small, compressed micro-partitions with metadata such as the min and max of each column. Queries filtering on those columns skip partitions that can't match. Clustering keys improve pruning on very large tables.

Time Travel lets you query or restore data as it was in the past, within the retention period, and UNDROP dropped objects. Zero-copy cloning creates an instant, writable copy of a table, schema or database that shares storage until either copy changes.

Analytical queries usually read a few columns across many rows. Columnar storage reads only those columns and compresses similar values extremely well, so far less data is scanned.

Right-size warehouses and set short auto-suspend, avoid SELECT *, use incremental models, schedule heavy jobs sensibly, set resource monitors, and review the most expensive queries each month in the usage views.

Running it twice gives the same result as running it once, for example by using MERGE on a unique key or overwriting a partition instead of blindly appending. It makes retries and backfills safe.

A calculated column is computed row by row when data refreshes and stored in the model, so it increases its size. A measure is calculated at query time in the current filter context. Prefer measures for aggregations; use columns for attributes you slice by.

Filter context is the set of filters applied to a calculation by visuals, slicers and CALCULATE. Row context exists when iterating over a table, such as in calculated columns or SUMX, giving access to the current row. Row context doesn't filter on its own.

It evaluates an expression in a modified filter context: adding, replacing or removing filters. It's behind almost every advanced measure, such as year-over-year, percentage of total and time intelligence.

Filters flow cleanly from dimensions to facts, DAX stays simple, the VertiPaq engine compresses narrow fact tables well, and users see understandable tables. Flat wide tables and many-to-many relationships cause slow, confusing models.

Import loads data into Power BI's in-memory engine: very fast, but only as fresh as the last refresh. DirectQuery sends queries to the source live: always current, but slower and with some DAX limits. Composite models mix both.

It answers specific questions for a specific audience, puts the key number top-left, shows comparisons with targets or past periods, uses colour to highlight rather than decorate, and says when the data was last refreshed.

When data is skewed or has outliers, such as income or order values. A few huge orders pull the mean up, while the median shows the typical value. Report both when the gap between them is informative.

If there were truly no effect, the p-value is the probability of seeing a result at least as extreme as the one observed. A small p-value means the data would be surprising under 'no effect'. It isn't the probability that the hypothesis is true.

Pick one primary metric up front, calculate the sample size from the minimum effect you care about, randomise properly, run for full weekly cycles, and don't stop early just because results look significant. Check for sample ratio mismatch before trusting results.

Correlation means two things move together; causation means one changes the other. Ice cream sales and sunburn correlate because of sunshine. Randomised experiments are the most reliable way to establish causation.

A trend that appears in every group can reverse when the groups are combined, because group sizes differ. Always check segment-level results, such as by device or country, before concluding from an overall average.

A Type I error is a false positive: declaring an effect that isn't real. A Type II error is a false negative: missing a real effect. The significance level controls Type I errors; statistical power, mainly through sample size, controls Type II.

Keep it to about two minutes: present (your current focus and strengths), past (one or two achievements that prove them, with numbers), and future (why this role is the natural next step). Tailor every part to the job description.

Situation, Task, Action, Result. Briefly set the scene and your responsibility, spend most of the time on what you did, and finish with a measurable result and what you learned. Prepare five or six stories you can adapt to many questions.

Choose a real, professional disagreement. Show that you listened, used data to find common ground, proposed options, and reached an outcome that served the business. End with the result and what you'd keep doing.

Interviewers want ownership. Explain what happened, how you found the error, how quickly you told people, how you fixed it, and the safeguard you added, such as a test or a peer review, so it couldn't happen again.

Start with the decision or outcome, not the method. Use analogies and one clear chart, avoid jargon, and check understanding. Describe a specific time you did this and what changed as a result.

Ask about how success is measured in the first six months, the data stack and its biggest pain points, how the team handles data quality, who the main stakeholders are, and what the team is proudest of. Thoughtful questions show genuine interest.