TCO and Lock-in

TCO and Lock-in

Total Cost of Ownership and Vendor Lock-in

Total cost of ownership adds up everything a stack costs over years: compute and storage bills, the engineers who operate it, the analysts' time lost to slow or inconsistent answers, and the cost of leaving. At BookNest's size (Comparing Pricing Models) every cloud bill is smaller than one day of an engineer's time, so operations effort, not price, decides; at a hundred times the size, compute pricing dominates and the models diverge.

Lock-in comes from four places: proprietary storage you cannot read without the vendor, a SQL dialect your models are written in, features with no equivalent elsewhere (zero-copy sharing, time travel), and egress fees on the way out. Open table formats (Iceberg 129 , Delta; Lakehouses, Data Quality and Governance) remove the first, because several engines read the same files. Dialects are the next easiest to soften. SQLGlot, the parser under SQLMesh (github.com/tobymao/sqlglot (https://github.com/tobymao/sqlglot 9,654 ), MIT), rewrites SQL between dialects:

transpile.py: one BookNest query rewritten for other enginesPython

"""transpile.py: a PostgreSQL query rewritten by SQLGlot for four other engines."""
import sqlglot
sql = """SELECT genre, date_trunc('month', order_date) AS month,
       sum(gross_amount) FILTER (WHERE status <> 'cancelled') AS gross,
       count(DISTINCT customer_id) AS buyers
FROM mart.sales WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1, 2"""
print("sqlglot", sqlglot.__version__)
for dialect in ("duckdb", "bigquery", "snowflake", "clickhouse"):
    print(f"--- {dialect}")
    print(sqlglot.transpile(sql, read="postgres", write=dialect, pretty=True)[0])

Output

sqlglot 30.8.0
--- duckdb
...
  order_date >= CURRENT_DATE - INTERVAL '90' DAYS
--- bigquery
SELECT
  genre,
  TIMESTAMP_TRUNC(order_date, MONTH) AS month,
...
--- snowflake
...
  SUM(IFF(status <> 'cancelled', gross_amount, NULL)) AS gross,
...
--- clickhouse
...
  dateTrunc('MONTH', order_date) AS month,

SQLGlot 30.8.0 (installed with SQLMesh; 30.21.0 is current) rewrote FILTER into IFF for Snowflake, date_trunc into TIMESTAMP_TRUNC and dateTrunc, and each engine's interval syntax. Transpiled SQL still needs testing on the target, since a function may return another type there and not every construct exists in every dialect. Transpiling shortens a migration; tests such as Generic and Singular Tests's reconciliation prove it.

MENU