DP-600 Prepare Data — All Questions With Answers

{
  "table_name": "fact_sales",
  "operation": "optimize",
  "z_order_by": ["CustomerKey", "OrderDateKey"],
  "v_order": true
}

MERGE INTO silver_inventory AS target
USING bronze_inventory_updates AS source
ON target.item_id = source.item_id
WHEN MATCHED THEN
  UPDATE SET target.quantity = source.quantity, target.last_updated = source.update_time
WHEN NOT MATCHED THEN
  INSERT (item_id, quantity, last_updated) VALUES (source.item_id, source.quantity, source.update_time)

let
    Source = Sql.Database("contoso.database.windows.net", "sales"),
    Navigation = Source{[Schema="dbo",Item="Orders"]}[Data],
    FilteredRows = Table.SelectRows(Navigation, each [OrderDate] > #date(2023, 1, 1)),
    RemovedColumns = Table.RemoveColumns(FilteredRows,{"InternalID"})
in
    RemovedColumns

{
  "rule": "reject_if_null",
  "column": "customer_id",
  "action": "drop"
}

{
  "source": "s3://raw-data",
  "format": "csv",
  "compression": "gzip",
  "encoding": "utf-8"
}

{"action": "merge", "left": "sales_df", "right": "products_df", "join_type": "inner", "on": "product_id"}

Error: AnalysisException: [UNRESOLVED_COLUMN.WITH_SUGGESTION] A column or function parameter with name 'cust_id' cannot be resolved. Did you mean one of the following? ['customer_id', 'cust_no']

{"task": "clean_data", "status": "failed", "reason": "java.lang.OutOfMemoryError: Java heap space", "stage": "transformation"}

{
  "source": "RawStorage",
  "destination": "BronzeLakehouse",
  "format": "Parquet",
  "compression": "Snappy",
  "partitioning": "None"
}

Error: 'AnalysisException: cannot resolve 'date_col' given input columns: [id, name, event_timestamp]'

{
  "action": "LOAD",
  "destination": "SalesTable",
  "mode": "overwrite"
}

{
  "settings": {
    "compression": "None",
    "fileFormat": "Parquet",
    "partitioning": "Year/Month/Day"
  }
}

{
  "source": "Raw_Data",
  "format": "JSON",
  "schema": "Inferred",
  "encoding": "UTF-8"
}

{
  "connection": "SQL_DB",
  "query": "SELECT * FROM Sales",
  "delta": "Partitioned_By_Date"
}

{
  "pipeline": "Ingest_Daily",
  "trigger": "Schedule",
  "concurrency": 5,
  "retry": 3
}

{
  "name": "SalesShortcut",
  "source": {
    "adlsGen2": {
      "location": "https://datalake1.dfs.core.windows.net/raw",
      "subpath": "/sales/2023/"
    }
  },
  "target": {
    "lakehouse": "LKH_Finance",
    "path": "Files/ExternalSales"
  }
}

%%pyspark
df = spark.read.format("parquet").load("Files/SalesData")
df.write.format("delta").saveAsTable("SilverSales")

%%pyspark
# Attempting to write to a Delta table
df.write.format("delta").mode("append").partitionBy("TransactionDate").saveAsTable("Sales_Partitioned")

{
  "policy": "DENY",
  "principal": "ServiceAccount_A",
  "resource": "/lakehouse/default/Tables/Sales",
  "actions": ["READ"]
}