Ingestr assets can skip table creation on zero rows, breaking downstream SQL assets run (observed in chess template) + need input /suggestions

[aryahassibi]

Summary

When an ingestr source returns zero rows for the run interval, the destination table is not created. Any downstream SQL asset that references it then fails with Catalog Error: Table with name ... does not exist.

I observed this when inspecting the chess tutorial, in the chess template. There chess_playground.games is missing when the games source for both players return zero rows, so chess_playground.player_summary fails. This likely applies to other tutorials/examples that chain ingestr -> SQL assets too (also because we have a lot of chess.com examples)

In the chess example, this pipeline run fails when all players have zero games for the interval. If at least one player has games, the table is created and the downstream succeeds.

Default interval is "yesterday".if no games occurred in that window for all players, a fail occurs.

Following up on a previous issue: even if we pick very famous players, the example pipelines still CAN technically fail if they did not play within the default interval (yesterday).

Suggestions

• Modify the asset so that it creates empty tables for profiles/games before downstream SQL runs.
• Maybe, have ingestr create the destination table schema even when zero rows are extracted. (possibly something that we don't want)
• Alternatively, add an option to "materialize empty table" for sources that can return zero rows
• Or in tutorials/examples, consider a short note about the default interval and the empty-table behavior.
• Specify an interval when running bruin.

Question

• What do you suggest?
• Now should I find all such problems in the code base and modify them based on the decision we make here?

Reproduce

bruin init chess

In .bruin.yml, set:

connections:
  chess:
    - name: chess-default
      players:
        - awryaw
        - albertojgomez

Then Run:

cd ./bruin
bruin run ./chess/pipeline.yml

Expected

chess_playground.games should exist even if it is empty, or the downstream SQL asset should still run and return zero stats.

Actual

chess_playground.player_summary fails because chess_playground.games does not exist. if none of the players have played during the interval (aka since yesterday).

Error:

Internal: Catalog Error: Table with name games does not exist!
LINE 17:     COUNT(*) AS total_games,

[albertobruin]

The problem with this is that if there is not data in the source we cannot guess/infer the schema of the table, unless it's columns are defined in the asset. Maybe ingestr assets should try to create the table if no data is at the source, as long as schema is defined in columns section? This might be quite a bit of work though and not sure if the investment is worth it

[karakanb]

We have had an internal discussion about this and decided to close this issue as there doesn't seem to be something to fix when the schema is unknown, happy to look into it if there are any other concrete examples.