CCNA Data Modelling Questions

Enabling 'spark.sql.sources.partitionOverwriteMode=dynamic' allows you to replace only the partitions that exist in the write data. This is the correct, atomic, and efficient way to replace a specific date partition without affecting other partitions, making it the standard best practice for partition-level updates in Databricks.

The Gold layer provides final, aggregated datasets that are ready for BI and reporting. By presenting data in this format, it masks the underlying complexity of the raw and silver data, providing a performant and understandable view of the business to end-users and non-technical stakeholders.

The DELETE command updates the Delta log to exclude the records from future reads. Running VACUUM removes the orphaned files that contain the deleted data. This combination ensures the records are both logically and physically removed from the data lake, fulfilling the legal requirements of GDPR compliance.

Delta Lake records every commit in the transaction log, so time travel can reconstruct the table as of a specific version or timestamp, directly satisfying the requirement to query prior states. Corrections create new versions rather than destroying old ones, so auditors can retrieve the exact state at any retained point in time using the same table.

The medallion architecture organizes data into Bronze, Silver, and Gold to represent increasing levels of refinement. This structured approach allows teams to manage data quality incrementally, ensures that business logic is applied consistently, and provides an immutable raw history that can be reprocessed whenever requirements change.

A star schema places a central fact table joined to denormalized dimensions, and including hierarchical attributes such as region, country, and city within the geography dimension lets analysts drill down without extra joins. Conformed dimensions shared across facts like sales and promotions allow consistent analysis by product, store, and promotion independently while keeping queries simple.

Z-Ordering colocates related data in the same set of files, so queries filtering on account_id can skip many files. It is ideal when the table is already partitioned by txn_date and you need to optimize a secondary column without repartitioning. Running OPTIMIZE ... Z-ORDER BY account_id will reorganize data within each partition, improving data skipping for account_id filters.

Z-ORDER BY product_id co-locates related product_id values within each partition, enabling data skipping when filtering on product_id. This reduces the amount of data scanned during joins and filters, directly improving query performance for the described workload. It is a common optimization for high-cardinality columns used in filters and joins.

Z-ORDER BY account_id will co-locate similar account_id values within each partition, enabling data skipping for filters on account_id. Since the table is already partitioned by transaction_date, this adds efficient skipping for account_id without changing the partitioning scheme. This directly addresses the slow queries filtering on account_id.

A Type 2 dimension inserts a new versioned row whenever the category changes and closes the prior row with an end date, preserving history. Fact rows can then join on the surrogate key or on the effective-date range valid at the transaction timestamp, so each sale continues to show the correct historical category without duplicating the whole catalog for unchanged attributes.

Z-Ordering on join keys physically co-locates related data, which is essential for join efficiency. This reduces the need for extensive shuffling during join operations, as the compute engine can perform more efficient local joins. This is a critical optimization for large-scale analytical tables within the Lakehouse architecture.

The MERGE operation is ideal for late-arriving data because it can identify existing records and update them with the corrected information. By using event time as a matching key, the pipeline can ensure that even if data arrives out of order, the state of the Silver table remains accurate.

A dedicated dimension table for delay reasons allows efficient slicing and grouping. It ensures consistency and supports adding new reasons without altering the fact table. This is a standard star schema approach. In Databricks, it can be joined efficiently and optimized with Z-ORDER.

Gold is the analytical layer. It transforms Silver data into business-ready aggregates, KPIs, and reports. By focusing on business logic here, the engineering team ensures that the data is prepared specifically for downstream consumption, reducing the computational load on end-user tools while ensuring consistent metrics across the organization.

Partitioning by product_category, which has low cardinality, avoids the small file problem and enables partition pruning for filters on that column. Z-ORDER BY transaction_timestamp clusters data within each partition to accelerate sorting and range queries on that column. This combination optimally supports both filtering and sorting.

MERGE INTO is essential for SCD Type 2 because it allows updating existing records (e.g., setting end dates) and inserting new versions in a single atomic operation. By joining on the business key and comparing effective dates, you can expire old rows and add new ones. This ensures historical accuracy and ACID compliance in Delta Lake.

Star schemas use denormalized dimensions, which reduces the number of joins needed for analytical queries. This simplicity leads to faster query performance, especially in a Lakehouse where join operations can be expensive. Databricks' Photon engine and Delta Lake optimizations work well with star schemas. This is a key reason to choose a star schema.

The MERGE operation allows for efficient upserts by matching on visit_id. It updates existing records and inserts new ones in a single ACID transaction, minimizing data rewriting. This is the standard approach for handling late-arriving data in Delta Lake and ensures atomicity and consistency.

MERGE INTO allows you to update existing records when the incoming data has a newer timestamp and insert new records when they don't exist. This ensures that the Silver table always reflects the latest version. It is the standard way to upsert data in Delta Lake while maintaining ACID compliance.

Because the table is partitioned by region and date, failing to include these in the WHERE clause forces the engine to scan every partition. Z-Ordering only clusters data within individual partitions. If the engine doesn't prune the partitions first, the Z-Ordering benefits are largely ignored during the scan.

Partitioning by event_date enables partition pruning so queries filtering on a date range skip unrelated files, while Z-ORDER on user_id co-locates related user rows within each partition's files. Together they reduce bytes scanned for both the date filter and the user aggregation, and Z-ORDER statistics let Delta skip files whose user_id min/max ranges fall outside the predicate.

Type 2 SCD preserves history by creating new rows for changes, with effective start and end dates. This allows point-in-time queries by filtering on the desired date. In Databricks, this can be implemented using Delta Lake's merge operations and time travel. It is the standard technique for temporal analysis in data warehousing.