I knew the mechanics but fumbled on spelling out which rows survive the inner join row by row.
Start by clarifying the join keys (State and Category) and the grain of each data source. Then, for each join type, simulate the row-level results and aggregate Sales and MonthlyTarget, highlighting how inner join filters to matching keys while left join retains all Orders rows with nulls for unmatched Targets. Finally, discuss the implications for analysis, such as missing targets and potential double-counting.
Pro tip: Emphasize that in a left join, if multiple Targets rows match an Orders row, Sales can be duplicated, inflating totals; always check for one-to-many relationships before joining. Also, mention that Tableau's physical joins are executed at the data source level, so the row-level results directly impact extract size and performance.
Confirm that Orders contains Sales and Targets contains MonthlyTarget, both keyed on State and Category. Note that Orders may have multiple rows per State-Category (e.g., per order), while Targets likely has one row per State-Category per month.
For inner join, only rows with matching State and Category in both sources are kept. If Orders has multiple rows per key, each matches the single Targets row, resulting in the same number of rows as Orders for matching keys. Sales values are preserved, and MonthlyTarget is repeated for each matching Orders row.
For left join, all Orders rows are kept. For keys present in Targets, MonthlyTarget is populated; for keys not in Targets, MonthlyTarget is null. If Targets has multiple rows per key (e.g., different months), Orders rows are duplicated for each matching Targets row, inflating Sales.
For inner join, SUM(Sales) equals total Sales for matching keys only; SUM(MonthlyTarget) is the sum of targets for matching keys, but if multiple Orders rows per key, MonthlyTarget is counted multiple times. For left join, SUM(Sales) includes all Orders (with duplication if multiple Targets matches); SUM(MonthlyTarget) includes targets for matching keys, ignoring nulls.
Highlight that inner join may exclude orders without targets, while left join may inflate Sales if Targets has multiple rows per key. Recommend aggregating before joining or using relationships to avoid duplication, and always validate row counts and totals.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Start by defining data blending in Tableau and the roles of primary and secondary sources. Then explain the mechanics of how blending occurs on common dimensions, and specifically address what happens to unmatched combinations in terms of null vs zero. Use the given examples (NY-Technology, TX-Furniture, FL-Technology) to illustrate the behavior.
Pro tip: Emphasize that data blending is not a join; it's a relationship at the viz level, and unmatched secondary values appear as null, not zero. Mention that you can use ZN() or IFNULL() to convert nulls to zeros if needed for calculations.
Explain that data blending combines data from multiple sources by linking on common dimensions, with one source as primary and others as secondary. The primary source defines the granularity of the view.
Describe how Tableau performs blending: it queries the primary source, then queries the secondary source for only the relevant dimension values, and aggregates the results. The linking fields must have the same name and data type.
Clarify that when a combination exists in the primary but not in the secondary, the secondary measures appear as null (not zero) in the view. This is because there is no matching row in the secondary source.
Use the given examples: NY-Technology, TX-Furniture, and FL-Technology. If these combinations are absent in the Targets secondary source, their target values will be null. Explain that this is different from a zero target, which would imply a known value of zero.
Mention that nulls can affect calculations (e.g., averages) and visualizations. Suggest using ZN() or IFNULL() to treat nulls as zeros if appropriate, but caution that this changes the meaning.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Five sub-parts in one question, which was a lot.
Start by explaining Tableau's order of operations and how each filter type is applied at different stages. Then, for each filter scenario, describe how the filter interacts with the data model (Orders and Targets) and the resulting view. Finally, highlight the differences in impact, especially when filters are applied to different data sources or at different stages.
Pro tip: Emphasize that context filters create a temporary table that affects subsequent filters, and that data source filters are applied before any other filters, which can significantly change the data available for analysis. Mentioning that measure filters are applied after dimension filters and context filters will show depth.
Outline the sequence: extract filters, data source filters, context filters, dimension filters, measure filters, and table calc filters. This sets the foundation for understanding each filter's impact.
Describe that this filter is applied after context filters but before measure filters. It reduces the data from the Orders source based on Category, affecting all subsequent calculations and the view.
Explain that this filter applies to the Targets data source. Since it's a dimension filter, it's applied at the same stage as the Orders filter, but only affects data from Targets, potentially causing mismatched filtering if not aligned.
Describe that a context filter is applied before dimension filters. It creates a temporary table that filters the data early, affecting all subsequent filters and calculations. This can change the results of other filters, especially if they are on the same or related dimensions.
Explain that a measure filter is applied after dimension filters, so it operates on aggregated data. A data source filter on Targets is applied at the very beginning, before context filters, so it restricts the data from Targets before any other operations.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Start by defining the Orders and Targets tables and their granularities, then walk through two scenarios: one where blending causes double-counting due to many-to-many relationships, and another where joining at different levels of aggregation produces incorrect totals. For each, show the correct method (e.g., pre-aggregation or using a bridge table) and explain why it yields the accurate aggregate.
Pro tip: Always validate aggregates by comparing row counts and sums before and after the operation; this simple check often reveals granularity issues that are otherwise easy to overlook.
Describe the Orders table (e.g., order_id, product_id, sales_amount, order_date) and Targets table (e.g., region, month, target_amount). Clearly state the granularity of each: Orders at order-line level, Targets at region-month level.
Explain that joining Orders to Targets on region and month creates a many-to-many relationship because multiple orders per region-month match multiple target rows (if targets are not unique per region-month). This duplicates order amounts, inflating the aggregate sales.
Describe blending (e.g., in Tableau) where Orders are at daily level and Targets at monthly level. Blending aggregates Orders to the common dimension (month) but may incorrectly sum targets if not handled, or produce nulls for days without targets, leading to incorrect totals.
For each scenario, show the correct method: pre-aggregate Orders to region-month before joining to Targets, or use a left join with aggregation after ensuring uniqueness. Emphasize that the correct aggregate should match the sum of order amounts without duplication.
Conclude that understanding granularity is key; always check cardinality before joining or blending. Recommend performing aggregations at the finest common grain and using tools' granularity settings carefully.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Start by defining blending and joining, then contrast them across the key dimensions: cross-database support, granularity, linking fields, performance, and limitations with LODs and table calculations. Use a decision framework that prioritizes data integration needs and analytical requirements, and illustrate with a concrete example.
Pro tip: Mention that blending is often a last resort when ETL or federated queries aren't possible, and always validate results because blending can silently drop or duplicate data due to linking field mismatches.
Briefly explain that joining combines tables at the row level within a single data source, while blending combines data from multiple sources at the aggregate level using linking fields.
Discuss that blending is necessary when data resides in different databases or systems that cannot be joined directly, whereas joins require data in the same source or a federated connection.
Explain that blending operates on aggregated data, so granularity must be consistent; linking fields must have matching data types and ideally unique values to avoid mismatches.
Compare performance: joins are generally faster as they are processed at the database level, while blending can be slower due to separate queries and local aggregation, especially with large datasets.
Highlight that FIXED LOD expressions and table calculations cannot be used in secondary sources of a blend, and that blending may not support certain complex calculations across sources.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.