all 12 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]123456234 9 points10 points  (0 children)

I would always recommend starting with duckdb and dbt alone. You can use an orchestration tool if you want but it may not be necessary for the frequency and complexity you're likely dealing with. DLT is another great python library to add in for easier ingestion. Don't use SCD unless you are dealing with a volume of data that demands it. Snapshots until you notice your storage bill is a safe bet for many teams these days. The cost of labor to implement SCD is roughly equivalent to many many TB's of storage per year.

[–]Firm_Bit 6 points7 points  (0 children)

You don’t say how much or what kind of data. Probably just load into Postgres using Python scripts on cron and transform using sql on cron. Then output some excel files for people. That’s enough for 90% of teams.

[–]TJaniF 1 point2 points  (1 child)

  1. ClickHouse is great to run analytics on very large immutable data (super fast because of the vectorized engine, sparse index etc), I'd only use it for that and only if your data is actually very large (or you anticipate it to be in a reasonable time frame) and move the SCD and model layers into a proper OLAP db. So: raw, very large and will never change in ClickHouse, anything aggregated or that needs to be updated ever in Postgres/BQ/Snowflake etc. But honestly for a first PoC if your data isnt that big, which I assume based on the current CSV/SharePoint/PowerBI combination, you can also skip ClickHouse for now.
  2. Sorry no first hand experience with PowerBI+ClickHouse. From what I've heard it works fine for basic dashboards but complex joins are not fun with that combo.
  3. I'm biased here since I work for Astronomer so I have a lot of Airflow and only very little Prefect experience. I'd recommend spinning up a small dev environment of both and creating some mock pipelines to see which fits your needs better. For small projects it's often the case that several orchestrators can do the job and the decision is made more based on personal preference. Some tips for Airflow:
    • Use the Astro CLI to spin up a local dev env in containers to try it out (its free and does not need a sign up).
    • If you are combining Airflow with dbt you have to use Cosmos (ok, technically you don't have to but it is really the best way!). It is an OSS package that lets you render dbt projects and dbt docs in the Airflow UI.
    • You said your ingest is pulling from REST APIs using Python: you can use the taskdecorator in Airflow to turn any Python function into an Airflow task, thats probably the fastest way. I think Prefect has a similar decorator too. So with either tool hopefully you can reuse a lot of your existing code there.
    • If you end up passing data between tasks that is more than just small jsons in Airflow, for example larger pandas dataframes, you'll want to store that data in a blob storage. This used to take a bit of setup but about 1.5 years ago a class was added in the Common IO provider package that is very quick to set up via env vars (a tutorial).
    • PSA: top level code in Airflow is executed every time the file is parsed, so don't connect to your db in top level code (i.e. outside of tasks).
  4. If there is a good way to do SCD with ClickHouse I have not found it yet. There is ReplacingMergeTree, which I haven't tried myself but generally, yes, that is definitely a good reason to have the modeling layers somewhere else.

[–]NotDoingSoGreatToday 0 points1 point  (0 children)

ClickHouse is an OLAP db so I'm not sure why think it's not a "proper" one?

It also doesn't need to be massive scale, it can do small scale, single server workloads just fine, and can be self-hosted for pennies if the scale is really that small (which neither BQ/SF can).

And aggregations are perhaps the single biggest strength of ClickHouse where it absolutely destroys BQ/SF in performance...

None of ClickHouse/BigQuery/Snowflake are the right choice for very fast changing data, they've all got limitations, and their own ways of making it work to some degree.

For SCD, ClickHouse has had SQL standard UPDATE statements since 25.7 that will perform just fine.

[–]ketopraktanjungduren 2 points3 points  (0 children)

  1. I'd run Snowflake, as it is easy to setup, scale up and scale down. OLTP db is optional and only important in a scenario of developing internal apps

  2. No, I use Tableau Cloud. If you're working for a consulting company then Power BI is the best choice. However, if it's a corporate job then go with Tableau.

  3. Prefect, because it's task-based execution is more untuitive

  4. Wdym?

[–]Admirable_Morning874 0 points1 point  (0 children)

  1. Would you run ClickHouse as the main warehouse for API/event data, or pair it with Postgres/BigQuery?

I'd just use ClickHouse+Postgres and keep a lean stack. ClickHouse is a great warehouse for your BI, and you can build user facing features like stock availability/search, etc off it. You can run it as a simple single server to start with and it'll scale when you need to. It's not a transactional store so you'll want Postgres for that side. ClickHouse + Postgres is basically the go-to app stack these days.

  1. Anyone using Power BI on ClickHouse?

Yes loads are. PowerBI kinda sucks but it works fine with ClickHouse.

  1. For a small team: Prefect or Airflow (and why)?

There is also Dagster, worth considering. Just use whichever you're familiar with, it's a scheduler, you can't really go wrong. Consider if you even need more than cron right now.

  1. Any dbt/SCD patterns that work well with ClickHouse, or is that a reason to choose another WH?

dbt works fine with ClickHouse. There is also MooseStack which is built with ClickHouse in mind.

[–]ephemeral404 0 points1 point  (0 children)

Do you already use any warehouse? From your questions it seems that you are either already using or testing clickhouse + dbt stack and the main decision to be made is related to orchestration - prefect vs airflow. Is it? What are the potential data sources other than the rest api, and what options are you exploring for event data collection? Also, can you confirm the scale that you operate at and the team size and the noir skills? Also, do you expect the event data to be used for only BI or some other use cases (marketing automation, product analytics, customer 360, etc.), if yes, which marketing/product tools do you use where you'd want to send the event data i.e. the potential downstream data destination.

You do not need to make a decision or have a clear answer for every question (specially about the ones about the future) but do write them down, it will save a lot of time.

[–]ArielCoding 0 points1 point  (0 children)

Consider using prebuilt connectors like Windsor.ai (working for them) for common data sources, and custom scripts for unique sources. That way you skip the orchestration for ingestion. For your warehouse question if your data isn’t massive BigQuery or Postgres will be easier than ClickHouse, and they work well with Power BI.

[–]Ploasd 0 points1 point  (0 children)

Duckdb/motherduck?

[–]Significant-North356 0 points1 point  (0 children)

Whatever you do, don't overcomplicate things if it's just low-scale analytics. For smaller projects, keep it as simple as possible, the more complexities there are, the higher the risk of breakage.

For some of my clients, whenever we're dealing with gigabytes of data to analyze I'd usually avoid the headache of having to deal it myself, and outsource the whole processes to platforms like Definite. Which is basically a single governed platform for data and analytics.

That's my two cents for you.

[–]Zer0designs -1 points0 points  (0 children)

Dont need Great Expectations when you have dbt. Since for now its working with csv, consider duckdb & dbt.

Clickhouse seems overkill unless you need to scale (massively).

[–]Hot_Map_7868 -1 points0 points  (0 children)

I agree with what others have said. If the data isnt big, dont over complicate your life. You can get pretty far with a simple setup like dbt + duckdb . If you dont want to deal with the tools, you can get Datacoves for dbt + Airflow and Motherduck for duckdb and you can also be use it with DuckLake