CCNA Data Transformation, Cleansing, Quality Questions

The OPTIMIZE command is specifically built to compact small files in Delta tables. By running it on a schedule or as part of the pipeline, you ensure that Gold tables remain performant for readers. It is an essential maintenance task in any production-grade Delta Lake environment to ensure high-performance query execution.

This configuration uses `APPLY CHANGES` to upsert records by `user_id`, keeping the latest based on `event_timestamp`. SCD Type 1 ensures only the current record is stored, so each `user_id` appears once. It handles duplicates by overwriting with the latest event, which is exactly what is needed.

The APPLY CHANGES API (formerly APPLY CHANGES INTO) supports deduplication by specifying a sequence column (e.g., event timestamp) and a key (event_id). It ensures only the latest record per key is retained, handling late-arriving data by ordering on the sequence column. This directly meets the requirement.

regexp_replace() is a built-in Spark SQL function that can remove all non-digit characters from a string using a regular expression like '[^0-9]'. It operates in a distributed manner, making it efficient for large datasets. This is the simplest and most scalable approach for standardizing phone numbers.

The ALTER TABLE ADD CONSTRAINT command natively integrates with Delta Lake's transaction log to enforce validation at the moment of ingestion. It is highly performant and ensures that no transaction containing a null value in the specified column will be committed, effectively preventing data quality issues at the source.

Schema enforcement at the Silver layer is critical to ensure data consistency. By validating that columns match expected types and structures, you prevent downstream failures in analytical queries and reporting tools, which often lack the robustness to handle unexpected schema changes or malformed data types automatically.

SCD Type 1 with `APPLY CHANGES` uses the specified keys to upsert records, keeping only the latest version based on the sequence column. This ensures each `transaction_id` appears once, with the most recent data. It handles duplicates by overwriting existing rows, which is appropriate for deduplication when the latest record is desired.

Constraints in Delta Lake are hard requirements. If an operation violates these rules, the commit fails, and the transaction is aborted. This guarantees that the table remains in a consistent state and prevents invalid data from entering the storage layer, which is crucial for maintaining reliable audit trails and reports.

This approach is the gold standard for robust ETL. By tagging records with the reason for failure, engineers can easily analyze the patterns of error and fix the source issues. It maintains a clean primary table while simultaneously building a valuable dataset of quality issues for analysis.

Delta Lake supports primary key constraints, which are natively enforced during the transaction. This is the most efficient and robust way to guarantee uniqueness, as the engine rejects any attempt to insert a duplicate value, maintaining the 'single source of truth' integrity required for production-grade analytical and operational data.

The lpad function pads the left side of a string with a specified character until it reaches the desired length. Using lpad(zip_code, 5, '0') ensures every zip code becomes exactly 5 characters, restoring leading zeros that were lost. This is the correct and idiomatic way to fix the issue in PySpark without complex logic.

The OPTIMIZE command compacts small files into larger ones, reducing the number of files and improving query performance. It also supports Z-Ordering for multi-dimensional clustering. This is the standard Delta Lake operation for file compaction. It is efficient and can be scheduled regularly. This command directly addresses the issue of many small files.

Using dropDuplicates on a streaming DataFrame, combined with a watermark, allows Spark to manage the state of seen records efficiently. The watermark specifies the time limit for which duplicates are tracked, ensuring the state doesn't grow indefinitely, which is essential for long-running streaming pipelines consuming data with late arrivals.

Enforcing schema early is the most effective way to identify and fix data quality issues before they become deeply embedded in the refined datasets. By ensuring the Bronze layer is consistent, you simplify the entire transformation pipeline, making it easier to pinpoint the source of any issues when they arise.

The `expect_or_drop` decorator instructs DLT to drop records that violate the expectation and continue processing. The dropped records are not written to the target table, but the pipeline does not fail. Metrics are recorded to track the number of dropped records. This allows the pipeline to maintain data quality while handling invalid data gracefully, which is the intended behavior for this expectation.

Hashing with a salt provides a secure, non-reversible way to mask PII while maintaining referential integrity. This allows analysts to group data by the hash key without seeing the actual sensitive values. It is a highly recommended practice for balancing data utility with strict regulatory and privacy requirements.

APPLY CHANGES INTO inherently supports schema evolution, allowing the target table to adapt to new columns present in the source stream. This is essential for long-running pipelines where source data structures might change over time, ensuring that the target reflects the latest source state without requiring manual intervention.

The regexp_replace function replaces all non-digit characters with an empty string, effectively removing hyphens, parentheses, and spaces. This standardizes the phone numbers to a contiguous digit string, which is the desired outcome. It operates on the column 'phone' and creates a new column 'phone_clean' without modifying the original data, aligning with typical cleansing practices.

DLT automatically generates system tables that store the results of all quality expectations. These tables are readily available for querying via Databricks SQL, making it the fastest and most efficient way to visualize quality trends natively within the platform, without needing extra infrastructure or complex integrations.

Salting adds a random factor to the join key, which breaks up the large, heavy partitions caused by skewed values. By distributing the data across more executors, you ensure that no single task is overwhelmed, which is the most effective way to eliminate skew and improve join performance.

The 'to_timestamp' function in Spark SQL accepts a format string or an array of formats. This allows the engine to attempt parsing against multiple patterns, which is the most robust and performant way to handle format inconsistencies without writing complex, slow-running row-based logic or custom UDFs.

Using expect_or_drop for email nulls removes those rows, while expect for negative age records and retains them, flagging as invalid. This matches the requirement to drop rows with null email and flag negative ages without dropping them.

This correctly uses dot notation to extract nested fields from the STRUCT column and renames them as top-level columns. The WHERE clause filters out records where the nested page_id is NULL, preserving all other columns via the wildcard. Databricks SQL supports this syntax for nested data, making it a valid and efficient solution for flattening and cleansing clickstream events.

By defining two tables, you leverage the declarative nature of DLT to materialize valid and invalid datasets concurrently. Using the inverse logical condition for the audit table ensures that all records are accounted for, meeting both the ingestion requirement and the audit policy without halting the pipeline's progress.

These expectations drop records that violate the conditions while allowing the pipeline update to complete successfully. `expect_or_drop` is the correct decorator when you want to discard bad records and not fail the pipeline. Both conditions are expressed as SQL expressions, which are evaluated per row. The pipeline continues processing, and dropped records are tracked in event logs and metrics.