hudi VS sqlfluff

Apache Iceberg is one of the three types of lakehouse, the other two are Apache Hudi and Delta Lake.

Structured, Semi-structured and Unstructured can be stored in one single format, a lakehouse storage format like Delta, Iceberg or Hudi (assuming those don't require low-latency SLAs like subsecond).

Apache Hudi

Try Apache Hudi, it is fully integrated with AWS and offers almost everything that you requested.

the corresponding kafka topics have 30d retention and I intend on having s3 sink connector for long term storage (open to other ideas here too, I noticed theres a hudi connector also)

Apache Hudi

You could have a look at Apache Hudi - especially if you're running your Data Pipelines using Spark or Flink.

Repo : https://github.com/sqlfluff/sqlfluff

I'm surprised sqlfluff hasn't been mentioned yet. Perhaps not a comprehensive list, but it's worked for everything I've thrown at it. There's an ANSI keyword list [0], and then dialect-specific lists for everything from DB2 [1] to Snowflake [2].

[0]: https://github.com/sqlfluff/sqlfluff/blob/main/src/sqlfluff/...

It has tons of annoying quirks, but I couldn't imagine running a DBT project without it: https://github.com/sqlfluff/sqlfluff

Move SQL queries to sql files and read from files (Use sqlfluff to lint the code https://github.com/sqlfluff/sqlfluff)

SQLFluff is a good CLI tool for this and includes support for jinja and dbt. I don't think there's a VSCode plugin for it yet.

This linter can really enforce some best practices https://github.com/sqlfluff/sqlfluff

A list of best practices:

BigQuery SQL and SQLFluff

There's a SQL parser/linter called SQLFluff that my team uses for our CI/CD. I've made a few pull requests to fix the parser for the particular SQL dialect we used, and my college compiler classes definitely helped.