Engineering tricks

Snippets that
saved me hours.

Small, production-tested patterns. Copy them, adapt them, ship them.

SQL · Oracle / PostgreSQL

Find what's actually slow

Before tuning anything, rank statements by total elapsed time — not by execution count. The worst query is rarely the most frequent one.

SQLOracle

-- Top 10 statements by total elapsed time
SELECT sql_id,
       ROUND(elapsed_time/1e6, 2) AS elapsed_s,
       executions,
       ROUND(elapsed_time/NULLIF(executions,0)/1e6, 4) AS per_exec_s
FROM   v$sql
WHERE  parsing_schema_name NOT IN ('SYS', 'SYSTEM')
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

PySpark · Databricks

Safe casting that doesn't lose rows

Silent nulls from bad casts are the most common silver-layer data loss. Cast into a new column, then quarantine the failures instead of dropping them.

PythonPySpark

from pyspark.sql import functions as F

def safe_cast(df, col, to_type):
    """Cast, keeping a flag for rows that failed."""
    casted = F.col(col).cast(to_type)
    return (df
        .withColumn(f"{col}_cast", casted)
        .withColumn(f"{col}_bad",
            F.col(col).isNotNull() & casted.isNull()))

SQL · Databricks

Latest snapshot without a self-join

When a source drops a full CSV snapshot every day, QUALIFY beats the classic group-by-then-join by a wide margin — and it reads far better.

SQLDatabricks

-- One row per player: the most recent snapshot
SELECT *
FROM   bronze.player_snapshots
QUALIFY ROW_NUMBER() OVER (
          PARTITION BY player_id
          ORDER BY snapshot_date DESC, ingested_at DESC
        ) = 1;

Python · Lakeflow

Expectations that warn before they drop

Start every quality rule as a warning. Promote it to a drop only once you've seen a week of real volumes — otherwise your first bad day silently deletes production data.

PythonLakeflow

@dlt.table(name="silver_registration")
@dlt.expect("valid_season", "season_year >= 2015")
@dlt.expect_or_drop("has_player_id", "player_id IS NOT NULL")
def silver_registration():
    return (dlt.read_stream("bronze_registration")
              .transform(clean_text)
              .transform(dedupe_latest))

Python · Pandas

Read CSVs with mixed encodings

Legacy exports arrive as a mix of UTF-8 and Latin-1 in the same folder. Fail loudly per file rather than corrupting the whole batch.

PythonIngestion

import pandas as pd

def read_any(path, encodings=("utf-8", "utf-8-sig", "latin-1")):
    for enc in encodings:
        try:
            return pd.read_csv(path, encoding=enc), enc
        except UnicodeDecodeError:
            continue
    raise ValueError(f"Could not decode {path}")

Azure · CLI

Grant storage access without keys

Managed identity plus a scoped role assignment removes the account key from your config entirely — and from the incident report when the repo leaks.

BashAzure CLI

# Assign the workspace identity read access to one container
az role assignment create \
  --assignee "$WORKSPACE_PRINCIPAL_ID" \
  --role "Storage Blob Data Reader" \
  --scope "/subscriptions/$SUB/resourceGroups/$RG/providers/\
Microsoft.Storage/storageAccounts/$ACCT/blobServices/default/\
containers/$CONTAINER"

No snippets match that filter.